[ psqlodbc-Bugs-1000709 ] SQLForeignKeys missing FK_NAME missing PK_NAME

Bugs item #1000709, was opened at 2006-08-14 21:23
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000709&group_id=1000125

Category: None
Group: None
>Status: Closed
Resolution: None
Priority: 3
Submitted By: Farid Zidan (faridz)
Assigned to: Hiroshi Inoue (hinoue)
Summary: SQLForeignKeys missing FK_NAME missing PK_NAME

Initial Comment:
Missing info. SQLForeignKeys resultset columns FK_NAME and PK_NAME return NULLs. Driver 8.02.00.02

----------------------------------------------------------------------

Comment By: Farid Zidan (faridz)
Date: 2006-08-23 11:11

Message:
That explains it.
SQL_NO_ACTION is the ODBC 3.0 equivalent to ODBC 2.0
SQL_RESTRICT so the driver is returning the correct info the
the update and delete rules. Thanks.

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2006-08-23 08:58

Message:
I'm not the man who implemented SQLForeignKeys.
The driver has returned SQL_NO_ACTION for restrict rules from the first. Probably it's
because of the following description

[UPDATE_RULE]
Action to be applied to the foreign key when the SQL operation is UPDATE. Can have one of the following values. (The referenced table is the table that has the primary key; the referencing table is the table that has the foreign key.)
SQL_CASCADE: When the primary key of the referenced table is updated, the foreign key of the referencing table is also updated.

SQL_NO_ACTION: If an update of the primary key of the referenced table would cause a "dangling reference" in the referencing table (that is, rows in the referencing table would have no counterparts in the referenced table), then the update is rejected. If an update of the foreign key of the referencing table would introduce a value that does not exist as a value of the primary key of the referenced table, then the update is rejected. (This action is the same as the SQL_RESTRICT action in ODBC 2.x.)

SQL_SET_NULL: When one or more rows in the referenced table are updated such that one or more components of the primary key are changed, the components of the foreign key in the referencing table that correspond to the changed components of the primary key are set to NULL in all matching rows of the referencing table.

SQL_SET_DEFAULT: When one or more rows in the referenced table are updated such that one or more components of the primary key are changed, the components of the foreign key in the referencing table that correspond to the changed components of the primary key are set to the applicable default values in all matching rows of the referencing table.

NULL if not applicable to the data source.

regards,
Hiroshi Inoue

----------------------------------------------------------------------

Comment By: Farid Zidan (faridz)
Date: 2006-08-22 14:51

Message:
No. All my tables have on delete restrict and on update
restrict for their foreign key constraints. The dll returns
no action for all their fk constraints instead of restrict.

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2006-08-22 14:40

Message:
> Returned values for
> UPDATE_RULE and DELETE_RULE are all
> SQL_NO_ACTION instead of the actual
> delete/update rule, if one exists.

Are there any delete/update rules other
than NO ACTION or RESTRICT on your tables ?

regards,
Hiroshi Inoue

----------------------------------------------------------------------

Comment By: Farid Zidan (faridz)
Date: 2006-08-22 12:37

Message:
Attached dll does not return invalid values for UPDATE_RULE
or DELETE_RULE columns of the resultset, however, it does
not the correct values either. Returned values for
UPDATE_RULE and DELETE_RULE are all SQL_NO_ACTION instead of
the actual delete/update rule, if one exists.

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2006-08-22 02:24

Message:
Could you try the attached dll for testing ?

Unfortunatey it's not the solution even though
it works.

regards,
Hiroshi Inoue

----------------------------------------------------------------------

Comment By: Farid Zidan (faridz)
Date: 2006-08-20 13:32

Message:
Mylog is attached. You can duplicate this issue by
downloading the application from www.zidsoft.com and
creating a PostgreSQL data source and then using the
repository wizard to create the respository in the data
source. Keep the application repository set to <Embedded
Repository> and do a DBMS comparison between the <Embedded
Repository> and the PostgreSQL data source. Add a Tables
Comparison and then compare all the FK for the tabls by
clicking the FK view and then pressing F5

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2006-08-20 06:04

Message:
> However, I am seeing unexpected values in the
> resultset for the following columns:
> UPDATE_RULE, DELETE_RULE unexpected values: 515
> DEFERRABILITY unexpected values: 0, 22, 24, 34, > 36, 40, 44, 46, 48, 50, 56, 5639, 6151, ...

Could you send me the Mylog uotput ?

regards,
Hiroshi Inoue

----------------------------------------------------------------------

Comment By: Farid Zidan (faridz)
Date: 2006-08-19 23:39

Message:
Verified that it correctly work in the given dll. However, I
am seeing unexpected values in the resultset for the
following columns:
UPDATE_RULE, DELETE_RULE unexpected values: 515
DEFERRABILITY unexpected values: 0, 22, 24, 34, 36, 40, 44,
46, 48, 50, 56, 5639, 6151, ...

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2006-08-19 07:01

Message:
Please install the 8.2.0002 version and replace
the dll by the one at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html .

regards,
Hiroshi Inoue

----------------------------------------------------------------------

Comment By: Farid Zidan (faridz)
Date: 2006-08-19 03:06

Message:
Still the same error. I was using 08.01.0100 version of the
driver.

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2006-08-19 02:50

Message:
If you are using the 8.2.00.02 driver, you can
simply replace the dll psqlodbc35w.dll.

regards,
Hiroshi inoue

----------------------------------------------------------------------

Comment By: Farid Zidan (faridz)
Date: 2006-08-19 01:50

Message:
I could not enter the setup for the fix driver.
'The setup routines for the PostgreSQL ODBC driver could not
be loaded due to system error code 126'

If this will be fixed in the next release of the driver I
will wait for the release. Thanks.

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2006-08-18 13:57

Message:
Please try the dll at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html .

regards,
Hiroshi Inoue

----------------------------------------------------------------------

You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000709&group_id=1000125