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
> -----Original Message-----
> From: Hirosi Taguti
> Sent: Wednesday, August 25, 2010 10:33 AM
> To: 'dbi'
> Subject: RE: DB2, how to insert TIMESTAMP
>
> 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,
> >
> >
