Обсуждение: Strange Update query ...

Hello,
I have a problem with update on recordset with Visual Basic 6.

I'm using the recordset as following :

   Set rs = New ADODB.Recordset
   rs.Open "SELECT id,myfield1,myfield2,myfield3 from mytable where
id=6",dbConn, adOpenKeyset, adLockOptimistic, adCmdText

   rs("myfield1").value = myvalue1
   rs("myfield2").value = myvalue2
   rs("myfield3").value = myvalue3
   rs.Update
   rs.close
   set rs=Nothing

But the line is not updated, so I checked the log, and I saw that the query
which was generated by odbc driver is :

UPDATE mytable SET myfield1=myvalue1,myfield2=myvalue2,myfield3=myvalue3
WHERE (id=6 AND myfield1=myvalue1 AND myfield2=myvalue2 AND myfield3=myvalue3 )'

I wonder why the odbc driver add those "AND ..." statement, because I think
they are useless, and they fail my rs.update

I use the following connection parameters :

DRIVER={PostgreSQL
ANSI};DATABASE=mydb;SERVER=localhost;PORT=5432;SSLMODE=prefer;UID=postgres;PWD=XXXXXXXXX;
ReadOnly=0;FakeOidIndex=0;ShowOidColumn=1;RowVersioning=1;ShowSystemTables=0;ConnSettings=;
Fetch=10000;Socket=4096;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=32767;
Debug=0;CommLog=1;Optimizer=1;Ksqo=0;UseDeclareFetch=0;TextAsLongVarchar=1;
UnknownsAsLongVarchar=1;BoolsAsChar=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;
LFConversion=1;UpdatableCursors=0;DisallowPremature=0;TrueIsMinus1=1;BI=0;ByteaAsLongVarBinary=0;
UseServerSidePrepare=0;LowerCaseIdentifier=0


Thanks in advance for your answers.

Regards,

Thomas

Thomas Chabaud wrote:
> Hello,
> I have a problem with update on recordset with Visual Basic 6.
>
> I'm using the recordset as following :
>
>   Set rs = New ADODB.Recordset
>   rs.Open "SELECT id,myfield1,myfield2,myfield3 from mytable where
> id=6",dbConn, adOpenKeyset, adLockOptimistic, adCmdText
>
>   rs("myfield1").value = myvalue1
>   rs("myfield2").value = myvalue2
>   rs("myfield3").value = myvalue3
>   rs.Update
>   rs.close
>   set rs=Nothing
>
> But the line is not updated, so I checked the log, and I saw that the
> query which was generated by odbc driver is :
>
> UPDATE mytable SET myfield1=myvalue1,myfield2=myvalue2,myfield3=myvalue3
> WHERE (id=6 AND myfield1=myvalue1 AND myfield2=myvalue2 AND
> myfield3=myvalue3 )'

Are they really myvalue..s not oldvalue..s after the "AND .." ?

> I wonder why the odbc driver add those "AND ..." statement, because I
> think they are useless, and they fail my rs.update

AFAIK the ODBC driver doesn't add them. Maybe ado does it.

regards,
Hiroshi Inoue

I tried to replicate the situation you describe, without success.
I guess ADO for some reason is trying to build an SQL statement
corresponding to a parameter query.
I'd bet on a ADO misinterpretation of your intention caused by the myvalue1,
myvalue2 and myvalue3 data types.
Could you post the DIM statements of those variables ?


Helder M. Vieira




>   Set rs = New ADODB.Recordset
>   rs.Open "SELECT id,myfield1,myfield2,myfield3 from mytable where
> id=6",dbConn, adOpenKeyset, adLockOptimistic, adCmdText
>
>   rs("myfield1").value = myvalue1
>   rs("myfield2").value = myvalue2
>   rs("myfield3").value = myvalue3
>   rs.Update
>   rs.close
>   set rs=Nothing
>
...
>
> UPDATE mytable SET myfield1=myvalue1,myfield2=myvalue2,myfield3=myvalue3
> WHERE (id=6 AND myfield1=myvalue1 AND myfield2=myvalue2 AND
> myfield3=myvalue3 )'

Hélder M. Vieira wrote:

> I tried to replicate the situation you describe, without success.
> I guess ADO for some reason is trying to build an SQL statement
> corresponding to a parameter query.
> I'd bet on a ADO misinterpretation of your intention caused by the
> myvalue1, myvalue2 and myvalue3 data types.
> Could you post the DIM statements of those variables ?


Isn't myfieldx timestamp or float type ?
If I remember correctly, ADO builds the SQL statement
  UPDATE mytable SET
myfield1=myvalue1,myfield2=myvalue2,myfield3=myvalue3 WHERE (id=6 AND
     myfield1=current_value1 AND myfield2=current_value2 AND
myfield3=current_value3 )'

The clauses after the "AND" are for optimistic concurrency control by
vaues but
the clause could be false if it contains timestamp or float field,

regards,
Hiroshi Inoue

Hiroshi, I made several tests, and the log always shows an UPDATE statement
filled with constant values, such as:

'UPDATE mytable SET myfield1=4,myfield2=5,myfield3=6
WHERE (id=6 AND myfield1=1 AND myfield2=2 AND myfield3=3 )'

I tried with several data types, with and without explicit declaration of
the 'myvaluex' variables, and never saw variable names in the generated
UPDATE statement.

Thomas Chabaud's log shows:

'UPDATE mytable SET myfield1=myvalue1,myfield2=myvalue2,myfield3=myvalue3
WHERE (id=6 AND myfield1=myvalue1 AND myfield2=myvalue2 AND
myfield3=myvalue3 )'

I can't figure out how variable names could appear in the UPDATE statement,
so I think Thomas Chabaud should provide some information about the nature
of those variables.


Hélder M. Vieira

Hélder M. Vieira a écrit :
> I tried to replicate the situation you describe, without success.
> I guess ADO for some reason is trying to build an SQL statement
> corresponding to a parameter query.
> I'd bet on a ADO misinterpretation of your intention caused by the
> myvalue1, myvalue2 and myvalue3 data types.
> Could you post the DIM statements of those variables ?
>
>
> Helder M. Vieira
>
>
>
>

I have find the bug, it was an error in the code, there was a
function before my code which changed the values, and the values I supposed
to be new values in the update statement were in fact the old values ...

Sorry for the waste of time, and thanks a lot for your explanations.

Regards,

Thomas