Thread
-
Odbc parser error
jose' soares <sferac@bo.nettuno.it> — 1998-09-17T08:34:51Z
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 -
Re: [INTERFACES] Odbc parser error
Byron Nikolaidis <byronn@insightdist.com> — 1998-09-17T20:33:31Z
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 -
Re: [HACKERS] Re: [INTERFACES] Odbc parser error
Billy G. Allie <bill.allie@mug.org> — 1998-09-17T23:37:22Z
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 | -
Re: [HACKERS] Re: [INTERFACES] Odbc parser error
Byron Nikolaidis <byronn@insightdist.com> — 1998-09-18T13:23:26Z
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
-
Re: [HACKERS] Re: [INTERFACES] Odbc parser error
Herouth Maoz <herouth@oumail.openu.ac.il> — 1998-09-21T23:41:03Z
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 -
Re: [HACKERS] Re: [INTERFACES] Odbc parser error
Jose Soares <jose@sferacarta.com> — 1998-10-22T08:13:15Z
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' -
Re[2]: [HACKERS] Re: [INTERFACES] Odbc parser error
jose' soares <sferac@bo.nettuno.it> — 1998-10-26T16:22:25Z
Hi all, JS> Yes, I would to suggest a way to solve this problem because in Access we JS> can't link two tables JS> by a numeric field, for example: JS> I have 2 tables: JS> ORDER_MASTER: ORDER_DETAIL: JS> -------------- -------------- JS> numero SERIAL <---> numero SERIAL JS> ... ... JS> When I try to create a new ORDER, the connection goes down with message: JS> # conn=75511800, query='SELECT "order_detail"."numero" FROM JS> "order_detail" WHERE ("numero" = NULL )' JS> # ERROR from backend during send_query: 'ERROR: parser: parse error at JS> or near "null"' JS> I know this is not standard but Access understand both syntaxes (i.e.: JS> SELECT * FROM table WHERE field IS NULL; = SELECT * FROM table JS> WHERE field = NULL; JS> SELECT * FROM table WHERE field IS NOT NULL; = SELECT * FROM table JS> WHERE field <> NULL; JS> Why not to get PostgreSQL to understand it also ? Done. I modify the pgsql/src/backend/gram.y at line 3318 I inserted the following two lines: <DELETED> 3318 | a_expr '=' NULL_P 3319 { $$ = makeA_Expr(ISNULL, NULL,$1, NULL); 3320 | a_expr '=' a_expr 3321 { $$ = makeA_Expr(OP, "=", $1, $3); } <DELETED> now SELECT * FROM table WHERE field IS NULL; is the same as: SELECT * FROM table WHERE field = NULL; How about to add it to official release ? ------------------------------------------------------------------- I think I found a bug using IS NULL operator: hygea=> \d comuni Table = comuni +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | istat | char() not null | 6 | | nome | char() | 50 | | provincia | char() | 2 | | codice_fiscale | char() | 4 | | cap | char() | 5 | | regione | char() | 3 | | distretto | char() | 4 | +----------------------------------+----------------------------------+-------+ hygea=> select * from comuni where COMUNI is null; ^^^^^^------------------COMUNI is not a field but the table name. istat|nome|provincia|codice_fiscale|cap|regione|distretto -----+----+---------+--------------+---+-------+--------- (0 rows) Jose' -
Re: [HACKERS] Re: [INTERFACES] Odbc parser error
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-10-26T17:28:25Z
> 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 -
Re[2]: [HACKERS] Re: [INTERFACES] Odbc parser error
jose' soares <sferac@bo.nettuno.it> — 1998-10-27T15:01:11Z
Hello Thomas, lunedì, 26 ottobre 98, you wrote: >> 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 ? TGL> Or, how about insisting that your commercial software suppliers conform TGL> to one of the simplest and clearest part of the SQL standard they claim TGL> to support? Instead of using those suppliers who insist on making small TGL> and large incompatibilities in their popular software packages to ensure TGL> that only their own products can interoperate with them? You are right Tom, but you know there is no way to have something from those bastards, I prefer to talk with a wall. TGL> *slap* Ow! Ok, Ok! TGL> It looks like a nice patch. But it is too late to get it into v6.4. It TGL> looks like a good addition to v6.5... TGL> *slap* OW! OK! TGL> In fact, it is such a nice feature that I'm sure it will fit nicely into TGL> v6.4.1, and you are welcome to submit a patchset to post on TGL> postgresql.org to fix v6.4. TGL> *grin* TGL> It would be best to include patches for both gram.y and a full gram.c so TGL> that users may not have to rebuild the parser. Also, if we run into TGL> parser syntax conflicts in the future it would be a candidate for TGL> removal, I suppose. Should I send the patch now or after v6.4 is released ? Jose' -
Re: [HACKERS] Re: [INTERFACES] Odbc parser error
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-10-27T15:55:47Z
> 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