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:12:10 GMT
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

Mime
View raw message