You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@commons.apache.org by "Mark Thomas (JIRA)" <ji...@apache.org> on 2013/07/26 17:51:48 UTC
[jira] [Commented] (DBCP-157) [dbcp] allow to register a wrapper
extending DelegatingPreparedStatement to enhance setString for
compatibility ("schema not null" issue)
[ 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