|
Problem IC62393
|
Status: Closed |
.NET STORED PROCEDURES AND UDF'S WITH DATE TYPE INPUT PARAMETER CAUSE SQL04302N IN DATE TYPE AS TIMESTAMP(0) MODE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Platform: Windows
The Oracle Compatibility Feature DATE data type as Timestamp(0)
is enabled (DB2_COMPATIBILITY_VECTOR=ORA or 040) and a .NET
StoredProcedure or UDF with a DATE data type input parameter is
invoked. You receive a SQL04302N error with the token
"String was not recognized as a valid DateTime".
Example procedure definition:
CREATE PROCEDURE test.clrdate
(
IN in1_DT DATE
)
NOT DETERMINISTIC
NULL CALL
OLD SAVEPOINT LEVEL
EXECUTION CONTROL FILEWRITE
LANGUAGE CLR
EXTERNAL NAME 'testdate.dll:test.date!testdateclr'
FENCED
EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
PARAMETER STYLE SIMPLE CALL WITH NULLS
NO DBINFO@
Example Call:
db2 call test.clrdate('1761-09-14 03:09:29.0')
SQL04302N Exception "String was not recognized as a valid
DateTime"
The db2diag.log contains the following entries:
2009-07-15-11.17.36.421000+540 I1511441F525 LEVEL: Error
PID : 2544 TID : 2444 PROC :
db2fmp64.exe
INSTANCE: DB2 NODE : 000
EDUID : 2444
FUNCTION: DB2 UDB, DB2 .NET Routine Infrastructure,
sqlecMethodManager.InvokeMet
hod, probe:10
MESSAGE : Class test.date, function testdateclr, parameter 0
caused exception
String was not recognized as a valid DateTime.
DATA #1 : Hexdump, 4 bytes
0x00000000061A7648 : 0701 0000
....
2009-07-15-11.17.36.421000+540 I1511968F787 LEVEL: Error
PID : 2544 TID : 2444 PROC :
db2fmp64.exe
INSTANCE: DB2 NODE : 000
EDUID : 2444
FUNCTION: DB2 UDB, DB2 .NET Routine Infrastructure,
sqlecRoutineLoaderClass.sqle
cRunDotNetRo, probe:10
DATA #1 : String, 360 bytes
System.FormatException: String was not recognized as a valid
DateTime.
at IBM.Data.DB2.Routine.sqlecMethodManager.InvokeMethod()
at
IBM.Data.DB2.Routine.sqlecRoutineLoaderClass.sqlecRunDotNetRouti
ne(IntPtr
ufob, IntPtr pSqlca, IntPtr nativeThreadID, IntPtr
invokerSQLSTATE, IntPtr invok
erMessage, IntPtr scratchpad, IntPtr dbinfo, IntPtr
processReturn)
DATA #2 : Hexdump, 4 bytes
0x00000000061A57D8 : 32EF FFFF
2... |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* DB2 LUW on Windows with .NET routines using DATE as *
* Timestamp(0) as input parameter *
****************************************************************
* PROBLEM DESCRIPTION: *
* A SQL04302N error with the token "String was not recognized *
* as a valid DateTime" is returned when invoking a LANGUAGE *
* CLR Stored Procedure or UDF which has been cataloged with an *
* input parameter whose data type is DATE. *
* *
* Example procedure definition: *
* CREATE PROCEDURE test.clrdate *
* ( *
* IN in1_DT DATE *
* ) *
* NOT DETERMINISTIC *
* NULL CALL *
* OLD SAVEPOINT LEVEL *
* EXECUTION CONTROL FILEWRITE *
* LANGUAGE CLR *
* EXTERNAL NAME 'testdate.dll:test.date!testdateclr' *
* FENCED *
* EXTERNAL ACTION *
* INHERIT SPECIAL REGISTERS *
* PARAMETER STYLE SIMPLE CALL WITH NULLS *
* NO DBINFO@ *
* *
* Example Call: *
* db2 call test.clrdate('1761-09-14 03:09:29.0') *
* SQL04302N Exception "String was not recognized as a valid *
* *
* DateTime" *
* *
* The db2diag.log contains the following entries: *
* 2009-07-15-11.17.36.421000+540 I1511441F525 LEVEL: Error *
* PID : 2544 TID : 2444 PROC : *
* db2fmp64.exe *
* INSTANCE: DB2 NODE : 000 *
* EDUID : 2444 *
* FUNCTION: DB2 UDB, DB2 .NET Routine Infrastructure, *
* sqlecMethodManager.InvokeMet *
* hod, probe:10 *
* MESSAGE : Class test.date, function testdateclr, parameter 0 *
* *
* caused exception *
* String was not recognized as a valid DateTime. *
* DATA #1 : Hexdump, 4 bytes *
* 0x00000000061A7648 : 0701 0000 *
* .... *
* *
* 2009-07-15-11.17.36.421000+540 I1511968F787 LEVEL: Error *
* PID : 2544 TID : 2444 PROC : *
* db2fmp64.exe *
* INSTANCE: DB2 NODE : 000 *
* EDUID : 2444 *
* FUNCTION: DB2 UDB, DB2 .NET Routine Infrastructure, *
* sqlecRoutineLoaderClass.sqle *
* cRunDotNetRo, probe:10 *
* DATA #1 : String, 360 bytes *
* System.FormatException: String was not recognized as a valid *
* *
* DateTime. *
* at IBM.Data.DB2.Routine.sqlecMethodManager.InvokeMethod() *
* at *
* IBM.Data.DB2.Routine.sqlecRoutineLoaderClass.sqlecRunDotNetRou
* ne(IntPtr *
* ufob, IntPtr pSqlca, IntPtr nativeThreadID, IntPtr *
* invokerSQLSTATE, IntPtr invok *
* erMessage, IntPtr scratchpad, IntPtr dbinfo, IntPtr *
* processReturn) *
* DATA #2 : Hexdump, 4 bytes *
* 0x00000000061A57D8 : 32EF FFFF *
* 2... *
****************************************************************
* RECOMMENDATION: *
* Install V97 fp1 *
**************************************************************** |
| Local Fix: |
Change the type of the input parameter in the procedure
definition from DATE to TIMESTAMP(0). |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Fixpack 1 for DB2 V9.7 includes the fix for this issue. When
invoking a LANGUAGE CLR Stored Procedure or UDF with an input
parameter of data type DATE the procedure will execute
successfully. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 05.08.2009
29.12.2009
29.12.2009 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.,
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
