Hardy Merrill wrote: > Ok, I found this link > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odapps.asp > > but I'm not sure that's ODBC 3.5. > > Also, this page has a table with 3 columns: > SQLSTATE (5 digit number) > Error - terse error description > Can be returned from > > The 'Can be returned from' column is a list of ODBC functions > that I'm completely unfamiliar with - but SQLExecute > and SQLPrepare may(?) be similar to the DBI functions. > > Also, scanned through the listed Error's to try to > find a match for our "trying to insert a duplicate key" > error described in the thread below. I came up with these > posibilities: > > 23000 Integrity constraint violation SQLBulkOperations > SQLExecDirect > SQLExecute > SQLParamData > SQLSetpos > > 3C000 Duplicate cursor name SQLSetCursorName > > 40002 Integrity constraint violation SQLEndTrans > > 42S11 Index already exists SQLExecDirect > SQLPrepare > > Which one is most appropriate? We definitely want to > know the difference between this "duplicate key" constraint > and some other constraint violation, so I really don't > know which one is most appropriate. Thoughts?
3C000 and 42S11 both look like DDL errors, and not constraint violations. 23000 and 40002 appear to match, but do they cover all constraints (primary key, unique, check, and foreign key). It looks like the difference between 23000 and 40002 is that 23000 is the result of a constraint violation, but 40002 is a constraint violation and the database has forced a rollback (i.e. you tried to commit violating data [so you must have had constraints deferred], so it rolls back instead). http://msdn.microsoft.com/library/default.asp?url=/library/en-us/his/htm/_sna_troubleshooting_the_odbc_driver_for_db2_oledb.asp ... has a list of class prefixes. " 21 Cardinality violation 22 Data exception 23 Constraint violation " From that it would be nice if: 21 would be for PKs and unique keys 22 would be for check constraints 23 would be foreign key constraints? But looks like there's an overlap there - all the above are still constraints. Doesn't seem to be the case though, since only 23000 and 40002 look like constraint violations mentioned in the list here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odp4.asp Then there's Oracle's SQLSTATE list: http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a97269/pc_09err.htm#3170 Uses 23000 for check and foreign keys, and for not null and unique constraints but only within DDL. ORA-0001 (unique constraint violation) is listed as 72000 here, along with a heap of other Oracle errors. 21000 is used for 'exact fetch returns too many rows' - so that's what it means by cardinality violation. And MySQL's SQLSTATE list: http://www.mysql.com/doc/en/Error-returns.html Uses 23000 for all constraint violations. Seems a bit of a mess :-( -- Andy Hassall ([EMAIL PROTECTED]) icq(5747695) (http://www.andyh.co.uk) Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
