Re: MS Access 2000 w/ PGSQL 8.0.x - Mailing list pgsql-odbc

I have a solution!

Problem #1: The table I was trying to delete from did not have a
primary key defined!  Add primary key fixed that.
Problem #2: THe "cache lookup failed" comes from if a form has a
hardcoded SQL statement in it that is looking where a boolean is true
(SELECT * FROM <TABLE> WHERE <FIELD>;).  That is valid in Access, but
does not seem to fly to well between the ODBC driver and Postgres.

Resolutions: Add primary keys (which should have been there anyway).
Check all forms to see if there is an SQL statement in the
RecordSource property.  I do not have "Bools as Char' checked and
everything (for now) seems to be running fine.

Thanks for the help!
Tim

On 8/23/05, Jeff Eckermann <jeff_eckermann@yahoo.com> wrote:
> "Timothy Reed" <tareed@gmail.com> wrote in message
> news:dc53e53005082204126f08654f@mail.gmail.com...
> > Yes I always relink the tables after an ODBC change (found that one
> > out the hardway).
> >
> > If the driver is configured to use Bools as Chars they show as Text.
> >
> > if it is configure to NOT use Bools as Chars they show as Yes/No
> >
> > I am using Jet for the time being, just trying to get the application
> > to work as-is for now.
>
> In principle, if your query references the values that are shown in your
> Access table, you should have no problem.  The "cache lookup failed" error
> is still mysterious.
>
> >
> > On 8/22/05, Jeff Eckermann <jeff_eckermann@yahoo.com> wrote:
> >> "Timothy Reed" <tareed@gmail.com> wrote in message
> >> news:dc53e530050819151357b5be95@mail.gmail.com...
> >> >I am having some issues when trying to query for booleans from Access
> >> > to Postgres 8.0.x.   If I change to use Bools as Chars then I am
> >> > unable to delete from a table with a boolean in it, as postgres cannot
> >> > convert from boolean to char.  If I do not use bools as chars then I
> >> > get  "cache lookup failed for function 0"
> >>
> >> When you made the change, did you relink the table(s) affected?  When you
> >> do, what datatype does Access show for that column?
> >>
> >> What query are you using to do the delete?  Is it a Jet or passthrough
> >> query?
> >>
> >> >
> >> > I have followed what the tech docs have recommended to do with adding
> >> > the new msaccessbool function and = operator
> >> > (http://techdocs.postgresql.org/v2/Guides/Using%20Microsoft%20Access%20with%20PostgreSQL/view#55)
> >> >
> >> > But to no avail!
> >> >
> >> > Any help would be greatly appreciated.
> >> >
> >> >
> >> > ODBC Driver settings:
> >> > Disable Genetic Optimizer: YES
> >> > KSQO: YES
> >> > Recognize Unique Indexes: YES
> >> > Declare/Fetch : NO
> >> > Parse Statements: NO
> >> > Cancel As FreeStmt: NO
> >> > Unknown Sizes: Max
> >> > Text as LongVarChar: YES
> >> > Unknows as LongVarChar: NO
> >> > Bools as Char : NO
> >> > Read Only: NO
> >> > Show System Tables: NO
> >> > LF <-> CR/LF: YES
> >> > Updatable Cursors: NO
> >> > bytea as LO: NO
> >> > Row Versioning: NO
> >> > Disallow Premature: NO
> >> > True as -1: YES
> >> > Server side prepare: NO
> >> >
> >> >
> >> > Table Stucture:
> >> > Column Type
> >> > Employee ID integer
> >> > First Name text
> >> > Last Name text
> >> > Clocker boolean
> >> > Labor Type ID bigint
> >> > PC Handle text
> >> > On Payroll boolean
> >> > Address 1 text
> >> > Address 2 text
> >> > City text
> >> > State text
> >> > Zip Code text
> >> > Hire Date timestamp without time zone
> >> > Pay Type text
> >> > Status text
> >> > Pay Rate Old money
> >> > Social Security text
> >> > Phone Number text
> >> > Shift Start text
> >> > Shift End text
> >> > Sign Off boolean
> >> > Hire Date Temp timestamp without time zone
> >> > Stop Date Temp timestamp without time zone
> >> > Stop Date timestamp without time zone
> >> > Rehire Date timestamp without time zone
> >> > Continuous Date timestamp without time zone
> >> > Direct Labor boolean
> >> > Agency boolean
> >> > Active boolean
> >> > Agency2 boolean
> >> > Payroll ID bigint
> >> > EMail text
> >> > ADP Employee ID integer
> >> >
> >> >
> >> > SQL STATEMENT:
> >> >
> >> > SELECT "public"."Employees"."Employee ID" FROM "public"."Employees"
> >> > WHERE NOT ("Active" = 0)  ORDER BY "public"."Employees"."Last Name"
> >> > ,"public"."Employees"."First Name";
> >> >
> >> >
> >> > RESULT:
> >> > ERROR:  cache lookup failed for function 0
> >> >
> >> >
> >> > TIA,
> >> >
> >> > --
> >> > --------
> >> > Timothy Reed
> >> >
> >> > tareed@gmail.com
> >> >
> >> > "I reject your reality, and substitue my own" - Adam Savage
> >> >
> >> > ---------------------------(end of
> >> > broadcast)---------------------------
> >> > TIP 3: Have you checked our extensive FAQ?
> >> >
> >> >               http://www.postgresql.org/docs/faq
> >> >
> >>
> >>
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 3: Have you checked our extensive FAQ?
> >>
> >>                http://www.postgresql.org/docs/faq
> >>
> >
> >
> > --
> > --------
> > Timothy Reed
> >
> > tareed@gmail.com
> >
> > "I reject your reality, and substitue my own" - Adam Savage
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


--
--------
Timothy Reed

tareed@gmail.com

"I reject your reality, and substitue my own" - Adam Savage