On 5/8/12 10:12 AM, Rick Hillegas wrote:
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
Another possibly relevant detail: The CAST operator is supposed to
truncate String types as necessary, raising a warning (but not an error)
on truncation. See part 2 of the SQL Standard, section 6.12 (<cast
specification>), General Rules 10.c.ii and 11.c.ii.
The truncation behavior we're seeing may be due to the insertion of
implicit CASTs.
Note also that Derby is not the only database with this behavior:
http://stackoverflow.com/questions/4628140/sql-server-silently-truncates-varchars-in-stored-procedures
Regards,
-Rick
