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

From: "Dmitry Samokhin" <sdld(at)mail(dot)ru>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Unable to update JOIN'ed recordset with PSQLODBC 08.02.0400 and ADO
Date: 2007-08-01 09:56:18
Message-ID: f8plbo$2j47$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

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

Responses

Browse pgsql-odbc by date

  From Date Subject
Next Message Paul Lambert 2007-08-03 01:45:19 Strange ODBC behavior.
Previous Message Hiroshi Saito 2007-07-31 00:23:59 Re: Need help with testing -- psqlodbc-08.02.0400