db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <derby-...@db.apache.org>
Subject [jira] Created: (DERBY-157) Server throws DRDA protocol exception for certain date/time strings passed from an ODBC client.
Date Thu, 03 Mar 2005 19:39:48 GMT
Server throws DRDA protocol exception for certain date/time strings passed from an ODBC client.

         Key: DERBY-157
         URL: http://issues.apache.org/jira/browse/DERBY-157
     Project: Derby
        Type: Bug
  Components: Network Server  
 Environment: I found this problem using Derby Network Server with the DB2 Runtime Client,
but I imagine it could occur for other clients, as well...
    Reporter: A B
 Assigned to: A B 

NOTE: This bug is NOT the same as DERBY-149, although the two bugs reproduce in the same kind
of scenario.  DERBY-149 describes a problem that occurs when an invalid date/time string is
specified using the Universal Driver (JDBC); this bug describes a problem that occurs when
a date/time string is deemed as "valid" by an ODBC driver (in this case, the DB2 Runtime Client)
but as "invalid" by the server.


When a client program passes a datetime string value to Derby Network Server, the driver first
does a check of the string to see if it's valid.  For JDBC programs using the Universal Driver
(JCC), the JCC driver appears to base it's check on the java.sql.Date/Time/Timestamp specifications,
which only allow specific forms of the strings.  For example, the JDBC spec for "java.sql.Date.valueOf"
says that the string value must represent a date in the format "yyyy-mm-dd".

Derby Network Server _assumes_ that the java.sql.Date/Time/Timestamp specifications in this
regard will hold true for the value in question, and so <b> it does NOT have logic to
see if the received string causes an error </b>.  This is fine when using the JCC driver
because the formats rejected by Network Server are also the formats rejected by the JCC driver,
and so invalid datetime strings shouldn't ever make it to the server (they'll be caught by
the driver).


Other drivers--and in particular, ODBC drivers--can have different notions about what a valid
datetime string is.  In the particular case that prompted this bug, the DB2 Runtime Client
allows a date string of the format "yyyy/mm/dd", and so if it sees a string with this format,
it will pass it on to Network Server.  However, since Network Server only recognizes the form
"yyyy-mm-dd" (per JDBC spec), it will throw a java.lang.IllegalArgumentException.  

Currently, Derby Network Server doesn't catch this exception (because it assumes the datetime
is either valid or else caught by the driver, as is the case with JCC), and so the exception
is interpreted by the server as "unexpected".  This causes the server to throw a generic protocol
exception and deallocate the connection.


Here is a fragment of a C++ program that shows how to recreate the problem, assuming that
1) table "u.tt1" has been created as "u.tt1(d date)", 2) "hstmt" has been properly initalized
as a statement on a connection to a Derby database, and 3) the DB2 Runtime Client is being


SQLCHAR dateObj[10];

strcpy((char *) SQLStmt, "insert into u.tt1 values (?)");
RetCode = SQLPrepare(hstmt, SQLStmt, SQL_NTS);

// Bind the parameter marker used in the SQL statement to
// an application variable
RetCode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, 
	SQL_DATE, sizeof(dateObj), 
	0, dateObj, sizeof(dateObj), NULL);

// Populate the bound variable with a datetime value that will
// pass the ODBC driver check but that is NOT recognized by the
// Derby Network Server.
strcpy(dateObj, "1948/04/08");

// Execute the SQL statement
RetCode = SQLExecute(hstmt);
if (RetCode == SQL_SUCCESS) {
	printf("OK, all good.\n");
else {
	printf("Error executing insert statement:\n");


The result of this fragment will be:

[IBM][CLI Driver] SQL30020N  Execution failed because of a Distributed Protocol Error that
will affect the successful execution of subsequent commands and SQL statements:  Reason Code
"0x124C"("011D")"".  SQLSTATE=58009


The fix for this problem is straightforward; one just needs to add "try-catch" logic to catch
the IllegalArgumentException in the server and then re-throw it as a valid SQLException.

This message is automatically generated by JIRA.
If you think it was sent incorrectly contact one of the administrators:
If you want more information on JIRA, or have a bug to report see:

View raw message