ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mario Ds Briggs <mario.bri...@in.ibm.com>
Subject Re: Ibatis conversion error on DB2
Date Fri, 04 Sep 2009 13:16:25 GMT
Based on the proof you have provided (the traces, the SQLSTATES of the
error & spring DAO exception - DataIntegrityViolationException) there is
nothing further you need to look at execpt values being inserted/updated.

SQL0406N  A numeric value in the UPDATE or INSERT statement is not
      within the range of its target column.

Explanation:

The value of a host variable or a numeric value calculated during
processing of the UPDATE or INSERT SQL statement is outside the target
column range. This problem may be caused by the values occurring in the
object column, the SQL operation being performed on those values, or
both.

The statement cannot be processed.

User response:

See the explanation of message SQL0405 for ranges allowed for numeric
data types except decimal floating-point.
*  For DECFLOAT(16) values:
   *  0
   *  In the positive range of 1.000000000000000E-383 to
      9.999999999999999E+384
   *  In the negative range of -9.999999999999999E+384 to
      -1.000000000000000E-383

*  For DECFLOAT(34) values:
   *  0
   *  In the positive range of 1.000000000000000000000000000000000E-6143
      to 9.999999999999999999999999999999999E+6144
   *  In the negative range of
      -9.999999999999999999999999999999999E+6144 to
      -1.000000000000000000000000000000000E-6143

These are the limits of normal decimal floating-point numbers. Valid
decimal floating-point values include the special values INFINITY,
-INFINITY, NAN, -NAN, SNAN, and -SNAN. These special values are illegal
for all other DB2 numeric types.

NOTE: For system catalog updates, see the SQL Reference for valid ranges
in various columns of updatable catalogs.

 sqlcode: -406

 sqlstate: 22003

SQL0302N  The value of a host variable in the EXECUTE or OPEN statement
      is too large for its corresponding use.

Explanation:

The value of an input host variable was found to be too large for its
use in the SELECT, VALUES, or prepared statement. One of the following
occurred:
*  The corresponding host variable or parameter marker used in the SQL
   statement is defined as string, but the input host variable contains
   a string that is too long.
*  The corresponding host variable or parameter marker used in the SQL
   statement is defined as numeric, but the input host variable contains
   a numeric value that is out of range.
*  The terminating NUL character is missing from the C language
   NUL-terminated character string host variable.
*  Federated system users: in a pass-through session, a data
   source-specific restriction might have been violated.

This error occurs as a result of specifying either an incorrect host
variable or an incorrect SQLLEN value in an SQLDA on an EXECUTE or OPEN
statement.

The statement cannot be processed.

User response:

Ensure that the input host variable value is the correct type and
length. If the input host variables supply values to parameter markers,
match values with the implied data type and length of the parameter
marker.

Federated system users: for a pass-through session, determine what data
source is causing the error (see the Troubleshooting Guide for
procedures to follow to identify the failing data source). Examine the
SQL dialect for that data source to determine which specific restriction
has been violated, and adjust the failing statement as needed.

 sqlcode: -302

 sqlstate: 22001, 22003
Mario




                                                                           
             adi203793                                                     
             <aditya.prajapati                                             
             @tcs.com>                                                  To 
                                       user-java@ibatis.apache.org         
             04/09/2009 18:20                                           cc 
                                                                           
                                                                   Subject 
             Please respond to         Re: Ibatis conversion error on DB2  
             user-java@ibatis.                                             
                apache.org                                                 
                                                                           
                                                                           
                                                                           
                                                                           





No,
It's not like that,When the sql query failed.
I have tried with same parameter and It worked properly.
As I mentioned earlier I am not able to replicate it.
This was the first and last time query failed while updating and
same case while inserting a record.Next time it inserted properly.
These are the only 2 intanse when code crashed in past 1.5 year.
We have rollout on very next week.
Please suggest me.
Thanks.


Mario Briggs wrote:
>
> when it succeeds it means u inserted a value which was in the range. When
> it fails, u or user was try to insert a value that was not in the
required
> range.
>
>
>
>

>              adi203793

>              <aditya.prajapati

>              @tcs.com>
To
>                                        user-java@ibatis.apache.org

>              04/09/2009 14:10
cc
>

>
Subject
>              Please respond to         Re: Ibatis conversion error on DB2

