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.
> -----Original Message-----
> Sent: Friday, October 30, 2009 10:16 AM
> To: dbi
> Subject: DB2, how to insert TIMESTAMP
>
> 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,
>
>
