ODBC 7.0006 bugs - Mailing list pgsql-odbc

I found some bugs working with Access'97-ODBC 7.006 on a Win98 client and
PostgreSQL 7.1.2 on a  Redhat 6.2 server:

1) subselect side-effects:
if I run the following query:

select expr1 as field1, expr2 as field2, ... expr-j as field-j, ... expr-n
as field-n;
where expr-j is a subselect (e.g. select count(*) from table_x)

I only have results for columns 1 .. j . the driver omits columns j+1 .. n
(all the columns subsequent the subselect expression)
I can workaround the problem putting the subselect as last expression, but
there is no solution when the query contains more than one subselects

2) updating a table without row versioning:

I can't update a table with a float field (but the problem could affect also
other kind of fields) with row-versioning deactivated.
This happens because the driver executes the query :

UPDATE "d_info" SET "conf"='1'::float8  WHERE "id" = 758 AND "descr" = 'IntA
EPD-TX' AND "conf" IS NULL AND "id_dist" IS NULL AND "mean" =
'1200.51020408163'::float8 AND "sigma" = '944.166085407992'::float8 AND
"nelems" = 49 AND "ntotelems" = 1838 AND "noutrange" = 0 AND "nintrvs" = 400
AND "gp1" = 9 AND "gp2" IS NULL AND "unit" = 'ms' AND "norm" = '0.1'::float8
AND "nsigma" = 5 AND "type" = 0 AND "filterzero" = '1' AND "sql_descr" =
'SELECT descr FROM aggregates_master WHERE idagg=gp1_param;''

as you can see the driver uses all fields to identify the row even if I
specified in Access that the unique identifier is column "id".
I suppose that the problem is that "mean" and "sigma" fields (in general
every float field with a lot of decimal places) aren't the same as they're
represented in ascii format, so the backend updates 0 rows and Access
reports a strange error about a condivision violation.

If I activate the row versioning the driver works correctly (UPDATE "d_info"
SET "conf"='1'::float8  WHERE "id" = value and xmin = ...)

I suppose the same problem could affect also a DELETE statement

3) The driver setting "Use Declare/Fetch" is driver and not datasource
dependent. This means that I can't open tables from a datasource using a
normal select and from another datasource with a declare/fetch statement
I had to work around renaming psqlodbc.dll in psql2dbc.dll and building
another driver profile in the registry.
I think that moving this feature from driver to datasource settings would be
very useful. In general I think that making all settings on a per-datasource
basis
would be helpful.

4) I can't execute a query like

select 'hello world';
or
select 'hello world'::text;

I get the error
    Invalid column number in describe col.

instead

select text('hello world');

works fine.

Similarly in any query I cant simply put a string expression specifying the
field name:

    select ... , 'string expression' as str_expr, ... from ....
or
    select ... , 'string expression'::text as str_expr, ... from ....

reports the same error previously specified
instead

    select ... , 'string expression' , ... from ....        (without 'as'
clause)

or

    select ... , text('string expression') as str_expr, ... from ....
(with
function-like explicit typecasting)

works fine.


5) The odbc connection logfile generated contains the password in a plain
text format.
I think this could be a security violation and should be removed.


Any comment is appreciated

Regards,
    David Ciarniello