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



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Unable to update JOIN"ed recordset with PSQLODBC 08.02.0400 and ADO

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



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

Re: Unable to update JOIN"ed recordset with PSQLODBC 08.02.0400 and ADO

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"


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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Unable to update JOIN"ed recordset with PSQLODBC 08.02.0400 and ADO

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"" 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
>



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate