Thread: Re: Possible bugs in ODBC driver

Re: Possible bugs in ODBC driver

From
Byron Nikolaidis
Date:

Dario Fumagalli wrote:

> Dear Byron,
>
> We are a software house situated in Italy.
> We focus our applications on Linux+PostgreSQL 6.3.1 (server side) + Borland
> Delphi/C++ Builder or Access 97 (front ends).
> We (and much more people than perhaps you think about) often use Borland
> compilers because they produce with relatively small developement time fast
> executables and powerful programs.
>
> I'm the person in charge of evaluating software updates/drivers/patches
> before incorporating them in our applications.
>
> So as soon as I saw your post about a new release of the PostgreSQL ODBC
> driver I downloaded and installed it.
>
> Now, I have to say that this driver seems "nicer" (the old driver was like
> a sort of "poor man's ODBC driver") and somewhat faster.
>
> But... there are some glitches we had not with the previous drivers
> (neither 0.21 nor 0.3x):
>
> 1) There is a minor glitch that (at least under Borland C++ Builder
> Professional 3.0 + BDE 4.1) causes the metadata of the last table to appear
> duplicated (if you like I can send you a screen shot). I.e. if I have a
> database with the following three tables:
>
> - customers
> - orders
> - order_details
>
> The DatabaseExplorer utility Borland gives you to quickly manage tables
> using SQL (it is like the psql shell in Unix), tells you the database has
> the following structure:
>
> - customers
> - orders
> - order_details
> - order_details
>
> with the last entry (and its fields, indexes and so on...) repeated twice.
> We can see this problem on all our databases. It seems to be some sort of
> linked list problem. This problem never appeared using the old drivers.
>

We have had this problem inhouse, and maybe it is possible that you have it.
The SQLTables() function in the driver does a query very similar to a '\d' in
the psql (you might want to try that and see if you get the same problem).  The
query is:

select relname, usename from pg_class, pg_user where relkind = 'r'
and relname !~ '^xinv[0-9]+' and int4out(usesysid) = int4out(relowner) order by
relname;

If the relowner does not match the usesysid for all relations, you will get
multiple entries.
Maybe we could change the above query to use "distinct" to prevent this.


> 2) There is a very serious new limit on the maximum number of table columns
> the driver seems to manage.
> We have a fairly large database of pharmaceutics, with tables having over
> 70 fields. We are using it since Feb/98 without a problem until I installed
> the new ODBC driver. Now, if I perform a query like:
>
> SELECT * from codifa WHERE descriz LIKE 'ASPRO%'
>
> the program stops telling there is a database problem. It says:
>
> "Record size is too big for table"
>

Is that message "Record size is too big for table" coming from your products or
from the ODBC driver?  The new driver does do a good job of getting the real
error message back to the user.  It tries to concatenate any backend error
message with other messages.   If that message is from your products, then it
would be good if you could find the real error message.

The new driver (as did the old I think) only has one hard limit on number of
fields and that is 512 for the bitmap.

Another option for finding the real error message is to use the new 'CommLog'
feature of the new driver.  If this feature is selected, it will create a file
called "psqlodbc.log" in your root directory of your PC with all queries and
error messages to/from the backend.  You could then look in there and see what
happened and send it to me.

Also, are you using Postgres v6.3?

Byron

P.S., please use the "pgsql-interfaces@postgreSQL.org" to post these notes for
everyone to see, including me.


Re: [INTERFACES] Re: Possible bugs in ODBC driver

From
Hannu Krosing
Date:
Byron Nikolaidis wrote:
>
> Dario Fumagalli wrote:
>
> > But... there are some glitches we had not with the previous drivers
> > (neither 0.21 nor 0.3x):
> >
> > 1) There is a minor glitch that (at least under Borland C++ Builder
> > Professional 3.0 + BDE 4.1) causes the metadata of the last table to appear
> > duplicated (if you like I can send you a screen shot). I.e. if I have a
> > database with the following three tables:
> >
> > - customers
> > - orders
> > - order_details
> >
> > The DatabaseExplorer utility Borland gives you to quickly manage tables
> > using SQL (it is like the psql shell in Unix), tells you the database has
> > the following structure:
> >
> > - customers
> > - orders
> > - order_details
> > - order_details
> >
> > with the last entry (and its fields, indexes and so on...) repeated twice.
> > We can see this problem on all our databases. It seems to be some sort of
> > linked list problem. This problem never appeared using the old drivers.
> >
>
> We have had this problem inhouse, and maybe it is possible that you have it.
> The SQLTables() function in the driver does a query very similar to a '\d' in
> the psql (you might want to try that and see if you get the same problem).  The
> query is:
>
> select relname, usename from pg_class, pg_user where relkind = 'r'
> and relname !~ '^xinv[0-9]+' and int4out(usesysid) = int4out(relowner) order by
> relname;
>
> If the relowner does not match the usesysid for all relations, you will get
> multiple entries.
> Maybe we could change the above query to use "distinct" to prevent this.

Maybe, but probably not. I remember taht the old driver used to exhibit
this behaviour for all queries that were issued from Borland products
(i.e. using BDE). Then somebody patched it and it disappeared.

So my guess is that it has creeped back in to the part where tables are
listed. It probably has nothing to do with the actual query returning
duplicate rows.

I don't remember exactly what caused it, but it was an PostODBC/BDE
interaction problem and not a case of the query returning wrong
arguments. Notice that the repeated one is always the last.

OTOH, I may be entirely wrong, as I have not tried to use it with
Borland stuff for more than a year. (but I will try again shortly).

Hannu