On 5/8/12 9:40 AM, dag.wan...@oracle.com wrote:
Hi,
I see that Derby silently truncates a too long string argument to a
stored procedure with a formal argument of VARCHAR(n), cf. enclosed
program. Is this correct behavior? I'll try to grok the standard on
this, but it looks suspect to me.. The program prints 5 at the
"cs.execute", but throws an exception as expected at the "ps.execute".
Dag
-----------------------------------------------------------
package silentvarcharargtruncation;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
public class SilentVarcharArgTruncation {
public static void main(String[] args) throws SQLException {
Connection c =
DriverManager.getConnection("jdbc:derby:wombat;create=true");
Statement s = c.createStatement();
try {
s.executeUpdate("drop table t");
} catch (SQLException e) {}
try {
s.executeUpdate("drop procedure p");
} catch (SQLException e) {}
s.executeUpdate("create table t(v varchar(5))");
s.executeUpdate("create procedure p (a varchar(5)) modifies sql data "
+
"external name
'silentvarcharargtruncation.SilentVarcharArgTruncation.testLength' " +
"language java parameter style java");
CallableStatement cs = c.prepareCall("call p(?)");
cs.setString(1, "123456");
// This silently truncates
cs.execute();
PreparedStatement ps = c.prepareStatement("insert into t values(?)");
ps.setString(1, "123456");
// This does not truncate, throws
ps.execute();
}
public static void testLength (String s) throws SQLException {
System.out.println(s.length());
}
}
The same behavior is seen with Derby function:
connect 'jdbc:derby:memory:db;create=true';
create function parseInt( raw varchar( 2 ) ) returns int
language java parameter style java no sql external name
'java.lang.Integer.parseInt';
values ( parseInt( '1' ) );
values ( parseInt( '12' ) );
-- succeeds but is truncated to '12'
values ( parseInt( '123' ) );
Regards,
-Rick
