RE: bind_param () - did something change?

 > Date: Mon, 28 Jan 2013 19:33:46 +0000
> From: [email protected]
> To: [email protected]
> Subject: 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. No doesn't bug me.  The big problem is not really if 8~7 work it is more to do with theclient they are using.  OCI8 really hasn't changed much since Oracle 8.If they can get it to compile and connect then good on 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.
>  The main point it the present fetch is somthing like this 1) parse statement2) idetify lobs3) get lob thingy (can't remeber off the top of my head,, lob locator perhaps??) on execute4) run an extra select to actully get the lob5) munge it back into the select call Just a buch of extra steps and round trips that are not needed.  If memory serves me correctly.  
> 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  Yep that is true it is a two step process generate the lob_locatior and insert intothe orginal table and create the lob for the locator. The newer way to do it is just insert it directly with the persitant data interface. I think there is a flag someplace I think 'ora_pers_lob' for the select anway Cheers
> 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/
> >   		 	   		
>