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

["Hiroshi Saito" <z-saito(at)guitar(dot)ocn(dot)ne(dot)jp>]

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(at)mail(dot)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(at)mail(dot)ru> wrote in message 
> news:f8plbo$2j47$1(at)news(dot)hub(dot)org(dot)(dot)(dot)
> > 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