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=prefe r;UID=postgres;PWD=XXXXXXXXX;
ReadOnly=0;FakeOidIndex=0;ShowOidColumn=1;RowVersioning=1;Sh owSystemTables=0;ConnSettings=;
Fetch=10000;Socket=4096;UnknownSizes=0;MaxVarcharSize=254;Ma xLongVarcharSize=32767;
Debug=0;CommLog=1;Optimizer=1;Ksqo=0;UseDeclareFetch=0;TextA sLongVarchar=1;
UnknownsAsLongVarchar=1;BoolsAsChar=0;Parse=0;CancelAsFreeSt mt=0;ExtraSysTablePrefixes=dd_;
LFConversion=1;UpdatableCursors=0;DisallowPremature=0;TrueIs Minus1=1;BI=0;ByteaAsLongVarBinary=0;
UseServerSidePrepare=0;LowerCaseIdentifier=0


Thanks in advance for your answers.

Regards,

Thomas

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Strange Update query ...

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Strange Update query ...

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 )'



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Strange Update query ...

H=E9lder 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=20
> corresponding to a parameter query.
> I'd bet on a ADO misinterpretation of your intention caused by the=20
> 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=20
myfield1=3Dmyvalue1,myfield2=3Dmyvalue2,myfield3=3Dmyvalue3 WHERE (id=3D6=
AND
myfield1=3Dcurrent_value1 AND myfield2=3Dcurrent_value2 AND=20
myfield3=3Dcurrent_value3 )'

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

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Strange Update query ...

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

'UPDATE mytable SET myfield1=3D4,myfield2=3D5,myfield3=3D6
WHERE (id=3D6 AND myfield1=3D1 AND myfield2=3D2 AND myfield3=3D3 )'

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

Thomas Chabaud's log shows:

'UPDATE mytable SET myfield1=3Dmyvalue1,myfield2=3Dmyvalue2,myfield3=3Dmy=
value3
WHERE (id=3D6 AND myfield1=3Dmyvalue1 AND myfield2=3Dmyvalue2 AND=20
myfield3=3Dmyvalue3 )'

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


H=E9lder M. Vieira



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Strange Update query ...

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

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 suppos=
ed=20
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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match