On Fri, Jan 31, 2014 at 09:11:28AM -0500, John Scoles wrote: > A final note on this. > > Seems there was a very very long unknown bug in DBI which was only fix a > few days ago wiht DB 1.6.31
If you mean Callbacks getting an inner handle, that wasn't a bug as such. More like a design choice that proved non-optimal. > > [1]http://blogs.perl.org/mt/mt.fcgi?__mode=view&_type=entry&id=5570&blog_id=2165 That's http://blogs.perl.org/users/byterock/2014/01/callbacks-ate-my-brain.html I presume. > The end result of this bug was that when callbacks are used on the > statement handle some attributes will not be there so you > programmer who did this > > $sth->FETCH( 'ParamValues' ), # WTF? - returns a reference to an array of > hashes > > was most likely complaing that the > > $sth->{ParamValues}, > > should return a ref but was just returning undef. > > So he 'Kludged' the code to get the value directly with the FETCH which > works I'm not sure what you're saying here John. Using $sth->FETCH('ParamValues') is perfectly reasonable. It was required before 1.631 and optional with 1.631+ now that $h->{ParamValues} works. > sort of, but it does bleed memory every so slighly. Are you sure? This is the first I've heard of such a leak. Tim. > The latest version of DBI with the > > $sth->{ParamValues}, > > Should solve all you problems > > As a bonus I have another topic for me blog > > Cheers > John > > > -------------------------------------------------------------------------------------------------------- > > Date: Wed, 29 Jan 2014 14:21:28 +0000 > Subject: Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE) > From: hhferre...@gmail.com > To: boh...@ntlworld.com > CC: byter...@hotmail.com > > You are right Martin.. Shame on me :( > At the time you suggested that we did not know about the callbacks, sorry > for that, our fault :(� > Now that we know the root of the problem I'm sure we will be able to > implement a solution.� > Thanks a lot for your time, tips and patience :) > I would be more than pleased to offer you a ginginha, porto /portuguese > wine accompanied by a special > local cheese or a "portuguese egg tart" case you pass by here! > Best Regards, > H�lder Hugo Ferreira > > On Wed, Jan 29, 2014 at 12:04 PM, Martin J. Evans <[2]boh...@ntlworld.com> > wrote: > > On 29/01/14 11:02, hhferreira wrote: > > Hey Guys! > > John, your tip about the callbacks revealed to be very accurate!! > > I seem to remember saying a long time back in this thread: > > "Have you got some sort of execute callback? I ask because of the > following in the trace: > > � � {{ execute callback CODE(0xb832be8) being invoked > > and it is only present before the error." > > hmmm. > � > > We managed to isolate the issue into this statement: > > � debug( "Executing SQL on OptiDb database:", > � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � > � �sql_executer � � � � => > sprintf( "%s line %s (%s)", (caller(0))[1,2], > $sth->{private_keep_alive_seconds} ? 'active: keeping > connection open' : 'maintenance' ), > � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � > � �statement � � � � � �=> > $sth->{Statement}, > *params � � � � � � � => $sth->FETCH( 'ParamValues' ), # WTF? - > returns a reference to an array of > hashes* > � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � > � �connected_since � � �=> > sprintf( "%s (%.3f seconds)", > � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � > � � � � � � � � � � � � � � � > � � �strftime( "%H:%M:%S", localtime( > $dbh->{private_connected_at_timestamp}[0] ) ), > � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � > � � � � � � � � � � � � � � � > � � �tv_interval( $dbh->{private_connected_at_timestamp} ), > � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � > � � � � � � � � � � � � � � � > � � �), > � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � > � �keep_alive_timestamp => > $dbh->{private_keep_alive_until_timestamp} > � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � > � � � � � � � � � � � � � � � > �? sprintf( "%s (%s seconds to live)", > � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � > � � � � � � � � � � � � � � � > � � � � � � �strftime( "%H:%M:%S", localtime( > $dbh->{private_keep_alive_until_timestamp} ) ), > � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � > � � � � � � � � � � � � � � � > � � � � � � �( $dbh->{private_keep_alive_until_timestamp} - time() ), > � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � > � � � � � � � � � � � � � � � > � � � � � � �) > � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � > � � � � � � � � � � � � � � � > �: '<not set>', > � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �); > > I guess the former developer added that suggestive comment for a > reason :) > > Now we don't really now how to resolve this issue here because this > debug function cannot simply be > removed as it logs into a file very important data required in case we > need to track down business > issues. I suppose the problem here is that the ora_varchar2_table > parameters are themselves arrays > and when they reach a certain size it crashes. > > Any tip to replace this $sth->FETCH('ParamValues') statement into a > workable one? Maybe reduce the > number of items in each of the inner arrays to 100 or so could be a > solution... We will try to > investigate in this direction. If you have some sort of magic that > could share we would be very > appreciated :) > > P.S. If you ever come to Portugal let us know and we will be very > pleased to go out with you for a > beer or two! > > Best Regards, > H�lder Hugo Ferreira > > <snipped a load of old stuff> > > All the FETCH does it return a long list of your parameters. I also > recollect asking you to output > them some time ago. However ParamValues returns a hashref with keys > based on the parameter > name/number. The code in DBD::Oracle for ParamValues creates a brand new > hash and copies each > parameter into it: > > � � � � else if (kl==11 && strEQ(key, "ParamValues")) { > � � � � � � � � HV *pvhv = newHV(); > � � � � � � � � if (imp_sth->all_params_hv) { > � � � � � � � � � � � � SV *sv; > � � � � � � � � � � � � char *key; > � � � � � � � � � � � � I32 keylen; > � � � � � � � � � � � � hv_iterinit(imp_sth->all_params_hv); > � � � � � � � � � � � � while ( (sv = > hv_iternextsv(imp_sth->all_params_hv, &key, &keylen)) ) { > � � � � � � � � � � � � � � � � phs_t *phs = (phs_t*)(void*)SvPVX(sv); � > � /* placeholder struct � */ > � � � � � � � � � � � � � � � � (void)hv_store(pvhv, key, keylen, > newSVsv(phs->sv), 0); > � � � � � � � � � � � � } > � � � � � � � � } > � � � � � � � � retsv = newRV_noinc((SV*)pvhv); > � � � � � � � � cacheit = FALSE; > > � � � � } > > You had thousands of parameters and I'd imagine this will slow your code > a lot. The return hashref > should go out scope in your code and the hash will get thrown away. Are > you sure you are really using > 1000s of parameters in your debug? > > I know of no way to limit what ParamValues returns other than change the > C code above. > > I still don't see how this is responsible for your problem however. > > Martin > > References > > Visible links > 1. > http://blogs.perl.org/mt/mt.fcgi?__mode=view&_type=entry&id=5570&blog_id=2165 > 2. mailto:boh...@ntlworld.com