JDBC, STRICT_TRANS_TABLES, & Null OUT Parameter Problem
Hi,
I've been wrestling with the following issue for a few days and can't understand the interaction between the driver and database. There seem to be variations on this message throughout the forum, so once and for all, I'd like to understand what is happening.
GOAL:
---------------
With STRICT_TRANS_TABLES enabled, pass NULL as an INOUT DECIMAL parameter to a stored procedure and have the innodb table column's default value inserted ( using the form DEFAULT(colX) ). I've tried setting every combination of jdbcCompliantTruncation and noAccessToProcedureBodies properties from the connection string with no difference in the results.
PROBLEM:
---------------
The above fails for numerous datatypes: I've tested it with CHAR, DECIMAL, and DATE (presuming other types will have the same issue). When the value of the object is null, I noticed that the driver sets the null value of out parameters to string value 'null'. This causes an error when calling the stored procedure as an INOUT parameter with and without jdbcCompliantTruncation=false. I assume that the server is converting this before calling the stored procedure when STRICT_TRANS_TABLES is disabled, because it works fine. What is the proper way to send a NULL value as a OUT parameter to a stored procedure with STRICT_TRANS_TABLES enabled. If this can't be done, is it a bug or intentional or is null considered an invalid value (the table columns specify NOT NULL and have DEFAULT values, which the documentation on the STRICT_TRANS_TABLES states is valid).
ENV:
---------------
os: Windows XP 32 bit
server: mysqld 5.1.55 (ia32)
driver: mysql-connector-java-5.1.14-bin.jar
TEST CASE DATA:
---------------
0. SQL MODE:
sql_mode="ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO,IGNORE_SPACE,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY,PIPES_AS_CONCAT,STRICT_TRANS_TABLES"
1. Create Table:
DROP TABLE IF EXISTS "core"."decimaltest";
CREATE TABLE "core"."decimaltest"(
"pk" int(11) NOT NULL AUTO_INCREMENT,
"col1" decimal(8,5) NOT NULL DEFAULT '0.00000',
PRIMARY KEY ("pk")
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
2. Create Procedure:
DELIMITER $$
DROP PROCEDURE IF EXISTS `dec_proc` $$
CREATE DEFINER="root"@"127.0.0.1" PROCEDURE "dec_proc"( INOUT p_col1 decimal(8,5) )
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;
BEGIN
INSERT INTO core.decimaltest( pk, col1 ) VALUES( NULL, IFNULL( p_col1, DEFAULT(col1) ) );
SELECT col1 INTO p_col1;
END;
COMMIT;
END $$
DELIMITER ;
3. Create Java Class:
package test;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.Properties;
public class DecimalTest{
private static final void test( final CallableStatement cs ){
try{
final ResultSet rs=cs.executeQuery();
if( rs!=null ){
final BigDecimal d=cs.getBigDecimal( 1 );
System.out.println( d==null?"(IS NULL)":"OUT:"+d.toPlainString()+(cs.wasNull()?" (WAS NULL)":" (WAS NOT NULL)") );
}
}
catch( final Exception e ){
e.printStackTrace( System.out );
}
}
public static final void main( final String v[] ) throws Exception{
Class.forName( "com.mysql.jdbc.Driver" );
final Properties p=new Properties();
p.setProperty( "user","root" );
p.setProperty( "password","WHATEVER" );
final Connection c=DriverManager.getConnection( "jdbc:mysql://127.0.0.1:3306/core?jdbcCompliantTruncation=false", p );
final CallableStatement cs=c.prepareCall( "{CALL core.dec_proc(?)}" );
cs.registerOutParameter( 1, java.sql.Types.DECIMAL );
cs.setNull( 1, java.sql.Types.DECIMAL );
test( cs );
cs.registerOutParameter( 1, java.sql.Types.NULL );
cs.setNull( 1, java.sql.Types.NULL );
test( cs );
cs.registerOutParameter( 1, java.sql.Types.DECIMAL );
cs.setBigDecimal( 1, null );
test( cs );
cs.registerOutParameter( 1, java.sql.Types.NULL );
cs.setBigDecimal( 1, null );
test( cs );
cs.registerOutParameter( 1, java.sql.Types.DECIMAL );
cs.setObject( 1, null );
test( cs );
cs.registerOutParameter( 1, java.sql.Types.NULL );
cs.setObject( 1, null );
test( cs );
//ALWAYS WORKS
cs.registerOutParameter( 1, java.sql.Types.DECIMAL );
cs.setBigDecimal( 1, BigDecimal.ZERO );
test( cs );
}
}
RESULT (x5):
For all exception the last test(cs) call.
java.sql.SQLException: Incorrect decimal value: 'null' for column 'p_col1' at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2111)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2273)
at com.mysql.jdbc.CallableStatement.executeQuery(CallableStatement.java:943)
at test.DecimalTest.test(DecimalTest.java:14)
at test.DecimalTest.main(DecimalTest.java:34)
All help and insight is appreciated,
Jack