On Mon, Sep 29, 2003 at 01:58:14PM -0700, [EMAIL PROTECTED] wrote: > Aren't the mysql sql state known. i remember coding using java.sql with > the mysql database driver, it had alot of the error code, state, and > messages implemented. Also, this was somewhat close to Oracle.
MySQL have recently added support for SQL_STATE. > Thus without having readup on DBI and DBD, I would say it should follow > the code/state standards of mysql. :-) We should follow what they, and everyone else, should follow: the ANSI SQL-99 standard. (But recent ODBC as per the MS web site is probably close enough.) Tim. > Anyway, just a thought. > > -rkl > > > 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? > > > > TIA. > > -- > > Hardy Merrill > > Red Hat, Inc. > > > > > > Hardy Merrill [EMAIL PROTECTED] wrote: > >> Tim Bunce [EMAIL PROTECTED] wrote: > >> > On Mon, Sep 29, 2003 at 12:14:17PM -0400, Hardy Merrill wrote: > >> > > Tim Bunce [EMAIL PROTECTED] wrote: > >> > > > On Mon, Sep 29, 2003 at 09:32:10AM -0400, Hardy Merrill wrote: > >> > > > > Thomas A. Lowery [EMAIL PROTECTED] wrote: > >> > > > > > # Does the error message contains the phrase: duplicate key > >> > > > > > if ($dbh->errstr =~ m/duplicate\skey/i) { > >> > > > > ^^^^^^^^^^^^^^ > >> > > > > Just to clarify, as Thomas said above, each database produces > >> > > > > different error messages to indicate that you are trying > >> > > > > to insert a duplicate key - so what you search for here will > >> > > > > vary depending on which database you are using. > >> > > > > >> > > > Of course, in an ideal world, DBD's would all set $DBI::state > >> > > > ($h->state) to the appropriate value: "23000" > >> > > > > >> > > > Maybe someone could define a list of the errors that most > >> frequently > >> > > > need to be explicitly checked for. The DBI spec could then list > >> > > > those along with the standard SQL_STATE error codes and suggest > >> that > >> > > > drivers support at least that basic set of codes. > >> > > > >> > > I think this is an excellent idea! > >> > > >> > Does that mean you're volunteering to coordinate making the list? > >> > That is, soliciting input from dbi-users and dbi-announce and produce > >> > a list of the corresponding SQL_STATE values (based on ODBC 3.5). > >> > I hope so :) > >> > >> Damn, I knew I shouldn't have responded to that ;-) > >> Just kidding :) > >> > >> As I've never used ODBC, if you can point me to the proper > >> ODBC 3.5 docs, I'll solicit input to create a list of SQL_STATE > >> values. I searched google for 'odbc 3.5' and 'odbc' and 'odbc > >> sql state', and I'm not having much luck finding a list of > >> sql state's. > >> > >> Hardy > >> > >> > > >> > Tim. > >> > > >> > > The only situation I can think of that I've had to code > >> > > around where different databases produce different results > >> > > is this one - this "trying to insert a duplicate record" > >> > > error. There must be others ;-) > >> > > > >> > > -- > >> > > Hardy Merrill > >> > > Red Hat, Inc. > > > >
