Thread: RE: [INTERFACES] Foreign Keys
Here is the psqlodbc.log file. The first time the form opens, it correctly
opens the first rentalorder. I immediately moved to the new record in
Access which opens a message box with an ODBC error (no description). I the
blank rental order looks okay but the rental order lines records all have
errors. Even if I move back to a valid record, the order lines will show
only errors.
<<psqlodbc_270.log>>
-----Original Message-----
From: Byron Nikolaidis [SMTP:byronn@insightdist.com]
Sent: Tuesday, March 02, 1999 12:52 PM
To: Michael Davis
Cc: 'pgsql-interfaces@postgreSQL.org'
Subject: Re: [INTERFACES] Foreign Keys
Michael Davis wrote:
> I have narrowed the problem down a little. Here is what I have
discovered
> so far:
>
> - sql.log (odbc trace facility) has no references to primary
or
> foreign keys
>
> - if I open the form on an exising record, it works great
>
> - if I open the form on a new record, an error occurs in a
select
> statement that basically locks up the subform
>
> - the error occurs on "select orderlineid from orderlines
where
> orderid = NULL" the null is the result of being on a new or blank
record.
> Access gets the orderid of the current record, it is null. Access
gets all
> orderlineids associated with the orderid. This fails on the new
record.
> When the form is on a valid record, Access then gets the orderline
record
> for each orderlineid returned by the previous statement. This
seems to be a
> long way around getting data, but I don't have any control over
this
> interaction.
>
> It appears that I cant add new records using Access97 with
PostgreSQL as my
> database engine. Has anyone used Access97 or other similar front
end tools
> to interface with a PostgreSQL database? Any suggestions on what
I might
> try next to work around this problem?
>
>
Logs, logs, logs (see my last response). I don't know how else to
put it!
Its next to impossible to figure things out without the logs.
Byron
Attachment
Michael Davis wrote:
> Here is the psqlodbc.log file. The first time the form opens, it correctly
> opens the first rentalorder. I immediately moved to the new record in
> Access which opens a message box with an ODBC error (no description). I the
> blank rental order looks okay but the rental order lines records all have
> errors. Even if I move back to a valid record, the order lines will show
> only errors.
>
Here's what I see. The first problems are the permission denied errors. It may be due to the
uid=admin. You could probably set the uid to something more reasonable in your connect string
in Access.
conn=153944104, query='SELECT "logvalues"."id" FROM "logvalues" '
ERROR from backend during send_query: 'ERROR: logvalues: Permission denied.'
conn=153944104, query='SELECT "memberid" ,"firstname" ,"lastname" FROM "membership" ORDER BY
"lastname" ,"firstname" '
ERROR from backend during send_query: 'ERROR: membership: Permission denied.'
conn=153944104, query='SELECT "memberid" ,"firstname" ,"middleinitial" ,"lastname" ,"surname"
,"birthdate" ,"statusid" ,"accountbalance" ,"duesbalance" ,"seminarbalance" ,"selected"
,"otheritemsdiscoursesplusone" ,"originalcontactid" ,"gender" ,"email" ,"optname"
,"recordtype" FROM "membership" '
ERROR from backend during send_query: 'ERROR: membership: Permission denied.'
conn=153944104, query='SELECT "membership"."memberid" ,"membership"."firstname"
,"membership"."lastname" ,"memberservices"."serviceid" FROM "memberservices","membership"
WHERE (("memberservices"."serviceid" IN (8 ,12 ) ) AND ("membership"."memberid" =
"memberservices"."memberid" ) ) ORDER BY "membership"."lastname" ,"membership"."firstname" '
ERROR from backend during send_query: 'ERROR: memberservices: Permission denied.'
conn=153944104,
SQLDriverConnect(out)='DSN=PostgreSQL;DATABASE=mp;SERVER=192.168.97.2;PORT=5432;UID=Admin;PWD=;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=1;CONNSETTINGS='
conn=153944104, query='SELECT "memberid" ,"firstname" ,"middleinitial" ,"lastname" ,"surname"
,"birthdate" ,"statusid" ,"accountbalance" ,"duesbalance" ,"seminarbalance" ,"selected"
,"otheritemsdiscoursesplusone" ,"originalcontactid" ,"gender" ,"email" ,"optname"
,"recordtype" FROM "membership" '
ERROR from backend during send_query: 'ERROR: membership: Permission denied.'
STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while executing the query'
The second problem is this:
conn=153237224, query='SELECT "RentalOrders"."rentalorderlinesid" FROM "rentalorderlines"
"RentalOrders" WHERE ("rentalorderid" = 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'
Since postgres will not recognize the syntax (where 'col' = null)... it only recognizes
"isnull". I was hoping someone would have added the ability for the parser to handle this at
some point (Hey Dave, maybe you could contribute something here man :-).
Byron
> The second problem is this:
>
> conn=153237224, query='SELECT "RentalOrders"."rentalorderlinesid" FROM "rentalorderlines"
> "RentalOrders" WHERE ("rentalorderid" = 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'
>
>
> Since postgres will not recognize the syntax (where 'col' = null)... it only recognizes
> "isnull". I was hoping someone would have added the ability for the parser to handle this at
> some point (Hey Dave, maybe you could contribute something here man :-).
>
>
> Byron
>
I do not poke my nose into odbc as I have nothing to do with it, but
this parsing problem caught my attention. To me, 'isnull' and '= null'
are the same token. So I fixed the aforementioned problem like this:
1. In backend/parser.scan.l, find the line that reads:
identifier {letter}{letter_or_digit}*
and put this following macro after it:
isnull ={space}*(null|NULL)
it does not matter where before the beginning of the rules section you
will put it, but it is better to keep related things close to each other.
2. In the same file, find the line that reads:
{identifier} {
and insert the following rule before it
{isnull} {
int i;
ScanKeyword *keyword;
for(i = 0; yytext[i]; i++)
if (isascii((unsigned char)yytext[i]) &&
isupper(yytext[i]))
yytext[i] = tolower(yytext[i]);
if (i >= NAMEDATALEN)
yytext[NAMEDATALEN-1] = '\0';
keyword = ScanKeywordLookup((char*)"isnull");
return keyword->value;
}
3. run make && make install in the src directory, then stop and restart postmaster
I understand it is an ugly hack but if you are desperate to get things running ...
If you don't try to use it as NULL = 'col', you should be OK.
--Gene
Byron Nikolaidis wrote:
> Michael Davis wrote:
>
> > Here is the psqlodbc.log file. The first time the form opens, it correctly
> > opens the first rentalorder. I immediately moved to the new record in
> > Access which opens a message box with an ODBC error (no description). I the
> > blank rental order looks okay but the rental order lines records all have
> > errors. Even if I move back to a valid record, the order lines will show
> > only errors.
> >
> The second problem is this:
>
> conn=153237224, query='SELECT "RentalOrders"."rentalorderlinesid" FROM "rentalorderlines"
> "RentalOrders" WHERE ("rentalorderid" = 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'
>
> Since postgres will not recognize the syntax (where 'col' = null)... it only recognizes
> "isnull". I was hoping someone would have added the ability for the parser to handle this at
> some point (Hey Dave, maybe you could contribute something here man :-).
The second problem is affecting my system as well.. do you think that there's a way around it without waiting for
someoneto patch the PostgreSQL code ?
--
C'ya!
Valerio Santinelli a.k.a. TANiS
[tanis@mediacom.it]+:+[http://www.mediacom.it/~tanis]
Valerio Santinelli ha scritto:
> Byron Nikolaidis wrote:
>
> > Michael Davis wrote:
> >
> > > Here is the psqlodbc.log file. The first time the form opens, it correctly
> > > opens the first rentalorder. I immediately moved to the new record in
> > > Access which opens a message box with an ODBC error (no description). I the
> > > blank rental order looks okay but the rental order lines records all have
> > > errors. Even if I move back to a valid record, the order lines will show
> > > only errors.
> > >
> > The second problem is this:
> >
> > conn=153237224, query='SELECT "RentalOrders"."rentalorderlinesid" FROM "rentalorderlines"
> > "RentalOrders" WHERE ("rentalorderid" = 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'
> >
> > Since postgres will not recognize the syntax (where 'col' = null)... it only recognizes
> > "isnull". I was hoping someone would have added the ability for the parser to handle this at
> > some point (Hey Dave, maybe you could contribute something here man :-).
>
> The second problem is affecting my system as well.. do you think that there's a way around it without waiting for
someoneto patch the PostgreSQL code ?
>
> --
>
>
> Valerio Santinelli a.k.a. TANiS
> [tanis@mediacom.it]+:+[http://www.mediacom.it/~tanis]
When I started to use Access97 with PostgreSQL I had this problem too and I made a change to
./src/backend/parser/gram.y
to make Access recognize the syntax "column_name" = NULL and it works fine now.
Here attached the patch to gram.y.
Buona fortuna.
-Jose'-
"No other success in life can compensate for failure in the home" (David O. McKay)
> Valerio Santinelli ha scritto:
>
> > Byron Nikolaidis wrote:
> >
> > > Michael Davis wrote:
> > >
> > > > Here is the psqlodbc.log file. The first time the form opens, it correctly
> > > > opens the first rentalorder. I immediately moved to the new record in
> > > > Access which opens a message box with an ODBC error (no description). I the
> > > > blank rental order looks okay but the rental order lines records all have
> > > > errors. Even if I move back to a valid record, the order lines will show
> > > > only errors.
> > > >
> > > The second problem is this:
> > >
> > > conn=153237224, query='SELECT "RentalOrders"."rentalorderlinesid" FROM "rentalorderlines"
> > > "RentalOrders" WHERE ("rentalorderid" = 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'
> > >
> > > Since postgres will not recognize the syntax (where 'col' = null)... it only recognizes
> > > "isnull". I was hoping someone would have added the ability for the parser to handle this at
> > > some point (Hey Dave, maybe you could contribute something here man :-).
> >
> > The second problem is affecting my system as well.. do you think that there's a way around it without waiting for
someoneto patch the PostgreSQL code ?
> >
> > --
> >
> >
> > Valerio Santinelli a.k.a. TANiS
> > [tanis@mediacom.it]+:+[http://www.mediacom.it/~tanis]
>
> When I started to use Access97 with PostgreSQL I had this problem too and I made a change to
./src/backend/parser/gram.y
> to make Access recognize the syntax "column_name" = NULL and it works fine now.
> Here attached the patch to gram.y.
>
> Buona fortuna.
>
> -Jose'-
> "No other success in life can compensate for failure in the home" (David O. McKay)
Sorry, I forgot attachment.
-Jose'-
*** ./src/backend/parser/gram.old.y Wed Mar 3 15:03:27 1999
--- ./src/backend/parser/gram.y Wed Dec 2 12:54:12 1998
***************
*** 3323,3328 ****
--- 3323,3330 ----
{ $$ = makeA_Expr(OP, "<", $1, $3); }
| a_expr '>' a_expr
{ $$ = makeA_Expr(OP, ">", $1, $3); }
+ | a_expr '=' NULL_P
+ { $$ = makeA_Expr(ISNULL, NULL, $1, NULL); }
| a_expr '=' a_expr
{ $$ = makeA_Expr(OP, "=", $1, $3); }
| ':' a_expr
jose' soares wrote:
> > Valerio Santinelli ha scritto:
> >
> > > Byron Nikolaidis wrote:
> > >
> > > > Michael Davis wrote:
> > > >
> > > > > Here is the psqlodbc.log file. The first time the form opens, it correctly
> > > > > opens the first rentalorder. I immediately moved to the new record in
> > > > > Access which opens a message box with an ODBC error (no description). I the
> > > > > blank rental order looks okay but the rental order lines records all have
> > > > > errors. Even if I move back to a valid record, the order lines will show
> > > > > only errors.
> > > > >
> > > > The second problem is this:
> > > >
> > > > conn=153237224, query='SELECT "RentalOrders"."rentalorderlinesid" FROM "rentalorderlines"
> > > > "RentalOrders" WHERE ("rentalorderid" = 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'
> > > >
> > > > Since postgres will not recognize the syntax (where 'col' = null)... it only recognizes
> > > > "isnull". I was hoping someone would have added the ability for the parser to handle this at
> > > > some point (Hey Dave, maybe you could contribute something here man :-).
> > >
> > > The second problem is affecting my system as well.. do you think that there's a way around it without waiting for
someoneto patch the PostgreSQL code ?
> > >
> > > --
> > >
> > >
> > > Valerio Santinelli a.k.a. TANiS
> > > [tanis@mediacom.it]+:+[http://www.mediacom.it/~tanis]
> >
> > When I started to use Access97 with PostgreSQL I had this problem too and I made a change to
./src/backend/parser/gram.y
> > to make Access recognize the syntax "column_name" = NULL and it works fine now.
> > Here attached the patch to gram.y.
> >
> > Buona fortuna.
> >
> > -Jose'-
> > "No other success in life can compensate for failure in the home" (David O. McKay)
>
> Sorry, I forgot attachment.
> -Jose'-
>
> ------------------------------------------------------------------------
> *** ./src/backend/parser/gram.old.y Wed Mar 3 15:03:27 1999
> --- ./src/backend/parser/gram.y Wed Dec 2 12:54:12 1998
> ***************
> *** 3323,3328 ****
> --- 3323,3330 ----
> { $$ = makeA_Expr(OP, "<", $1, $3); }
> | a_expr '>' a_expr
> { $$ = makeA_Expr(OP, ">", $1, $3); }
> + | a_expr '=' NULL_P
> + { $$ = makeA_Expr(ISNULL, NULL, $1, NULL); }
> | a_expr '=' a_expr
> { $$ = makeA_Expr(OP, "=", $1, $3); }
> | ':' a_expr
Thanks. That was what i was looking for. It's been real useful!
I also put this other line to handle the inverse case (NULL = field)
| NULL_P '=' a_expr
{ $$ = makeA_Expr(ISNULL, NULL, $3, NULL);
--
C'ya!
Valerio Santinelli a.k.a. TANiS
[tanis@mediacom.it]+:+[http://www.mediacom.it/~tanis]
> Thanks. That was what i was looking for. It's been real useful!
> I also put this other line to handle the inverse case (NULL = field)
>
> | NULL_P '=' a_expr
> { $$ = makeA_Expr(ISNULL, NULL, $3, NULL); }
This leads to a shift/reduce conflict in yacc. I've been working on this
a bit, and have modified gram.y (so far just in my local copy) to allow
Jose's syntax and to allow the same syntax in a constraint declaration.
But this variant may be tougher to do correctly...
- Tom
"Thomas G. Lockhart" wrote:
> > Thanks. That was what i was looking for. It's been real useful!
> > I also put this other line to handle the inverse case (NULL = field)
> >
> > | NULL_P '=' a_expr
> > { $$ = makeA_Expr(ISNULL, NULL, $3, NULL); }
>
> This leads to a shift/reduce conflict in yacc. I've been working on this
> a bit, and have modified gram.y (so far just in my local copy) to allow
> Jose's syntax and to allow the same syntax in a constraint declaration.
> But this variant may be tougher to do correctly...
I don't exactly know what you mean, but it's working fine here.
--
C'ya!
Valerio Santinelli a.k.a. TANiS
[tanis@mediacom.it]+:+[http://www.mediacom.it/~tanis]