db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: silent truncation, correct?
Date Tue, 08 May 2012 17:46:13 GMT
On 5/8/12 10:12 AM, Rick Hillegas wrote:
> On 5/8/12 9:40 AM, dag.wanvik@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

Mime
View raw message