Skip site navigation (1) Skip section navigation (2)

Re: error with access 2003 - column "ctid" does not exist

From: Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com>
To: "David P(dot) Lurie" <dbase4(at)hotmail(dot)com>, pgsql-odbc(at)postgresql(dot)org
Subject: Re: error with access 2003 - column "ctid" does not exist
Date: 2004-07-19 13:52:14
Message-ID: 20040719135214.30472.qmail@web20805.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-odbc
--- "David P. Lurie" <dbase4(at)hotmail(dot)com> wrote:
> Access 2003
> psqlodbc 7.03.02
> postgresql 7.4.3 (cygwin)
> 
> I can successfully use tables and views as linked
> tables in access. Views
> can be updated with access forms after writing
> appropriate update, delete
> and insert rules for each view.
> 
> ADO pass-through queries will be needed for some
> reports, in order to pass
> runtime parameters.
> 
> A test subroutine to create an ADO recordset for a
> pass-through query on a
> view fails on recordset open with the error: column
> "ctid" does not exist.
> 
> Substitution of the SQL statement used to create the
> view runs without
> error.
> 
> The docs list ctid as a system column with the
> current location of the row.
> The same problem occurs with single or multitable
> views.
> 
> Is there something additional needed to access a
> view via ADO, or is this a
> limitation of the odbc driver?

The ODBC driver relies on the ctid to identify the
row, when doing updates.  Here is a piece from the
developer docs:

"ctid
The physical location of the row version within its
table. Note that although the ctid can be used to
locate the row version very quickly, a row's ctid will
change each time it is updated or moved by VACUUM
FULL. Therefore ctid is useless as a long-term row
identifier. The OID, or even better a user-defined
serial number, should be used to identify logical
rows. "

I am thinking that you could just include "ctid" in
the select list for your view, and everything would
work.  The only issue would be, in the case of a
multitable view, which available ctid value you
choose.  I suspect that the choice will not make a
difference.

I don't know enough to say with assurance that this
would work reliably.  Perhaps someone who knows more
can comment.

> 
> The test subroutine follows;  I just comment out one
> of the two recordset
> open statements before execution. The mailing list
> will likely break up the
> query string assignment statement into multiple
> lines. It should just be two
> lines, with a continuation character at the end of
> the first line:
> 
> Sub TestODBC()
> Dim cnn1 As New ADODB.Connection
> Dim rst1 As New ADODB.Recordset
> Dim strqry As String
> 'Open recordset
> cnn1.Open
>
"Provider=MSDASQL;DSN=PostgreSQL30;database=emr;UID=postgres;"
> Set rst1 = New ADODB.Recordset
> rst1.CursorType = adOpenDynamic
> rst1.LockType = adLockOptimistic
> rst1.CursorLocation = adUseServer
> strqry = "SELECT (patients.acctmaj::text ||
> patients.acctmin::text)::character varying AS
> acctnum, patients.acctmaj,
> patients.acctmin, patients.lastname,
> patients.firstname,
> patients.middleinit, allergies.allergiesid,
> allergies.drug,
> allergies.reaction, allergies.comment" & _
>    " FROM public.patients JOIN public.allergies ON
> patients.acctmaj::text =
> allergies.acctmaj::text AND patients.acctmin::text =
> allergies.acctmin::text
> ORDER BY (patients.acctmaj::text ||
> patients.acctmin::text)::character
> varying, allergies.drug;"
> rst1.Open strqry, cnn1
> 'rst1.Open "select * from
> public.vwpatientallergies", cnn1
> Do Until rst1.EOF
>     Debug.Print rst1.Fields(3).Value,
> rst1.Fields(4).Value,
> rst1.Fields(5).Value, rst1.Fields(7).Value,
> rst1.Fields(8).Value,
> rst1.Fields(9).Value, vbNewLine
>     rst1.MoveNext
> Loop
> End Sub
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
>       subscribe-nomail command to
> majordomo(at)postgresql(dot)org so that your
>       message can get through to the mailing list
> cleanly
> @postgresql.org so that your
>       message can get through to the mailing list
> cleanly
> 



		
__________________________________
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail

In response to

Responses

pgsql-odbc by date

Next:From: Michael AldorDate: 2004-07-19 15:11:20
Subject: Re: ADO Connection with Postgres
Previous:From: Dave PageDate: 2004-07-19 10:53:55
Subject: 07.05.0001 Snapshot available

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group