Details
-
Improvement
-
Status: Closed
-
Minor
-
Resolution: Won't Fix
-
None
-
None
-
None
-
Operating System: All
Platform: Other
-
34187
Description
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)
...>>