By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,590 Members | 2,174 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,590 IT Pros & Developers. It's quick & easy.

DB2 V8.1 does not accept the adDBTimeStamp data type

P: n/a
When our application tries to send a parameter of the type adDBTimeStamp to
DB2 we an error message saying

SQLSTATE 22007 An invalid datetime format was detected; that is, an invalid
string representation or value was specified.

We get this message after upgradeing to DB2 Enterprise Edition V8.1 fixpack
6. On db2 version 7.2 it worked fine.

--
Regards
-----------
Odd Bjørn Andersen

ErgoSolutions AS
Postboks 4364 Nydalen, 0402 Oslo
Telefon 23 14 50 00, Telefaks 23 14 50 01
Direkte tlf.nr. 23 14 46 73, Mobilnr 970 84597
www.ergogroup.no
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Odd Bjørn Andersen wrote:
When our application tries to send a parameter of the type adDBTimeStamp to
DB2 we an error message saying

SQLSTATE 22007 An invalid datetime format was detected; that is, an invalid
string representation or value was specified.

We get this message after upgradeing to DB2 Enterprise Edition V8.1 fixpack
6. On db2 version 7.2 it worked fine.


Please post an example of string used and code demonstrating this problem. A
very short VB program inserting a timestamp value to a table defined with one
column f type timestamp would be sufficient.

Jan M. Nelken
Nov 12 '05 #2

P: n/a

"Jan M. Nelken" <Un**********@Invalid.Domain> wrote in message
news:41**************@Invalid.Domain...
Odd Bjørn Andersen wrote:
When our application tries to send a parameter of the type adDBTimeStamp to DB2 we an error message saying

SQLSTATE 22007 An invalid datetime format was detected; that is, an invalid string representation or value was specified.

We get this message after upgradeing to DB2 Enterprise Edition V8.1 fixpack 6. On db2 version 7.2 it worked fine.

Please post an example of string used and code demonstrating this problem.

A very short VB program inserting a timestamp value to a table defined with one column f type timestamp would be sufficient.

Jan M. Nelken


This is out VB program where the error occurs. As you can see it first sets
the parameter, and then executes a stored procedure. I have also included
the declarations for the stored procedure.
We don't want to make any changes to the code since this is working fine in
V7 of DB2. And I cannot see why an upgrade of DB2 should trigger a rewrite
of code. Is there anything else we should be aware of and which have changed
since V7, for instance setting of environment variables or settings in the
db2cli.ini file ?

THE VB CODE:

public function ........ ()

On Error GoTo errorhandling:

Dim rs As ADODB.Recordset

Dim cmd As Command

Dim param As ADODB.Parameter

Dim RetVal As Date
Set cmd = New ADODB.Command

cmd.ActiveConnection = mConn

cmd.CommandText = vardb + "HentServerTid"

cmd.CommandType = adCmdStoredProc
--> Set param = cmd.CreateParameter("tid", adDBTimeStamp, adParamOutput)

cmd.Parameters.Append param
Set cmd.ActiveConnection = mConn

--> Set rs = cmd.Execute
RetVal = CDate(cmd("tid"))
FinnDatoTid = RetVal

Set rs = Nothing

Exit Function

And this the declaration for the stored procedure:

#include <memory.h> /* for memcpy() */

#include <string.h>

#include <sqlenv.h>

#include <sql.h>

#include <sqlda.h>

#include <stdio.h>

SQL_API_RC SQL_API_FN HentServerTid(

void *reserved1,

void *reserved2,

struct sqlda *inout_sqlda,

struct sqlca *ca)

{

/* Declare Host Variables */

EXEC SQL BEGIN DECLARE SECTION;

--> char tid[27] = {'\0'};

EXEC SQL END DECLARE SECTION;

/* Declare a local SQLCA */

EXEC SQL INCLUDE SQLCA;

EXEC SQL WHENEVER SQLWARNING CONTINUE;

EXEC SQL WHENEVER SQLERROR GOTO error_exit;

short length;

....

--
Regards
-----------
Odd Bjørn Andersen

ErgoSolutions AS
Postboks 4364 Nydalen, 0402 Oslo
Telefon 23 14 50 00, Telefaks 23 14 50 01
Direkte tlf.nr. 23 14 46 73, Mobilnr 970 84597
www.ergogroup.no
Nov 12 '05 #3

P: n/a
Odd Bjørn Andersen wrote:
This is out VB program where the error occurs. As you can see it first sets
the parameter, and then executes a stored procedure. I have also included
the declarations for the stored procedure.
We don't want to make any changes to the code since this is working fine in
V7 of DB2. And I cannot see why an upgrade of DB2 should trigger a rewrite
of code. Is there anything else we should be aware of and which have changed
since V7, for instance setting of environment variables or settings in the
db2cli.ini file ?


I am guessing from posted fragments that stored procedure RETURNs a timestamp
value from DB2.

Here is simple test:

Create a test stored procedure GetTS() from command line like this:

db2 create procedure GetTS(OUT ts timestamp) language SQL begin values current
timestamp into ts end

You can test it from command line like this:

db2 call GetTS(?)
Now prepare VB program calling this stored procedure and test it.
Jan M. Nelken
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.