Which version of the ODBC driver do you use? Ithink the latest version is
7.01.00.06 and is available from odbc.postgresql.org (or from the main site
through links). There should be some FAQs and docs available there. Another
possibility is techdoc.postgresql.org.Also try looking at the mailing
archives (fts.postgresql.org I *think* has an easily searchable interface).
Row versioning is where the xmin field, which I *think* is what stores the
minimum transaction id to which the row is visible, is used as a field to
say wether the row has changed since it was read, otherwise access checks by
specifying every field in the where condition (which leads to problems with
floats).
Also which version of Access are you using? I've had problems with Acess 97
not believing a backend should be able to index any field it can't. Because
Access can't index memo fields and text fields are ,as default, translated
as memo Access refuse to link them or use them in joins etc.... you can get
around the by uunchecking the Text as LongVarChar box in the driver
settings. This of course limits the size of your text fields in the same way
as Access text fields (255 character max, or 254 with my settings).
I also try to make sure Access always has a nice easy primary key to use (I
introduce a serial for the purpose if none present themselves). There might
be problems with booleans. I tend to just use int2 instead (on the postgres
side).
Incase its helpfull here is the connection string I use in Access 2000:
csp = "ODBC;DRIVER={PostgreSQL};UID=" & unm & ";PWD=" & pd & ";DATABASE=" &
DBName & ";SERVER=" & DBSrvr &
";PORT=5432;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIO
NING=1;SHOWSYSTEMTABLES=0;CONNSETTINGS="
and here are the driver's ODBC registry settings I use:
[HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\PostgreSQL]
"APILevel"="1"
"ConnectFunctions"="YYN"
"Driver"="C:\\WINDOWS\\SYSTEM\\psqlodbc.dll"
"DriverODBCVer"="02.50"
"FileUsage"="0"
"Setup"="C:\\WINDOWS\\SYSTEM\\psqlodbc.dll"
"SQLLevel"="1"
"UsageCount"=dword:00099999
"Fetch"="100"
"CommLog"="0"
"Optimizer"="1"
"Ksqo"="1"
"UniqueIndex"="1"
"ReadOnly"="0"
"UseDeclareFetch"="0"
"UnknownSizes"="0"
"TextAsLongVarchar"="1"
"UnknownsAsLongVarchar"="0"
"BoolsAsChar"="0"
"Parse"="0"
"CancelAsFreeStmt"="0"
"MaxVarcharSize"="254"
"MaxLongVarcharSize"="16382"
"ExtraSysTablePrefixes"="dd_;"
"ConnSettings"=""
"CPTimeout"="60"
> -----Original Message-----
> From: Manfred Kraft [SMTP:mkr@alcedis.de]
> Sent: Wednesday, September 12, 2001 1:05 PM
> To: 'Henshall, Stuart - WCP'
> Subject: AW: [ODBC] 'ODBC call failed' a few seconds after Opening
> the table in A
>
> In the meantime I found out, that it always has to do with fields of type
> text. Any query that avoids showing text-fields doesn't make any problem.
> And I think the failing tables have throughout a primary key with more
> than
> 1 fields.
>
> In the ODBC-log-file I found the names of text-fields in the
> SELECT-statement replaced by '#S_C_H#'.
> Instead of a request "SELECT id_1, id_2, remark FROM any_table WHERE id_1
> =
> 11 AND id_2 = 45",
> the driver sent "SELECT id_1, id_2, '#S_C_H#' FROM any_table WHERE id_1 =
>
> 11 AND id_2 = 45".
> In tables, where the call failure uses to occur immediately, this happens
> already in the first request-statement, whereas in a table, where the
> failure occurs after a few seconds, some packages are requestet correctly
> and then the fieldname is being replaced.
>
> I didn't do the test with row-versioning yet.
>
> By the way, it's not clear to me what row versioning means, and generally
> I'm missing a dokumentation of the driver settings. There are some others
> I
> don't know their meaning.
> Is there any dokumentation available for the ODBC-driver?
>
> best regards
> Manfred
>
>
> -----Ursprungliche Nachricht-----
> Von: Henshall, Stuart - WCP
> [SMTP:SHenshall@westcountrypublications.co.uk]
> Gesendet am: Dienstag, 11. September 2001 10:06
> An: 'Manfred Kraft'; 'pgsql-odbc@postgresql.org'
> Betreff: Re: [ODBC] 'ODBC call failed' a few seconds after Opening
> the
> table in A
>
> Are you using floats? If so then it might be trying to refresh based on
> all
> the fields and failing because of floating point precision.
> Make sure that all tables have primary keys and try enabling row
> versioning. If you enable row versioning you will need this, or something
> similar, in the backend:
> create function int4eq(xid,int4)
> returns bool
> as ''
> language 'internal';
>
> create operator = (
> leftarg=xid,
> rightarg=int4,
> procedure=int4eq,
> commutator='=',
> negator='<>',
> restrict=eqsel,
> join=eqjoinsel
> );
> Hope this helps,
> - Stuart
>
> > -----Original Message-----
> > From: Manfred Kraft [SMTP:mkr@alcedis.de]
> > Sent: Monday, September 10, 2001 3:55 PM
> > To: 'pgsql-odbc@postgresql.org'
> > Subject: 'ODBC call failed' a few seconds after Opening the table in
> > Access
> >
> > Hi,
> >
> > Does anybody else know that strange behaviour: In nearly each database I
> > have one or more tables, losing their ODBC-Connection after a specific
> > time
> > ofter opening. All other tables of the same database are ok.
> >
> > Deatils: e.g. there is one table I can be sure that some 3 to 10 seconds
> > after having opend its datasheet view in Access I get the "ODBC call
> > failed" message and the contents of all fileds change to #NAME? (that's
> in
> >
> > the german version; I don't know whether it's the same Error-string in
> the
> >
> > english one).
> >
> > My first idea was, that is has to do with altered tables, but that's not
> > generally true.
> > Dumping and restoring the database doesn't solve the problem, whereas
> > recreating it with my own set of create-statements and afterwards
> > inserting
> > the data does. But that's no praktical solution.
> >
> > Thanks for helpful hints.
> >
> > Manfred
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
