Hello DBI Users,
I'm using DBI with the following modules installed:
DBI-1.21
DBD-CSV-0.2002
Text-CSV_XS-0.23
SQL-Statement-1.004
File-Spec-0.82
The following code snipplet illustrates my problem
when i want to update a field with a zero length
string.
I'm not an SQL guru at all, so sorry if it's something
trivial. If i do an "UPDATE test SET f1='' WHERE f3='baz'"
it does not set f1 to the empty string as i expected,
instead i see garbage in the database.
However if i do an "UPDATE test SET f1='''''' WHERE f3='baz'"
it sets f1 to the empty string. Again I don't know much
about SQL but shouldn't this set f1 to two quotation marks?
Please see the code below for a complete example.
(perl 5.6.1 is used on a RedHat 7.2 updated to the latest patches)
Thank You:
Miklos Muller
###
### CODE BEGINS
###
#!/usr/bin/perl -w
use strict;
use DBI;
my $dbh = DBI->connect( "DBI:CSV:f_dir=/tmp" );
$dbh->do( "DROP TABLE test" );
$dbh->do( "CREATE TABLE test ( f1 varchar, f2 varchar, f3 varchar )" );
$dbh->do( "INSERT INTO test VALUES ( 'foo', 'bar', 'baz' )" );
$dbh->do( "UPDATE test SET f1='' WHERE f3='baz'" );
my @a = $dbh->selectrow_array( "SELECT f1 FROM test" );
printf( "%s\n", $a[0] ); # prints: '?0?baz' incorrectly
$dbh->do( "UPDATE test SET f1='''''' WHERE f3='baz'" );
@a = $dbh->selectrow_array( "SELECT f1 FROM test" );
printf( "%s\n", $a[0] ); # prints an empty string correctly
$dbh->disconnect;
###
### CODE ENDS
###
