Re: error with access 2003 - column "ctid" does not exist
От | Jeff Eckermann |
---|---|
Тема | Re: error with access 2003 - column "ctid" does not exist |
Дата | |
Msg-id | 20040719135214.30472.qmail@web20805.mail.yahoo.com обсуждение исходный текст |
Ответ на | error with access 2003 - column "ctid" does not exist ("David P. Lurie" <dbase4@hotmail.com>) |
Список | pgsql-odbc |
--- "David P. Lurie" <dbase4@hotmail.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@postgresql.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
В списке pgsql-odbc по дате отправления: