On Wed, Mar 12, 2003 at 01:46:48PM +0100, Bernhard Donaubauer wrote:
> Hello!
>
> I have a problem to insert NULL values in CLOB/BLOB fields.
I think that's not currently supported. Patches welcome.
Tim.
> 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
>
