Re: [HACKERS] Re: [INTERFACES] Odbc parser error - Mailing list pgsql-interfaces

Byron Nikolaidis wrote:
>
> Jose' Soares (Sferacarta Software) wrote:
>
> > Hi all,
> >
> > Seems that ODBC driver have some problems while it translate Access
> > commands.
> > I created a form with a subform joined by two columns.
> >
> > after the Access Addnew event, log file returns the following error:
> > -----------------------------------------------------------------------
> > conn=75511800, query='SELECT "risanamento"."oid" FROM "risanamento" WHERE (("distretto" =  '' ) AND ("progressivo"
= NULL ) ) ' 
> > ERROR from backend during send_query: 'ERROR:  parser: parse error at or near "null"'
> > STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while executing the query'
> > ------------------------------------------------------------------------
> > NB: Note that parser translate ("progressivo" =  NULL) instead of
> > ("progressivo" IS NULL).
> >
> >
>
> Yes, the NULL works for parameters of an update statement, where Access would specify a statement such as "update
tableset param = 
> ? where x = 1".  But it doesn't work in a select statement.  I don't think I have much to work with here.  The
statementcomes in as 
> something like "select * from table where x = ?".  I have to replace the ? with something.  On updates, 'NULL' works
fine.
>
> I'm not sure what to do about this.  On other dbms, parameter passing is handled through a separate protocol to the
backend,usually 
> after a prepare statement, so on these its no problem to send a null, or large amounts of ascii/binary data, without
havingto worry 
> about direct substitution into the sql string or hitting the upper limit of the statement string.  I think until
Postgreshas such 
> as protocol for parameter substitution/passing, it will be difficult to fix this problem.
>
> Any suggestions?
>
Yes, I would to suggest a way to solve this problem because in Access we
can't link two tables
by a numeric field, for example:

I have 2 tables:

ORDER_MASTER:        ORDER_DETAIL:
--------------        --------------
numero    SERIAL    <--->    numero    SERIAL
...            ...

When I try to create a new ORDER, the connection goes down with message:

# conn=75511800, query='SELECT "order_detail"."numero" FROM
"order_detail" WHERE ("numero" =  NULL )'
# ERROR from backend during send_query: 'ERROR:  parser: parse error at
or near "null"'


I know this is not standard but Access understand both syntaxes (i.e.:

SELECT * FROM table WHERE field IS NULL;      =  SELECT * FROM table
WHERE field = NULL;
SELECT * FROM table WHERE field IS NOT NULL;  =  SELECT * FROM table
WHERE field <> NULL;

Why not to get PostgreSQL to understand it also ?

Thank you for any help
        Jose'