[Zope] Bug in Z ODBC DA?

[Martijn Faassen]

Hi there,

My voyage through bug land seems to continue:

I've been using the Z ODBC database adaptor to speak SQL to an Access
database on the NT server that Zope is running on here. This all works
fine for SELECT and INSERT statements. Now I've gotten to DELETE
statements, however.

I'm trying to delete a record from a table called 'entiteit' (entity).
Each record in that table has a unique 'entiteit_id'. Records in the
table 'entiteit' can be pointed to by records in the table 'bestelling'
(orders), by referring to this 'entiteit_id'. I've established a
referential integrity relationship in Access between the two tables, to
ensure that records in 'entiteit' can't be removed if their entiteit_id
is still in use by records in the 'bestelling' table.

Whenever I try to DELETE a record (by entiteit_id) in the entiteit
table, I get an ODBC error message in the Zope traceback, saying
basically (I think) that this violates referential integrity. This is
odd, as I'm trying to delete records that *aren't* referred to from
anywhere (the only table that *can* refer to entiteit is bestelling
anyway). It gives this error message for any table. (traceback at the
end)

In order to try to rule out it was an ODBC problem in itself, or a fault
of mine in making this Access table, I've created a small Python script
that uses the Python DB API to talk to ODBC. This script can delete
records just fine, unless they're referred to from the bestelling table.
ODBC therefore seems to be okay.

Unless someone can give a reasonable explanation of why this happens,
this points to a bug in the Zope ODBC database adaptor. Anybody have
similar experiences?

Apologies for my Dutch here :)

Regards,

Martijn

The promised traceback:

<!--
 Error type:  sql.error
 Error value: ('S1000', -1612, "[Microsoft][ODBC Microsoft Access 97
Driver] The record cannot be deleted or changed because table
'bestelling' includes related records.")
 -->

<!--
Traceback (innermost last):
  File f:\Program Files\Zope\lib\python\ZPublisher\Publish.py, line 877,
in publish_module
  File f:\Program Files\Zope\lib\python\ZPublisher\Publish.py, line 590,
in publish
    (Info: /bestelling/entiteit/report_delete_entiteit)
  File f:\Program Files\Zope\lib\python\OFS\DTMLMethod.py, line 155, in
__call__
    (Object: report_delete_entiteit)
  File f:\Program Files\Zope\lib\python\OFS\DTMLMethod.py, line 151, in
__call__
    (Object: report_delete_entiteit)
  File f:\Program Files\Zope\lib\python\DocumentTemplate\DT_String.py,
line 513, in __call__
    (Object: report_delete_entiteit)
  File f:\Program Files\Zope\lib\python\DocumentTemplate\DT_Util.py,
line 266, in eval
    (Object: sql.delete_entiteit(REQUEST))
  File <string>, line 0, in ?
  File f:\Program Files\Zope\lib\python\Shared\DC\ZRDB\DA.py, line 390,
in __call__
    (Object: CopySource)
  File f:\Program Files\Zope\lib\python\Products\ZODBCDA\db.py, line
209, in query
sql.error: (see above)
-->