Constraint violation precision

[vki]

Hello all

I work on a clien/server application that uses ODBC to access a database. It is done through a "generic"-ODBC layer. We do so because it is flexible and we can face any client-specific demand regarding the DBMS they want to use. We developped that layer in C.

I currently work on a MySQL 4.1.3-beta DB on a Windows 2000 server, with th InnoDB engine.

When a foreign key constraint is violated during execution, the error message delivered by MySQL is somewhat poor in description. For instance if I try to delete a record that is still referenced in a table, the message I get is "Cannot delete or update a parent row: a foreign key or constraint fails". This causes me a problem because when the ODBC-layer is used with say DB2, the error message tells me which constraint was violated, and I can use that to send meaningful information to the client station, which can display a correct error message. Currenly I can do that with MySQL, and all the client gets is a default error message. So my question is: when a constraint violation occurs, is there any way to programatically know which one was?

During my research on that topic I came accross this link concerning Mimer:

http://developer.mimer.se/howto/howto_47.htm

Basically it explains how to get that additionnal info using stored procedures. Is there a possiblity to do that with MySQL too?

Thanks.

 

[Bastien]

Can you get the error number and map that to a correct message?

http://dev.mysql.com/doc/mysql/en/Error-handling.html

Bastien

Cat, the other other white meat

 

[vki]

Hello Bastien,

Thanks for your time. The error messages I'm alking about are the following:

Error: 1216 SQLSTATE: 23000 (ER_NO_REFERENCED_ROW) Message: Cannot add or update a child row: a foreign key constraint fails

Error: 1217 SQLSTATE: 23000 (ER_ROW_IS_REFERENCED) Message: Cannot delete or update a parent row: a foreign key constraint fails

Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY) Message: Duplicate entry '%s' for key %d

Error: 1048 SQLSTATE: 23000 (ER_BAD_NULL_ERROR) Message: Column '%s' cannot be null

So yes I can get the error number, I can get the message returned by the ODBC driver (messages above). But as you can see they don't tell me which constraint was violated, which is what I'm looking for.

Do you think there is any way to know that?

When I use a MySQL prompt to violate the constraint, I can issue the "show innodb status" command, and this will give me the information I want. Is there a possibility to do something similar in a C program (by using the MySQL API or anything else)?

Thanks again,
Vincent