SEO

While stranded on information super highway W Gemini wrote:
> Hemant Shah wrote:
>
>> While stranded on information super highway W Gemini wrote:
>>
>>>Hemant Shah wrote:
>>>
>>>>Folks,
>>>>
>>>> I recently upgraded/migrated my database from UDB 7.2 on AIX to UDB 8.1 on
>>>> Linux. Now I cannot insert data into a TIMESTAMP column. The string I am
>>>> using for TIMESTAMP is in ISO format.
>>>>
>>>> The old database was using ISO-8859-1 character set, the new database is
>>>> using UTF-8. I have a perl script that inserts data into one of the table
>>>> that has time stamp column. The script worked in UDB 7 database but fails
>>>> with SQL0180N in UDB 8.
>>>>
>>>>
>>>>1246: $InsertStmt_CodeChanged_Hdl->execute($SourceNum, $RevBranch, $RevDecimal,
>>>>1247: $TimeStamp, $Author, $Command, $SubCommand,
>>>>1248: $LinesAdded, $LinesDeleted);
>>>> DB<2> p $TimeStamp
>>>>2004-03-28-17:06:39.816398
>>>> DB<3> n
>>>>lidp12|InsertCodeChangedData: DBD:B2::st execute failed: [IBM][CLI Driver][DB2/LINUX] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
>>>>
>>>>
>>>>I tried to run following command on the client system but that did not help
>>>>either.
>>>>
>>>> db2 bind @db2cli.lst DATETIME ISO blocking all grant public
>>>> db2 bind @db2ubind.lst DATETIME ISO blocking all grant public
>>>>
>>>> How can I fix it? I need to fix it A.S.A.P.
>>>>
>>>> Thanks.
>>>>
>>>
>>>Try 2004-03-28-17.06.39.816398.
>>>
>>
>>
>> That worked, but I have 2 questions.
>>
>> 1) What could have changed in UDB V8. The script worked fine with UDB 7.2.
>>
>> 2) I get the timestamp from the database using CURRENT TIMESTAMP and the
>> database returns it in "2004-03-28-17:06:39.816398" format, but when I try
>> to insert it I get error. Instead of using $TimeStamp, I also tried
>> "CURRENT TIMESTAMP" in the insert statement and got same error.
>> Why is it that the database returns it in one format and wants it in
>> different format for insert?
>>
>>
>>
> I am not quite sure. There are two formats db2 uses,
> yyyy-mm-dd-hh.mm.ss.nnnnnn for regular and yyyy-mm-dd hh:mm:ss.nnnnnn
> for ODBC/CLI. I have never seen people use yyyy-mm-dd-hh:mm:ss.nnnnnn.
> Could you send me a small script that is failing please? I will take a
> look.
>

The code is part of a large perm module, but here is the fragment of the code
that is using TIMESTAMP.

my $Junk;

($Junk, $TimeStamp) = $DbHandle->selectrow_array("SELECT COUNT(*),
CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1");
# Timestamp returned is 2004-04-05 14:01:59.780272

# Replace space between date and time so that it can be used in insert
# statement.
$TimeStamp =~ s: :-:;

# Timestamp now is 2004-04-05-14:01:59.780272

$InsertStmt_CodeChanged = "INSERT INTO GBLCODE.CODECHANGED
(SRC_NUM, BRANCH, REVISION, CHG_DATE, USER_NAME,
COMMAND, SUB_COMMAND, LINES_ADDED, LINES_DELETED)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?)";

$InsertStmt_CodeChanged_Hdl->execute($SourceNum, $RevBranch, $RevDecimal,
$TimeStamp, $Author, $Command,
$SubCommand, $LinesAdded,
$LinesDeleted);




This code worked on DB2 UDB EE 6 and 7 on AIX, but fails under UDB 8 ESE
on Linux.



--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.