Unable to update JOIN'ed recordset with PSQLODBC 08.02.0400 and ADO - Mailing list pgsql-odbc

From Dmitry Samokhin
Subject Unable to update JOIN'ed recordset with PSQLODBC 08.02.0400 and ADO
Date
Msg-id f8plbo$2j47$1@news.hub.org
Whole thread Raw
List 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



pgsql-odbc by date:

Previous
From: "Hiroshi Saito"
Date:
Subject: Re: Need help with testing -- psqlodbc-08.02.0400
Next
From: Paul Lambert
Date:
Subject: Strange ODBC behavior.