Обсуждение: MS Access 2000 w/ PGSQL 8.0.x
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" 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
"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 >
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. 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
"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 >
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