[
https://issues.apache.org/jira/browse/DBCP-157?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13720903#comment-13720903
]
Mark Thomas commented on DBCP-157:
----------------------------------
This wouldn't be too hard to do for DelegatingResultSet as all construction is
through a couple of static methods. Those could be moved to factory and that
factory could itself be configurable to allow custom factories that returned
custom implementations (that I think would have to extend the existing
implementation - switching to interfaces would be a step too far).
DelegatingPreparedStatement is a little more work (there are no static methods
used for construction) but that could follow a similar pattern.
That immediately leads to thinking that instances of all the Delegating*
classes should be created this way.
This is now looking to be a fairly invasive change for a problem that a) is
caused by a 3rd party not following the relevant specs (and that always makes
me less inclined to make a change) and b) doesn't appear to occur that often
based on the lack of comments on this issue that has been open rather a while.
Overall, I am leaning towards resolving this as WONTFIX but keeping the idea in
mind in case other use cases emerge that could benefit from the ability to use
custom Delegating* classes.
I won't resolve this issue just yet to give other folks a chance to comment.
> [dbcp] allow to register a wrapper extending DelegatingPreparedStatement to
> enhance setString for compatibility ("schema not null" issue)
> -----------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DBCP-157
> URL: https://issues.apache.org/jira/browse/DBCP-157
> Project: Commons Dbcp
> Issue Type: Improvement
> Environment: Operating System: All
> Platform: Other
> Reporter: Ralf Hauser
> Priority: Minor
> Fix For: 2.0
>
>
> 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:
> com.mysql.jdbc.PreparedStatement@82d603: INSERT INTO schmNotNull ( subject)
> VALUES ('testSubj');
> DEBUG [main] (DBTest.java:594) - retVal1: 1
> DEBUG [main] (DBTest.java:596) - insSenderStmt:
> com.mysql.jdbc.PreparedStatement@82d603: INSERT INTO schmNotNull ( subject)
> VALUES ('');
> DEBUG [main] (DBTest.java:600) - retVal2: 1
> DEBUG [main] (DBTest.java:602) - insSenderStmt:
> com.mysql.jdbc.PreparedStatement@82d603: 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, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
