DB2 TIMESTAMP to STRING

ladine · #1 (**permalink**) 07-16-10, 14:29

HI,

I migrate from v7 to v9 and I have a problem when my application wants to display a db2 timestamp data in a string field.

In v7, we used the following lines in the db2cli.ini:

PATCH1=131072
LOBMAXCOLUMNSIZE=1048575
LONGDATACOMPAT=1

But PATCH1=131072 is now depreciated.

So I tried to add:

DateTimeStringFormat=ISO
MapTimestampDescribe = 1
MapTimestampCDefault = 1
PATCH2=33

and now I have the CLI message "CLI0111E Numeric value out of range. SQLSTATE=22033" !

How could I have the same behaviour that with the old configuration
"PATCH1=131072" ?

Could you help me please? It's very important.

Thanks,
Lad.

PS: Excuse me for my bad english.

przytula_guy · #2 (**permalink**) 07-17-10, 03:58

what was the patch for and which date format do you need ??

ladine · #3 (**permalink**) 07-21-10, 03:25

I want these formats:
for timestamp data : 'YYYY-MM-DD-00.00.00.000000'
for date data: 'YYYY-MM-DD'

We had in the v7 db2cli.ini :

[MyDBAlias]
PATCH1=131072
LOBMAXCOLUMNSIZE=1048575
LONGDATACOMPAT=1
DESCRIPTION=
DBALIAS=MYDBALIAS

We have in the v9 db2cli.ini

[MyDBAlias]
DateTimeStringFormat=ISO
MapTimestampDescribe = 1
MapTimestampCDefault = 1
PATCH2="24,33"
DBALIAS=MYDBALIAS

In the command center it seems OK now (with the new patch1 and patch2 values) but from my application I have the CLI0111E error.

An idea?

ladine · #4 (**permalink**) 07-22-10, 05:34

Hi,

I put the trace

-With the DB2 7.2 client :

Quote:

SQLDescribeCol( hStmt=1:3, iCol=3, pszColName=&00215978, cbColNameMax=32, pcbColName=&002159a4, pfSQLType=&002159b4, pcbColDef=&002159c4, pibScale=&002159d8, pfNullable=&002159e8 )
---> Time elapsed - +3,270000E-003 seconds

SQLDescribeCol( pszColName="DATE_TRAIT_FICHIER" - X"444154455F54524149545F46494348494552", pcbColName=18, pfSQLType=SQL_CHAR, pcbColDef=26, pibScale=0, pfNullable=SQL_NO_NULLS )
<--- SQL_SUCCESS Time elapsed - +7,004200E-002 seconds

SQLColAttributes( hStmt=1:3, iCol=3, fDescType=SQL_COLUMN_LENGTH, rgbDesc=NULL, cbDescMax=0, pcbDesc=NULL, pfDesc=&00215b68 )
---> Time elapsed - +3,454000E-003 seconds

SQLColAttributes( pfDesc=26 )
<--- SQL_SUCCESS Time elapsed - +3,463800E-002 seconds

SQLColAttributes( hStmt=1:3, iCol=3, fDescType=SQL_DESC_DISTINCT_TYPE, rgbDesc=&00215c20, cbDescMax=28, pcbDesc=&00215c48, pfDesc=NULL )
---> Time elapsed - +3,369000E-003 seconds

SQLColAttributes( rgbDesc="" - X"", pcbDesc=0 )
<--- SQL_SUCCESS Time elapsed - +3,856000E-002 seconds

SQLBindCol( hStmt=1:3, iCol=3, fCType=SQL_C_CHAR, rgbValue=&00234b4a, cbValueMax=27, pcbValue=&00234bf0 )
---> Time elapsed - +3,048000E-003 seconds
SQLBindCol( )
<--- SQL_SUCCESS Time elapsed - +2,933600E-002 seconds

SQLExecute( hStmt=1:3 )
---> Time elapsed - +3,312000E-003 seconds
sqlccsend( ulBytes - 232 )
sqlccsend( Handle - 40082160 )
sqlccsend( ) - rc - 0, time elapsed - +6,513000E-003
sqlccrecv( )
sqlccrecv( ulBytes - 32646 ) - rc - 0, time elapsed - +6,424400E-002

SQLExecute( )
<--- SQL_SUCCESS Time elapsed - +9,049500E-002 seconds

SQLFetch( hStmt=1:3 )
---> Time elapsed - +3,573000E-003 seconds

SQLFetch( )
<--- SQL_SUCCESS Time elapsed - +1,260600E-002 seconds
( iCol=1, fCType=SQL_C_LONG, rgbValue=12, pcbValue=4 )

( iCol=2, fCType=SQL_C_TYPE_DATE, rgbValue=2004-12-01, pcbValue=6 )

( iCol=3, fCType=SQL_C_CHAR, rgbValue="2004-12-01-09.38.05.000000" - X"323030342D31322D30312D30392E33382E30352E30303030 3030", pcbValue=26 )

- With the DB2 9.7 client:

Quote:

SQLDescribeCol( hStmt=1:3, sCol=3, pszColName=&00200be4, sColNameMaxByteLen=32, psColNameCharLen=&00200c10, psSQLType=&00200c20, puiColDef=&00200c30, psScale=&00200c44, psNullable=&00200c54 )
---> Time elapsed - +3,093000E-003 seconds

SQLDescribeCol( pszColName="DATE_TRAIT_FICHIER", psColNameCharLen=18, psSQLType=SQL_CHAR, pcbColDef=26, psScale=6, psNullable=SQL_NO_NULLS )
<--- SQL_SUCCESS Time elapsed - +5,790800E-002 seconds

