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
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
would be helpful.
4) I can't execute a query like
select 'hello world';
select 'hello world'::text;
I get the error
Invalid column number in describe col.
select text('hello world');
Similarly in any query I cant simply put a string expression specifying the
select ... , 'string expression' as str_expr, ... from ....
select ... , 'string expression'::text as str_expr, ... from ....
reports the same error previously specified
select ... , 'string expression' , ... from .... (without 'as'
select ... , text('string expression') as str_expr, ... from ....
function-like explicit typecasting)
5) The odbc connection logfile generated contains the password in a plain
I think this could be a security violation and should be removed.
Any comment is appreciated
pgsql-odbc by date
|Next:||From: Hiroshi Inoue||Date: 2001-07-05 23:44:52|
|Subject: Re: ODBC 7.0006 bugs|
|Previous:||From: Cedar Cox||Date: 2001-07-05 08:34:43|
|Subject: Re: non-us datestyle |