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 ( $cols>0 );
> 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 ( $cols>0 );
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."
