Tim Bunce wrote on 08/11/2000 10:22:14:
> I need a volunteer to write a test script (like the other t/*.t files)
> that will... create a table with NULL and NOT NULL variants of CHAR and
> VARCHAR columns, and then do a series of inserts and selects with and
> without trailing spaces etc with ora_ph_type set to various values.
>
> Wouldn`t be too hard to do as a series of nested loops (a little like
> t/long.t, only simpler) driven by a data structure that says what to
> expect in each case.
>
> Any volunteers? This is your chance to give back...
We've been having problems migrating from OCI_V7 to OCI_V8 using DBD-Oracle
because of VARCHAR2 trailing space issues. I have tried and failed to get
the $dbh->{ora_ph_type} trick mentioned in Changes to work. Invariably,
when ora_ph_type is set to "5" (or "97"), I get the following error from the
following pseudocode:
$dbh->{ora_ph_type} = 5;
$dbh->do("CREATE TABLE foobar (foo VARCHAR2(20))");
$sth = $dbh->prepare("INSERT INTO foobar VALUES (?)");
$sth->execute("trailing ");
ORA-01461: can bind a LONG value only for insert into a LONG column
(DBD ERROR: OCIStmtExecute)
I have taken you up on your challenge to produce a test file, t/ph_type.t.
It doesn't do much right now, so feel free to beef it up. At least it
reproduces the problem.
Let me know if there is anything more I can do.
james
t/ph_type.t:
#!perl -w
sub ok ($$;$) {
my($n, $ok, $warn) = @_;
++$t;
die "sequence error, expected $n but actually $t"
if $n and $n != $t;
($ok) ? print "ok $t\n"
: print "# failed test $t at line ".(caller)[2]."\nnot ok $t\n";
if (!$ok && $warn) {
$warn = $DBI::errstr || "(DBI::errstr undefined)" if $warn eq '1';
warn "$warn\n";
}
}
use strict;
use DBI;
$| = 1;
my $dbuser = $ENV{ORACLE_USERID} || 'scott/tiger';
my $dsn = $ENV{ORACLE_DSN} || 'dbi:Oracle:';
my $dbh = DBI->connect($dsn, $dbuser, '', {AutoCommit=>0,PrintError=>1});
unless($dbh) {
warn "Unable to connect to Oracle ($DBI::errstr)\nTests skipped.\n";
print "1..0\n";
exit 0;
}
use vars qw($tests);
print "1..$tests\n";
my ($sth,$expect,$tmp);
# drop table but do not even warn if not there
eval { $dbh->do("DROP TABLE foobar") };
#warn $@ if $@;
ok(0, $dbh->do("CREATE TABLE foobar (foo VARCHAR2(20))"));
my $val_with_trailing_space = "trailing ";
my $val_with_embedded_nul = "embedded\0nul";
my @tests =
([ 1, "VARCHAR2", 1, 1 ],
[ 5, "STRING", 0, 0 ],
[ 96, "CHAR", 0, 1 ],
[ 97, "CHARZ", 0, 0 ]);
for my $test_ary (@tests) {
my ($ph_type, $name, $strips_trailing, $embed_nul_ok) = @$test_ary;
print "#testing $name..\n";
ok(0, $dbh->{ora_ph_type} = $ph_type );
$expect = $val_with_trailing_space;
$expect =~ s/\s+$// if $strips_trailing;
ok(0, $sth = $dbh->prepare("INSERT INTO foobar VALUES (?)"));
ok(0, $sth->execute($val_with_trailing_space));
ok(0, $sth = $dbh->prepare("SELECT foo FROM foobar WHERE foo = '$expect'"));
ok(0, $sth->execute );
ok(0, $tmp = $sth->fetchrow_hashref );
ok(0, $sth && $sth->finish );
ok(0, $dbh->rollback );
($expect = $val_with_embedded_nul) =~ s/\0.*//;
ok(0, $sth = $dbh->prepare("INSERT INTO foobar VALUES (?)"));
ok(0, $sth->execute($val_with_embedded_nul));
ok(0, $sth = $dbh->prepare("SELECT foo FROM foobar WHERE foo = '$expect'"));
ok(0, $sth->execute );
$tmp = $sth->fetchrow_hashref;
ok(0, $embed_nul_ok ? !$tmp : $tmp );
ok(0, $sth && $sth->finish );
ok(0, $dbh->rollback );
}
ok(0, $dbh->disconnect );
BEGIN { $tests = 62 }
