Bother.
-- Forwarded message --
From: Jonathan Leffler [EMAIL PROTECTED]
Date: Thu, Apr 3, 2008 at 5:49 PM
Subject: Re: Blank inserted with varchar copy using prepare
To: Rutherdale, Will [EMAIL PROTECTED]
On Thu, Apr 3, 2008 at 3:34 PM, Rutherdale, Will [EMAIL PROTECTED]
wrote:
I am using Perl 5.8.8 on SunOS pnc 5.8 with Informix (Server Version
9.30) and DBI 1.51 and DBD::Informix 2005.2.
I try to copy a list of tables using DBI. I have found that a certain
column declared as varchar(64) is copied incorrectly when it contains an
empty string. The value in the target location is a string containing
one space instead of being empty.
Here is a code fragment copied from the actual script:
foreach my $tb ( @$table_list )
{
my ( $sel ) = $from_dbh-prepare( SELECT * FROM $tb );
$sel-execute();
my ( $cols, $val_str ) = ( $sel-{NUM_OF_FIELDS}, () );
$val_str = ( . (?, x ($cols-1)) . ?) if ( $cols0 );
my ( $ins ) = $to_dbh-prepare( INSERT INTO $tb VALUES . $val_str
);
my ( $fetch_tuple_sub ) = sub { $sel-fetchrow_arrayref };
my @tuple_status;
my ( $rc ) = $ins-execute_for_fetch( $fetch_tuple_sub,
[EMAIL PROTECTED] );
my ( @errors ) = grep { ref $_ } @tuple_status;
$sel-finish();
$ins-finish();
}
Is this a known bug? Is there a way I can get this code to faithfully
reproduce the data including blank strings of type varchar(64)?
Perl 5.10.0 on Solaris 10, DBI 1.604, DBD::Informix 2008.0229, ESQL/C
3.00.UC2.
#!/bin/perl -w
use strict;
use DBD::Informix::TestHarness;
my($dbh) = connect_to_test_database({RaiseError = 1});
my($tbl1) = dbd_ix_something_1;
my($tbl2) = dbd_ix_something_2;
$dbh-do(create {temp} table $tbl1 ( col VARCHAR(64) NOT NULL));
$dbh-do(create {temp} table $tbl2 ( col VARCHAR(64) NOT NULL));
$dbh-do(INSERT INTO $tbl1 VALUES('a')); # Non-blank VARCHAR
$dbh-do(INSERT INTO $tbl1 VALUES(' ')); # Single-blank VARCHAR
$dbh-do(INSERT INTO $tbl1 VALUES(''));# Empty (non-null) VARCHAR
my($from_dbh) = $dbh;
my($to_dbh) = $dbh;
my($table_list) = [ $tbl1 ];
foreach my $tb ( @$table_list )
{
my ( $sel ) = $from_dbh-prepare( SELECT * FROM $tb );
$sel-execute();
my ( $cols, $val_str ) = ( $sel-{NUM_OF_FIELDS}, () );
$val_str = ( . (?, x ($cols-1)) . ?) if ( $cols0 );
my ($new) = $tb;
$new =~ s/1/2/;
my ( $ins ) = $to_dbh-prepare( INSERT INTO $new VALUES . $val_str);
my ( $fetch_tuple_sub ) = sub { $sel-fetchrow_arrayref };
my @tuple_status;
my ( $rc ) = $ins-execute_for_fetch( $fetch_tuple_sub,
[EMAIL PROTECTED] );
my ( @errors ) = grep { ref $_ } @tuple_status;
$sel-finish();
$ins-finish();
}
Runs OK - first time.
Black JL: perl will.sciatl.pl
# DBI-connect('dbi:Informix:stores', '', '');
# Connect Attribute: RaiseError = 1
# Connect Attribute: ChopBlanks = 1
Black JL: sqlcmd -d stores -F unload -e 'select * from dbd_ix_something_1'
a|
|
\ |
Black JL: sqlcmd -d stores -F unload -e 'select * from dbd_ix_something_2'
a|
|
\ |
Black JL:
Basically, this code is copying the single blank and the empty but non-null
string accurately.
So, in the absence of a reproduction with DBD::Informix 2008.0229, I'm going
to claim no longer a problem. There have been issues in the handling of
VARCHAR, both in ESQL/C and in DBD::Informix on occasion, but not
self-evidently on this occasion.
I assume there's a reason why you can't do:
INSERT INTO dbase2:tablename SELECT * FROM dbase1:tablename;
Probably related to error -999 (not implemented yet).
--
Jonathan Leffler [EMAIL PROTECTED] #include disclaimer.h
Guardian of DBD::Informix - v2008.0229 - http://dbi.perl.org
Blessed are we who can laugh at ourselves, for we shall never cease to be
amused.
--
Jonathan Leffler [EMAIL PROTECTED] #include disclaimer.h
Guardian of DBD::Informix - v2008.0229 - http://dbi.perl.org
Blessed are we who can laugh at ourselves, for we shall never cease to be
amused.
