commons-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Phil Steitz (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DBCP-157) [dbcp] allow to register a wrapper extending DelegatingPreparedStatement to enhance setString for compatibility ("schema not null" issue)
Date Sat, 21 Mar 2009 22:43:50 GMT

     [ https://issues.apache.org/jira/browse/DBCP-157?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Phil Steitz updated DBCP-157:
-----------------------------

    Fix Version/s:     (was: 1.3)
                   2.0

This functionality is outside of the scope of dbcp 1.x.  Moving to 2.0.

> [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.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message