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