I am trying, for the very first time, to insert a bunch of data into a database.

I have read DBI over and over, but I cannot seem to resolve my problem.

In DBI it states clearly that one cannot change the data type of bound variables

The data type for a bind variable cannot be changed after the first bind_col call. In fact the whole \%attr parameter is 'sticky' in the sense that a driver only needs to consider the \%attr parameter for the first call for a given $sth and column.

But I have string data that is sometimes empty, and when I try to insert that row, the database complains.

I have tried many different combinations, but the only thing that seems to work is if I replace the empty string with a string of at least one character.

Sample code is shown below (most of my error checking has been removed to simplify the code)

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use DBD::ODBC;
use DBI qw(:sql_types);
use Data::Dumper;

my $username = 'user';
my $password = "password";
my $stat;

# connect
my $oracle_db = $ENV{ORACLE_DB};

my $dbh;
eval { $dbh = DBI->connect( "DBI:ODBC:$oracle_db", "$username", "$pass
+word" ); };
unless ($dbh) { die "couldn't connect, $DBI::errstr"; }

my $sql =
'INSERT INTO xyz.table (id,num, name,descr) VALUES (?,?,?,?)';
my $sth = $dbh->prepare($sql);

$stat = $sth->execute("302","1","Next",'descr 4');
print Dumper \$stat;

$stat = $sth->execute("302","1","Another",' ');
print Dumper \$stat;

$stat = $sth->execute("302","1","Yet",'');
print Dumper \$stat;

$stat = $sth->execute("302","1","Again",undef);
print Dumper \$stat;

$sth->bind_param(1,"302",SQL_VARCHAR);
$sth->bind_param(2,"1",SQL_VARCHAR);
$sth->bind_param(3,"AACH",SQL_VARCHAR);
$sth->bind_param(4,"",SQL_VARCHAR);
$stat = $sth->execute();
print Dumper \$stat;

exit;
[download]

I have also tried inserting $sth->finish() in-between each attempt, to no avail.

Next and Another will be inserted into the db, but nothing else will.

I am using ODBC to connect to an Oracle database using "DataDirect" wire protocol. One of the error messages is (added /n to make it easier to read):

DBD::ODBC::st execute failed: 
[DataDirect][ODBC Oracle Wire Protocol driver]
Data type for parameter 4 has changed since first SQLExecute call. 
(SQL-HY000)
(DBD: st_execute/SQLExecute err=-1) at question.pl line 33.
[download]

Notes: If I comment out the insertions of Next and Another, then the other rows will be inserted (i.e. there is no restriction to empty or null values for the 4th parameter)

So, my question is... how do I make an empty string look to be the same datatype as a non-empty string?

Sandy