OLE-DB Question (KeyColumn)

Hello VB6, ADO on the Informix OLE-DB provider, with client side cursor and batch updates. I have a table with 3 columns: PrimCol, col2 and col3. PrimCol is a primary key. I create a Recordset from "select * from table1" The problem is the KEYCOLUMN property of the primary key field is False. Therefore, when I do an UpdateBatch, it generates the following SQL: Update Table1 set col2 = ? where PrimCol = ? and col2 = ? and col3 = ? By the time it generates the SQL, col3 has changed, and the update fails. I really want it to generate: Update Table1 set col2 = ? where PrimCol = ? I have set the Recordset Property "Update Criteria" to AdCriteriaKey, but to no effect as it does not recognize the primary key. Any ideas, anybody Thanks -- Bashar Chalabi CTL, London

Bashar, Are you sure you do not want to do it in transaction? That would prevent col3 from changing from under you (and protect you from overwriting other user's changes to it as well). Leopold Bashar Chalabi <bashar@ctl.com> wrote in message news:tWg34.127$uL.1239@news.colt.net... > Hello > > VB6, ADO on the Informix OLE-DB provider, with client side cursor and batch > updates. > > I have a table with 3 columns: PrimCol, col2 and col3. PrimCol is a primary > key. > > I create a Recordset from "select * from table1" > > The problem is the KEYCOLUMN property of the primary key field is False. > Therefore, when I do an UpdateBatch, it generates the following SQL: > > Update Table1 set col2 = ? where PrimCol = ? and col2 = ? and col3 = ?> > By the time it generates the SQL, col3 has changed, and the update fails. > > I really want it to generate: > > Update Table1 set col2 = ? where PrimCol = ?> > I have set the Recordset Property "Update Criteria" to AdCriteriaKey, but to > no effect as it does not recognize the primary key. > > Any ideas, anybody > > Thanks > > -- > Bashar Chalabi > CTL, London > >

> Are you sure you do not want to do it in transaction? That would prevent > col3 from changing from under you (and protect you from overwriting other > user's changes to it as well). That's the whole point. I need the original value of Col3 in my Recordset, but I must be able to let it change in the database (my process takes a few hours to run before it updates the database. That's a long time to block changes). I am not overwriting somebody else's changes as I am not changing the value of Col3. I found a really ugly solution: Instead of "Select * from Table1", I do: Select PrimCol, Col2, Col3 || '' from Table1. This turns Col3 into an expression, which means it is not present anymore in the Update statement. But there must be a more elegant way to do it. That's why the "Update Criteria" dynamic property of the recordset is there. -- Bashar Chalabi CTL, London > Bashar Chalabi <bashar@ctl.com> wrote in message > news:tWg34.127$uL.1239@news.colt.net... > > Hello > > > > VB6, ADO on the Informix OLE-DB provider, with client side cursor and > batch > > updates. > > > > I have a table with 3 columns: PrimCol, col2 and col3. PrimCol is a > primary > > key. > > > > I create a Recordset from "select * from table1" > > > > The problem is the KEYCOLUMN property of the primary key field is False. > > Therefore, when I do an UpdateBatch, it generates the following SQL: > > > > Update Table1 set col2 = ? where PrimCol = ? and col2 = ? and col3 = ?> > > > By the time it generates the SQL, col3 has changed, and the update fails. > > > > I really want it to generate: > > > > Update Table1 set col2 = ? where PrimCol = ?> > > > I have set the Recordset Property "Update Criteria" to AdCriteriaKey, but > to > > no effect as it does not recognize the primary key. > > > > Any ideas, anybody > > > > Thanks > > > > -- > > Bashar Chalabi > > CTL, London > > > > > >

Bashar, The server does not tell the client if the column in the results set comes from the primary key or unique index. To support the functionality you need the provider would have to parse the SQL and query the system catalogs for this information. Since applications often ask for IColumnsRowset for simple things, they would have to pay the penalty of this complex processing. Besides, provider would have to track the SQL grammar. So I think your workaround is beautiful. Leopold Bashar Chalabi <bashar@ctl.com> wrote in message news:Owu34.130$uL.1544@news.colt.net... > > Are you sure you do not want to do it in transaction? That would prevent > > col3 from changing from under you (and protect you from overwriting other > > user's changes to it as well). > > > That's the whole point. I need the original value of Col3 in my Recordset, > but I must be able to let it change in the database (my process takes a few > hours to run before it updates the database. That's a long time to block > changes). > > I am not overwriting somebody else's changes as I am not changing the value > of Col3. > > > I found a really ugly solution: > > Instead of "Select * from Table1", I do: > > Select PrimCol, Col2, Col3 || '' from Table1.> > This turns Col3 into an expression, which means it is not present anymore in > the Update statement. > > But there must be a more elegant way to do it. That's why the "Update > Criteria" dynamic property of the recordset is there. > > -- > Bashar Chalabi > CTL, London > > > > Bashar Chalabi <bashar@ctl.com> wrote in message > > news:tWg34.127$uL.1239@news.colt.net... > > > Hello > > > > > > VB6, ADO on the Informix OLE-DB provider, with client side cursor and > > batch > > > updates. > > > > > > I have a table with 3 columns: PrimCol, col2 and col3. PrimCol is a > > primary > > > key. > > > > > > I create a Recordset from "select * from table1" > > > > > > The problem is the KEYCOLUMN property of the primary key field is False. > > > Therefore, when I do an UpdateBatch, it generates the following SQL: > > > > > > Update Table1 set col2 = ? where PrimCol = ? and col2 = ? and col3 = ?> > > > > > By the time it generates the SQL, col3 has changed, and the update > fails. > > > > > > I really want it to generate: > > > > > > Update Table1 set col2 = ? where PrimCol = ?> > > > > > I have set the Recordset Property "Update Criteria" to AdCriteriaKey, > but > > to > > > no effect as it does not recognize the primary key. > > > > > > Any ideas, anybody > > > > > > Thanks > > > > > > -- > > > Bashar Chalabi > > > CTL, London > > > > > > > > > > > >