| CC: | <morten_bjoernsvik [...] yahoo.no> |
| Subject: | DBD::DB2 v1.78 'current timestamp binding failure |
| Date: | Thu, 1 Jul 2010 16:55:56 +0200 |
| To: | <bug-DBD-DB2 [...] rt.cpan.org> |
| From: | Morten Bjørnsvik <morten.bjornsvik [...] experian-da.no> |
Hi
I have problems binding 'current timestamp', I'm not sure if this is a bug or user-error,
This has been working previously, but I deleted some tables, and then it did not worked.
# this does not work
$sth->bind_param( 2, 'current timestamp', DBI::SQL_DATETIME );
# this works
$sth->bind_param( 2, '2010-07-01 10:00:00', DBI::SQL_DATETIME );
db2inst1@mortenb2:~> db2ls
Install Path Level Fix Pack Special Install Number Install Date Installer UID
---------------------------------------------------------------------------------------------------------------------
/opt/ibm/db2/V9.5 9.5.0.0 0 Tue Sep 16 09:54:35 2008 CEST 0
Install Path Level Fix Pack Special Install Number Install Date Installer UID
---------------------------------------------------------------------------------------------------------------------
/opt/ibm/db2/V9.5 9.5.0.4 4 Wed Jun 24 09:40:12 2009 CEST 0
Test program:
mbj@mortenb2:~/perl> ./test-dbdbinding.pl
DBD::DB2::VERSION:1.78
plain insertion ok
DBD::DB2::st execute failed: [IBM][CLI Driver] CLI0113E SQLSTATE 22007: An invalid datetime format was detected; that is, an invalid string representation or value was specified. SQLSTATE=22007 at ./test-dbdbinding.pl line 45.
got error: -99999
got error: [IBM][CLI Driver] CLI0113E SQLSTATE 22007: An invalid datetime format was detected; that is, an invalid string representation or value was specified. SQLSTATE=22007
due to error at ./test-dbdbinding.pl line 53.
#!/opt/perl/bin/perl -w
use Data::Dumper;
use DBI;
require DBD::DB2;
my $connect_hash = {
dbi => 'DBI:DB2:psecana',
user => 'db2inst2',
pw => 'start1'
};
print "DBD::DB2::VERSION:" . $DBD::DB2::VERSION . "\n";
my $dbh = DBI->connect( $connect_hash->{dbi}, $connect_hash->{user}, $connect_hash->{pw} );
if(! defined $dbh) {
die("error unable to connect using " . Dumper($connect_hash));
}
# standard insert
my $sql = "insert into nodehost (host,time,master,refresh,status) values('192.168.2.31:5555',current timestamp,1,current timestamp,'ok')";
my $sth = $dbh->prepare( $sql );
$sth->execute();
if( defined $dbh->err() || defined $sth->errstr() ) {
if( defined $dbh->err() ) {
print "got error: " . $dbh->err() . "\n";
}
if(defined $sth->errstr()) {
print "got error: " . $sth->errstr() . "\n";
}
die("due to error");
}
else {
print "plain insertion ok\n";
}
# bind insert:
$sql = "insert into nodehost (host,time,master,refresh,status) values(?,?,?,?,?)";
$sth = $dbh->prepare( $sql );
$sth->bind_param( 1, '192.168.2.32:5555', DBI::SQL_VARCHAR );
$sth->bind_param( 2, 'current timestamp', DBI::SQL_DATETIME );
#$sth->bind_param( 2, '2010-07-01 10:00:00', DBI::SQL_DATETIME );
$sth->bind_param( 3, 0, DBI::SQL_INTEGER );
$sth->bind_param( 4, 'current timestamp', DBI::SQL_DATETIME );
#$sth->bind_param( 4, '2010-07-01 10:00:00', DBI::SQL_DATETIME );
$sth->bind_param( 5, 'ok', DBI::SQL_VARCHAR );
$sth->execute();
if( defined $dbh->err() || defined $sth->errstr() ) {
if( defined $dbh->err() ) {
print "got error: " . $dbh->err() . "\n";
}
if(defined $sth->errstr()) {
print "got error: " . $sth->errstr() . "\n";
}
die("due to error");
}
else {
print "bind insertion is ok\n";
}
$sth->finish();
$dbh->disconnect();
=pod
db2inst1@mortenb2:~> db2 "describe table nodehost"
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
HOST SYSIBM VARCHAR 20 0 No
TIME SYSIBM TIMESTAMP 10 0 No
MASTER SYSIBM INTEGER 4 0 Yes
REFRESH SYSIBM TIMESTAMP 10 0 No
STATUS SYSIBM VARCHAR 20 0 Yes
5 record(s) selected.
=cut
----------------------------------------------------------------
Morten Bjørnsvik, Experian Decision Analytics AS
Sørkedalsveien 10c, Postbox 5275 Majorstua, 0303 Oslo, Norway
Mob: +47 92 44 83 02, Dir: +47 21 52 12 65, Fax: +47 21 52 12 51
----------------------------------------------------------------