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