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

In reply to DBI: data types, binding and insertions by Sandy

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.