pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedForeign Keys
24+ messages / 8 participants
[nested] [flat]
* Foreign Keys
@ 1999-02-25 15:32 Michael Davis <michael.davis@prevuenet.com>
1999-02-25 15:37 ` Re: [INTERFACES] Foreign Keys Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 1 reply; 24+ messages in thread
From: Michael Davis @ 1999-02-25 15:32 UTC (permalink / raw)
To: pgsql-interfaces; pgsql postgres <pgsql-sql@postgreSQL.org>; pgsql-hackers@postgreSQL.org
What is the possibility of recording foreign keys (a partial foreign key
implementation) in the database such that the ODBC interface can correctly
report them to client applications? This would require an enhancement to
Postgres and to the ODBC driver for Postgres. I have a problem with Access
97 not working properly with forms that contain sub forms representing
parent/child or master/detail tables. For example: orders and orderlines.
I think this is caused by the lack of foreign key support. I am basing this
conclusion on the SQL statement that Access sent through ODBC when looking
up records for the sub form. The where clause of the SQL statement was
based on the primary key of the child or detail table. The where clause
needs to be based on the column that is linked to the parent or master
table. I already have indexes installed on both the primary key and the
field that links to the master/parent table.
I am willing to explore a work around and/or help with these enhancements.
This issue, at least for the moment, appears to be a show stopper for me.
Any Suggestions?
Thanks, Michael
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [INTERFACES] Foreign Keys
1999-02-25 15:32 Foreign Keys Michael Davis <michael.davis@prevuenet.com>
@ 1999-02-25 15:37 ` Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 0 replies; 24+ messages in thread
From: Byron Nikolaidis @ 1999-02-25 15:37 UTC (permalink / raw)
To: Michael Davis <michael.davis@prevuenet.com>; +Cc: pgsql-interfaces; pgsql postgres <pgsql-sql@postgreSQL.org>; pgsql-hackers@postgreSQL.org
Michael Davis wrote:
> What is the possibility of recording foreign keys (a partial foreign key
> implementation) in the database such that the ODBC interface can correctly
> report them to client applications? This would require an enhancement to
> Postgres and to the ODBC driver for Postgres. I have a problem with Access
> 97 not working properly with forms that contain sub forms representing
> parent/child or master/detail tables. For example: orders and orderlines.
> I think this is caused by the lack of foreign key support. I am basing this
>
The driver does support SQLForeignKeys. It does say it supports this function
in SQLFunctions.
However, the current implementation is based on the SPI/trigger stuff from the
pg_trigger table. If you don't have any trigger info, then SQLForeignKeys will
not report anything. This is most likely the problem.
Byron
^ permalink raw reply [nested|flat] 24+ messages in thread
* RE: [INTERFACES] Foreign Keys
@ 1999-02-25 16:45 Michael Davis <michael.davis@prevuenet.com>
0 siblings, 0 replies; 24+ messages in thread
From: Michael Davis @ 1999-02-25 16:45 UTC (permalink / raw)
To: pgsql-interfaces; pgsql postgres <pgsql-sql@postgreSQL.org>; pgsql-hackers@postgreSQL.org
Excellent! I will start working on it.
-----Original Message-----
From: Byron Nikolaidis [SMTP:byronn@insightdist.com]
Sent: Thursday, February 25, 1999 8:38 AM
To: Michael Davis
Cc: 'pgsql-interfaces@postgreSQL.org'; pgsql postgres;
pgsql-hackers@postgreSQL.org
Subject: Re: [INTERFACES] Foreign Keys
Michael Davis wrote:
> What is the possibility of recording foreign keys (a partial
foreign key
> implementation) in the database such that the ODBC interface can
correctly
> report them to client applications? This would require an
enhancement to
> Postgres and to the ODBC driver for Postgres. I have a problem
with Access
> 97 not working properly with forms that contain sub forms
representing
> parent/child or master/detail tables. For example: orders and
orderlines.
> I think this is caused by the lack of foreign key support. I am
basing this
>
The driver does support SQLForeignKeys. It does say it supports
this function
in SQLFunctions.
However, the current implementation is based on the SPI/trigger
stuff from the
pg_trigger table. If you don't have any trigger info, then
SQLForeignKeys will
not report anything. This is most likely the problem.
Byron
^ permalink raw reply [nested|flat] 24+ messages in thread
* RE: [INTERFACES] Foreign Keys
@ 1999-02-25 20:26 Michael Davis <michael.davis@prevuenet.com>
1999-02-25 21:12 ` Re: [INTERFACES] Foreign Keys Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 1 reply; 24+ messages in thread
From: Michael Davis @ 1999-02-25 20:26 UTC (permalink / raw)
To: pgsql-interfaces
I have created the following trigger on the parent/master table (orders):
CREATE TRIGGER Orderlines_fkey
BEFORE DELETE OR UPDATE ON Orders FOR EACH ROW
EXECUTE PROCEDURE check_foreign_key ();
check_foreign_key() actually deletes rows from the orderlines table when
every a row is deleted from orders. This triggers works great. Access,
however, is still not recognizing the foreign key relationship between
orders and orderlines. Any other suggestions?
Thanks, Michael
-----Original Message-----
From: Byron Nikolaidis [SMTP:byronn@insightdist.com]
Sent: Thursday, February 25, 1999 8:38 AM
To: Michael Davis
Cc: 'pgsql-interfaces@postgreSQL.org'; pgsql postgres;
pgsql-hackers@postgreSQL.org
Subject: Re: [INTERFACES] Foreign Keys
Michael Davis wrote:
> What is the possibility of recording foreign keys (a partial
foreign key
> implementation) in the database such that the ODBC interface can
correctly
> report them to client applications? This would require an
enhancement to
> Postgres and to the ODBC driver for Postgres. I have a problem
with Access
> 97 not working properly with forms that contain sub forms
representing
> parent/child or master/detail tables. For example: orders and
orderlines.
> I think this is caused by the lack of foreign key support. I am
basing this
>
The driver does support SQLForeignKeys. It does say it supports
this function
in SQLFunctions.
However, the current implementation is based on the SPI/trigger
stuff from the
pg_trigger table. If you don't have any trigger info, then
SQLForeignKeys will
not report anything. This is most likely the problem.
Byron
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [INTERFACES] Foreign Keys
1999-02-25 20:26 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
@ 1999-02-25 21:12 ` Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 0 replies; 24+ messages in thread
From: Byron Nikolaidis @ 1999-02-25 21:12 UTC (permalink / raw)
To: Michael Davis <michael.davis@prevuenet.com>; +Cc: pgsql-interfaces
Michael Davis wrote:
> I have created the following trigger on the parent/master table (orders):
>
> CREATE TRIGGER Orderlines_fkey
> BEFORE DELETE OR UPDATE ON Orders FOR EACH ROW
> EXECUTE PROCEDURE check_foreign_key ();
>
> check_foreign_key() actually deletes rows from the orderlines table when
> every a row is deleted from orders. This triggers works great. Access,
> however, is still not recognizing the foreign key relationship between
> orders and orderlines. Any other suggestions?
>
> Thanks, Michael
>
Yeah, there is more we can try. First of all, we need to verify that Access
is actually calling SQLForeignKeys. Can you get a "trace" log of the
session? This would be the "sql.log" file which contains all api calls to
odbc. Make sure the file is clear beforehand. Then run the test. Quit
Access, turn tracing off and there's your logfile. You can send it to me.
It might also be helpful to have the commlog file (produced by the
driver)...usually "psqlodbc.log". It may have a process id number appended
to the filename so make sure you get the right one.
Byron
^ permalink raw reply [nested|flat] 24+ messages in thread
* RE: [INTERFACES] Foreign Keys
@ 1999-02-25 22:36 Michael Davis <michael.davis@prevuenet.com>
0 siblings, 0 replies; 24+ messages in thread
From: Michael Davis @ 1999-02-25 22:36 UTC (permalink / raw)
To: pgsql-interfaces
The sql.log file is over 5M. There are no references to foreign keys
anywhere in the log file. I noticed the following:
Orders 138:118 ENTER SQLExecDirect
HSTMT 0x09072230
UCHAR * 0x040f10f0 [ -3] "SELECT
Config, nValue FROM MSysConf\ 0"
SDWORD -3
This failed because my Postgres database does not have this table. Any idea
what MsysConf, what it contains, or how to create it? Could this have
anything to do with my Access subform problem?
Thanks, Michael
-----Original Message-----
From: Byron Nikolaidis [SMTP:byronn@insightdist.com]
Sent: Thursday, February 25, 1999 2:12 PM
To: Michael Davis
Cc: 'pgsql-interfaces@postgreSQL.org'
Subject: Re: [INTERFACES] Foreign Keys
Michael Davis wrote:
> I have created the following trigger on the parent/master table
(orders):
>
> CREATE TRIGGER Orderlines_fkey
> BEFORE DELETE OR UPDATE ON Orders FOR EACH ROW
> EXECUTE PROCEDURE check_foreign_key ();
>
> check_foreign_key() actually deletes rows from the orderlines
table when
> every a row is deleted from orders. This triggers works great.
Access,
> however, is still not recognizing the foreign key relationship
between
> orders and orderlines. Any other suggestions?
>
> Thanks, Michael
>
Yeah, there is more we can try. First of all, we need to verify
that Access
is actually calling SQLForeignKeys. Can you get a "trace" log of
the
session? This would be the "sql.log" file which contains all api
calls to
odbc. Make sure the file is clear beforehand. Then run the test.
Quit
Access, turn tracing off and there's your logfile. You can send it
to me.
It might also be helpful to have the commlog file (produced by the
driver)...usually "psqlodbc.log". It may have a process id number
appended
to the filename so make sure you get the right one.
Byron
^ permalink raw reply [nested|flat] 24+ messages in thread
* RE: [INTERFACES] Foreign Keys
@ 1999-02-25 22:40 Michael Davis <michael.davis@prevuenet.com>
0 siblings, 0 replies; 24+ messages in thread
From: Michael Davis @ 1999-02-25 22:40 UTC (permalink / raw)
To: 'Byron Nikolaidis' <byronn@insightdist.com>; +Cc: pgsql-interfaces
The trigger is installed on orders and would have no idea that this trigger
ties to orderlines. How would Access or the ODBC driver know that this is
the equivalent to a foreign key constraint. Seems to that I may to alter
the trigger so that the ODBC driver and Access see the trigger as a foreign
key constraint. Should the trigger need to be applied to orderlines instead
of orders? What is needed to actually tie the orderlines table to the
orders table?
Thanks, Michael
-----Original Message-----
From: Byron Nikolaidis [SMTP:byronn@insightdist.com]
Sent: Thursday, February 25, 1999 2:12 PM
To: Michael Davis
Cc: 'pgsql-interfaces@postgreSQL.org'
Subject: Re: [INTERFACES] Foreign Keys
Michael Davis wrote:
> I have created the following trigger on the parent/master table
(orders):
>
> CREATE TRIGGER Orderlines_fkey
> BEFORE DELETE OR UPDATE ON Orders FOR EACH ROW
> EXECUTE PROCEDURE check_foreign_key ();
>
> check_foreign_key() actually deletes rows from the orderlines
table when
> every a row is deleted from orders. This triggers works great.
Access,
> however, is still not recognizing the foreign key relationship
between
> orders and orderlines. Any other suggestions?
>
> Thanks, Michael
>
Yeah, there is more we can try. First of all, we need to verify
that Access
is actually calling SQLForeignKeys. Can you get a "trace" log of
the
session? This would be the "sql.log" file which contains all api
calls to
odbc. Make sure the file is clear beforehand. Then run the test.
Quit
Access, turn tracing off and there's your logfile. You can send it
to me.
It might also be helpful to have the commlog file (produced by the
driver)...usually "psqlodbc.log". It may have a process id number
appended
to the filename so make sure you get the right one.
Byron
^ permalink raw reply [nested|flat] 24+ messages in thread
* RE: [INTERFACES] Foreign Keys
@ 1999-02-25 22:46 Michael Davis <michael.davis@prevuenet.com>
0 siblings, 0 replies; 24+ messages in thread
From: Michael Davis @ 1999-02-25 22:46 UTC (permalink / raw)
To: pgsql-interfaces
The sql.log file is over 5M. There are no references to foreign keys
anywhere in the log file. I noticed the following:
Orders 138:118 ENTER SQLExecDirect
HSTMT 0x09072230
UCHAR * 0x040f10f0 [ -3] "SELECT
Config, nValue FROM MSysConf\ 0"
SDWORD -3
This failed because my Postgres database does not have this table. Any idea
what MsysConf, what it contains, or how to create it? Could this have
anything to do with my Access subform problem?
Thanks, Michael
-----Original Message-----
From: Byron Nikolaidis [SMTP:byronn@insightdist.com]
Sent: Thursday, February 25, 1999 2:12 PM
To: Michael Davis
Cc: 'pgsql-interfaces@postgreSQL.org'
Subject: Re: [INTERFACES] Foreign Keys
Michael Davis wrote:
> I have created the following trigger on the parent/master table
(orders):
>
> CREATE TRIGGER Orderlines_fkey
> BEFORE DELETE OR UPDATE ON Orders FOR EACH ROW
> EXECUTE PROCEDURE check_foreign_key ();
>
> check_foreign_key() actually deletes rows from the orderlines
table when
> every a row is deleted from orders. This triggers works great.
Access,
> however, is still not recognizing the foreign key relationship
between
> orders and orderlines. Any other suggestions?
>
> Thanks, Michael
>
Yeah, there is more we can try. First of all, we need to verify
that Access
is actually calling SQLForeignKeys. Can you get a "trace" log of
the
session? This would be the "sql.log" file which contains all api
calls to
odbc. Make sure the file is clear beforehand. Then run the test.
Quit
Access, turn tracing off and there's your logfile. You can send it
to me.
It might also be helpful to have the commlog file (produced by the
driver)...usually "psqlodbc.log". It may have a process id number
appended
to the filename so make sure you get the right one.
Byron
^ permalink raw reply [nested|flat] 24+ messages in thread
* RE: [INTERFACES] Foreign Keys
@ 1999-03-02 18:09 Michael Davis <michael.davis@prevuenet.com>
1999-03-02 19:52 ` Re: [INTERFACES] Foreign Keys Byron Nikolaidis <byronn@insightdist.com>
1999-03-03 09:33 ` Re: [INTERFACES] Foreign Keys Valerio Santinelli <tanis@mediacom.it>
0 siblings, 2 replies; 24+ messages in thread
From: Michael Davis @ 1999-03-02 18:09 UTC (permalink / raw)
To: pgsql-interfaces
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?
-----Original Message-----
From: Byron Nikolaidis [SMTP:byronn@insightdist.com]
Sent: Thursday, February 25, 1999 2:12 PM
To: Michael Davis
Cc: 'pgsql-interfaces@postgreSQL.org'
Subject: Re: [INTERFACES] Foreign Keys
Michael Davis wrote:
> I have created the following trigger on the parent/master table
(orders):
>
> CREATE TRIGGER Orderlines_fkey
> BEFORE DELETE OR UPDATE ON Orders FOR EACH ROW
> EXECUTE PROCEDURE check_foreign_key ();
>
> check_foreign_key() actually deletes rows from the orderlines
table when
> every a row is deleted from orders. This triggers works great.
Access,
> however, is still not recognizing the foreign key relationship
between
> orders and orderlines. Any other suggestions?
>
> Thanks, Michael
>
Yeah, there is more we can try. First of all, we need to verify
that Access
is actually calling SQLForeignKeys. Can you get a "trace" log of
the
session? This would be the "sql.log" file which contains all api
calls to
odbc. Make sure the file is clear beforehand. Then run the test.
Quit
Access, turn tracing off and there's your logfile. You can send it
to me.
It might also be helpful to have the commlog file (produced by the
driver)...usually "psqlodbc.log". It may have a process id number
appended
to the filename so make sure you get the right one.
Byron
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [INTERFACES] Foreign Keys
1999-03-02 18:09 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
@ 1999-03-02 19:52 ` Byron Nikolaidis <byronn@insightdist.com>
1 sibling, 0 replies; 24+ messages in thread
From: Byron Nikolaidis @ 1999-03-02 19:52 UTC (permalink / raw)
To: Michael Davis <michael.davis@prevuenet.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [INTERFACES] Foreign Keys
1999-03-02 18:09 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
@ 1999-03-03 09:33 ` Valerio Santinelli <tanis@mediacom.it>
1 sibling, 0 replies; 24+ messages in thread
From: Valerio Santinelli @ 1999-03-03 09:33 UTC (permalink / raw)
To: Michael Davis <michael.davis@prevuenet.com>; +Cc: pgsql-interfaces
I'm experiencing your exactly same problem.
I'm using MSAccess97 to manage data contained in a PostgreSQL database and when
I add a new record through a form containing two sub-reports linked to the main
one through foreign keys I get two ODBC errors caused by MSAccess97 passing NULL
as ID for the two sub-reports.
I haven't found a way around it yet.. :(
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?
>
> -----Original Message-----
> From: Byron Nikolaidis [SMTP:byronn@insightdist.com]
> Sent: Thursday, February 25, 1999 2:12 PM
> To: Michael Davis
> Cc: 'pgsql-interfaces@postgreSQL.org'
> Subject: Re: [INTERFACES] Foreign Keys
>
> Michael Davis wrote:
>
> > I have created the following trigger on the parent/master table
> (orders):
> >
> > CREATE TRIGGER Orderlines_fkey
> > BEFORE DELETE OR UPDATE ON Orders FOR EACH ROW
> > EXECUTE PROCEDURE check_foreign_key ();
> >
> > check_foreign_key() actually deletes rows from the orderlines
> table when
> > every a row is deleted from orders. This triggers works great.
> Access,
> > however, is still not recognizing the foreign key relationship
> between
> > orders and orderlines. Any other suggestions?
> >
> > Thanks, Michael
> >
>
> Yeah, there is more we can try. First of all, we need to verify
> that Access
> is actually calling SQLForeignKeys. Can you get a "trace" log of
> the
> session? This would be the "sql.log" file which contains all api
> calls to
> odbc. Make sure the file is clear beforehand. Then run the test.
> Quit
> Access, turn tracing off and there's your logfile. You can send it
> to me.
>
> It might also be helpful to have the commlog file (produced by the
> driver)...usually "psqlodbc.log". It may have a process id number
> appended
> to the filename so make sure you get the right one.
>
> Byron
>
>
--
C'ya!
Valerio Santinelli a.k.a. TANiS
[tanis@mediacom.it]+:+[http://www.mediacom.it/~tanis]
^ permalink raw reply [nested|flat] 24+ messages in thread
* RE: [INTERFACES] Foreign Keys
@ 1999-03-02 20:51 Michael Davis <michael.davis@prevuenet.com>
1999-03-02 22:10 ` Re: [INTERFACES] Foreign Keys Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 1 reply; 24+ messages in thread
From: Michael Davis @ 1999-03-02 20:51 UTC (permalink / raw)
To: 'Byron Nikolaidis' <byronn@insightdist.com>; +Cc: pgsql-interfaces
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
Attachments:
psqlodbc_270.log (application/octet-stream, 30.2K) download
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [INTERFACES] Foreign Keys
1999-03-02 20:51 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
@ 1999-03-02 22:10 ` Byron Nikolaidis <byronn@insightdist.com>
1999-03-03 07:04 ` Re: [INTERFACES] Foreign Keys Gene Selkov Jr. <selkovjr@xnet.com>
1999-03-03 09:41 ` Re: [INTERFACES] Foreign Keys Valerio Santinelli <tanis@mediacom.it>
0 siblings, 2 replies; 24+ messages in thread
From: Byron Nikolaidis @ 1999-03-02 22:10 UTC (permalink / raw)
To: Michael Davis <michael.davis@prevuenet.com>; +Cc: pgsql-interfaces; David Hartwig <daybee@bellatlantic.net>
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [INTERFACES] Foreign Keys
1999-03-02 20:51 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
1999-03-02 22:10 ` Re: [INTERFACES] Foreign Keys Byron Nikolaidis <byronn@insightdist.com>
@ 1999-03-03 07:04 ` Gene Selkov Jr. <selkovjr@xnet.com>
1 sibling, 0 replies; 24+ messages in thread
From: Gene Selkov Jr. @ 1999-03-03 07:04 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; pgsql-interfaces; pgsql-hackers@postgreSQL.org
> 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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [INTERFACES] Foreign Keys
1999-03-02 20:51 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
1999-03-02 22:10 ` Re: [INTERFACES] Foreign Keys Byron Nikolaidis <byronn@insightdist.com>
@ 1999-03-03 09:41 ` Valerio Santinelli <tanis@mediacom.it>
1999-03-03 13:17 ` Re: [INTERFACES] Foreign Keys jose' soares <sferac@bo.nettuno.it>
1 sibling, 1 reply; 24+ messages in thread
From: Valerio Santinelli @ 1999-03-03 09:41 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; +Cc: pgsql-interfaces
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 someone to patch the PostgreSQL code ?
--
C'ya!
Valerio Santinelli a.k.a. TANiS
[tanis@mediacom.it]+:+[http://www.mediacom.it/~tanis]
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [INTERFACES] Foreign Keys
1999-03-02 20:51 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
1999-03-02 22:10 ` Re: [INTERFACES] Foreign Keys Byron Nikolaidis <byronn@insightdist.com>
1999-03-03 09:41 ` Re: [INTERFACES] Foreign Keys Valerio Santinelli <tanis@mediacom.it>
@ 1999-03-03 13:17 ` jose' soares <sferac@bo.nettuno.it>
1999-03-03 17:10 ` Re: [INTERFACES] Foreign Keys jose' soares <sferac@bo.nettuno.it>
0 siblings, 1 reply; 24+ messages in thread
From: jose' soares @ 1999-03-03 13:17 UTC (permalink / raw)
To: Valerio Santinelli <tanis@mediacom.it>; +Cc: pgsql-interfaces; Michael Davis <michael.davis@prevuenet.com>
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 someone to 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)
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [INTERFACES] Foreign Keys
1999-03-02 20:51 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
1999-03-02 22:10 ` Re: [INTERFACES] Foreign Keys Byron Nikolaidis <byronn@insightdist.com>
1999-03-03 09:41 ` Re: [INTERFACES] Foreign Keys Valerio Santinelli <tanis@mediacom.it>
1999-03-03 13:17 ` Re: [INTERFACES] Foreign Keys jose' soares <sferac@bo.nettuno.it>
@ 1999-03-03 17:10 ` jose' soares <sferac@bo.nettuno.it>
1999-03-04 10:01 ` Re: [INTERFACES] Foreign Keys Valerio Santinelli <tanis@mediacom.it>
0 siblings, 1 reply; 24+ messages in thread
From: jose' soares @ 1999-03-03 17:10 UTC (permalink / raw)
To: Valerio Santinelli <tanis@mediacom.it>; pgsql-interfaces; Michael Davis <michael.davis@prevuenet.com>
> 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 someone to 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'-
Attachments:
gram.patch (text/plain, 447B) download
*** ./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
* Re: [INTERFACES] Foreign Keys
1999-03-02 20:51 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
1999-03-02 22:10 ` Re: [INTERFACES] Foreign Keys Byron Nikolaidis <byronn@insightdist.com>
1999-03-03 09:41 ` Re: [INTERFACES] Foreign Keys Valerio Santinelli <tanis@mediacom.it>
1999-03-03 13:17 ` Re: [INTERFACES] Foreign Keys jose' soares <sferac@bo.nettuno.it>
1999-03-03 17:10 ` Re: [INTERFACES] Foreign Keys jose' soares <sferac@bo.nettuno.it>
@ 1999-03-04 10:01 ` Valerio Santinelli <tanis@mediacom.it>
1999-03-04 13:54 ` Re: [INTERFACES] Foreign Keys Thomas G. Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 1 reply; 24+ messages in thread
From: Valerio Santinelli @ 1999-03-04 10:01 UTC (permalink / raw)
To: jose' soares <sferac@bo.nettuno.it>; +Cc: pgsql-interfaces; Michael Davis <michael.davis@prevuenet.com>
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 someone to 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]
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [INTERFACES] Foreign Keys
1999-03-02 20:51 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
1999-03-02 22:10 ` Re: [INTERFACES] Foreign Keys Byron Nikolaidis <byronn@insightdist.com>
1999-03-03 09:41 ` Re: [INTERFACES] Foreign Keys Valerio Santinelli <tanis@mediacom.it>
1999-03-03 13:17 ` Re: [INTERFACES] Foreign Keys jose' soares <sferac@bo.nettuno.it>
1999-03-03 17:10 ` Re: [INTERFACES] Foreign Keys jose' soares <sferac@bo.nettuno.it>
1999-03-04 10:01 ` Re: [INTERFACES] Foreign Keys Valerio Santinelli <tanis@mediacom.it>
@ 1999-03-04 13:54 ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
1999-03-04 21:26 ` Re: [INTERFACES] Foreign Keys Valerio Santinelli <tanis@mediacom.it>
0 siblings, 1 reply; 24+ messages in thread
From: Thomas G. Lockhart @ 1999-03-04 13:54 UTC (permalink / raw)
To: Valerio Santinelli <tanis@mediacom.it>; +Cc: jose' soares <sferac@bo.nettuno.it>; pgsql-interfaces; Michael Davis <michael.davis@prevuenet.com>
> 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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [INTERFACES] Foreign Keys
1999-03-02 20:51 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
1999-03-02 22:10 ` Re: [INTERFACES] Foreign Keys Byron Nikolaidis <byronn@insightdist.com>
1999-03-03 09:41 ` Re: [INTERFACES] Foreign Keys Valerio Santinelli <tanis@mediacom.it>
1999-03-03 13:17 ` Re: [INTERFACES] Foreign Keys jose' soares <sferac@bo.nettuno.it>
1999-03-03 17:10 ` Re: [INTERFACES] Foreign Keys jose' soares <sferac@bo.nettuno.it>
1999-03-04 10:01 ` Re: [INTERFACES] Foreign Keys Valerio Santinelli <tanis@mediacom.it>
1999-03-04 13:54 ` Re: [INTERFACES] Foreign Keys Thomas G. Lockhart <lockhart@alumni.caltech.edu>
@ 1999-03-04 21:26 ` Valerio Santinelli <tanis@mediacom.it>
0 siblings, 0 replies; 24+ messages in thread
From: Valerio Santinelli @ 1999-03-04 21:26 UTC (permalink / raw)
To: Thomas G. Lockhart <lockhart@alumni.caltech.edu>; +Cc: jose' soares <sferac@bo.nettuno.it>; pgsql-interfaces; Michael Davis <michael.davis@prevuenet.com>
"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]
^ permalink raw reply [nested|flat] 24+ messages in thread
* RE: [INTERFACES] Foreign Keys
@ 1999-03-03 00:56 Michael Davis <michael.davis@prevuenet.com>
1999-03-03 16:41 ` Re: [INTERFACES] Foreign Keys Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 1 reply; 24+ messages in thread
From: Michael Davis @ 1999-03-03 00:56 UTC (permalink / raw)
To: 'Byron Nikolaidis' <byronn@insightdist.com>; +Cc: pgsql-interfaces; David Hartwig <daybee@bellatlantic.net>
I will fix the permission denied problem by granting access to these tables
to the Admin user. I don't think this is the real cause of my problem. The
null issue is the real problem Considering your response, does this mean I
am the first user to encounter this issue? I am not very satisfied with
Access97 and intend to replace it very soon. I am planning to port the back
end to PostgreSQL and the front end to TCL/TK in the near future. I am
concerned about creating reports with TCL/TK and how they will interact with
a printer on Windows 95. Access97 automatically handles a lot of things
that I will have to implement in TCL/TK. Printing is one of them. What are
other users of PostgreSQL using for a front end? The front end tool must
run on Windows 95. My current Access97 database application has 9 users
that have Windows 95 workstations.
I would like to make available tools/instructions for upgrading from
Access97 to PostgreSQL (back end) and from Access97 to TCL/TK (front end).
This will take some time to implement all the details. I will start this
process if others would like to contribute their experiences and work
arounds.
Could this problem be fixed in the ODBC driver of does it need to be fixed
in PostgreSQL? None of the commercial databases that I have used (including
Oracle) support the syntax you are suggesting. I wonder how this works with
SQL Server?
-----Original Message-----
From: Byron Nikolaidis [SMTP:byronn@insightdist.com]
Sent: Tuesday, March 02, 1999 3:11 PM
To: Michael Davis
Cc: 'pgsql-interfaces@postgreSQL.org'; David Hartwig
Subject: Re: [INTERFACES] Foreign Keys
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=5
432;UID=Admin;PWD=;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;RO
WVERSIONING=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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [INTERFACES] Foreign Keys
1999-03-03 00:56 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
@ 1999-03-03 16:41 ` Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 0 replies; 24+ messages in thread
From: Byron Nikolaidis @ 1999-03-03 16:41 UTC (permalink / raw)
To: Michael Davis <michael.davis@prevuenet.com>; +Cc: pgsql-interfaces; David Hartwig <daybee@bellatlantic.net>
Michael Davis wrote:
> I will fix the permission denied problem by granting access to these tables
> to the Admin user. I don't think this is the real cause of my problem. The
> null issue is the real problem Considering your response, does this mean I
> am the first user to encounter this issue? I am not very satisfied with
>
<snip>
> Could this problem be fixed in the ODBC driver of does it need to be fixed
> in PostgreSQL? None of the commercial databases that I have used (including
> Oracle) support the syntax you are suggesting. I wonder how this works with
> SQL Server?
>
It really should be fixed in the backend. I don't want to get into parsing sql
strings too much. From what I've heard, its a fairly simple change to make
"col=null" work like "col isnull", and someone else on the list mentioned they
did it.
Can we just get this into the next release of Postgres?
Byron
^ permalink raw reply [nested|flat] 24+ messages in thread
* foreign keys
@ 1999-09-27 07:10 Anjan Borthakur <anjan@bombay.retortsoft.com>
1999-09-27 07:23 ` Re: [INTERFACES] foreign keys Rusty Brooks <rbrooks@utdallas.edu>
0 siblings, 1 reply; 24+ messages in thread
From: Anjan Borthakur @ 1999-09-27 07:10 UTC (permalink / raw)
To: pgsql-interfaces
hi,
postgres doesn't seem to have incorporated foreign key constraints. i am
using postgres 6,4.then how do they maintain referntial integrity
constraints,discussions are welcome.
-anjan
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [INTERFACES] foreign keys
1999-09-27 07:10 foreign keys Anjan Borthakur <anjan@bombay.retortsoft.com>
@ 1999-09-27 07:23 ` Rusty Brooks <rbrooks@utdallas.edu>
0 siblings, 0 replies; 24+ messages in thread
From: Rusty Brooks @ 1999-09-27 07:23 UTC (permalink / raw)
To: Anjan Borthakur <anjan@bombay.retortsoft.com>; +Cc: pgsql-interfaces
On Mon, 27 Sep 1999, Anjan Borthakur wrote:
==> postgres doesn't seem to have incorporated foreign key constraints. i am
==>using postgres 6,4.then how do they maintain referntial integrity
==>constraints,discussions are welcome.
They don't. You are going to have to. This wasn't added to 6.5.1 either
but maybe in the next release? This is a GOOD REASON to use
transactions... when you delete something, make sure you delete all
references to it, or if there are references you want to preserve, don't
delete the item. If there is something wrong you can always roll back.
That said, Postgres does support the _keywords_ refers to so I put them in
my data model hoping for the future. (My saved text data model that is, I
don't think they have any effect at all on the created tables).
,!!!!!!!, !!! !!! ,!!!!!!!!, !!!!!!!!!!!!! !!!, ,!!! +
!!! !!!, ,!!! ,!!! ,!!!' !!! `!!!!!' `!!! ,!!! *
===!!!'===========!!!'========!!!,=======================!!!======--+***+--
!!!!,,!!!!' ,!!! ,!!! !!!!, ,!!! `!!!!! *
,!!!!!!!!,' !!!' !!!' !!!!, !!!' !!!' *
!!! !!!, ,!!! ,!!! ,!!! ,!!! ,!!! ,!!! +
,!!! `!!! !!!,..!!!' !!!, ,!!!' !!!' !!!' +
!!! !!!, ,!!!!!!!!! `!!!!!!!!' ,!!! ,!!! +
:
================================================================== :
Rusty Brooks * rbrooks@utdallas.edu .
Life is all about getting what you want.
Love is all about giving something up to get what you need.
1.79 x 10^12 furlongs per fortnight -- it's not just a good idea, it's the
law!
^ permalink raw reply [nested|flat] 24+ messages in thread
end of thread, other threads:[~1999-09-27 07:23 UTC | newest]
Thread overview: 24+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-02-25 15:32 Foreign Keys Michael Davis <michael.davis@prevuenet.com>
1999-02-25 15:37 ` Byron Nikolaidis <byronn@insightdist.com>
1999-02-25 16:45 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
1999-02-25 20:26 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
1999-02-25 21:12 ` Byron Nikolaidis <byronn@insightdist.com>
1999-02-25 22:36 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
1999-02-25 22:40 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
1999-02-25 22:46 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
1999-03-02 18:09 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
1999-03-02 19:52 ` Byron Nikolaidis <byronn@insightdist.com>
1999-03-03 09:33 ` Valerio Santinelli <tanis@mediacom.it>
1999-03-02 20:51 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
1999-03-02 22:10 ` Byron Nikolaidis <byronn@insightdist.com>
1999-03-03 07:04 ` Gene Selkov Jr. <selkovjr@xnet.com>
1999-03-03 09:41 ` Valerio Santinelli <tanis@mediacom.it>
1999-03-03 13:17 ` jose' soares <sferac@bo.nettuno.it>
1999-03-03 17:10 ` jose' soares <sferac@bo.nettuno.it>
1999-03-04 10:01 ` Valerio Santinelli <tanis@mediacom.it>
1999-03-04 13:54 ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
1999-03-04 21:26 ` Valerio Santinelli <tanis@mediacom.it>
1999-03-03 00:56 RE: [INTERFACES] Foreign Keys Michael Davis <michael.davis@prevuenet.com>
1999-03-03 16:41 ` Byron Nikolaidis <byronn@insightdist.com>
1999-09-27 07:10 foreign keys Anjan Borthakur <anjan@bombay.retortsoft.com>
1999-09-27 07:23 ` Re: [INTERFACES] foreign keys Rusty Brooks <rbrooks@utdallas.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