RE: ODBC 7.0006 bugs - Mailing list pgsql-odbc

Hello,
    1) Could be the Jet query handler is throwing up over this. Try as a
pass through query
    2) This is so that Access can tell wether some one else has altered
the record you are working on. If you don't use row versioning it needs to
look to see if any fields have changed (which creates the problem you
described). Also Access can be a bit funny about text/memo fields.
    3) I can see you're point. However I tend not to use DSN's but
rather connection strings so its helpful to be able to turn on logging with
out editing the program.
    4) Are you using Jet for this? Select 'Hello World'; works fine in
psql.It also works just fine in Access query builder (although select 'hello
world'::text doesn't). How are you entering this query?  Also I believe
typecasting by ::text is a Postgresism. I think the SQL'92 way is
CAST('Hello World' AS text). Neither works with Access queries (AFAIK). In
Access something like CStr('Hello World') uses the VB string conversion
routine and should be applicable most places (NOT pass through queries
though).
    5) I disagree. If I'm having problems connecting I want to see all
the options in the connection string. Don't log when you're not debugging,
it slows everything down.
- Stuart

> -----Original Message-----
> From:    David Ciarniello [SMTP:brainlost@rocketmail.com]
> Sent:    Thursday, July 05, 2001 10:50 AM
> To:    pgsql-odbc@postgresql.org
> Subject:    ODBC 7.0006 bugs
>
> 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
>
>
>
>
>
>
>