commons-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject DO NOT REPLY [Bug 34187] New: - [dbcp] allow to register a wrapper extending DelegatingPreparedStatement to enhance setString for compatibility ("schema not null" issue)
Date Sat, 26 Mar 2005 09:57:34 GMT
DO NOT REPLY TO THIS EMAIL, BUT PLEASE POST YOUR BUG·
RELATED COMMENTS THROUGH THE WEB INTERFACE AVAILABLE AT
<http://issues.apache.org/bugzilla/show_bug.cgi?id=34187>.
ANY REPLY MADE TO THIS MESSAGE WILL NOT BE COLLECTED AND·
INSERTED IN THE BUG DATABASE.

http://issues.apache.org/bugzilla/show_bug.cgi?id=34187

           Summary: [dbcp] allow to register a wrapper extending
                    DelegatingPreparedStatement to enhance setString for
                    compatibility ("schema not null" issue)
           Product: Commons
           Version: unspecified
          Platform: Other
        OS/Version: All
            Status: NEW
          Severity: enhancement
          Priority: P2
         Component: Dbcp
        AssignedTo: commons-dev@jakarta.apache.org
        ReportedBy: hauser@acm.org


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)
...>>

-- 
Configure bugmail: http://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug, or are watching the assignee.

---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org


Mime
View raw message