>              user-java@ibatis.

>                 apache.org

>

>

>

>

>
>
>
>
>
> Hi Mario,
> Thanks for the reply.
> The OS name i5/AS400. One more thing value which I have inserted is
> perfectly fine.
> As I mentioned sometimes it failed but most of the times it worked.
> Please help me out!!!!
>
>
> Mario Briggs wrote:
>>
>> Nothing to do with ibatis. Look up the error definitions of SQLStates
>> SQL302 & SQL406 and you will understand why it occurs arbitarily :-)
>>
>> The value that is trying to be inserted in out-of-range for the column
in
>> the db.
>>
>> Mario
>>
>>
>>
>>
>
>>              adi203793
>
>>              <aditya.prajapati
>
>>              @tcs.com>
> To
>>                                        user-java@ibatis.apache.org
>
>>              04/09/2009 11:33
> cc
>>
>
>>
> Subject
>>              Please respond to         Ibatis conversion error on DB2
>
>>              user-java@ibatis.
>
>>                 apache.org
>
>>
>
>>
>
>>
>
>>
>
>>
>>
>>
>>
>>
>> I have been using Ibatis through Spring to acccessing data from DB2 to
>> java.
>> I have been run this query so many times sometimes it works and
sometimes
>> not.
>> means I m not able to replicate it.while updating the record I got the
>> error
>> like this:
>>
>> UPDATE STHEDRL1 SET STHRIN=? WHERE STHINT=? AND STHNUM=? AND STHSCD=?
AND
>> STHADT=?
>> Types: [java.lang.String, java.lang.String, java.lang.Integer,
>> java.lang.String, java.lang.Integer]
>> ERRORID rg.springframework.dao.DataIntegrityViolationExc eption
>> --EXCEPTION DETAILS--
>> Message : SqlMapClient operation; SQL [];
>> --- Cause: java.sql.SQLException: [SQL0302] Conversion error on host
>> variable or parameter *N.; nested exception is
>> com.ibatis.common.jdbc.exception.NestedSQLExceptio n:
>> --- The error occurred while applying a parameter map.
>> --- Check the ShopDatesEntrySqlMap.updateRingForUltraInspectionR
>> ecord-InlineParameterMap.
>> --- Check the statement (update failed).
>> Please send ur suggesstion.Thanks in advance.
>>
>> and after few days again I got the error:which I have never received
>> earlier.we have used this query so many times
>> and after few minuts its working fine again.Please send ur
>> suggesstion.Thanks in advance.
>>
>> Cause: java.sql.SQLException: [SQL0406] Conversion error on assignment
to
>> column TRNPTM.
>> at org.springframework.jdbc.support.SQLStateSQLExcept
>> ionTranslator.translate(SQLStateSQLExceptionTransl ator.java:111)
>> at org.springframework.jdbc.support.SQLErrorCodeSQLEx
>> ceptionTranslator.translate(SQLErrorCodeSQLExcepti
onTranslator.java:322)
>> at org.springframework.orm.ibatis.SqlMapClientTemplat
>> e.execute(SqlMapClientTemplate.java:212)
>> at org.springframework.orm.ibatis.SqlMapClientTemplat
>> e.insert(SqlMapClientTemplate.java:397)
>> Caused by: com.ibatis.common.jdbc.exception.NestedSQLExceptio n:
>> --- The error occurred while applying a parameter map.
>> --- Check the
>> PresentationData.insertCarMarkIntoMFCARCHG-InlineParameterMap.
>> --- Check the statement (update failed).
>>
>> --
>> View this message in context:
>>
>
http://www.nabble.com/Ibatis-conversion-error-on-DB2-tp25288763p25288763.html

>
>>
>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
>> For additional commands, e-mail: user-java-help@ibatis.apache.org
>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
>> For additional commands, e-mail: user-java-help@ibatis.apache.org
>>
>>
>>
>
> --
> View this message in context:
>
http://www.nabble.com/Ibatis-conversion-error-on-DB2-tp25288763p25290403.html

>
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
>

--
View this message in context:
http://www.nabble.com/Ibatis-conversion-error-on-DB2-tp25288763p25293765.html

Sent from the iBATIS - User - Java mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org




---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org


Mime
View raw message