Re: Latest ODBC driver?

Hiroshi Inoue wrote:

> > No - it's the Unicode one that disagrees with BDE - specifically,
> > text/varchar columns will all be empty if requested as Unicode strings -
> > which is exactly what BDE does because the driver offers that as the
> > default.
>
> As for BDE, I'm not able to test it by myself unfortunately.
> I have a little hope in the next release with the combination
> of the Unicode driver and BDE.
> At the Advanced Options (DataSource) Page 2 of setup dialog
> you can see Extra opts option. Settng the option to 0x4 may
> take some effect though I'm not sure.
>
> You can test it using the snapshot driver at http://www.geocities.jp/inocchichichi/psqlodbc/index.html
> .
> If you already installed the 8.2.0002 driver, you can
> simply replace the dll psqlodbc35w.dll at the site.

I'm afraid that I'm not the bearer of particularly good news, but I thought it
worth summarising where I'm at. All the results below are from an NT4W SP6a
machine with ODBC DLLs 3.50.33.8 and BDE 4.51, nothing else particularly
remarkable about it; the old server is 7.1.2, the new one 8.1.3 both hosted on
Linux/Slackware.

Installing ODBC drivers 7.02.001 (Insight) my apps can connect to live data on
the old server using ODBC default settings, that's been the situation for around
five years. Needless to say if I change this and try to use the v7 drivers to
connect to the v8 server I get an error dialogue box from the app ("Key
violation. blank Alias: p0xDontClash", in my experience this is a pretty generic
error that happens when a query fails badly).

Removing the DSN and the ODBC v7 driver, installing 8.01.0200 and redoing the
DSN using the ANSI driver for connection to the new (v8) server. The app
establishes its initial connections without any problem, then at around the time
where I would have expected it to complete its initial query memory usage starts
increasing at around 50kB per second. Changing this to use the Unicode driver
behaves in a very similar fashion (the data comprises timestamps and numeric
values, with only a small amount of non-critical text). Reverting to the ANSI
driver and connecting to the v7 server appears to never complete the initial
query, but doesn't explode either.

Similarly switching from 8.01.0200 to 8.02.0002 and noting that this does not
provide an explicit ANSI driver, attempting to connect to the v8 server I get
"Unknown user name or password. Unexpected protocol character during
authentication; socket has been closed. The driver returned invalid (or failed
to return) SQL_DRIVER_ODBC_VER: 03.51 General SQL error.". Noting that the ODBC
DLLs on this machine are at 3.50 (not 3.51) is that the result of an explicit
version check?

Replacing the single file psqlodbc35w.dll (in c:\Program
Files\psqlODBC\0802\bin) with the latest snapshot driver from the URL above (and
rebooting at this point to make sure nothing was left in memory) reported a
driver version of 7.03.0284 but when trying to add a DSN from the NT Control
Panel I get "The setup routines for the PostgreSQL ODBC driver could not be
loaded due to system error code 126." followed by "Could not load the setup or
translator library".

Without very much hope, trying ODBCng rev 64 from
http://projects.commandprompt.com/public/odbcng/browser/binaries/mODBC_Installer.msi.
Setup is OK and <Test> button works. However trying to connect to the database
gives "General SQL error. [Microsoft][ODBC Driver Manager] Driver does not
support this function".

So far I have not tried enabling client logging or reconciling client activity
with server logs. Allowing that I'm a novice in this particular area I might
need help with these.

It seems to me that I have three possible options:

a) Work back through every possible v8 and v7 driver version in case there's one
that works. I don't much like the idea of this since even if I found such a
beast I would probably be running an unqualified combination of server and
client software.

b) Revert to a v7 server on the new hardware, with the appropriate driver. I
don't much like this one either since part of the reason I'm putting time into
this is to allow me to (finally) use replication.

c) Trying to work out what is specific to this app that causes a failure.
However since most queries in the suite are scripted this could still leave
timebombs in the syste,

The other options- investigating mySQL or Oracle- are quite simply too hideous
to contemplate.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]