[ http://issues.apache.org/jira/browse/DBCP-157?page=all ]
Phil Steitz updated DBCP-157:
-----------------------------
Bugzilla Id: (was: 34187)
Fix Version: 1.3
> [dbcp] allow to register a wrapper extending DelegatingPreparedStatement to
> enhance setString for compatibility ("schema not null" issue)
> -----------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DBCP-157
> URL: http://issues.apache.org/jira/browse/DBCP-157
> Project: Commons Dbcp
> Type: Improvement
> Environment: Operating System: All
> Platform: Other
> Reporter: Ralf Hauser
> Priority: Minor
> Fix For: 1.3
>
> It would be great to be able to register a wrapper enhancing
> org.apache.commons.dbcp.DelegatingPreparedStatement. One purpose of this could
> be to enhance its setString() method to cope with portability issues between
> databases and their jdbc drivers:
> - mysql considers "" as a non-null string
> - oracle complains that an attempt to insert a null string was made with "".
> A little test case to illustrate will be appended at the end of this
> description.
> So, to create portability, dbcp users have the advantage that their
> applications
> don't use the jdbc.jar's setString and getString methods directly anyway.
> Therefore, the proposed wrapper's setString() method should
> 1) detect whether ojdbc_14.jar or alike is used
> 2) if getting "", change this to a never used string such as
> "eCoUnTeReDsTrInGoFlEnGtHzErO"
> Similarly, the DelegatingResultSet's getString() would have to be wrapped
> accordingly, therefore
> 3) the DelegatingResultSet's wrapper's getString() would do the same in
> reverse.
> Anyway, even if either oracle or mysql breaks the standard, it is unlikely to
> have them quickly converge to the same behaviour, thus such an enhancement
> could
> allow programmers to elegantly fix this and gain easy portability not
> requiring
> them make their applications/db schemes aware of such idiosyncrasies.
> -------------
> In the below code, the mysql jdbc complains at the third attempt to insert
> while
> recent oracle jdbc already refuses to execute variant 2:
> stmt = this.conn.createStatement(
> java.sql.ResultSet.TYPE_FORWARD_ONLY,
> java.sql.ResultSet.CONCUR_UPDATABLE);
> stmt.executeUpdate("DROP TABLE IF EXISTS schmNotNull");
> stmt
> .executeUpdate("CREATE TABLE schmNotNull ("
> + "msg_id INT NOT NULL AUTO_INCREMENT
> UNIQUE, "
> + "subject CHAR(255) NOT NULL, PRIMARY
> KEY (msg_id))");
> PreparedStatement insSenderStmt = null;
>
> //attempt 1
> insSenderStmt = this.conn
> .prepareStatement("INSERT INTO schmNotNull (
> subject) "
> + " VALUES (?);");
> insSenderStmt.setString(1, "testSubj"); // subject
> log.debug("insSenderStmt: "
> + ((DelegatingPreparedStatement) insSenderStmt)
> .getDelegate().toString());
> int retVal = insSenderStmt.executeUpdate();
> log.debug("retVal1: " + retVal);
>
> //attempt 2
> insSenderStmt.setString(1, "");
> log.debug("insSenderStmt: "
> + ((DelegatingPreparedStatement) insSenderStmt)
> .getDelegate().toString());
> retVal = insSenderStmt.executeUpdate();
> log.debug("retVal2: " + retVal);
>
> //attempt 3
> insSenderStmt.setString(1, null);
> log.debug("insSenderStmt: "
> + ((DelegatingPreparedStatement) insSenderStmt)
> .getDelegate().toString());
> retVal = insSenderStmt.executeUpdate();
> log.debug("retVal3: " + retVal);
> so, the output of mysql looks like:
> <<DEBUG [main] (DBTest.java:590) - insSenderStmt:
> [EMAIL PROTECTED]: INSERT INTO schmNotNull ( subject)
> VALUES ('testSubj');
> DEBUG [main] (DBTest.java:594) - retVal1: 1
> DEBUG [main] (DBTest.java:596) - insSenderStmt:
> [EMAIL PROTECTED]: INSERT INTO schmNotNull ( subject)
> VALUES ('');
> DEBUG [main] (DBTest.java:600) - retVal2: 1
> DEBUG [main] (DBTest.java:602) - insSenderStmt:
> [EMAIL PROTECTED]: INSERT INTO schmNotNull ( subject)
> VALUES (null);
> java.sql.SQLException: Column 'subject' cannot be null
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2847)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622)
> at com.mysql.jdbc.Connection.execSQL(Connection.java:2376)
> at com.mysql.jdbc.Connection.execSQL(Connection.java:2297)
> at
> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1860)
> at
> com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1957)
> at
> com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1880)
> at
> com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1741)
> at
> org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
> at
> com.privasphere.privalope.test.DBTest.testSchemaNotNull(DBTest.java:605)
> ...>>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
