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 }