Re: bind_param () - did something change?

On 28/01/2013 19:03, John Scoles wrote:
> I do not think so. That section of DBD::Oracle code does need some rework as it was written for Oracle 8. It should be updated to use the Oracle 10+ functionality for both select and updates.  Alas one never seems to have any time work, (well finish actully) the work on this one.  CheersJohn

In addition (and I know you'll not like it) but people are still using 
Oracle 8 and 9. Steffen only recently submitted a load of patches for 
tests in the trunk which fail on Oracle 8. That table 
(http://search.cpan.org/~pythian/DBD-Oracle-1.56/lib/DBD/Oracle.pm#WHICH_VERSION_OF_DBD::ORACLE_IS_FOR_ME?) 
in DBD::Oracle looks wrong as I know Steffen is using older Oracle's 
than the table suggests you can use. I'm not in a great rush to remove 
support for any Oracle versions whilst people are still using them. I 
know the arguments but no one having the tuits to remove support for 
older Oracles is a good thing to me unless a good case for improvement 
and tuits can be made. I'd rather continue to support all we can in the 
code using conditional code as it does now.

As for Merijn's problem we discussed this on irc. I'm not sure my 
answers satisfied Merijn but I believe the situation right now is that 
if you have a table with multiple lobs and you are updating one of them 
you need to specify ora_field. The driver does not know the columns on 
an insert, it only scans the SQL for placeholders and does not parse the 
SQL as such. In addition, the parameters may not align with a column - 
they could be args to a function in an insert/update/delete. As Merijn 
discovered, many/most DBDs don't even return the column names in an 
insert/update/delete statement (I know DBD::ODBC doesn't and Merijn 
found DBD::Pg and DBD::Oracle doesn't either - none of which surprised 
me as in DBD::ODBC's case the call to SQLDescribeParam does not return 
column names).

Martin

>   > Date: Mon, 28 Jan 2013 14:31:44 +0100
>> From: [email protected]
>> To: [email protected]
>> Subject: bind_param () - did something change?
>>
>> I have a table with 5 BLOB's. BLOB's are easy in DBD::CSV and
>> DBD::Unify, but they need "some help" in Oracle.
>>
>> I had a script that did load a table from a CSV file by first inserting
>> all the records without the blob's and then update each blob in turn
>> ((DBD::Oracle would not allow me to have 5 BLOB's in one insert or
>> update).
>>
>> Given that c_ll + m_nr are a primary key, I had to change
>>
>> foreach my $blob (qw( w_tl w_xml0 w_xml1 w_xml2 w_xml3 attr )) {
>>      print STDERR "Setting $blob in ll_verz_rel ...\n";
>>      my $sth = $dbh->prepare ("update ll_verz_rel set $blob = ? where c_ll = ? and m_nr = ?");
>>      for (@llvr) {
>> 	$_->{$blob} or next;
>> 	$sth->bind_param (1, $_->{$blob}, { ora_type => ORA_BLOB   });
>> 	$sth->bind_param (2, $_->{c_ll},  { ora_type => ORA_NUMBER });
>> 	$sth->bind_param (3, $_->{m_nr},  { ora_type => ORA_NUMBER });
>> 	$sth->execute ();
>> 	}
>>      }
>>
>> to
>>
>> foreach my $blob (qw( w_tl w_xml0 w_xml1 w_xml2 w_xml3 attr )) {
>>      print STDERR "Setting $blob\tin ll_verz_rel ... ";
>>      my $sth = prepar ("update ll_verz_rel set $blob = ? where c_ll = ? and m_nr = ?");
>>         $sth->bind_param (1, undef, { ora_type => ORA_BLOB, ora_field => $blob });
>>      for (@llvr) {
>> 	$_->{$blob} or next;
>> 	$sth->execute ($_->{$blob}, $_->{c_ll}, $_->{m_nr});
>> 	}
>>      }
>>
>> to get it to insert the records. It FAILED to work without the
>> ora_field addition
>>
>> Now in this case I don't really mind the change. It makes my code
>> easier, but if I bind to one parameter only, the bind should/could know
>> what to bind to, it shouldn't need the ora_field entry in the hashref.
>> In above case, there is one ONE blob in the statement at any time, so
>> there is no conflict at all, ever.
>>
>> -- 
>> H.Merijn Brand  http://tux.nl   Perl Monger  http://amsterdam.pm.org/
>> using perl5.00307 .. 5.17   porting perl5 on HP-UX, AIX, and openSUSE
>> http://mirrors.develooper.com/hpux/        http://www.test-smoke.org/
>> http://qa.perl.org   http://www.goldmark.org/jeff/stupid-disclaimers/
>