Обсуждение: Unable to update JOIN'ed recordset with PSQLODBC 08.02.0400 and ADO
In the following environment:
WinXP SP2 + ADO + MS OLE DB Provider for ODBC drivers + PSQLODBC ANSI
08.02.0400
I'm unable to update a recordset of JOIN'ed tables. The method
'rs("colname") = <new value>' started to fail with 08.02.0400, but it works
fine with 08.02.0200. Something wrong with tables and columns metadata ADO
gets from the driver, I suppose.
My test suite included. Please note only columns from table t1 are loaded
into the recordset, JOIN is used just for sorting rows.
Test suite:
Server side:
------------
CREATE TABLE t1
(
a integer NOT NULL,
b integer,
x character varying(255),
CONSTRAINT pk_t1 PRIMARY KEY (a)
)
WITHOUT OIDS;
CREATE TABLE t2
(
b integer NOT NULL,
c integer,
CONSTRAINT pk_t2 PRIMARY KEY (b)
)
WITHOUT OIDS;
INSERT INTO t1 (a, b, x) VALUES (1, 100, 'String 1');
INSERT INTO t1 (a, b, x) VALUES (2, 200, 'String 2');
INSERT INTO t2 (b, c) VALUES (100, 1000);
INSERT INTO t2 (b, c) VALUES (200, 2000);
Client code:
------------
Sub Test()
Dim strSql As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open _
"Provider=MSDASQL;" & _
"DRIVER=PostgreSQL ANSI;" & _
"SERVER=127.0.0.1;" & _
"DATABASE=test;" & _
"UID=postgres;" & _
"PWD=postgres;" & _
"UseServerSidePrepare=1;"
strSql = "SELECT t1.* FROM t1 INNER JOIN t2 ON t1.b = t2.b ORDER BY t2.c"
rs.ActiveConnection = cn
rs.CursorLocation = adUseClient
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Source = strSql
rs.Open
MsgBox rs("x")
rs("x") = "Modified string"
rs.Update
MsgBox rs("x")
rs.Close
End Sub
Regards,
Dmitry
Release 08.02.0500 is out, but a bug I described in my post two months ago
still exists. Please look into it and restore the functionality broken since
08.02.0300, when an opportunity offers.
Regards,
Dmitry
"Dmitry Samokhin" <sdld@mail.ru> wrote in message
news:f8plbo$2j47$1@news.hub.org...
> In the following environment:
> WinXP SP2 + ADO + MS OLE DB Provider for ODBC drivers + PSQLODBC ANSI
> 08.02.0400
>
> I'm unable to update a recordset of JOIN'ed tables. The method
> 'rs("colname") = <new value>' started to fail with 08.02.0400, but it
> works fine with 08.02.0200. Something wrong with tables and columns
> metadata ADO gets from the driver, I suppose.
> My test suite included. Please note only columns from table t1 are loaded
> into the recordset, JOIN is used just for sorting rows.
>
> Test suite:
>
> Server side:
> ------------
>
> CREATE TABLE t1
> (
> a integer NOT NULL,
> b integer,
> x character varying(255),
> CONSTRAINT pk_t1 PRIMARY KEY (a)
> )
> WITHOUT OIDS;
>
> CREATE TABLE t2
> (
> b integer NOT NULL,
> c integer,
> CONSTRAINT pk_t2 PRIMARY KEY (b)
> )
> WITHOUT OIDS;
>
> INSERT INTO t1 (a, b, x) VALUES (1, 100, 'String 1');
> INSERT INTO t1 (a, b, x) VALUES (2, 200, 'String 2');
>
> INSERT INTO t2 (b, c) VALUES (100, 1000);
> INSERT INTO t2 (b, c) VALUES (200, 2000);
>
> Client code:
> ------------
>
> Sub Test()
> Dim strSql As String
> Dim cn As New ADODB.Connection
> Dim rs As New ADODB.Recordset
>
> cn.Open _
> "Provider=MSDASQL;" & _
> "DRIVER=PostgreSQL ANSI;" & _
> "SERVER=127.0.0.1;" & _
> "DATABASE=test;" & _
> "UID=postgres;" & _
> "PWD=postgres;" & _
> "UseServerSidePrepare=1;"
>
> strSql = "SELECT t1.* FROM t1 INNER JOIN t2 ON t1.b = t2.b ORDER BY t2.c"
>
> rs.ActiveConnection = cn
> rs.CursorLocation = adUseClient
> rs.CursorType = adOpenKeyset
> rs.LockType = adLockOptimistic
> rs.Source = strSql
>
> rs.Open
>
> MsgBox rs("x")
> rs("x") = "Modified string"
> rs.Update
> MsgBox rs("x")
>
> rs.Close
> End Sub
>
> Regards,
> Dmitry
>
>
Hi.
Sorry, very late reaction....Surely it reproduces a problem. It seems that it
was missed at the time of some problem solutions. Probably, It seems to
be MOLE-BASHING completely.:-(
However, The following is the one solution method....
' strSql = "SELECT t1.* FROM t1 INNER JOIN t2 ON t1.b = t2.b ORDER BY t2.c"
strSql = "SELECT t1.* FROM t1,t2 WHERE t1.b = t2.b ORDER BY t2.c"
But, Investigation will be continued.
I appreciate your perseverance. Thanks!
Regards,
Hiroshi Saito
----- Original Message -----
From: "Dmitry Samokhin" <sdld@mail.ru>
> Release 08.02.0500 is out, but a bug I described in my post two months ago
> still exists. Please look into it and restore the functionality broken since
> 08.02.0300, when an opportunity offers.
>
> Regards,
> Dmitry
>
>
> "Dmitry Samokhin" <sdld@mail.ru> wrote in message
> news:f8plbo$2j47$1@news.hub.org...
>> In the following environment:
>> WinXP SP2 + ADO + MS OLE DB Provider for ODBC drivers + PSQLODBC ANSI
>> 08.02.0400
>>
>> I'm unable to update a recordset of JOIN'ed tables. The method
>> 'rs("colname") = <new value>' started to fail with 08.02.0400, but it
>> works fine with 08.02.0200. Something wrong with tables and columns
>> metadata ADO gets from the driver, I suppose.
>> My test suite included. Please note only columns from table t1 are loaded
>> into the recordset, JOIN is used just for sorting rows.
>>
>> Test suite:
>>
>> Server side:
>> ------------
>>
>> CREATE TABLE t1
>> (
>> a integer NOT NULL,
>> b integer,
>> x character varying(255),
>> CONSTRAINT pk_t1 PRIMARY KEY (a)
>> )
>> WITHOUT OIDS;
>>
>> CREATE TABLE t2
>> (
>> b integer NOT NULL,
>> c integer,
>> CONSTRAINT pk_t2 PRIMARY KEY (b)
>> )
>> WITHOUT OIDS;
>>
>> INSERT INTO t1 (a, b, x) VALUES (1, 100, 'String 1');
>> INSERT INTO t1 (a, b, x) VALUES (2, 200, 'String 2');
>>
>> INSERT INTO t2 (b, c) VALUES (100, 1000);
>> INSERT INTO t2 (b, c) VALUES (200, 2000);
>>
>> Client code:
>> ------------
>>
>> Sub Test()
>> Dim strSql As String
>> Dim cn As New ADODB.Connection
>> Dim rs As New ADODB.Recordset
>>
>> cn.Open _
>> "Provider=MSDASQL;" & _
>> "DRIVER=PostgreSQL ANSI;" & _
>> "SERVER=127.0.0.1;" & _
>> "DATABASE=test;" & _
>> "UID=postgres;" & _
>> "PWD=postgres;" & _
>> "UseServerSidePrepare=1;"
>>
>> strSql = "SELECT t1.* FROM t1 INNER JOIN t2 ON t1.b = t2.b ORDER BY t2.c"
>>
>> rs.ActiveConnection = cn
>> rs.CursorLocation = adUseClient
>> rs.CursorType = adOpenKeyset
>> rs.LockType = adLockOptimistic
>> rs.Source = strSql
>>
>> rs.Open
>>
>> MsgBox rs("x")
>> rs("x") = "Modified string"
>> rs.Update
>> MsgBox rs("x")
>>
>> rs.Close
>> End Sub
>>
>> Regards,
>> Dmitry
Thanks for your attention and a workaround! Investigating different types of queries returning the required result I found out that it fails even on "SELECT ... FROM t1 CROSS JOIN t2 WHERE t1.b = t2.b ..." although the documentation states: 7.2.1.1. Joined Tables ... FROM T1 CROSS JOIN T2 is equivalent to FROM T1, T2. It is also equivalent to FROM T1 INNER JOIN T2 ON TRUE (see below). ... Unfortunately, my simple suite is mainly for the community to easily reproduce the problem. In our real applications, we need to utilize OUTER joins also. Since the PostgreSQL next release, 8.3, is on the way now and requires focusing developers' attention on, please feel free to put all this into the background; I've risen it up in order to not forget completely :)) Regards, Dmitry. ""Hiroshi Saito"" <z-saito@guitar.ocn.ne.jp> wrote in message news:06e601c8044b$0cc89770$c601a8c0@HP22720319231... > Hi. > > Sorry, very late reaction....Surely it reproduces a problem. It seems that > it was missed at the time of some problem solutions. Probably, It seems to > be MOLE-BASHING completely.:-( > > However, The following is the one solution method.... > > ' strSql = "SELECT t1.* FROM t1 INNER JOIN t2 ON t1.b = t2.b ORDER BY > t2.c" > strSql = "SELECT t1.* FROM t1,t2 WHERE t1.b = t2.b ORDER BY t2.c" > > But, Investigation will be continued. > I appreciate your perseverance. Thanks! > > Regards, > Hiroshi Saito >