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

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