Thread: Unable to update JOIN'ed recordset with PSQLODBC 08.02.0400 and ADO

Unable to update JOIN'ed recordset with PSQLODBC 08.02.0400 and ADO

From

"Dmitry Samokhin"

Date:

01 August 2007, 09:56:18

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

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

From

"Dmitry Samokhin"

Date:

01 October 2007, 09:53:23

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

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

From

"Hiroshi Saito"

Date:

01 October 2007, 16:49:27

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

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

From

"Dmitry Samokhin"

Date:

02 October 2007, 10:23:39

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
>