Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    16

    Unanswered: DB2 Timestamp Preparestatement Update error

    Hi all,

    I have a preparestatement as follows :

    "INSERT INTO TEST_TABLE1(SYSTEM_ID, T_BIGINT, T_SMALLINT, T_VARCHAR, T_DECIMAL, T_DATE, T_REAL, T_INTEGER, T_TIMESTAMP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"

    This works fine :

    I retrieve the value and update the fields with same values back and use the following

    "UPDATE TEST_TABLE1 SET T_BIGINT=? , T_SMALLINT=? , T_VARCHAR=? , T_DECIMAL=? , T_DATE=? , T_REAL=? , T_INTEGER=? , T_TIMESTAMP=? WHERE SYSTEM_ID=? "

    When execute under jdk1.3.x and DB2 FixPak3
    I get the following error :

    1937 [main] ERROR : COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001

    In short, the insert stmt works fine but the update doesn't. I tested with other tables and I realize that tables with column of type java.sql.Date and java.sql.Timestamp fail in prepareStatement's update (but work fine with insert).


    I checked into the SQL log and I get is :

    SQLExecute( hStmt=1:2 )
    ---> Time elapsed - +2.440000E-004 seconds
    ( Package="SYSSH200 ", Section=4 )
    ( Row=1, iPar=1, fCType=SQL_C_BIGINT, rgbValue=1001, pcbValue=8, piIndicatorPtr=8 )
    ( Row=1, iPar=2, fCType=SQL_C_BIGINT, rgbValue=1101, pcbValue=8, piIndicatorPtr=8 )
    ( Row=1, iPar=3, fCType=SQL_C_WCHAR, rgbValue="E" - - X"4500", pcbValue=2, piIndicatorPtr=2 )
    ( Row=1, iPar=4, fCType=SQL_C_TYPE_TIMESTAMP, rgbValue=2003-09-25 12:02:27.696000000, pcbValue=16, piIndicatorPtr=16 )
    ( Row=1, iPar=5, fCType=SQL_C_TYPE_TIMESTAMP, rgbValue=2003-09-25 12:02:27.836000000, pcbValue=16, piIndicatorPtr=16 )
    ( Row=1, iPar=6, fCType=SQL_C_WCHAR, rgbValue="E" - - X"4500", pcbValue=2, piIndicatorPtr=2 )
    ( COMMIT REPLY RECEIVED=1 )
    ( Re-executing since original execute got SQLCODE=-301 )
    ( Row=1, iPar=1, fCType=SQL_C_BIGINT, rgbValue=1001, pcbValue=8, piIndicatorPtr=8 )
    ( Row=1, iPar=2, fCType=SQL_C_BIGINT, rgbValue=1101, pcbValue=8, piIndicatorPtr=8 )
    ( Row=1, iPar=3, fCType=SQL_C_WCHAR, rgbValue="E" - - X"4500", pcbValue=2, piIndicatorPtr=2 )
    ( Row=1, iPar=4, fCType=SQL_C_TYPE_TIMESTAMP, rgbValue=2003-09-25 12:02:27.696000000, pcbValue=16, piIndicatorPtr=16 )
    ( Row=1, iPar=5, fCType=SQL_C_TYPE_TIMESTAMP, rgbValue=2003-09-25 12:02:27.836000000, pcbValue=16, piIndicatorPtr=16 )


    ( iConvFunction=26, iConv=56, iPar=5, iRow=1,
    pSourceBuff=&1a73ea28, pTargetBuff=&1a92f0fc, pTargetBuffStart=&1a92f0b4,
    iTargetBytesLeft=32650, uiTotalBytesWritten=72, iSourceByteLength=16,
    iTracePoint=130, iTraceFunction=705, iTraceComponent=42,
    pTargetBuffStart (Data)=x'0000E903000000000000000004313130310045202 0202020202020202D202020202020202020202020202020003 23030332D30392D32352D31322E30322E32372E36393630303 000' )
    ( pBiParms->pSqldd=" SQLDD Information
    ~~~~~~~~~~~~~~~~~
    numAllocVals = 6
    numVals = 6
    numLobVals = 0
    numNonNullLobVals = 0
    Default Data Type Definition = 2
    Max Row Size = 0
    flags = 0
    codepage = 1252
    singleByteCP = 1208
    doubleByteCP = 1200
    DDvalue[0] DDtype = 47, DDlen = 8, DDcodepage = 0, DDLobLenSize = 0
    DDvalue[1] DDtype = 87, DDlen = 30, DDcodepage = 1252, DDLobLenSize = 0
    DDvalue[2] DDtype = 61, DDlen = 26, DDcodepage = 1252, DDLobLenSize = 48
    DDvalue[3] DDtype = 61, DDlen = 26, DDcodepage = 1252, DDLobLenSize = 0
    DDvalue[4] DDtype = 87, DDlen = 1, DDcodepage = 1252, DDLobLenSize = 0
    DDvalue[5] DDtype = 47, DDlen = 8, DDcodepage = 0, DDLobLenSize = 0
    There is no extension for this dataDescriptor
    " )
    ( Unretrieved error message="[IBM][CLI Driver][DB2/NT] SQL0301N The value of input host variable or parameter number "2" cannot be used because of its data type. SQLSTATE=07006 " )
    ( COMMIT REQUESTED=1 )
    ( COMMIT REPLY RECEIVED=1 )

    SQLExecute( )
    <--- SQL_ERROR Time elapsed - +1.734240E-001 seconds

    has anybody come anything across like this.

    thanks
    Selva

  2. #2
    Join Date
    May 2003
    Posts
    16

    Db2 prepaprestatement update error

    Hi all,
    I narrowed the above problem to Timestamp and Date fields. they are the culprit

    Selva

  3. #3
    Join Date
    May 2003
    Posts
    16

    Corrected

    SORRY GUYS!, I found out that my order of parameter passing was wrong.

    Sorry for this thread.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •