Lists: | pgsql-odbc |
---|
From: | Thomas Chabaud <tc(at)geosys(dot)fr> |
---|---|
To: | pgsql-odbc(at)postgresql(dot)org |
Subject: | Strange Update query ... |
Date: | 2006-03-31 14:00:29 |
Message-ID: | 442D35FD.2050208@geosys.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
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
From: | Hiroshi Inoue <inoue(at)tpf(dot)co(dot)jp> |
---|---|
To: | tc(at)geosys(dot)fr |
Cc: | pgsql-odbc(at)postgresql(dot)org |
Subject: | Re: Strange Update query ... |
Date: | 2006-04-01 06:00:28 |
Message-ID: | 442E16FC.3030607@tpf.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
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
From: | Hélder M(dot) Vieira <hmv(at)mail(dot)telepac(dot)pt> |
---|---|
To: | <pgsql-odbc(at)postgresql(dot)org> |
Subject: | Re: Strange Update query ... |
Date: | 2006-04-01 09:13:15 |
Message-ID: | 004001c6556c$930fc130$580bfea9@hmv02 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
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 )'
From: | Hiroshi Inoue <inoue(at)tpf(dot)co(dot)jp> |
---|---|
To: | "Hélder M(dot) Vieira" <hmv(at)mail(dot)telepac(dot)pt> |
Cc: | pgsql-odbc(at)postgresql(dot)org |
Subject: | Re: Strange Update query ... |
Date: | 2006-04-01 23:12:39 |
Message-ID: | 442F08E7.2000903@tpf.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
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
From: | Hélder M(dot) Vieira <hmv(at)mail(dot)telepac(dot)pt> |
---|---|
To: | <pgsql-odbc(at)postgresql(dot)org> |
Subject: | Re: Strange Update query ... |
Date: | 2006-04-02 11:44:51 |
Message-ID: | 000a01c6564a$da5e2750$580bfea9@hmv02 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
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
From: | Thomas Chabaud <tc(at)geosys(dot)fr> |
---|---|
To: | |
Cc: | pgsql-odbc(at)postgresql(dot)org |
Subject: | Re: Strange Update query ... |
Date: | 2006-04-03 07:05:20 |
Message-ID: | 4430C930.6040606@geosys.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
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