Rick Hillegas <[email protected]> writes: > On 5/8/12 10:12 AM, Rick Hillegas wrote: >> On 5/8/12 9:40 AM, [email protected] 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.
AKA https://issues.apache.org/jira/browse/DERBY-5537 :) > The truncation behavior we're seeing may be due to the insertion of > implicit CASTs. The above mentioned bug has a patch attached. Would be interesting to see if it makes these implicit casts raise a warning too. > 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 -- Knut Anders
