Hello!
I have a problem to insert NULL values in CLOB/BLOB fields.
In the following example I insert four records into a testtable. The third row
causes my problem. I try to insert again NULL values (as i did with row 1)
into the lob fields. Therefore I call:
$sth->bind_param(2, undef(), { ora_type=>112, ora_field=>'mytext' });
$sth->bind_param(3, undef(), { ora_type=>113, ora_field=>'myblob' });
But despite of the undef() - Value the driver saves the values of row 2 for
the lob - Fields!
What is wrong?
Here is my testtable:
|DROP TABLE blobtest;
|CREATE TABLE blobtest (
| id INTEGER,
| mytext clob,
| myblob blob
|);
Here is my testscript:
|#!/usr/bin/env perl
|
|use warnings;
|use diagnostics;
|use strict;
|use Getopt::Long;
|use English;
|use DBI;
|
|my $langtext="";
|my $zaehler=0;
|while ( $zaehler < 500 ) {
| $langtext=$langtext . "0123456789";
| $zaehler=$zaehler+1;
|}
|
|#BD connect to Oracle
|my $dbv=DBI->connect("dbi:Oracle:i001.tux", "yucatan", "yucatan");
|
|#BD Datenbank - Verbindung konfigurieren
|$dbv->{PrintError} = 1;
|$dbv->{AutoCommit} = 1;
|
|#BD delete all rows
|$dbv->do("delete from blobtest");
|
|#BD Insert Statement
|my $insertsql=<<EOF;
| insert into blobtest (id, mytext, myblob)
| values (?, ?, ?)
|EOF
|
|#BD Prepare the Insert Statement
|my $sth=$dbv->prepare($insertsql);
|
|#BD insert first row
|$sth->bind_param(1, 0);
|$sth->bind_param(2, undef(), { ora_type=>112, ora_field=>'mytext' });
|$sth->bind_param(3, undef(), { ora_type=>113, ora_field=>'myblob' });
|$sth->execute();
|
|#BD insert second row
|$sth->bind_param(1, 1);
|$sth->bind_param(2, 'this_is_a_clob', { ora_type=>112,
| ora_field=>'mytext' });
|$sth->bind_param(3, 'this_is_a_blob', { ora_type=>113,
| ora_field=>'myblob' });
|$sth->execute();
|
|#BD insert third row
|$sth->bind_param(1, 2);
|$sth->bind_param(2, undef(), { ora_type=>112, ora_field=>'mytext' });
|$sth->bind_param(3, undef(), { ora_type=>113, ora_field=>'myblob' });
|$sth->execute();
|
|#BD insert fourth row
|$sth->bind_param(1, 3);
|$sth->bind_param(2, $langtext, { ora_type=>112, ora_field=>'mytext' });
|$sth->bind_param(3, $langtext, { ora_type=>113, ora_field=>'mytext' });
|$sth->execute();
If I launch the this query:
|select id, mytext, myblob, dbms_lob.getlength(mytext),
| dbms_lob.getlength(myblob)
|from blobtest
|order by id;
I get this result:
|id mytext myblob len mytext len myblob
| 0 NULL NULL 0 0
| 1 this_is_a_clob this_is_a_blob 14 14
| 2 this_is_a_clob this_is_a_blob 14 14 <-ERROR ROW
| 3 0123... 0123... 5000 5000
Regards,
Bernhard Donaubauer
