Insert Clob using Perl into DB2 v8 on OS/390

[Dark]

Im trying to insert a clob into the database and I get the following
error.
DBD::DB2::st execute failed: [IBM][CLI Driver][DB2] SQL0804N The
application program input parameters for the current request are not
valid. Reason code "103". If a host variable or S
QLVAR in the SQLDA is invalid then: host variable/SQLVAR number = "5",
SQLTYPE = "393", SQLLEN = "33", host variable/SQLVAR type = "INPUT".
SQLSTATE=07002
Below is the perl function doing the insert.

$sql .= "insert into db2p.LOBT_POS_RCPT_IMG(storekey, register_num,
trx_num, sales_date, store_dt_tm, insert_dt_tm, receipt_image) ";
$sql .= "values (?, ?, ?, ?, ?, ?, ?)";

#$sql .= "values ($store1, $reg, $trx, date('$sdate'), $storedt,
current timestamp, $image)";
if ($DBG_LEV > 0){printf "%s\n", $sql;}
local ($sth);
local ($now) = "current timestamp";

$sth = $sel_ldbh->prepare( "$sql");
$sth->bind_param( 1, $store1 );
$sth->bind_param( 2, $reg );
$sth->bind_param( 3, $trx );
$sth->bind_param( 4, $sdate );
$sth->bind_param( 5, $storedt );
$sth->bind_param( 6, $now );
$sth->bind_param( 7, $clob);
$sth->execute();

[Darin McBride]

Dark wrote:

Im trying to insert a clob into the database and I get the following
error.
DBD::DB2::st execute failed: [IBM][CLI Driver][DB2] SQL0804N The
application program input parameters for the current request are not
valid. Reason code "103". If a host variable or S
QLVAR in the SQLDA is invalid then: host variable/SQLVAR number = "5",
SQLTYPE = "393", SQLLEN = "33", host variable/SQLVAR type = "INPUT".
SQLSTATE=07002
Below is the perl function doing the insert.

$sql .= "insert into db2p.LOBT_POS_RCPT_IMG(storekey, register_num,
trx_num, sales_date, store_dt_tm, insert_dt_tm, receipt_image) ";
$sql .= "values (?, ?, ?, ?, ?, ?, ?)";
Are you sure that the problem is the CLOB? If host variable number is
"5", that looks to me as it were the store_dt_tm... what is the format
of the $storedt variable? I believe it needs to look like a DB2
timestamp. Last time I bothered to solve this problem in perl, I put
it in a module, and stopped thinking about it. If you look for DB2::db
on CPAN, you'll find my solution. In short, I deal with timestamps in
perl fashion (time since epoch) in perl, and in DB2 fashion (timestamp)
in DB2, and the DB2::db framework does the conversion automatically.

If you look at the DB2::db framework, you'll notice DB2::Row - it has
the time_to_timestamp function which can do the conversion. And, of
course, a timestamp_to_time function for the reverse. Since the
license is the Perl license, you can steal the code and reuse it as
long as you abide by the license (which I'm not a lawyer, so I'm not
sure what quite exactly that means in your situation). Or you can
install DB2::db, and just call the function. Or, I suppose, you could
just use the whole framework. ;-)
#$sql .= "values ($store1, $reg, $trx, date('$sdate'), $storedt,
current timestamp, $image)";
if ($DBG_LEV > 0){printf "%s\n", $sql;}
local ($sth);
local ($now) = "current timestamp";

$sth = $sel_ldbh->prepare( "$sql");
$sth->bind_param( 1, $store1 );
$sth->bind_param( 2, $reg );
$sth->bind_param( 3, $trx );
$sth->bind_param( 4, $sdate );
$sth->bind_param( 5, $storedt );
$sth->bind_param( 6, $now );
$sth->bind_param( 7, $clob);
$sth->execute();