SQLColAttributes( hStmt=1:3, iCol=3, sDescType=SQL_COLUMN_LENGTH, rgbDesc=<NULL pointer>, cbDescMax=0, pcbDesc=<NULL pointer>, pfDesc=&00200dd4 )
---> Time elapsed - +3,080000E-003 seconds

SQLColAttributes( piNumericAttribute=16 )
<--- SQL_SUCCESS Time elapsed - +3,634100E-002 seconds

SQLColAttributes( hStmt=1:3, iCol=3, sDescType=SQL_DESC_DISTINCT_TYPE, rgbDesc=&00200e8c, cbDescMax=28, pcbDesc=&00200eb4, pfDesc=<NULL pointer> )
---> Time elapsed - +3,034000E-003 seconds

SQLColAttributes( pCharAttribute="", psCharAttributeByteLen=0 )
<--- SQL_SUCCESS Time elapsed - +4,394000E-002 seconds

SQLBindCol( hStmt=1:3, iCol=3, fCType=SQL_C_CHAR, rgbValue=&0024ad2a, cbValueMax=17, pcbValue=&0024adc8 )
---> Time elapsed - +3,526000E-003 seconds

SQLBindCol( )
<--- SQL_SUCCESS Time elapsed - +2,932100E-002 seconds

SQLExecute( hStmt=1:3 )
---> Time elapsed - +4,200000E-003 seconds
( Requested Cursor Attributes=x'03000008' )
( Reply Cursor Attributes=x'0A000800' )
( Actual Cursor Attributes=x'0B000800' )

SQLExecute( )
<--- SQL_SUCCESS Time elapsed - +9,772500E-002 seconds

SQLFetch( hStmt=1:3 )
---> Time elapsed - +3,104000E-003 seconds

( iConvFunction=363, iConv=363, iCol=3, iRow=1,
pSourceBuff=&0334008a, pTargetBuff=&0024ad2a,
iSourceBuffSize=32757, iSourceBytesLeft=32715, iTargetBuffSize=17, iTargetBytesWritten=4,
iTracePoint=907, iTraceFunction=424673658,
Current Source Buffer Address=&03340060,
Current Source Buffer Contents (Data)=x'FF009F050000323031302D30362D3138323031302 D30362D31382D32302E35...- A very long list of characters - 02020202020' )
( pBoParms->pSqldd=" SQLDD Information
~~~~~~~~~~~~~~~~~
numAllocVals = 19
numVals = 14
numExtDtaVals = 0
numNonNullExtDtaVals = 0
numSqlArrayVals = 0
numNonDefScaleTS = 0
SQLAM level received in reply = 8
Default Data Type Definition = 2
Max Row Size = 162
flags = 2
codepage = 1252
singleByteCP = 1252
doubleByteCP = 0
xmlCP = 1208
DDvalue[0] DDtype = 4, DDlen = 4, DDExtent = 0, DDcodepage = 0, DDExtDtaLenSize = 0
DDvalue[1] DDtype = 56, DDlen = 10, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
DDvalue[2] DDtype = 60, DDlen = 26, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
DDvalue[3] DDtype = 72, DDlen = 15, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
DDvalue[4] DDtype = 56, DDlen = 10, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
DDvalue[5] DDtype = 73, DDlen = 12, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
DDvalue[6] DDtype = 73, DDlen = 12, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
DDvalue[7] DDtype = 72, DDlen = 4, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
DDvalue[8] DDtype = 72, DDlen = 4, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
DDvalue[9] DDtype = 56, DDlen = 10, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
DDvalue[10] DDtype = 56, DDlen = 10, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
DDvalue[11] DDtype = 74, DDlen = 3, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
DDvalue[12] DDtype = 74, DDlen = 3, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
DDvalue[13] DDtype = 73, DDlen = 32, DDExtent = 0, DDcodepage = 1252, DDExtDtaLenSize = 0
There is no extension for this dataDescriptor
" )

SQLFetch( )
<--- SQL_ERROR Time elapsed - +9,758200E-002 seconds

SQLError( hEnv=0:1, hDbc=0:0, hStmt=1:3, pszSqlState=&002086a8, pfNativeError=&002086bc, pszErrorMsg=&002086cc, cbErrorMsgMax=511, pcbErrorMsg=&002088d8 )
---> Time elapsed - +3,062000E-003 seconds
( iRowNumber=1, iColumnNumber=3 )

SQLError( pszSqlState="22003", pfNativeError=-99999, pszErrorMsg="[IBM][CLI Driver] CLI0111E Valeur numérique hors plage. SQLSTATE=22003", pcbErrorMsg=71 )
<--- SQL_SUCCESS Time elapsed - +6,111600E-002 seconds

SQLGetSqlca( hEnv=0:1, hDbc=0:0, hStmt=1:3, pSqlcaOut=&00209398 )
---> Time elapsed - +3,802000E-003 seconds

SQLGetSqlca( )
<--- SQL_SUCCESS Time elapsed - +2,184600E-002 seconds

SQLError( hEnv=0:1, hDbc=0:0, hStmt=1:3, pszSqlState=&002097e0, pfNativeError=&002097f4, pszErrorMsg=&00209804, cbErrorMsgMax=511, pcbErrorMsg=&00209a10 )
---> Time elapsed - +3,083000E-003 seconds

SQLError( )
<--- SQL_NO_DATA_FOUND Time elapsed - +3,976400E-002 seconds

Maybe it could help you to have an idea!

Internet Services

Web Development

Digital Marketing

Consumer Tech