pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedOdbc parser error
8+ messages / 6 participants
[nested] [flat]
* Odbc parser error
@ 1998-09-17 08:34 Sferacarta Software <sferac@bo.nettuno.it>
0 siblings, 1 reply; 8+ messages in thread
From: Sferacarta Software @ 1998-09-17 08:34 UTC (permalink / raw)
To: pgsql-interfaces
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).
I suspect that problem is linked with column type, in fact
"progressivo" is an int4. I changed this field to char and now it
works:
----------------------------------------------------------------------------------
conn=92477940, query='SELECT "risanamento"."oid" FROM "risanamento" WHERE (("distretto" = '' ) AND ("progressivo" = '' ) ) '
------------------------------------------------------------------------------------
"progressivo" is a counter field and it must be numeric.
Any hints will be appreciate.
Thank you in advance,
Marco Pollachini mailto:sferac@bo.nettuno.it
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [INTERFACES] Odbc parser error
@ 1998-09-17 20:33 Byron Nikolaidis <byronn@insightdist.com>
parent: Sferacarta Software <sferac@bo.nettuno.it>
0 siblings, 3 replies; 8+ messages in thread
From: Byron Nikolaidis @ 1998-09-17 20:33 UTC (permalink / raw)
To: Sferacarta Software <sferac@bo.nettuno.it>; +Cc: pgsql-interfaces@postgreSQL.org, "pgsql-hackers@hub.org" <pgsql-hackers@hub.org>
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 table set 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 statement comes 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 having to worry
about direct substitution into the sql string or hitting the upper limit of the statement string. I think until Postgres has such
as protocol for parameter substitution/passing, it will be difficult to fix this problem.
Any suggestions?
Byron
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [HACKERS] Re: [INTERFACES] Odbc parser error
@ 1998-09-17 23:37 Billy G. Allie <Bill.Allie@mug.org>
parent: Byron Nikolaidis <byronn@insightdist.com>
2 siblings, 1 reply; 8+ messages in thread
From: Billy G. Allie @ 1998-09-17 23:37 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; +Cc: pgsql-interfaces; pgsql-hackers@postgreSQL.org
Byron Nikolaidis <byronn@insightdist.com> wrote:
> 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.
[...]
> > 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 table set 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 statement comes 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 having to worry about direct substitution into
> the sql string or hitting the upper limit of the statement string. I think
> until Postgres has such as protocol for parameter substitution/passing, it
> will be difficult to fix this problem.
>
> Any suggestions?
>
> Byron
How about subsitution any occurance of /=[ \t]*?/ with "IS NULL". This should
work for both select and update statements.
--
____ | Billy G. Allie | Domain....: Bill.Allie@mug.org
| /| | 7436 Hartwell | Compuserve: 76337,2061
|-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com
|/ |LLIE | (313) 582-1540 |
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [HACKERS] Re: [INTERFACES] Odbc parser error
@ 1998-09-18 13:23 Byron Nikolaidis <byronn@insightdist.com>
parent: Billy G. Allie <Bill.Allie@mug.org>
0 siblings, 0 replies; 8+ messages in thread
From: Byron Nikolaidis @ 1998-09-18 13:23 UTC (permalink / raw)
To: Billy G. Allie <Bill.Allie@mug.org>; +Cc: pgsql-interfaces; pgsql-hackers@postgreSQL.org
Billy G. Allie wrote:
> How about subsitution any occurance of /=[ \t]*?/ with "IS NULL". This should
> work for both select and update statements.
> --
Yes, sure, it could be done, assuming I could get a regex library for VC++. But I
think it would be more of a kludge than a solution. Even the current substitution
of the ? with the parameter data is a kludge. The frontend shouldn't be rewriting
the sql string to stick parameters in.
The real point of the whole thing is that if the backend recognized parameters and
there was a protocol for passing the data over, we would be able to do some cool
things we have never been able to do. Not just passing NULL, but also passing
larger parameter data without worrying about running into the upper limit of the
statement string. It would also be easier to send binary data. And, assuming the
protocol allowed it, you could retrieve information from the backend about a
particular parameter, such as its precision, data type, nullablity, etc.
And its not just ODBC that would benefit. JDBC has the same semantics. And I
believe Embedded SQL does too.
Byron
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [HACKERS] Re: [INTERFACES] Odbc parser error
@ 1998-09-21 23:41 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Byron Nikolaidis <byronn@insightdist.com>
2 siblings, 0 replies; 8+ messages in thread
From: Herouth Maoz @ 1998-09-21 23:41 UTC (permalink / raw)
To: pgsql-interfaces; pgsql-hackers@postgreSQL.org
Byron Nikolaidis <byronn@insightdist.com> wrote:
> Yes, the NULL works for parameters of an update statement, where Access would
> specify a statement such as "update table set 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 statement comes 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 having to worry about direct substitution into
> the sql string or hitting the upper limit of the statement string. I think
> until Postgres has such as protocol for parameter substitution/passing, it
> will be difficult to fix this problem.
Well, before we jump high, I think this stems from a long-standing Postgres
problem - which I mentioned in the past, and I guess I'll continue to
mention, until we finally buy Oracle (and get a whole different set of
problems).
The problem is that in Postgres, NULL=NULL gives false.
As simple as that.
I am appaled to hear that this is still the situation. I thought by 6.4 (I
only have 6.2.1), the problem would probably be looked into, but I guess it
wasn't.
This NULL=NULL is FALSE problem explains why there is no problem in updates
(where the semantics of "=" is assignment, not comparison!).
The problem causes many other problems - like the inability to sort by two
fields when the first field may contain nulls. Why? Because sorting by two
fields means that when the values of the first fields in two rows are
compared and found equal, the second field is used for the comparison. But
if nulls are allowed, two rows with NULL in the first field are not
considered to have the same value! So, despite the nulls being sort of
"grouped together", their secondary sort fields will come out with an
arbitrary order!
This problem stems from Postgres's global definition that when you have
operand1 operator operand2
and operand1 or operand2 are null, the result will always be NULL. That's
nice when you are trying to add 5 to a column, and expect all places where
there was NULL before to stay NULL, because NULL signifies "no data here".
Since the comparison operator is just an operator, the result of the
comparison is not really FALSE, but NULL. NULL, however, is interpreted
almost as a "false". To show this, here is an example of comparison:
testing=> create table test6 (val int);
CREATE
testing=> copy test6 from stdin;
Enter info followed by a newline
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> \N
>> 4
>> \.
testing=> select ( val = 2 ) from test6;
?column?
--------
f
t
f
(4 rows)
By the way, the reason that I said "almost" is that the NOT boolean
operator, just like the binary operators I've discussed, returns NULL when
applied to NULL. Which means that NOT ( NULL = something ) will give you
the same result as NULL = something...
testing=> select ( val ) from test6 where NOT ( val = 2 );
val
---
1
4
(2 rows)
In short, something needs to be done about the semantics of the equality
operator. It should be treated as a special case - in order to maintain the
logic of logic, as well...
Suggested semantics:
Perhaps the general solution is always to treat NULLS as false in boolean
context, and have the equality operator return TRUE in the case where both
its operands are NULL.
Herouth
--
Herouth Maoz, B.Sc. Work: herouth@oumail.openu.ac.il
Home: herutma@telem.openu.ac.il
HOME PAGE: http://telem.openu.ac.il/~herutma/
Internet technical assistant Open University, Telem Project
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [HACKERS] Re: [INTERFACES] Odbc parser error
@ 1998-10-22 08:13 Jose' Soares <jose@sferacarta.com>
parent: Byron Nikolaidis <byronn@insightdist.com>
2 siblings, 1 reply; 8+ messages in thread
From: Jose' Soares @ 1998-10-22 08:13 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; +Cc: Sferacarta Software <sferac@bo.nettuno.it>; pgsql-interfaces@postgresql.org, "pgsql-hackers@hub.org" <pgsql-hackers@hub.org>
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 table set 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 statement comes 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 having to worry
> about direct substitution into the sql string or hitting the upper limit of the statement string. I think until Postgres has 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'
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [HACKERS] Re: [INTERFACES] Odbc parser error
@ 1998-10-26 17:28 Thomas G. Lockhart <lockhart@alumni.caltech.edu>
parent: Jose' Soares <jose@sferacarta.com>
0 siblings, 1 reply; 8+ messages in thread
From: Thomas G. Lockhart @ 1998-10-26 17:28 UTC (permalink / raw)
To: Sferacarta Software <sferac@bo.nettuno.it>; +Cc: Byron Nikolaidis <byronn@insightdist.com>; pgsql-hackers@postgreSQL.org
> JS> Yes, I would to suggest a way to solve this problem because in
> JS> Access we can't link two tables by a numeric field
> JS> # conn=75511800, query='SELECT "order_detail"."numero" FROM
> JS> "order_detail" WHERE ("numero" = NULL )'
> JS> I know this is not standard but Access understand both syntaxes
> JS> SELECT * FROM table WHERE field IS NULL;
> JS> SELECT * FROM table WHERE field = NULL;
> JS> Why not to get PostgreSQL to understand it also ?
> Done. I modify the pgsql/src/backend/gram.y
> How about to add it to official release ?
Or, how about insisting that your commercial software suppliers conform
to one of the simplest and clearest part of the SQL standard they claim
to support? Instead of using those suppliers who insist on making small
and large incompatibilities in their popular software packages to ensure
that only their own products can interoperate with them?
*slap* Ow! Ok, Ok!
It looks like a nice patch. But it is too late to get it into v6.4. It
looks like a good addition to v6.5...
*slap* OW! OK!
In fact, it is such a nice feature that I'm sure it will fit nicely into
v6.4.1, and you are welcome to submit a patchset to post on
postgresql.org to fix v6.4.
*grin*
It would be best to include patches for both gram.y and a full gram.c so
that users may not have to rebuild the parser. Also, if we run into
parser syntax conflicts in the future it would be a candidate for
removal, I suppose.
- Tom
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [HACKERS] Re: [INTERFACES] Odbc parser error
@ 1998-10-27 15:55 Thomas G. Lockhart <lockhart@alumni.caltech.edu>
parent: Thomas G. Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 0 replies; 8+ messages in thread
From: Thomas G. Lockhart @ 1998-10-27 15:55 UTC (permalink / raw)
To: Sferacarta Software <sferac@bo.nettuno.it>; +Cc: Byron Nikolaidis <byronn@insightdist.com>; pgsql-hackers@postgresql.org
> Should I send the patch now or after v6.4 is released ?
I would suggest waiting, since that will ensure that it doesn't get
lost. Try sending it in ~1 week after release and we'll post as a patch
and put it into the main tree.
- Tom
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~1998-10-27 15:55 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-09-17 08:34 Odbc parser error Sferacarta Software <sferac@bo.nettuno.it>
1998-09-17 20:33 ` Byron Nikolaidis <byronn@insightdist.com>
1998-09-17 23:37 ` Billy G. Allie <Bill.Allie@mug.org>
1998-09-18 13:23 ` Byron Nikolaidis <byronn@insightdist.com>
1998-09-21 23:41 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-10-22 08:13 ` Jose' Soares <jose@sferacarta.com>
1998-10-26 17:28 ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
1998-10-27 15:55 ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox