DB2, how to insert TIMESTAMP

[h-ta...@secom.co.jp]

Hello,

Is there a way to insert TIMESTAMP data or null
by 1 SQL?

$stmt = "INSERT INTO T_TAB1_T (C1, MEMO, T1) VALUES(?,?,?)";
This cannot insert null data.

$stmt = "INSERT INTO T_TAB1_T (C1, MEMO, T1) VALUES(?,?,TIMESTAMP(?))";
This is invalid param marker SQLSTATE=42610.

Regards,
H.T.

DROP TABLE T_TAB1_T;
CREATE TABLE T_TAB1_T (
C1 INTEGER NOT NULL,
MEMO VARCHAR(20),
T1 TIMESTAMP);

#!/usr/bin/perl
use strict;
use DBI;

my ($dbh, $sth, $stmt);
my ($seq, $seq_ok);

# connect
$dbh = DBI->connect("dbi:DB2:mydb", "ibm", "user",
{RaiseError => 1, AutoCommit => 1});
die "Error connect: $DBI::errstr" unless $dbh;
print "conn OK: $con\n";

# $stmt = "INSERT INTO T_TAB1_T (C1, MEMO, T1) VALUES(?,?,?)"; # error when null
$stmt = "INSERT INTO T_TAB1_T (C1, MEMO, T1) VALUES(?,?,TIMESTAMP(?))"; # invalid param karker SQLSTATE=42610
print "\$stmt: $stmt\n";
eval {
$sth = $dbh->prepare($stmt);
print "prepare ok\n";
};

$seq = 0;
while (<DATA>) {
++$seq;
chomp;
my ($C1, $MEMO, $T1) = split /,/;
print "$seq DATA: C1($C1), MEMO($MEMO), T1($T1)\n";
$sth->execute($C1, $MEMO, $T1);
print "$seq ok\n";
}
print "=== end ===\n";
eval {
$sth->finish();
$dbh->commit();
$dbh->disconnect();
};

__DATA__
1,insert data,2009-10-29 00:00:23.880004
1,insert null,

[Bruce Johnson]

On Oct 29, 2009, at 6:15 PM, 田口 浩 wrote:

> Hello,
>
> Is there a way to insert TIMESTAMP data or null
> by 1 SQL?
>
> $stmt = "INSERT INTO T_TAB1_T (C1, MEMO, T1) VALUES(?,?,?)";
> This cannot insert null data.

The simplest way around this is to prepare two statements:

$stmt = "INSERT INTO T_TAB1_T (C1, MEMO, T1) VALUES(?,?,TIMESTAMP(?))";

$stmt2 = "INSERT INTO T_TAB1_T (C1, MEMO) VALUES(?,?)";

$sth = $dbh->prepare($stmt);
$sth2 = $dbh->prepare($stmt2);

then in your while loop, test for T1

if ($T1) {
$sth->execute($c1, $memo, $t1);
}
else {
$sth2->execute($c1, $memo);
}

--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

[田口 浩]

Hello,

I send this old mail to dbi ML.
Someone told I should prepare 2 statement.
But it is not easy when the table has 2 or 3 TIMESTAMP columns.
And now I found I can INSERT NULL into TIMESTAMP column with the same script.
I think this is DB2 issue.
Or other reason, DBI or some else?

I tested in:
<OK>
SunOS 5.10
Oracle 10g
perl 5.8.8
DBI 1.607
DBD::Oracle 1.21
<Not OK>
AIX 5.3
DB2 8.2
perl 5.8.2
DBI 1.53
DBD::DB2 1.0

Regards,
H.T.

[田口 浩]

Hello,

I got a mail from IBM OpenSource Application Development Team.

On DB2 "" is NG, undef is OK.
On Oracle both "" and undef are OK.

Regards,
Hirosi Taguti