db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Marco Rico-Gomez <m.ricogo...@googlemail.com>
Subject Arithmetic operations and PreparedStatments
Date Tue, 01 Feb 2011 08:57:00 GMT
Hi all,

I have an update query that calculates and sets the value of an
decimal field in a single row. The field in question is defined as
decimal(20, 16) in the database and the calculation is based on
arithmetic operations. Here is the SQL statement:
update T_Professor set weight_In_B_D = ((((weight_In_B_D + 10) - 5) *
4) / 2) where (id = 1)

Now when I'm executing the sql statement (say in ij) everything works
fine. But when I'm trying to execute the same statement from within a
jdbc PreparedStatement with parameters I'll get the following
java.sql.SQLException: The resulting value is outside the range for
the data type DECIMAL/NUMERIC(31,31).
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
	... 34 more
Caused by: ERROR 22003: The resulting value is outside the range for
the data type DECIMAL/NUMERIC(31,31).
	at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
	at org.apache.derby.iapi.types.SQLDecimal.setWidth(Unknown Source)
	at org.apache.derby.exe.ac0b5b0099x012dxe042x51f3x000000e17f003.e3(Unknown
	at org.apache.derby.impl.services.reflect.DirectCall.invoke(Unknown Source)
	at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.doProjection(Unknown
	at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
	at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(Unknown
	at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(Unknown
	at org.apache.derby.impl.sql.execute.UpdateResultSet.collectAffectedRows(Unknown
	at org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown Source)
	at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown
	at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
	... 28 more
(I'm using version derby. The connection is made to an
embedded in memory instance.)

As of my understanding the two sql statements (raw and prepared)
should be the same and I'm wondering why executing the prepared
statement fails but executing the raw statment succeeds!

Here is the prepared statment:
update T_Professor set weight_In_B_D = ((((weight_In_B_D + ?) - ?) *
?) / ?) where (id = ?)
All parameters are set as Integer values. The parameter values are
identical to the values given in the raw statment.

Is this expected behaviour? What is the difference between the raw
statement and the preapred one? How can I make the prepared statement

I attached a test case to this mail that demonstrates this behaviour.

Thanks for your help!

\ Marco
Twitter: @mricog
Website: http://mrico.eu

View raw message