Re: DBI ORACLE ERROR
ERROR OCINlsEnvironmentVariableGet(OCI_NLS_CHARSET_ID) Check NLS
You need to set some environment variables
$ENV{ORACLE_HOME} = ...;
$ENV{OCI_NLS_CHARSET_ID} = ...;
From: Rakesh Ramachandran
Sent: February 3, 2017 1:11 AM
To: Deven T. Corzine
Cc: dbi-dev@perl.org; Trupti Anil Charwad
Subject: RE: DBI ORACLE ERROR
Hi Deven,
Thank you for the previous reply. Driver issue has been resolved. But I am
getting a new error, as below,
Error:
DBI connect('database=X:Hostname=10.94.XX.XX:Port=XXX','XX',...)
failed: ERROR OCINlsEnvironmentVariableGet(OCI_NLS_CHARSET_ID) Check NLS
settings etc. at E:\testcon.pl line 22.
ERROR OCINlsEnvironmentVariableGet(OCI_NLS_CHARSET_ID) Check NLS settings etc.
at E:\testcon.pl line 22.
I have enclosed my script here. Also, I have enclosed the screenshot of the
error. Please check them out.
Please assist me in this regard.
Regards,
Rakesh R
From: Deven T. Corzine [mailto:de...@ties.org]
Sent: Friday, February 3, 2017 7:12 AM
To: Rakesh Ramachandran
Cc: dbi-dev@perl.org
Subject: Re: DBI ORACLE ERROR
The error message is telling you the problem. You need to install the
DBD::Oracle module:
https://metacpan.org/pod/DBD::Oracle
DBD::Oracle - Oracle database driver for the DBI module
...
metacpan.org
Oracle database driver for the DBI module ... NAME; VERSION; SYNOPSIS;
DESCRIPTION; CONSTANTS; DBI CLASS METHODS. connect. OS authentication
This is the database driver which allows DBI to talk to Oracle. If you look at
the list of available drivers in the error message, Oracle wasn't among them.
Note that you will need to have the Oracle client libraries installed before
you try to build this module.
Deven
On Thu, Feb 2, 2017 at 12:42 AM, Rakesh Ramachandran
mailto:rr00440...@techmahindra.com>> wrote:
Hi Perl team,
I have been learning Perl scripting for a while. When I am trying to connect to
oracle DB, I am getting the following error.
I am using Padre IDE forscripting. I am using Perl 5, version 14, subversion 2
(v5.14.2) built for MSWin32-x86-multi-thread.
Please help me out for solving the issue.
I have also attached the screenshot of the actual Perl command line for your
reference.
Error:
install_driver(Oracle) failed: Can't locate DBD/Oracle.pm in @INC (@INC
contains: C:/Dwimperl/perl/site/lib C:/Dwimperl/perl/vendor/lib
C:/Dwimperl/perl/lib .) at (eval 4) line 3.
Perhaps the DBD::Oracle perl module hasn't been fully installed,
or perhaps the capitalisation of 'Oracle' isn't right.
Available drivers: ADO, DBM, ExampleP, File, Gofer, ODBC, Pg, Proxy, SQLite,
Sponge, mysql.
at dbscriptfororacle.pl line 30
Press any key to continue . . .
Regards,
Rakesh R
Disclaimer: This message and the information contained herein is proprietary
and confidential and subject to the Tech Mahindra policy statement, you may
review the policy at http://www.techmahindra.com/Disclaimer.html externally
http://tim.techmahindra.com/tim/disclaimer.html internally within TechMahindra.
Disclaimer: This message and the information contained herein is proprietary
and confidential and subject to the Tech Mahindra policy statement, you may
review the policy at http://www.techmahindra.com/Disclaimer.html externally
http://tim.techmahindra.com/tim/disclaimer.html internally within TechMahindra.
RE: Best way to retire old code for unsupported database versions
Yep one of the more unplesnt parts of being a DBDer, Looking back I had to drop a few #ifdefs when I was the more active with DBD::Oracle. I just made up a chart on the POD http://search.cpan.org/~pythian/DBD-Oracle-1.66/lib/DBD/Oracle.pm#WHICH_VERSION_OF_DBD::ORACLE_IS_FOR_ME? I my case I sort of just followed what the Oracle policy was. Support two versions back only. However nobody has been updating the table:) Really have to find a week or two to get back an lay with DBD::Oracle Cheers John > Date: Fri, 27 Sep 2013 09:28:51 +0100 > From: boh...@ntlworld.com > To: dbi-dev@perl.org > Subject: Re: Best way to retire old code for unsupported database versions > > On 27/09/13 03:52, Greg Sabino Mullane wrote: > > -BEGIN PGP SIGNED MESSAGE- > > Hash: RIPEMD160 > > > > > >> what to do about having something around that you can refer people who > >> want to run 4.x (???!!!). Is a simple information tidbit "if you > >> want to use xxx, you use version x.y.z version of DBD::mysql" ? > >> What have others done about this issue? > > > > Yes, it is basically as simple as that. Some thoughts: > > > > * Make your abandonment of old versions in large chunks, don't simply > > phase out older versions over time. In other words, consolidate the > > pain into as few releases as possible. Ideally, one every 10 years. :) > > > > * Have a version or two with lots of warnings in the docs before > > making the change. > > > > * Make the change at a major version bump. Then you can tell people > > they need version x, rather than x.y.z > > > > * This is also a good time to break other things - such as requiring > > a newer version of DBI. Consolidate that pain! > > > > * Provide instructions in the docs about how to reach the old versions. > > Have the frozen version available somewhere other than CPAN too. > > > > * Expect people who have been living off of those #ifdefs for years to > > suddenly crawl out of the woodwork after you flip the switch and complain. > > Anticipate their complaints in your docs so you can simply point them there. > > > > (MySQL 4.x? I know places still running 3.x!) > > Everything Greg said ++, plus: > > * when you make the break try and ensure your RT queue is clear of at least > bugs or you'll get people saying they can't upgrade to get a bug fix because > you withdrew support for their mysql version. > > * try and catch someone trying to build against old client libs and output a > meaningful message rather than just have the compile fail - preferably do > this in your Makefile.PL or Build script and with the correct exit status > you'll get a NA on a smoker instead of a Fail. > > * as well as DBI, think about what Perl version you require - especially wrt > unicode support although I see the latest DBI is 5.8.1 already. > > Martin
RE: [PATCH] Spelling fixes.
I had a change to go though it and it all looks ok. By the way 'dying' is correct the word you are thinking of is 'dyeing' English is such a silly language;) Cheers John > Date: Tue, 11 Jun 2013 08:03:54 +0200 > From: h.m.br...@xs4all.nl > To: dbi-dev@perl.org > Subject: Re: [PATCH] Spelling fixes. > > On Mon, 10 Jun 2013 17:57:25 +0300, Ville Skyttä > wrote: > > > - * We still localize, so we're safe from the callback dieing, > > + * We still localize, so we're safe from the callback dying, > > Changes like that make me weary to apply blindly. > > I, not having an English native tongue, would read that change as a > change from "The callback is going to die" to "the callback is going > to be painted". I know that would have to be "dyeing" but still not > an improvement. "dieing" is pretty common as "die" is a perl keyword. > Maybe "die-ing" would be the best approach. > > Thanks for the work, I'll go through all of them later if noone beats > me to it > > -- > H.Merijn Brand http://tux.nl Perl Monger http://amsterdam.pm.org/ > using perl5.00307 .. 5.19 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/
RE: problem with bound columns and fetchall_arrayref with a slice
Hmm you always come up with the head scratchers,
You are most likely on the right track. Somewhere way in the back of my mind I
recall that the fetchall_arrayref was optimized for speed, and so the binding
was left out but I am going back almost 10 years by memory.
Do the other DBD do the same thing??
Cheers
John
> Date: Mon, 3 Jun 2013 10:43:20 +0100
> From: boh...@ntlworld.com
> To: dbi-dev@perl.org
> Subject: problem with bound columns and fetchall_arrayref with a slice
>
> Hi,
>
> I've just hit a problem with bind_col and fetchall_arrayref when a slice is
> used and I'm wondering how I might fix it. I'm using DBD::Oracle and setting
> a bind type and some attributes but as soon as a slice is used in
> fetchall_arrayref, DBI rebinds the columns and I lose the column type and
> attribute. Here is an example:
>
> # $sth is just a select with 4 column
> # the first column is an integer and we want to keep it that way
> # as the result will be JSONified and we don't want JSON to think it
> # is a string and put quotes around it
> $sth->bind_col (1, undef, {TYPE => SQL_INTEGER, DiscardString => 1});
>
> my $list = $sth->fetchall_arrayref({});
> print Dumper ($list);
>
> Without the slice it produces:
>
> $VAR1 = [
>[
> 11,
> 'Abandoned',
> '1358247475.860400',
> '1358247475.860400'
>],
>
> and with the slice it produces:
>
> $VAR1 = [
>{
> 'modified_date_time' => '1358247475.860400',
> 'market_status_id' => '11',
> 'name' => 'Abandoned',
> 'created_date_time' => '1358247475.860400'
>},
>
> Notice the slice caused the market_status_id to look like a string. This
> happens because DBI binds the columns when you use a slice and it is
> overriding what was set for column 1 in the bind this code does.
>
> So this is how bind_col ends up being called:
>
> BIND COL 1 (TYPE => SQL_INTEGER, DiscardString => 1)
> BIND COL 1 (no type (i.e. type = 0) and no attrs)
> BIND COL 2 (no type and no attrs)
> BIND COL 3 (no type and no attrs)
> BIND COL 4 (no type and no attrs)
>
> The code in DBD::Oracle is possibly flawed in that every time bind_col is
> called it does:
>
> imp_sth->fbh[field-1].req_type = type;
> imp_sth->fbh[field-1].bind_flags = 0; /* default to none */
>
> regardless of whether bind_col has been called before and set a type or
> attributes. As type is a parameter to dbd_st_bind_col anyone not wishing to
> set a type has to say 0.
>
> I could fix my usage case by simply saying if bind_col has been called for a
> column which already has a type set and the incoming type is 0 don't touch it
> and if no attributes are passed don't clear any existing ones. It would work
> for me but I'd like to hear any comments.
>
> Martin
RE: Building DBD::Oracle with one version but deploying with another
Should Allways work for 1 DB version forward and one back. So a 10 instantshould should work a 9,10 and 11 at least that is the theory. Oracle is a little unclear on what a Version is. What I usually do is complie with the instant client and depoly it with the compiled code. This is how activestae gets awaywith it as they install the IC along with the dll so they need only compile it once/\. One can freely ditribute the IC with no gruff from Oracle. Cheers > Date: Fri, 19 Apr 2013 08:35:39 +0100 > From: boh...@ntlworld.com > To: dbi-dev@perl.org > Subject: Re: Building DBD::Oracle with one version but deploying with another > > On 18/04/13 18:41, Jan Dubois wrote: > > Sorry, I can't remember the details. I think you must use clients for > > the same version of Oracle on the server, e.g. if you compiled > > DBD::Oracle with an Oracle 10 instant client, then it doesn't seem to > > work with an Oracle 11 client. But my memories of that are foggy; I > > don't know if this is just a limitation on Windows, or if it applies > > everywhere. > > > > I also never tried to run DBD::Oracle compiled against the instant > > client with a server that has the regular client installed. I kind of > > expect it to work, if they are the same versions, but haven't verified > > it. > > > > Cheers, > > -Jan > > DBD::Oracle attempts to find the version of your client using sqlplus etc. > Once it knows the version it sets macros which affect what support is built > into DBD::Oracle. > > e.g., > > Installing on a linux, Ver#2.6 > Using Oracle in /home/martin/instantclient_11_2/ > DEFINE _SQLPLUS_RELEASE = "1102000200" (CHAR) > Oracle version 11.2.0.2 (11.2) > Looks like an Instant Client installation, okay > Your LD_LIBRARY_PATH env var is set to '/home/martin/instantclient_11_2/' > > DEFINE= -Wall -Wno-comment -DUTF8_SUPPORT -DORA_OCI_VERSION=\"11.2.0.2\" > -DORA_OCI_102 -DORA_OCI_112 > > Notice the ORA_OCI_102 and ORA_OCI_112 macros are defined in this case since > this was Oracle 11.2. If you search the source for those macros you'll see > loads of places where code is only included if they are defined and hence it > affects what you can do with DBD::Oracle. > > So if you built against a 10.2 client and then attempted to run against a > 11.2 client there are a) things you would not be able to do and b) possibly > DBD::Oracle would make a different set of OCI calls (you'd need to read the > code to see what). > > If you did it the other way around it is quite likely some things won't work. > > The instant client files required to run DBD::Oracle (as opposed to build it) > are quite small. What cannot you distribute those with the DBD::Oracle you > build. > > Martin > > > On Thu, Apr 18, 2013 at 10:16 AM, John Wiersba wrote: > >> Yes, I'm doing that. Each server can have a different environment than the > >> server the original DBD:Oracle was built on. Or the question still applies > >> if I want to use a different version of Oracle installed on the original > >> build server, especially if I remove the version of Oracle that was used to > >> build the original DBD::Oracle. > >> > >> > >> From: Jan Dubois > >> To: John Wiersba > >> Cc: Lyle ; "dbi-dev@perl.org" > >> Sent: Thursday, April 18, 2013 1:09 PM > >> > >> Subject: Re: Building DBD::Oracle with one version but deploying with > >> another > >> > >> I think you also need to add the ORACLE_HOME directory to > >> LD_LIBRARY_PATH (on the deployment machine) to make it work. > >> > >> Cheers, > >> -Jan > >> > >> On Thu, Apr 18, 2013 at 9:04 AM, John Wiersba wrote: > >>> Thanks, Lyle. I'm trying to build DBD::Oracle on Linux/AIX/Solaris for > >>> distribution to another server (assume the OS and perl versions on both > >>> servers) which will have a different ORACLE_HOME, possibly a different > >>> version of the Oracle client and likely in a different location. The > >>> target > >>> server may not have a C compiler. > >>> > >>> That's the same situation that ActiveState must have encountered, building > >>> DBD::Oracle with whatever version of Oracle they had downloaded and > >>> installed in some random location, but deploying it on the user's server > >>> which likely has a different version of Oracle installed in a different > >>> location. > >>> > >>> > >>> > >>> > > From: Lyle > To: dbi-dev@perl.org > Sent: Thursday, April 18, 2013 11:43 AM > Subject: Re: Building DBD::Oracle with one version but deploying with > another > > > On 18/04/2013 16:22, John Wiersba wrote: > > [A previous version of this question was asked on dbi-users -- I haven't > > gotten any response there. Not sure which list to post to.] > > > > Hi, I'd like to find out how to build/install DBD::Oracle with one > > version of Oracle client but then deploy it with a potentially different > > client version, say on a server
RE: Potential for new database driver
A pure perl version of the DBI already exists so I I take it you want to write a DBD::Blueprints? There are a number of pure perl DBD however I am not sure what Blueprints is??? Can you give us a link to it so I can at least see what you are writing a driver for?? Cheers John > From: libsys...@gmail.com > Subject: Potential for new database driver > Date: Mon, 4 Mar 2013 14:52:19 -0600 > To: dbi-dev@perl.org > > > Hi All, > > My name is Elliott Davis and I am relatively new to this list. I am > interested in writing a driver for Blueprints. I imagine this working similar > to the ODBC or the JDBC driver for DBI currently. I have read the DBI::DBD > documentation (only once, not twice as instructed) and have found it very > verbose. It seems that writing a C driver will be the optimal solution for > speed but a pure perl driver will make the module more maintainable. If I > were to work on the driver I think my goal would be a pure perl driver. The > DBI::DBD documentation suggest an initial email to the dev list before > pursuing code on any driver, so here I am. If anyone is currently working on > this or is interested in working on this could you please let me know. > > Best Regards, > > Elliott Davis
RE: Handling idle database connections
you might wan to try visit_handles http://search.cpan.org/~timb/DBI-1.623/DBI.pm#visit_handles and visit_child_handles http://search.cpan.org/~timb/DBI-1.623/DBI.pm#visit_child_handles Send a code ref down the pipe and close the ones that have been idle for a while. Just a though. > From: madhunapanthula.naaga.manikan...@deshaw.com > To: dbi-dev@perl.org > Subject: FW: Handling idle database connections > Date: Sat, 2 Feb 2013 13:09:03 + > > DBI-dev , can you please take a look and opine? > > Thanks a lot !! > Manikantan > > _ > From: Manikantan, Madhunapanthula_Naaga > Sent: Saturday, January 26, 2013 1:31 AM > To: 'dbi-us...@perl.org' > Subject: Handling idle database connections > > > Hello dbi-users, > > I have a ton of PERL scripts that open a database connection, do some > database related work for 5 to 10 mts and go on with other work for 2 to 3 > days. This means, the database connection will be idle after 5 mts but still > exists for 2-3 days. This can potentially choke my server once all the > available threads are consumed. I think the right approach is for the > developer to disconnect once he is done with database work. Since I have no > control over the scripts, I want to explore the below. > > I was wondering if there is any option to DBI that will say " if the > connection is idle for say 'X' mts disconnect automatically and set state > accordingly. On subsequent call to $dbh it can check the state and > re-establish connection as necessary". > > This will alleviate lot of my issues and any help/suggestion in this regard > is greatly appreciated !!! > > Thanks a lot !!! > Manikantan. >
RE: bind_param () - did something change?
> Date: Mon, 28 Jan 2013 19:33:46 +
> From: martin.ev...@easysoft.com
> To: dbi-dev@perl.org
> 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: h.m.br...@xs4all.nl
> >> To: dbi-dev@perl.org
> >> 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 ... &q
RE: bind_param () - did something change?
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 > Date: Mon,
28 Jan 2013 14:31:44 +0100
> From: h.m.br...@xs4all.nl
> To: dbi-dev@perl.org
> 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/
RE: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
> Date: Thu, 17 Jan 2013 19:47:06 + > From: martin.ev...@easysoft.com > To: byter...@hotmail.com > CC: c...@cam.ac.uk; dbi-dev@perl.org > Subject: Re: Problem with procedures returning a SYS_REFCURSOR which is not > open/executed - possible fixes > > On 17/01/2013 18:32, John Scoles wrote: > > > > > > > Date: Thu, 17 Jan 2013 13:48:15 + > > > From: martin.ev...@easysoft.com > > > To: c...@cam.ac.uk > > > CC: dbi-dev@perl.org > > > Subject: Re: Problem with procedures returning a SYS_REFCURSOR which > > is not open/executed - possible fixes > > > > > > On 17/01/13 12:26, Charles Jardine wrote: > > > > On 15/01/13 23:21, Martin J. Evans wrote: > > > > > > > >> I see loads of code setting indp so I created an indp2 in the phs and > > > >> passed it to OCIBindByName above and it is always 0 (Oracle > > assigned an > > > >> intact value to the host variable) whether a null cursor is > > returned or > > > >> not. It also did not seem to trigger ORA-01001 (invalid cursor) > > errors. > > > >> Also the test suite works with the indp set in the OCIBindByName. > > What a > > > >> PITA. I really wish when people write code like this they comment why > > > >> better. > > > >> > > > >> So it would seem resurrecting the phs->indp in the OCIBindByName does > > > >> not currently give me -1. Starting to wish I never started this. My > > > >> current change is better (in that it at least works whereas the > > previous > > > >> code did not at all) but you seem to suggest it is incomplete and > > that > > > >> concerns me. However, I've not been able to see what you suggested > > > >> should happen. I've already proved this speeds our application up > > a lot > > > >> compared with having to put a daft select 1 from dual in to just make > > > >> DBD::Oracle work so it would be a shame to fall at the last > > hurdle. Any > > > >> other ideas Charles? > > > >> > > > >> Bear in mind I cannot be getting a value from a previous execute > > as my > > > >> test code only does one execute so perhaps when you bind a > > SYS_REFCURSOR > > > >> you get a valid stmt handle back even if it is not executed. It seems > > > >> this must be the case since I can call > > OCIAtrrGet(OCI_ATTR_STMT_STATE) > > > >> on it after only one execute and it returns > > OCI_STMT_STATE_INITIALIZED. > > > > > > > > Martin, > > > > > > > > I have reproduced your results. If you supply an indp, Oracle does not > > > > ignore it. It explicitly sets it to 0, indicating a non-null value. > > > > It seems that OCI does not represent a null PL/SQL cursor reference > > > > as a normal null value. As you originally thought, it represents it > > > > with a statement handle which is not open. You were right - I was > > wrong. > > > > > > My post was simply based on observation and not what the docs said. > > However, I'm comforted to know that you get the same results. > > > > > > > I can't find any documentation of this special treatment of null > > values > > > > of type SQLT_RSET, but, in trawling through the OCI manual, and > > Oracle's > > > > knowledge base, I have found several examples of binds of type > > SQLT_RSET, > > > > none of which use indicator variables. > > > > > Just my 2p on some verbage. I think 'null' would be the 'wrong' word > > to use here when refering to a this type of 'SQLT_RSET' . > > > > This type is a referance so it would either be in one of two states > > 'initialized' or 'unitiliazied' points to something or doesn't, and > > even when it doesn't point to somthing is still takes up memory!! You > > have to love 'C' ;) > > > To be honest the cursor is uninitialised, executed or finished and in > this case it it is uninitialised and useless i.e., you cannot fetch from > from it. undef is the the only reasonable value to return to Perl. As > for any structure allocated in C land it is still deallocated as it is > in the descriptor, all my change does is: > > a) return undef if the cursor is unusable > b) stop DBD::Oracle attempting to describe an unusable cursor and erroring > &
RE: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
> Date: Thu, 17 Jan 2013 13:48:15 + > From: martin.ev...@easysoft.com > To: c...@cam.ac.uk > CC: dbi-dev@perl.org > Subject: Re: Problem with procedures returning a SYS_REFCURSOR which is not > open/executed - possible fixes > > On 17/01/13 12:26, Charles Jardine wrote: > > On 15/01/13 23:21, Martin J. Evans wrote: > > > >> I see loads of code setting indp so I created an indp2 in the phs and > >> passed it to OCIBindByName above and it is always 0 (Oracle assigned an > >> intact value to the host variable) whether a null cursor is returned or > >> not. It also did not seem to trigger ORA-01001 (invalid cursor) errors. > >> Also the test suite works with the indp set in the OCIBindByName. What a > >> PITA. I really wish when people write code like this they comment why > >> better. > >> > >> So it would seem resurrecting the phs->indp in the OCIBindByName does > >> not currently give me -1. Starting to wish I never started this. My > >> current change is better (in that it at least works whereas the previous > >> code did not at all) but you seem to suggest it is incomplete and that > >> concerns me. However, I've not been able to see what you suggested > >> should happen. I've already proved this speeds our application up a lot > >> compared with having to put a daft select 1 from dual in to just make > >> DBD::Oracle work so it would be a shame to fall at the last hurdle. Any > >> other ideas Charles? > >> > >> Bear in mind I cannot be getting a value from a previous execute as my > >> test code only does one execute so perhaps when you bind a SYS_REFCURSOR > >> you get a valid stmt handle back even if it is not executed. It seems > >> this must be the case since I can call OCIAtrrGet(OCI_ATTR_STMT_STATE) > >> on it after only one execute and it returns OCI_STMT_STATE_INITIALIZED. > > > > Martin, > > > > I have reproduced your results. If you supply an indp, Oracle does not > > ignore it. It explicitly sets it to 0, indicating a non-null value. > > It seems that OCI does not represent a null PL/SQL cursor reference > > as a normal null value. As you originally thought, it represents it > > with a statement handle which is not open. You were right - I was wrong. > > My post was simply based on observation and not what the docs said. However, > I'm comforted to know that you get the same results. > > > I can't find any documentation of this special treatment of null values > > of type SQLT_RSET, but, in trawling through the OCI manual, and Oracle's > > knowledge base, I have found several examples of binds of type SQLT_RSET, > > none of which use indicator variables. > Just my 2p on some verbage. I think 'null' would be the 'wrong' word to use > here when refering to a this type of 'SQLT_RSET' .This type is a > referance so it would either be in one of two states 'initialized' or > 'unitiliazied' points to something or doesn't, and even when it doesn't > point to somthing is still takes up memory!! You have to love 'C' ;) From my > deailing with OCI what I would expect to get is an 'itilaized' referance no > matter what state the object the ref is pointing to? I wish I had some time > to play with this one as it is a goodie. Maybe tonight:) Did you check this > in Martin or is it on a branch someplace?? Cheers > I think I'll undo the change which passes an indicator. I wish I knew what > that comment meant by causes an error but so long as I don't need the > indicator it is irrelevant. > > > I think is is important that PL/SQL null cursor references should > > reach Perl as undefined values. In the light of the above, I think > > that what you have already done is probably the best which can be done. > > It will treat cursor references which have explicitly closed as if > > they were null, but I think this is acceptable. > > Excellent. In addition someone else who reported a similar RT a while ago > tested my patch and it fixed their problem too. > > > I hope I haven't wasted too much if your time. > > Not at all. I am always grateful for your input. It was a avenue worth > exploring. > > Thanks again. > > I'll apply this patch later today. It will return undef for a non executed > output cursor. > > Martin > -- > Martin J. Evans > Easysoft Limited > http://www.easysoft.com
RE: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
Hmm you sure pick the head scratchers.
My first thoughts where why whould you make such a procedure?? But then I
relaisze it could have some use when prototying/develpopeing ect.
Looking at
'DBD::Oracle attempts to describe statements which are initialized but not
executed'
We might be able to do something here. I remember working on a patch some time
ago (ie 2008) to get the describe after the execute then do all the binding at
the end of an execute etc as the newer versions of Oracle return this data for
you. Not sure it that is the right time for this on as if I remember correctly
there was alot of reprogrmming involved.
Will have to look into that one.
Anyway I agree that pp_exec_rset is the place to make your change as the is
safe area to do it. Perhaps we can delay the pre_exec part untill after the
inital query is executed by that time you will know if you have a ref that you
will need to bind and return??
Been a while since I looked at this part of the code so bear with me if I sound
a little rusty.
Cheere
John
> Date: Fri, 11 Jan 2013 16:04:13 +
> From: martin.ev...@easysoft.com
> To: dbi-dev@perl.org
> Subject: Problem with procedures returning a SYS_REFCURSOR which is not
> open/executed - possible fixes
>
> I am using DBD::Oracle and calling a procedure which returns a reference
> cursor. However, sometimes the reference cursor is not opened and only the
> procedure knows this. The problem is if I call the procedure from DBD::Oracle
> and the cursor is not opened I get an Oracle error saying the cursor is not
> executed:
>
> test procedure:
> procedure p_n2(pcur OUT SYS_REFCURSOR) AS
> begin
> pcur := NULL;
> end;
>
> example perl:
> my $s = $h->prepare(q/begin mypkg.p_n2(?); end;/);
> $s->bind_param_inout(1, \my $cursor, 100, {ora_type => ORA_RSET});
> $s->execute; # errors
>
> The error occurs because DBD::Oracle attempts to call dbd_describe on the
> returned cursor (before perl land even sees it) and that code does things
> like call OCIAttrGet for PARAM_COUNT etc which Oracle disallows if the
> statement is not executed.
>
> An easy solution is to just open an empty cursor if the procedure cannot open
> a real one by doing something like:
>
> open pcur for select 1 from dual;
>
> but I don't like that as DBD::Oracle will make dozens of calls and do quite a
> bit of work in dbd_describe which is wasting time and the purpose of the
> change to my procedure is to speed this application up not slow it down.
>
> Just to be clear in case anyone thinks I've just invented a scenario and
> there is nothing actually wrong with DBD::Oracle - it is most definitely a
> bug for DBD::Oracle to attempt to describe a non-executed stmt.
>
> Possible solutions are complicated by these facts (some of which I only
> discovered after a few attempts of patching this):
>
> o DBD::Oracle magics a DBI sth into existence but it does this before the
> Oracle's execute is called in dbd_execute.
>
> o DBD::Oracle attempts to describe statements which are initialized but not
> executed.
>
> o DBD::Oracle will always call dbd_describe on any statement if it is not
> marked active when you attempt to access any attribute of the sth.
>
> So my first hack was to change dbd_describe to ask for the statement state
> and if it was initialized but not executed it just returns having done
> nothing else. This works because the sth is left in an Active state and
> dbd_describe has been called so the internal flag dbd_describe_done is set.
> However the down sides are a) you need to check to see if NUM_OF_FIELDS
> returns something before using it b) the sth is not really Active c)
> dbd_describe is a critical function and is used for all stmt handles, not
> just ones magicked into existence for returned cursors.
>
> My second attempt was to hijack the code in pp_exec_rset which is called just
> before execute to magic the stmt handle into existence and after execute to
> set the sth up and call dbd_describe. My thinking was that it was much safer
> changing code here. The first sub attempt to simply to check the stmt state
> and if initialized and not executed, don't call dbd_describe and don't set
> the sth Active. The idea was to check Active outside in perl land. It does
> not work because any time you attempt to access an attribute of a non-Active
> sth where dbd_describe has not been called, you guessed it, DBD::Oracle calls
> dbd_describe - so I am back where I started. My second sub attempt was to
> outright lie and set dbd_describe_done and leave Active off so from perl land
> I just need to test Active flag. This works and is a safer change since it
> ONLY applies to sth handles magicked into existence for returned cursors.
> Also, if you attempt to do anything else with the sth it errors as it should:
>
> DBD::Oracle::st fetch failed: ERROR no statement executing (perhaps you need
> to call execute first) at bz1245.pl l
RE: Oracle ping
Well I made that ping patch for DBIx::Connector a few months ago on one of my production boxes with no impact but then again I am not doing anything fancy (no rac raid etc). If it is critical to DBDx::Connector to ensure a query can be run then no, if you are going for 100% work alike betweeen all DBDs then no again and I am sure H.Merijn would like that. An option would be nice as well Cheers John > Subject: Re: Oracle ping > From: da...@justatheory.com > Date: Mon, 5 Nov 2012 10:26:52 -0800 > CC: c...@cam.ac.uk; dbi-dev@perl.org > To: martin.ev...@easysoft.com > > On Nov 5, 2012, at 7:59 AM, Martin J. Evans wrote: > > > Just to reiterate what I eventually said. I don't want ping changed in > > DBD::Oracle. > > > > All I was saying is you should only read anything useful into ping > > returning false and not true unless your only purpose is like Oracle's > > OCIPing which checks your connection is still there (although not usable > > for some things). I believe the connection caching only does anything when > > ping returns false. > > Hrm. Well I wonder if I *should* just use DBI->ping in DBIx::Connector for > Oracle rather than `select 1 from dual`. Or perhaps make it an option to > switch to the select statement. Thoughts? > > David >
RE: Oracle ping
> From: g...@turnstep.com > To: dbi-dev@perl.org > Subject: Re: Oracle ping > Date: Mon, 5 Nov 2012 02:47:06 + > > > -BEGIN PGP SIGNED MESSAGE- > Hash: RIPEMD160 > > >> Also by the time an application does execute some SQL, the the >> connection/node/server/network could have dropped and so the application >> 'will have to check & recover from write failures at that point anyway." > > This is a bit of a red herring. You could say that about any database action, > anytime ever. I personally think a simple select is better than relying > on a low-level library call, as it does a more complete end-to-end check > that is almost certainly closer to what the caller is going to use the > connection for. > Yes I am comming over to that way of thinking. Might just need some clarification in the DBD::Oracle doc then. i.e. May be able to ping but you cannot run querries or alike >> Mysql has 'mysql_ping' the same sort of thing as OCIPing except it can >> reconnect. > > Does it automatically reconnect? That certainly seems like the wrong thing > to do, especially in light of the docs for connect_cached. got that from the MySql doc it can be set up to reconnect if ping fails http://dev.mysql.com/doc/connector-python/en/myconnpy_MySQLConnection_ping.html> not didn't check the DBD driver to see if it does this though. Perhaps just some clarification on the DBI spec would be better to get all the DBD that impliment it in line and doing the same thing?? Cheers > - -- > Greg Sabino Mullane g...@turnstep.com > End Point Corporation http://www.endpoint.com/ > PGP Key: 0x14964AC8 201211042146 > http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 > > -BEGIN PGP SIGNATURE- > > iEYEAREDAAYFAlCXKI0ACgkQvJuQZxSWSsgMAACg4VdWNpWYgyrENkXKhO9G5Mez > /fkAoM6hBowTxDCAvKOD6G7yQBgItiO1 > =85sU > -END PGP SIGNATURE- > >
RE: dbi 1.622 with Turkish locale fails to connect to Oracle
You will have to give us the DBD::Oracle version as well. ORA-12514 is TNS:listener does not currently know of service requested in connect descriptor http://ora-12514.ora-code.com/ so I sort of suspect that you lintener and or service is not set up correctly to handle the language or the DB is in one languae and the clinet is trying to connect with another These sort of problems can be fixed with the 'ora_charset, ora_ncharset' flags at connection time Do a connect with the attribute dbd_verbose=>15 and that should dump what the client and DBs language code is. Cheers > Date: Wed, 31 Oct 2012 16:31:36 +0400 > Subject: dbi 1.622 with Turkish locale fails to connect to Oracle > From: svschwa...@gmail.com > To: dbi-dev@perl.org > > Hi guys > > I've installed dbi 1.622 on my test system. My simplest script connects to > oracle and does simple select. > It works with UK, US, RU and LT locales, but for some reason failes with > Turkish (LANG=tr_TR.iso88599) > > It failes with message: ORA-12514 (DBD ERROR: OCIServerAttach) > > I also saw a warning in DBI build log > > *** Your LANG environment variable is set to 'en_GB.UTF-8' > *** This may cause problems for some perl installations. > *** If you get test failures, please try again with LANG unset. > *** If that then works, please email dbi-dev@perl.org with details > *** including the output of 'perl -V' > > > *** You are using a perl configured with threading enabled. > *** You should be aware that using multiple threads is > *** not recommended for production environments. > > Is this critical ? > > Thanks > Sergey
RE: Oracle ping
Boy I think I really started something here.
I just heard back from OIC connection over at Oracle and here is his two cents
on the issue
"From an OCI point of view the OCIPing/OCIServerVersion calls are as
good as it gets for basic aliveness of the connection. I'd never have
thought they would guarantee the DB was writable. I believe they just
check the OCI and Network stacks. Since the connection doesn't know
anything about DB privileges or what action might be attempted on the
connection, I don't think the calls could do more. Also by the time
an application does execute some SQL, the the
connection/node/server/network could have dropped and so the application
'will have to check & recover from write failures at that point anyway."
So OCIPing and OCIServerVersion do the same thing and yes they 'Do not check
that a query can be executed'
As for history of Ping in DBD::Oracle
All version up till 1.21 use a query with a do
1.22 till 1.24 use just OCIServerversion
1.25 and later use either OICPing or OCIServerversion depending on the Oracle
client.
I think in the case of DBIx::Connector you want to really 100% know that a
connection can execute a Query?
The DBI doc sort of implies that but I checked a few other DBD and I see that
DBD::Pg uses an 'select' and Mysql has 'mysql_ping' the same sort of thing as
OCIPing except it can reconnect.
So For three DBDs you may see three separate behavours for DBH->ping
1) working but a zombie
2) error
3) reconnection, error or a zombie
So perhaps we need
1) A clarification on what DBH->ping does or is suppose to do (Tim Jump in)
2) A if DBH->Ping really means I can run a query a change to DBD::Oracle
3) Perhaps a new method like dbd->can_query??
David if you have a repeatable test or set of conditions or recipe of how you
get into this state where DBD::Oracle pings but cannot run queries my buddies
over at Oracle would love to get it.
Cheers
John
> Date: Fri, 2 Nov 2012 09:59:30 +
> From: martin.ev...@easysoft.com
> To: da...@justatheory.com
> CC: dbi-dev@perl.org
> Subject: Re: Oracle ping
>
> On 31/10/12 16:06, David E. Wheeler wrote:
> > Fellow DBIers,
> >
> > When I wrote DBIx::Connector, I borrowed this code from DBIx::Class to
> > “work around an issue”:
> >
> > sub ping {
> > my ($self, $dbh) = @_;
> > eval {
> > local $dbh->{RaiseError} = 1;
> > $dbh->do('select 1 from dual');
> > };
> > return $@ ? 0 : 1;
> > }
> >
> > The reason for this workaround is described in [this
> > comment](https://rt.cpan.org/Ticket/Display.html?id=47005#txn-808055) from
> > Peter Rabbitison:
>
> So, it appears someone discovered that DBD::Oracle's ping method can return
> true when you are still connected to the database (socket-wise) and yet you
> cannot issue a query. I didn't know that.
>
> >> DBD::Oracle has some shutdown state in which it will return 1 on ping as
> >> long as the socket is still open. This however did not guarantee the
> >> server is any longer in a state to execute
> >> queries. So what happened was:
> >>
> >> 1) the weird state is reached
> >> 2) a txn_do takes place and fails on the first sql command
> >> 3) the code calls ping() and gets a connected reply
> >> 4) the txn_do is not retried
> >> 5) ...
> >> 6) users lose profit
> >
> > So a few questions about this:
> >
> > 1. Was this issue reported somewhere?
>
> Not to my knowledge.
>
> > 2. If so, was it fixed or otherwise worked around?
>
> IIRC, DBD::Oracle has 2 ways to do ping in case OCIPing is not available.
> If OCIPing is not available it does a OCIServerVersion else OCIPing.
>
> > 3. And if it was fixed, in what version of DBD::Oracle?
>
> As far as I can see it still does what I said under 2.
>
> > Thanks,
> >
> > David
> >
>
> I've always assumed from the DBI docs that DBI's ping was not just supposed
> to say you were still connected, but you were in a state where you could
> issue queries, do inserts etc. It appears from what you've found that is not
> the case. It should be rt'ed but if anyone wants to look into what OCIPing
> really does and what DBD::Oracle should really do I'd appreciate it (as I am
> short on tuits right now).
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
RE: Patch proposal for leak in DBD::Oracle when calling 'execute_array' with UTF-8 NLS...
Hi guys had some time today to play again
Seems we have one of three choices
SvREFCNT_dec(tuples_utf8_av);
as pathced
or
av_clear(tuples_utf8_av);
at the send of the 'i' loop or
sv_2mortal((SV*)tuples_utf8_av);
at the begining
I have tried them all and they all get rid of the leak.
I also noticed that we are useing
safemalloc
which is sort of ugly Newz is better
My choice is to use the
sv_2mortal((SV*)tuples_utf8_av);
at the beginning and let perl worry about it.
I will do another pathc I think to get rid of the malloc code as I see it in
other places as well
Any more thoughts before I stick it in trunk???
cheers
John
> Date: Tue, 9 Oct 2012 21:21:13 +0200
> From: blan...@worldcom.ch
> To: martin.ev...@easysoft.com
> CC: dbi-dev@perl.org
> Subject: Re: Patch proposal for leak in DBD::Oracle when calling
> 'execute_array' with UTF-8 NLS...
>
>
>
> Hello Martin,
>
> Sorry for the late reply.
> Basically what my script tries to do is to copy lots of records from one
> database to another. To do this, I've come to the idea of using "execute
> array" to speed up the process. I generally use NLS_LANG set to UTF-8
> for Oracle, because it's the default encoding on my Linux distribution,
> so it was not a choice I've made for that specific application.
>
> It's when reviewing the code (after a little help from valgrind
> memory-checker), that I got the idea to turn UTF-8 off. Specifically the
> lines around ~ 3825 : ...
> /* update the utf8_flgs for this value */
> if (SvUTF8(sv)) {
> utf8_flgs[i] |= ARRAY_BIND_UTF8;
> if (SvTRUE(tuples_status)){
> av_push(tuples_utf8_av,newSViv(j));
> }
>
> ... made me think it was probably worth a try.
>
> As so far I understand, the 'tuples_utf8_av' is not given back to perl
> interpreter, I assumed that it was safe to decrement it's reference
> count to allow it to be garbage-collected. But as I said in my previous
> mail, I've no experience in Perl internals and that may be a false
> assumption.
>
> Thanks very much for your feedback.
> Best regards,
> Pierre-Alain Blanc
>
> On 09/10/12 09:53, Martin J. Evans wrote:
> > On 08/10/12 13:46, Pierre-Alain Blanc wrote:
> >>
> >>
> >> Hello,
> >>
> >> I've had a problem when using 'execute_array' to insert (lots of)
> >> records with DBD::Oracle (version 1.50): the script consumed too much
> >> memory and finally crashed (killed by kernel). I tried to trigger the
> >> garbage-collection with some code rewrite but it didn't help. But if
> >> I told Oracle *not* to use an UTF-8 charset (changing NLS_LANG from
> >> (for example) "german_germany.utf8" to "german_germany.we8dec"), the
> >> problem disappeared.
> >>
> >> After some investigations, I think the leak is in
> >> 'ora_st_execute_array' method of dbdimp.c. Please find the patch as
> >> attachment. As I'm completly new to writing C for Perl, it may be
> >> something I did not understand or did not correctly fixed. Sorry if
> >> it would be the case.
> >>
> >> Thanks & best regards, Pierre-Alain Blanc
> >>
> >>
> >
> > Thanks for looking into this. The patch:
> >
> > Index: dbdimp.c
> > ===
> > --- dbdimp.c (revision 15435)
> > +++ dbdimp.c (working copy)
> > @@ -3839,6 +3839,7 @@
> > }
> > Safefree(phs);
> > Safefree(utf8_flgs);
> > + SvREFCNT_dec(tuples_utf8_av);
> > /* Store array of bind typles, for use in OCIBindDynamic()
> > callback. */
> > imp_sth->bind_tuples = tuples_av;
> > imp_sth->rowwise = (columns_av == NULL);
> >
> > looks good but it does not explain how changing chrset made the
> > problem go away. Perhaps you could give me a better idea of what you
> > were doing then I can replicate and test it.
> >
> > Martin
>
RE: Patch proposal for leak in DBD::Oracle when calling 'execute_array' with UTF-8 NLS...
Well playing with it a bit tonight I tried this patch instead ++ AV *tuples_av =sv_2mortal(newAV()); -- tuples_utf8_av=newAV(); and it worked exactly the same. I have noticed a little thing though that may be another leak. Seems If I change the value of 'ora_array_chunk_size' It becomes stable but then seems to leak again later on. I will have to investigate a little more. Might just have to juggle things about a little to clear out memory when dealing with big ''ora_array_chunk_size' Cheers John > Date: Tue, 9 Oct 2012 16:57:05 +0200 > Subject: Re: Patch proposal for leak in DBD::Oracle when calling > 'execute_array' with UTF-8 NLS... > From: demer...@gmail.com > To: byter...@hotmail.com > CC: martin.ev...@easysoft.com; blan...@worldcom.ch; dbi-dev@perl.org > > On 9 October 2012 14:29, John Scoles wrote: > > > > I am sort of with Martin on this one as we would like to know why? > > > > At first glace decrementing the reference count like this would not seem to > > be a little overkill as tuples_utf8_av is created with newAV() which should > > take care of that for us. > > Should take care of what? > > newAV() doesnt create a mortal. > > Yves > > > -- > perl -Mre=debug -e "/just|another|perl|hacker/"
RE: Patch proposal for leak in DBD::Oracle when calling 'execute_array' with UTF-8 NLS...
Opps you are right Should read perlguts again. Looking again putting the tuples_utf8_av in the iteration may work but perhapse one should use 'sv_2mortal' at some point in the into to take care of the garbage collection for us. Like I said I only had a quick look at it. My preferace is to let XS take case for any decrementing for us, is my point I was trying to get across. cheers John > Date: Tue, 9 Oct 2012 16:57:05 +0200 > Subject: Re: Patch proposal for leak in DBD::Oracle when calling > 'execute_array' with UTF-8 NLS... > From: demer...@gmail.com > To: byter...@hotmail.com > CC: martin.ev...@easysoft.com; blan...@worldcom.ch; dbi-dev@perl.org > > On 9 October 2012 14:29, John Scoles wrote: > > > > I am sort of with Martin on this one as we would like to know why? > > > > At first glace decrementing the reference count like this would not seem to > > be a little overkill as tuples_utf8_av is created with newAV() which should > > take care of that for us. > > Should take care of what? > > newAV() doesnt create a mortal. > > Yves > > > -- > perl -Mre=debug -e "/just|another|perl|hacker/" >
RE: Patch proposal for leak in DBD::Oracle when calling 'execute_array' with UTF-8 NLS...
I am sort of with Martin on this one as we would like to know why? At first glace decrementing the reference count like this would not seem to be a little overkill as tuples_utf8_av is created with newAV() which should take care of that for us. However looking a little deeper maybe we need to move where we tuples_utf8_av=newAV(); down into the iteration of exe_count or at least clean it out at that level at each iteration. Seems the tuples_utf8_av is used just for the exe_count set of tuples not the full set of tuples. Just a few quick thoughts on it. Any testing I can do will have to wait till tonight when I can get to a UTF8 DB. Cheers John > Date: Tue, 9 Oct 2012 08:53:15 +0100 > From: martin.ev...@easysoft.com > To: blan...@worldcom.ch > CC: dbi-dev@perl.org > Subject: Re: Patch proposal for leak in DBD::Oracle when calling > 'execute_array' with UTF-8 NLS... > > On 08/10/12 13:46, Pierre-Alain Blanc wrote: > > > > > > Hello, > > > > I've had a problem when using 'execute_array' to insert (lots of) > > records with DBD::Oracle (version 1.50): the script consumed too much > > memory and finally crashed (killed by kernel). I tried to trigger the > > garbage-collection with some code rewrite but it didn't help. But if > > I told Oracle *not* to use an UTF-8 charset (changing NLS_LANG from > > (for example) "german_germany.utf8" to "german_germany.we8dec"), the > > problem disappeared. > > > > After some investigations, I think the leak is in > > 'ora_st_execute_array' method of dbdimp.c. Please find the patch as > > attachment. As I'm completly new to writing C for Perl, it may be > > something I did not understand or did not correctly fixed. Sorry if > > it would be the case. > > > > Thanks & best regards, Pierre-Alain Blanc > > > > > > Thanks for looking into this. The patch: > > Index: dbdimp.c > === > --- dbdimp.c (revision 15435) > +++ dbdimp.c (working copy) > @@ -3839,6 +3839,7 @@ > } > Safefree(phs); > Safefree(utf8_flgs); > + SvREFCNT_dec(tuples_utf8_av); > /* Store array of bind typles, for use in OCIBindDynamic() callback. */ > imp_sth->bind_tuples = tuples_av; > imp_sth->rowwise = (columns_av == NULL); > > looks good but it does not explain how changing chrset made the problem go > away. Perhaps you could give me a better idea of what you were doing then I > can replicate and test it. > > Martin > -- > Martin J. Evans > Easysoft Limited > http://www.easysoft.com
RE: Problem with Oracle collections/objects
Well partial answer for you Martin. Seems Cast Array and Collect are new extended SQL commands which allow you to directly select embedded types with SQL. So in this case we are mixing our metaphores. DBD::Oracle will do the casting for you and your code is trying to do the casting as well so in the end it gets muddleded. The CAST and crew doing its work on the back end my DBD::ORacle code playing about in the front. This I could see as a problem as some one who wants 'CAST' to work will find it doesn't which is bad. Though with embeded objects you have no need to use them. Will have to put in some code someplace to detect these new sql commands and ignor the built in DBD::Oracle object. No time for it today though. One way to check this theory is give it a try with an early version of DBD::Oracle that does not have the object stuff built in. I think 19 will do it. So iIf you can run your test below on a 1.19 install against 10g box and see what you get? >From what I see we are just running out of itter resouses in the OCI client so >it has slows down untill some are freed up, I have seen this before with >embedded objects when they go very deep with many types. One has to remeber >that the Varray, object and type are not really what one thinks they are. >When you get into the actulle implimetation on the DB level you will see they >are just flat releational tables. So a Varray in a table like this table(id >number ,my_Varray number(6) ) would actully be two tables one with ID and a >ref object field that points to another table that contains the ref and next >to it you varray values. Not the most efficient way to store your data and >why it has largely been ignored by most DBA since it came out way back in >oracle 8 I will play a little more when I get some time and see if I can rewrite the code to not use the CAST and array Cheers John > From: byter...@hotmail.com > To: martin.ev...@easysoft.com; dbi-dev@perl.org > CC: dbi-us...@perl.org > Subject: RE: Problem with Oracle collections/objects > Date: Tue, 13 Dec 2011 06:42:12 -0500 > > > Well I am able to recreate it which is a good thing. Like you said about 52 > iterations then it begins to gobble up memory in both oracle and perl so me > think i is running out of resources. > > I will give it a closer look > > Cheers > John > > > From: byter...@hotmail.com > > To: martin.ev...@easysoft.com; dbi-dev@perl.org > > CC: dbi-us...@perl.org > > Subject: RE: Problem with Oracle collections/objects > > Date: Sun, 11 Dec 2011 12:00:12 -0500 > > > > > > Well I wrote the code in DBD::Oracle for embedded objectes/types so I am > > the one to blame for that. From the SQL and connection string below looks > > like you are using the Oralce sql extensions to get the data back and not > > the native DBD::Oracle/OCI object selections so we might be looking at > > something else in DBD::Oracle. Will have to load this puppy up and have a > > look at the verbose trace > > > > Will have to wait till monday though swamped with SlJs here today > > > > Cheers > > John Scoles > > > > > Date: Fri, 9 Dec 2011 14:01:43 + > > > From: martin.ev...@easysoft.com > > > To: dbi-dev@perl.org > > > CC: dbi-us...@perl.org > > > Subject: Problem with Oracle collections/objects > > > > > > Hi, > > > > > > If anyone is around who wrote or has worked on the object/collections > > > support in DBD::Oracle I'd greatly appreciate it if you could take a > > > quick look at this problem as the code in DBD::Oracle for this has > > > defeated me so far. > > > > > > The problem is I have a query which uses types and collect and although > > > it works fine initially once it has been run a number of times in the > > > same connection it eventually goes from a 3s fetch time to a number of > > > minutes. I have reduced it to the example below. > > > > > > I can run this code all day long in sqlplus without a problem so I don't > > > think it is an Oracle issue. > > > > > > Changing the 3 numbers affects how many iterations it takes before it > > > goes wrong and how long the fetch eventually takes to retrieve the data. > > > Initially it seemed the calculation to find out which iteration it goes > > > wrong on was > > > > > > 2655 / objects fetched (the 3 numbers multipled together) gave the > > > iteration > > > > > > and that looked pretty close initially. Clutching at straws 2^31 / > > > 2655 is suspic
RE: Problem with Oracle collections/objects
Well I am able to recreate it which is a good thing. Like you said about 52
iterations then it begins to gobble up memory in both oracle and perl so me
think i is running out of resources.
I will give it a closer look
Cheers
John
> From: byter...@hotmail.com
> To: martin.ev...@easysoft.com; dbi-dev@perl.org
> CC: dbi-us...@perl.org
> Subject: RE: Problem with Oracle collections/objects
> Date: Sun, 11 Dec 2011 12:00:12 -0500
>
>
> Well I wrote the code in DBD::Oracle for embedded objectes/types so I am the
> one to blame for that. From the SQL and connection string below looks like
> you are using the Oralce sql extensions to get the data back and not the
> native DBD::Oracle/OCI object selections so we might be looking at something
> else in DBD::Oracle. Will have to load this puppy up and have a look at the
> verbose trace
>
> Will have to wait till monday though swamped with SlJs here today
>
> Cheers
> John Scoles
>
> > Date: Fri, 9 Dec 2011 14:01:43 +
> > From: martin.ev...@easysoft.com
> > To: dbi-dev@perl.org
> > CC: dbi-us...@perl.org
> > Subject: Problem with Oracle collections/objects
> >
> > Hi,
> >
> > If anyone is around who wrote or has worked on the object/collections
> > support in DBD::Oracle I'd greatly appreciate it if you could take a quick
> > look at this problem as the code in DBD::Oracle for this has defeated me so
> > far.
> >
> > The problem is I have a query which uses types and collect and although it
> > works fine initially once it has been run a number of times in the same
> > connection it eventually goes from a 3s fetch time to a number of minutes.
> > I have reduced it to the example below.
> >
> > I can run this code all day long in sqlplus without a problem so I don't
> > think it is an Oracle issue.
> >
> > Changing the 3 numbers affects how many iterations it takes before it goes
> > wrong and how long the fetch eventually takes to retrieve the data.
> > Initially it seemed the calculation to find out which iteration it goes
> > wrong on was
> >
> > 2655 / objects fetched (the 3 numbers multipled together) gave the
> > iteration
> >
> > and that looked pretty close initially. Clutching at straws 2^31 / 2655
> > is suspiciously 80.
> >
> > Here are some results with the 3 number variations:
> >
> > inner middle outer iteration_fail fetch_time_change
> > 10 100 500 53 8s->345s
> > 5 10 500 76 4s->200s
> > 20 100 500 37 12->632s
> > 5 120 500 64 5s->247s
> > 5 100 300 75 3s->121s
> >
> > It seems the inner number determines how bad the fetch time increases and
> > the sum how many iterations it takes to reach the point where it goes wrong.
> >
> > I'd greatly appreciate any ideas.
> >
> > The code to reproduce is:
> >
> > #!/usr/bin/perl
> > use warnings;
> > use strict;
> >
> > use DBI;
> >
> > my $dbh = DBI->connect('dbi:Oracle:host=xxx.yyy.com;sid=xxx;',
> > 'xxx', 'xxx',
> > # just so we can ctrl/c does not affect results
> > {ora_connect_with_default_signals => ['INT']});
> > eval {
> > $dbh->do(q/drop type TABLE_A/);
> > };
> > eval {
> > $dbh->do(q/drop type TABLE_B/);
> > };
> > eval {
> > $dbh->do(q/drop type RECORD_B/);
> > };
> > eval {
> > $dbh->do(q/drop type RECORD_A/);
> > };
> >
> > $dbh->do(q/CREATE OR REPLACE TYPE RECORD_B AUTHID DEFINER AS OBJECT (ID
> > INT)/);
> > $dbh->do(q/CREATE OR REPLACE TYPE TABLE_B IS TABLE OF RECORD_B/);
> > $dbh->do(q/CREATE OR REPLACE TYPE RECORD_A AUTHID DEFINER AS OBJECT (ID
> > INT, TBL TABLE_B)/);
> > $dbh->do(q/CREATE OR REPLACE TYPE TABLE_A IS TABLE OF RECORD_A/);
> >
> >
> > my $count = 0;
> >
> > while () {
> > my $stime = time;
> > my $j = do_it ($dbh);
> > my $etime = time;
> >
> > $count++;
> >
> > print $count ." - ".($etime - $stime)." secs\n";
> > }
> >
> > sub do_it {
> > my $dbh = shift;
> >
> > my $sql = <<"EOT";
> > SELECT
> > LEVEL ID,
> > (
> > SELECT
> > CAST (COLLECT(RECORD_A(ID, ARRAY)) AS TABLE_A) AS ARRAY
> > FROM
> > (
> > SELECT
> > LEVEL ID,
> > (
> > SELECT CAST(COLLECT(RECORD_B(ID)) AS TABLE_B) AS ARRAY
> > FROM (SELECT LEVEL ID FROM dual CONNECT BY LEVEL <= 10)
> > ) ARRAY
> > FROM
> > DUAL CONNECT BY LEVEL <= 100
> > )
> > ) ARRAY
> > FROM
> > DUAL CONNECT BY LEVEL <= 500
> > EOT
> >
> > my $s = $dbh->prepare($sql);
> > $s->execute;
> > my $r = $s->fetchall_arrayref;
> >
> > return $r;
> > }
> >
> > Thanks
> >
> > Martin
> > --
> > Martin J. Evans
> > Easysoft Limited
> > http://www.easysoft.com
>
RE: Problem with Oracle collections/objects
Well I wrote the code in DBD::Oracle for embedded objectes/types so I am the
one to blame for that. From the SQL and connection string below looks like you
are using the Oralce sql extensions to get the data back and not the native
DBD::Oracle/OCI object selections so we might be looking at something else in
DBD::Oracle. Will have to load this puppy up and have a look at the verbose
trace
Will have to wait till monday though swamped with SlJs here today
Cheers
John Scoles
> Date: Fri, 9 Dec 2011 14:01:43 +
> From: martin.ev...@easysoft.com
> To: dbi-dev@perl.org
> CC: dbi-us...@perl.org
> Subject: Problem with Oracle collections/objects
>
> Hi,
>
> If anyone is around who wrote or has worked on the object/collections support
> in DBD::Oracle I'd greatly appreciate it if you could take a quick look at
> this problem as the code in DBD::Oracle for this has defeated me so far.
>
> The problem is I have a query which uses types and collect and although it
> works fine initially once it has been run a number of times in the same
> connection it eventually goes from a 3s fetch time to a number of minutes. I
> have reduced it to the example below.
>
> I can run this code all day long in sqlplus without a problem so I don't
> think it is an Oracle issue.
>
> Changing the 3 numbers affects how many iterations it takes before it goes
> wrong and how long the fetch eventually takes to retrieve the data. Initially
> it seemed the calculation to find out which iteration it goes wrong on was
>
> 2655 / objects fetched (the 3 numbers multipled together) gave the
> iteration
>
> and that looked pretty close initially. Clutching at straws 2^31 / 2655
> is suspiciously 80.
>
> Here are some results with the 3 number variations:
>
> inner middle outer iteration_fail fetch_time_change
> 10 100 500 53 8s->345s
> 5 10 500 76 4s->200s
> 20 100 500 37 12->632s
> 5 120 500 64 5s->247s
> 5 100 300 75 3s->121s
>
> It seems the inner number determines how bad the fetch time increases and the
> sum how many iterations it takes to reach the point where it goes wrong.
>
> I'd greatly appreciate any ideas.
>
> The code to reproduce is:
>
> #!/usr/bin/perl
> use warnings;
> use strict;
>
> use DBI;
>
> my $dbh = DBI->connect('dbi:Oracle:host=xxx.yyy.com;sid=xxx;',
> 'xxx', 'xxx',
> # just so we can ctrl/c does not affect results
> {ora_connect_with_default_signals => ['INT']});
> eval {
> $dbh->do(q/drop type TABLE_A/);
> };
> eval {
> $dbh->do(q/drop type TABLE_B/);
> };
> eval {
> $dbh->do(q/drop type RECORD_B/);
> };
> eval {
> $dbh->do(q/drop type RECORD_A/);
> };
>
> $dbh->do(q/CREATE OR REPLACE TYPE RECORD_B AUTHID DEFINER AS OBJECT (ID
> INT)/);
> $dbh->do(q/CREATE OR REPLACE TYPE TABLE_B IS TABLE OF RECORD_B/);
> $dbh->do(q/CREATE OR REPLACE TYPE RECORD_A AUTHID DEFINER AS OBJECT (ID INT,
> TBL TABLE_B)/);
> $dbh->do(q/CREATE OR REPLACE TYPE TABLE_A IS TABLE OF RECORD_A/);
>
>
> my $count = 0;
>
> while () {
> my $stime = time;
> my $j = do_it ($dbh);
> my $etime = time;
>
> $count++;
>
> print $count ." - ".($etime - $stime)." secs\n";
> }
>
> sub do_it {
> my $dbh = shift;
>
> my $sql = <<"EOT";
> SELECT
> LEVEL ID,
> (
> SELECT
> CAST (COLLECT(RECORD_A(ID, ARRAY)) AS TABLE_A) AS ARRAY
> FROM
> (
> SELECT
> LEVEL ID,
> (
> SELECT CAST(COLLECT(RECORD_B(ID)) AS TABLE_B) AS ARRAY
> FROM (SELECT LEVEL ID FROM dual CONNECT BY LEVEL <= 10)
> ) ARRAY
> FROM
> DUAL CONNECT BY LEVEL <= 100
> )
> ) ARRAY
> FROM
> DUAL CONNECT BY LEVEL <= 500
> EOT
>
> my $s = $dbh->prepare($sql);
> $s->execute;
> my $r = $s->fetchall_arrayref;
>
> return $r;
> }
>
> Thanks
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
DBD::Oracle Release Candidate 2
Here is the next latest and greatest DBD::Oracle for your programming pleasure. You can find the Zip file here rel='attachment wp-att-20829'>DBD-Oracle-1.28_RC_2.tar This time round we have cleaned a few compiler warnings and fixed up a few of the tests. Thanks to H. Merijn Brand and Charles Jardine for those. Please enjoy. John Scoles
Re: DBD::Oracle Release Candidate 1
On 18/02/2011 4:12 AM, Charles Jardine wrote:
Ok that cleans that one up..
Hopefully I will have some time today to get to that one
Thanks
John
On 17/02/11 12:34, H.Merijn Brand wrote:
On Wed, 16 Feb 2011 12:32:12 +0100, "H.Merijn Brand"
wrote:
http://www.pythian.com/news/wp-content/uploads/DBD-Oracle-1.28_RC_1.zip
You need to work on longdouble support I guess
I wrote the tests in question. I included object properties of type
DATE and NUMERIC to provide tests against regression of some changes
I had made to get_attr_val to fix type mismatches.
I realise that I made a mistake by using literal Perl floating point
numbers in the insert statements. Doing it this way means that, on
the route into Oracle, the number is converted first into Perl's binary
floating point format and then into Oracle's base-100 format.
(I was only interested in testing the route out of Oracle.)
I think the best fix is simply to use strings in the insert statements.
Change 12345.6789 to '12345.6789' and 777.666 to '777.666'. This
way, the conversions both ways will be done by Oracle. Conversion of
a decimal string representation to a base-100 representation of adequate
precision will be exact. Nothing will be hidden under the carpet.
I should have written it like this originally. I am sorry.
Here's a patch to make the test PASS on all systems, but I'm not sure
if I'm using a carpet to shuv the problems under ...
--- t/58object.t.org2011-02-17 13:33:48.0 +0100
+++ t/58object.t2011-02-17 13:33:25.0 +0100
@@ -82,9 +82,9 @@ $dbh->do(qq{ INSERT INTO $table VALUES (
or die $dbh->errstr;
$dbh->do(qq{ INSERT INTO $table VALUES (2, $sub_type(NULL, 'obj2',
TO_DATE('2004-11-30 14:27:18', '-MM-DD
HH24:MI:SS'),
-12345.6789)) }
+12345.9375)) }
) or die $dbh->errstr;
-$dbh->do(qq{ INSERT INTO $table VALUES (3, $sub_type(5, 'obj3',
NULL, 777.666)) }
+$dbh->do(qq{ INSERT INTO $table VALUES (3, $sub_type(5, 'obj3',
NULL, 777.875)) }
) or die $dbh->errstr;
$dbh->do(qq{ CREATE OR REPLACE TYPE $inner_type AS OBJECT (
@@ -159,14 +159,14 @@ ok (scalar @row2, 'new: Fetch second row
cmp_ok(ref $row2[1], 'eq', 'DBD::Oracle::Object', 'new: Row 2
column 2 is an DBD::Oracle::Object');
cmp_ok(uc $row2[1]->type_name, "eq", uc "$schema.$sub_type", "new:
Row 2 column 2 object type");
is_deeply([$row2[1]->attributes], ['NUM', undef, 'NAME', 'obj2',
-'DATETIME', '2004-11-30T14:27:18', 'AMOUNT',
'12345.6789'], "new: Row 1 column 2 object attributes");
+'DATETIME', '2004-11-30T14:27:18', 'AMOUNT',
'12345.9375'], "new: Row 1 column 2 object attributes");
@row3 = $sth->fetchrow();
ok (scalar @row3, 'new: Fetch third row');
cmp_ok(ref $row3[1], 'eq', 'DBD::Oracle::Object', 'new: Row 3
column 2 is an DBD::Oracle::Object');
cmp_ok(uc $row3[1]->type_name, "eq", uc "$schema.$sub_type", "new:
Row 3 column 2 object type");
is_deeply([$row3[1]->attributes], ['NUM', 5, 'NAME', 'obj3',
-'DATETIME', undef, 'AMOUNT', '777.666'], "new: Row 1
column 2 object attributes");
+'DATETIME', undef, 'AMOUNT', '777.875'], "new: Row 1
column 2 object attributes");
ok (!$sth->fetchrow(), 'new: No more rows expected');
@@ -178,7 +178,7 @@ my $expected_hash = {
NUM => 5,
NAME=> 'obj3',
DATETIME=> undef,
-AMOUNT => 777.666,
+AMOUNT => 777.875,
};
is_deeply($obj->attr_hash, $expected_hash,
'DBD::Oracle::Object->attr_hash');
is_deeply($obj->attr, $expected_hash, 'DBD::Oracle::Object->attr');
Re: DBD::Oracle Release Candidate 1
On 17/02/2011 7:34 AM, H.Merijn Brand wrote:
On Wed, 16 Feb 2011 12:32:12 +0100, "H.Merijn Brand"
wrote:
http://www.pythian.com/news/wp-content/uploads/DBD-Oracle-1.28_RC_1.zip
You need to work on longdouble support I guess
Here's a patch to make the test PASS on all systems, but I'm not sure
if I'm using a carpet to shuv the problems under ...
Yep I think that is exactly what you are doing.
I will have to look exactly at this to see what is going in but your
test and the code below will help
Will get back to you soon
I hope
Cheers
John
--- t/58object.t.org2011-02-17 13:33:48.0 +0100
+++ t/58object.t2011-02-17 13:33:25.0 +0100
@@ -82,9 +82,9 @@ $dbh->do(qq{ INSERT INTO $table VALUES (
or die $dbh->errstr;
$dbh->do(qq{ INSERT INTO $table VALUES (2, $sub_type(NULL, 'obj2',
TO_DATE('2004-11-30 14:27:18', '-MM-DD HH24:MI:SS'),
-12345.6789)) }
+12345.9375)) }
) or die $dbh->errstr;
-$dbh->do(qq{ INSERT INTO $table VALUES (3, $sub_type(5, 'obj3', NULL,
777.666)) }
+$dbh->do(qq{ INSERT INTO $table VALUES (3, $sub_type(5, 'obj3', NULL,
777.875)) }
) or die $dbh->errstr;
$dbh->do(qq{ CREATE OR REPLACE TYPE $inner_type AS OBJECT (
@@ -159,14 +159,14 @@ ok (scalar @row2, 'new: Fetch second row
cmp_ok(ref $row2[1], 'eq', 'DBD::Oracle::Object', 'new: Row 2 column 2 is an
DBD::Oracle::Object');
cmp_ok(uc $row2[1]->type_name, "eq", uc "$schema.$sub_type", "new: Row 2 column 2
object type");
is_deeply([$row2[1]->attributes], ['NUM', undef, 'NAME', 'obj2',
-'DATETIME', '2004-11-30T14:27:18', 'AMOUNT', '12345.6789'], "new: Row 1
column 2 object attributes");
+'DATETIME', '2004-11-30T14:27:18', 'AMOUNT', '12345.9375'], "new: Row 1
column 2 object attributes");
@row3 = $sth->fetchrow();
ok (scalar @row3, 'new: Fetch third row');
cmp_ok(ref $row3[1], 'eq', 'DBD::Oracle::Object', 'new: Row 3 column 2 is an
DBD::Oracle::Object');
cmp_ok(uc $row3[1]->type_name, "eq", uc "$schema.$sub_type", "new: Row 3 column 2
object type");
is_deeply([$row3[1]->attributes], ['NUM', 5, 'NAME', 'obj3',
-'DATETIME', undef, 'AMOUNT', '777.666'], "new: Row 1 column 2 object
attributes");
+'DATETIME', undef, 'AMOUNT', '777.875'], "new: Row 1 column 2 object
attributes");
ok (!$sth->fetchrow(), 'new: No more rows expected');
@@ -178,7 +178,7 @@ my $expected_hash = {
NUM => 5,
NAME=> 'obj3',
DATETIME=> undef,
-AMOUNT => 777.666,
+AMOUNT => 777.875,
};
is_deeply($obj->attr_hash, $expected_hash, 'DBD::Oracle::Object->attr_hash');
is_deeply($obj->attr, $expected_hash, 'DBD::Oracle::Object->attr');
Re: DBD::Oracle Release Candidate 1
On Wed, Feb 16, 2011 at 5:45 AM, Martin J. Evans
wrote:
> On 11/02/11 18:43, John Scoles wrote:
>>
>> Here is the latest and greatest DBD::Oracle for your programming pleasure.
>>
>> You can find the Zip file here
>>
>> > href="http://www.pythian.com/news/wp-content/uploads/DBD-Oracle-1.28_RC_1.zip";>DBD-Oracle-1.28-RC1.zip
>>
>> This is a long overdue maintenance release that fixes a large number of
>> bug and issues which are detailed below in the Changes.
>>
>> Don't worry there are some new goodies in this release namely I have added
>> in 4 new
>> server side tracing/debugging attributes
>>
>> ora_driver_name
>> For 11g and later you can now set the name of the driver layer using OCI.
>> PERL, PERL5, ApachePerl so on. Names starting with "ORA" are reserved. You
>> can enter up to 8 characters. If none is enter then this will default to
>> DBDO where is the current version number. This value can be
>> retrieved on the server side using V$SESSION_CONNECT_INFO or
>> GV$SESSION_CONNECT_INFO
>>
>> ora_client_info
>> When passed in on the connection attributes it can specify any info you
>> want
>> onto the session up t0 64 bytes. This value can be
>> retrieved on the server side using V$SESSION view.
>>
>> ora_client_identifier
>> When passed in on the connection attributes it specifies the user
>> identifier
>> in the session handle. Most useful for web app as it can pass in the
>> session
>> user name which might be different than the connection user name. Can be
>> up
>> to 64 bytes long do not to include the password for security reasons and
>> the
>> first character of the identifier should not be ':'. This value can be
>> retrieved on the server side using V$SESSION view.
>>
>> ora_action
>> You can set this value to anything you want up to 32byes.This value can be
>> retrieved on the server side using V$SESSION view.
>>
>> We have also added in the connection attribute
>>
>> ora_connect_with_default_signals
>>
>> Whit this attribute you can localize the $SIG{} so this should solve the
>> problems with $SIG{} events that sometimes occur when using DBD::Oracle
>>
>>
>> Finally I would like to thank Martin Evans for volunteering to be another
>> co-maintainer of DBD::Oracle
>>
>>
>>
>> Cheers
>> John Scoles
>>
>>
>> Changes
>> Added connection attribute 'ora_connect_with_default_signals' that will
>> localize Perl's $SIG{INT} handler from Brian Phillips and T. Bunce
>> Fix in execute_array to stop possible endless loop when using a fetch sub
>> by Martin J. Evans
>> Adapted Martin J. Evans' ODBC 70execute_array.t into t/26exe_array.t by
>> John Scoles
>> Fix for execute_array to bring it up to spec. by Martin J. Evans and John
>> Scoles
>> Marked ProC, Oraperl.pm, ora_explain.pl, ora_context,
>> ora_use_proc_connection and ora_parse_lang as deprecated to be removed in
>> 1.29
>> Added in 4 new server side debug/trace attributes, ora_driver_name,
>> ora_client_info, ora_session_user and ora_action on the connection handle
>> from John Scoles
>> Cleaned up the pod a little by John Scoles
>> Fix for function name length, Some function names are over 31char long
>> which may cause problems for some OS/Compilers (VMS IA64 box.) from Jakob
>> Snoer
>> Fix for OCIPing in case where a 10 client tries to ping a <10 DB from
>> Tim Oertel
>> Fix for DBD-Oracle stored proc with array bug where second call array size
>> is unchanged from Tim Oertel
>> Fix for rt.cpan.org Ticket #=63332: Spelling error in POD from jonasbn
>> Fix for rt.cpan.org Ticket #=62152: t/28array_bind.t and t/31lob.t may
>> call plan() twice and others do not fail on not connect from John Scoles
>> Fix for rt.cpan.org Ticket #=61511 ORA-00942 when inserting into a table
>> with a LOB column over a synonym on HP-UX from Kris Lemaire
>> Fix for rt.cpan.org Ticket #=42842 Test 31lob fails with 64-bit Instant
>> Client by John Scoles
>> Fix for support for objects on big endian platforms from Charles Jardine,
>> John R Pierce
>> Fix for rt.cpan.org Ticket #=61225 Windows install (Stawberry Perl) fails
>> on long path names from David Tulloh
>> Fix for rt.cpan.org Ticket #=rt64524 Memory Leak when Oracle connection
>> fails by Martin J. Evans
>> Added all the missing ora_drcp values to dbh private_attribute_info by
>> Martin J. Evans
>> Removed a load of attributes from sth pr
Re: Question about passing a stored procedure file to DBD-Oracle
On 11/02/2011 12:33 PM, tran...@commerceinsurance.com wrote:
Never tried that myself but I do not see any reason why it should
not work. As long as the $record is correctly formatted.
To start try this on the prepare
$DBH->prepare(
"BEGIN".$record."END;"
);
That might do the trick.
Without seeing the original SQL it will be hard to give you a 100%
answer.
Cheers
John Scoles
I'm sorry in advance
if this is not the
correct forum/mechanism to ask this question - I have been
googling this
for a while, as well as re-reading all of the CPAN man page for
DBD-Oracle.
I need to pass a physical file(
or the
contents of that file), that contains code to create an Oracle
stored procedure
into DBD-Oracle. So like SQL PLus, where I'd say >
@package.spb,
I have been trying to do the same in every way imaginable with $DBH->prepare
/ do. As of right now, this is not working:
# load
file contents
open
(FILEDATA,
"$sql");
while ($record
= )
{
print
$record;
}
close(FILEDATA);
#
prepare the stored procedure
if
($sth
= $DBH->prepare(
$record
)){
print
$LOG
"prepared:
$HoH{$filename}{'full_filename'}
\n";
#
execute the
stored procedure
if
($sth->execute()){
print
$LOG
"it made it:
$HoH{$filename}{'full_filename'}
\n";
$HoH{$filename}{'build_state'}
= 'COMPILED';
} else
{
#
update
the hash, since we will not be loading this one
print $LOG
"couldnt
excute:
$HoH{$filename}{'full_filename'} \n";
$HoH{$filename}{'build_state'}
= 'FAILED
- COULD NOT
EXECUTE';
}
} else
{
#
update the hash, since we will not be loading this one
print
$LOG
"couldnt
prepare:
$HoH{$filename}{'full_filename'} \n";
$HoH{$filename}{'build_state'}
= 'FAILED
- COULD NOT
PREPARE';
} #
preparing sql
When executed, I get: DBD::Oracle::db
prepare
failed: ORA-24373: invalid length specified for statement (DBD
ERROR: OCIStmtPrepare) at
C:\Tronweb\TW_AUTOMATED_BUILDS\scripts\release_cvs_to_dev.pl
line 261.
This file that I'm trying to
prepare
is small, and I've even set up my Oracle session as:
#Open
an Oracle session
$DBH
= DBI->connect(
"dbi:Oracle:$sid",
$usr,
$pwd
)
or die
"Unable
to connect to $sid: $DBI::errstr";
$DBH->{RaiseError}
= 1; #
Shows the errors if CREATE PROCEDURE fails
$DBH->{LongReadLen}
= 5242880;
$DBH->{LongTruncOk}
= 0;
Do you have any suggestions for
this
dilemma?
THANK YOU for any advice /
direction
that you can provide!
T.j. Randall
tran...@commerceinsurance.com
Systems Analyst | IT
Application Services
| Desk: 508.949.4493 | Cell: 774.633.9123 | “Problems
are the price of
progress."-- Charles F. Kettering
DBD::Oracle Release Candidate 1
Here is the latest and greatest DBD::Oracle for your programming pleasure.
You can find the Zip file here
href="http://www.pythian.com/news/wp-content/uploads/DBD-Oracle-1.28_RC_1.zip";>DBD-Oracle-1.28-RC1.zip
This is a long overdue maintenance release that fixes a large number of
bug and issues which are detailed below in the Changes.
Don't worry there are some new goodies in this release namely I have
added in 4 new
server side tracing/debugging attributes
ora_driver_name
For 11g and later you can now set the name of the driver layer using OCI.
PERL, PERL5, ApachePerl so on. Names starting with "ORA" are reserved. You
can enter up to 8 characters. If none is enter then this will default to
DBDO where is the current version number. This value can be
retrieved on the server side using V$SESSION_CONNECT_INFO or
GV$SESSION_CONNECT_INFO
ora_client_info
When passed in on the connection attributes it can specify any info you want
onto the session up t0 64 bytes. This value can be
retrieved on the server side using V$SESSION view.
ora_client_identifier
When passed in on the connection attributes it specifies the user
identifier
in the session handle. Most useful for web app as it can pass in the session
user name which might be different than the connection user name. Can be up
to 64 bytes long do not to include the password for security reasons and the
first character of the identifier should not be ':'. This value can be
retrieved on the server side using V$SESSION view.
ora_action
You can set this value to anything you want up to 32byes.This value can be
retrieved on the server side using V$SESSION view.
We have also added in the connection attribute
ora_connect_with_default_signals
Whit this attribute you can localize the $SIG{} so this should solve the
problems with $SIG{} events that sometimes occur when using DBD::Oracle
Finally I would like to thank Martin Evans for volunteering to be
another co-maintainer of DBD::Oracle
Cheers
John Scoles
Changes
Added connection attribute 'ora_connect_with_default_signals' that
will localize Perl's $SIG{INT} handler from Brian Phillips and T. Bunce
Fix in execute_array to stop possible endless loop when using a fetch
sub by Martin J. Evans
Adapted Martin J. Evans' ODBC 70execute_array.t into t/26exe_array.t
by John Scoles
Fix for execute_array to bring it up to spec. by Martin J. Evans and
John Scoles
Marked ProC, Oraperl.pm, ora_explain.pl, ora_context,
ora_use_proc_connection and ora_parse_lang as deprecated to be removed
in 1.29
Added in 4 new server side debug/trace attributes, ora_driver_name,
ora_client_info, ora_session_user and ora_action on the connection
handle from John Scoles
Cleaned up the pod a little by John Scoles
Fix for function name length, Some function names are over 31char
long which may cause problems for some OS/Compilers (VMS IA64 box.) from
Jakob Snoer
Fix for OCIPing in case where a 10 client tries to ping a <10 DB
from Tim Oertel
Fix for DBD-Oracle stored proc with array bug where second call array
size is unchanged from Tim Oertel
Fix for rt.cpan.org Ticket #=63332: Spelling error in POD from jonasbn
Fix for rt.cpan.org Ticket #=62152: t/28array_bind.t and t/31lob.t
may call plan() twice and others do not fail on not connect from John Scoles
Fix for rt.cpan.org Ticket #=61511 ORA-00942 when inserting into a
table with a LOB column over a synonym on HP-UX from Kris Lemaire
Fix for rt.cpan.org Ticket #=42842 Test 31lob fails with 64-bit
Instant Client by John Scoles
Fix for support for objects on big endian platforms from Charles
Jardine, John R Pierce
Fix for rt.cpan.org Ticket #=61225 Windows install (Stawberry Perl)
fails on long path names from David Tulloh
Fix for rt.cpan.org Ticket #=rt64524 Memory Leak when Oracle
connection fails by Martin J. Evans
Added all the missing ora_drcp values to dbh private_attribute_info
by Martin J. Evans
Removed a load of attributes from sth private_attribute_info which
are not handle attributes but attributes to bind_param/prepare by Martin
J. Evans
Fix for rt.cpan.org Ticket #=64244 - don't bail out, skip tests
when we cannot connect by Martin J. Evans and John Scoles
Added DBI to PREREQ_PM in Makefile.PL by Martin J. Evans
Added build_requires in Makefile.PL by Martin J. Evans
Added workaround for ExtUtils::MakeMaker problems by Martin J. Evans
Added LICENSE to Makefile.PL by Martin J. Evans
Cheers John Scoles
Re: more on execute_array not complying with the specification
On 02/02/2011 10:52 AM, Martin J. Evans wrote:
The argument seems to have got confused here between what DBI does, what it
says it does and DBD::Oracle (which does neither). I don't want (and don't
think) any change (other than in pod) in DBI is necessary. The thread started
with my observations of differences between DBI and DBD::Oracle with the
summary:
a) even though RaiseError was set, no error was raised although a warning was.
b) execute_array returned undef (correct)
c) errstr is set but err is not (0)
d) the HandleError routine was not called - due to (a)?
e) the count of rows affected is -1 for all rows which worked - I believe this
is permissible
I later noticed:
f) DBD::Oracle was never returning the total rows affected in list context
g) DBI was documenting 2 fields in ArrayTupleStatus for an error put 3 in
On 02/02/11 12:15, Tim Bunce wrote:
On Tue, Feb 01, 2011 at 09:02:26PM +, Martin J. Evans wrote:
On 01/02/2011 20:50, Tim Bunce wrote:
On Tue, Feb 01, 2011 at 10:58:14AM -0500, John Scoles wrote:
My only concern is when it does error (no matter what the setting of
AutoCommit) you always get unef;
Umm, yes. Returning undef (or an empty list) if there was any kind of
error causes useful information (the total row count) to be discarded.
I don't see why when called in list context. Just because
execute_array partially succeeded (or failured) only means undef
needs to be returned for the first scalar and does not affect the
rows affected.
True for the total row count. I was thinking of the RaiseError case,
I think. Also, by that definition, this wouldn't work:
Yes, sorry, me being an idiot - of course, when RaiseError is set whether
scalar/list context nothing it returned as $tuples/$rows.
unless (($tuples, $rows) = $sth->execute_array(...)) {
...handle error...
}
That's not a big deal though. (Returning undef for the tuple count
isn't a big problem because you can determine that from the length of
ArrayTupleStatus.)
agreed.
I only added list context to DBI ages ago because I noticed the affected rows
was available for the batch but not individually and at the time I wanted it.
Some time since then this has stopped working and I probably ignored it since I
have a broken Oracle DB which fails the 26exe_array test anyway and I don't use
execute_array in this project now.
DBD::Oracle no longer (in 1.27 at least) works to the spec as it is now because
even against a working Oracle 10 database (working in that it commits working
tuples) rows_affected is always undef even when RaiseError is NOT set. See the
script I posted which output:
In point of fact it has never worked according to spec since 1.18 when
its own exe_array was introduced.
DB Version: 10.2.0.1.0
AutoCommit = 1 RaiseError = 0 # raise error NOT set
# warning output by PrintWarn:
DBD::Oracle::st execute_array warning: ORA-24381: error(s) in array DML (DBD
SUCCESS_WITH_INFO: OCIStmtExecute) [for Statement "insert into mytest values
(?,?)"] at
/home/martin/svn/dbd-odbc/trunk/rt_data/execute_array/execute_array_oracle.pl line 62.
execute_array did not raise error: '' # which you'd expect
execute_array = undef # which is to the spec as it is now
total affected rows = undef # OOPS - not set even though some rows were affected
Error from execute_array - errstr=ORA-24381: error(s) in array DML (DBD
SUCCESS_WITH_INFO: OCIStmtExecute), err=0, state=''
$tuple_status = [
-1, # because Oracle cannot give you these per tuple
[
1,
'ORA-1: unique constraint (SYSTEM.SYS_C005676)
violated (DBD SUCCESS_WITH_INFO)'
],
-1,
-1
];
Error captured in handler: undef # as you'd expect as RaiseError not set
# warning captured in SIGWARN handler:
Warning captured in SIGWARN handler: 'DBD::Oracle::st execute_array warning: ORA-24381:
error(s) in array DML (DBD SUCCESS_WITH_INFO: OCIStmtExecute) [for Statement "insert
into mytest values (?,?)"] at
/home/martin/svn/dbd-odbc/trunk/rt_data/execute_array/execute_array_oracle.pl line 62.
'
# good rows successfully committed:
$select * from mytest = [
[
'1',
'onetwothree '
],
[
'51',
'fiftyone'
],
[
'52',
'fiftythree '
],
[
'53',
'one '
Re: more on execute_array not complying with the specification
On 02/02/2011 7:15 AM, Tim Bunce wrote:
On Tue, Feb 01, 2011 at 09:02:26PM +, Martin J. Evans wrote:
On 01/02/2011 20:50, Tim Bunce wrote:
On Tue, Feb 01, 2011 at 10:58:14AM -0500, John Scoles wrote:
My only concern is when it does error (no matter what the setting of
AutoCommit) you always get unef;
Umm, yes. Returning undef (or an empty list) if there was any kind of
error causes useful information (the total row count) to be discarded.
I don't see why when called in list context. Just because
execute_array partially succeeded (or failured) only means undef
needs to be returned for the first scalar and does not affect the
rows affected.
True for the total row count. I was thinking of the RaiseError case,
I think. Also, by that definition, this wouldn't work:
unless (($tuples, $rows) = $sth->execute_array(...)) {
...handle error...
}
That's not a big deal though. (Returning undef for the tuple count
isn't a big problem because you can determine that from the length of
ArrayTupleStatus.)
This brings us back to the question of whether a failure of a single
tuple should cause execute_array() itself to return an error.
ODBC doesn't do that. It treats that situation as SUCCESS_WITH_INFO
and that does seem very reasonable.
The funny thing is being an big user of ODBC, I don't (and others in
this thread expressed the same belief).
I'm (wobbling) on the fence on that one. I can see it both ways,
though I'm more sympathetic to your viewpoint :)
I did a few tests and OCI (Oracle) can do it both ways, It will always
return 'SUCCESS_WITH_INFO' if there
is an error in your exe it is all in the manner in which mode you call
the exe and how you handle the result.
You can stop at the first error or batch the errors.
Perhaps we should go down that road
BatchErrors=>1
no errors raised but the $tuples will be smaller than length
(@tuple_status) so we know there was an error or the list context can be
increased to three
($tuples,$rows,$errors)
all of the above we can get from present code.
BatchErrors=>0 (DEFAULT)
stops on first error with error
Commits are handled as they are now either commits after the exe
"autocommit" or you have to do it yourself after. Would not matter what
the error condition is it should commit any good ones.
For me, if I use execute_array (with AutoCommit on or not) my
principle issue is I want to know if anything failed so I do:
$dbh->{RaiseError} = 1;
my $ret = eval {
execute_array(...)
};
do_something_on_error() if ($@ || !$ret); # amended for DBD::Oracle behavior
Ok. How would you get the total row count when using RaiseError?
Why put the onus on people who cannot accept some tuples to fail to
check the ArrayTupleStatus instead of put it on those who can accept
some to fail to examine ArrayTupleStatus? My feeling is that it is
very serious to ignore a partial failure and so the default should
be to ensure you know about it with RaiseError. For those people who
can accept a tuple to fail, they can look at ArrayTupleStatus and
still commit the changes.
I agree.
but I can live with that.
Perhaps we could default to the SUCCESS_WITH_INFO model and add an
attribute for people to use if they want tutple errors to cause
execute_array() itself to return an error.
We can debate the name later, but for now let's call it:
ArrayPromoteTupleError => 1
which defaults to 1, I hope.
Probably ;-)
Incidentally, I just blogged about this a few hours ago as I think
it is a serious problem to ignore potential failures. Add that to
the fact that some versions of Oracle database (broken) return
success with info but don't then commit the ok tuples.
I have been looking into that for some time and it seems it it is very
version/patch/OS specific to 11g. Might even be a bug on the instant
client. I have yet to play with an 11g combination that exhibits the
bug. And oracle says there is no bug.
Cheers
Yes, that's clearly a serious bug.
Tim.
Re: more on execute_array not complying with the specification
On 01/02/2011 11:04 AM, Martin J. Evans wrote: On 01/02/11 15:50, John Scoles wrote: On 01/02/2011 10:44 AM, Tim Bunce wrote: On Sun, Jan 30, 2011 at 05:46:49PM +, Martin J. Evans wrote: push @$tuple_status, [ $sth->err, $sth->errstr, $sth->state ]; so I guess the pod should say: "If the execution of a tuple causes an error, then the corresponding status array element will be set to a reference to an array containing the err, errstr and state set by the failed execution. If that is the case let me know and I'll amend the pod. Otherwise, I'll need to know what was intended. Yes, the state should be included and mentioned in the docs. Thanks. # NOTE: I'd like to do the following test but it requires Multiple # Active Statements and although I can find ODBC drivers which do this # it is not easy (if at all possible) to know if an ODBC driver can # handle MAS or not. Perhaps try to run it and if it gets an error, and the error indicates lack of MAS, then skip the test. Tim. p.s. Sorry for the delay. Ok I will have to add that into DBD::Oracle, What exactly should go into the "State" I am sort of unclear on what should be there? The state if you have not got one is S1000 - see state in DBI docs. I've not checked but I think if you don't set it when you call set_err (or the macro for XS) it defaults to S1000 if it is an error. Martin Funny in SQL for Ada there is a function to covert ot the SQLSTATE codes to ORA. Nothing like in in OCI though:(. Unfortunately there is no corresponding Oracle code for S1000 '01000' is success with warning though I will add that in
Re: more on execute_array not complying with the specification
On 01/02/2011 10:48 AM, Tim Bunce wrote: On Mon, Jan 31, 2011 at 08:39:40PM +, Martin J. Evans wrote: I imagine most DBDs [if not all] that implement execute_array [Just a reminder that drivers can opt to implement just execute_for_fetch() and use the DBI's default execute_array() method, which then calls execute_for_fetch().] themselves) the rows affected will be -1 per execute (as the driver does not know affected rows) and so I'd expect $rows to be -1 and not 18. DBI gets away with this as it does an execute for each row The thing is OCI will know the end result of rows effected I just does not know the #rows for each statement. So on an update with say 4 tuples and 6 rows updates the tupels would look like this -1 -1 -1 -1 wile the list context one would get Tuples=4 rows =6 My only concern is when it does error (no matter what the setting of AutoCommit) you always get unef; but I can live with that. Cheers John and hence knows the rows affected as the return from each execute. The statement saying sum of affected rows should not sum a load of -1s affected to be -N. Yes. The spec ought to say that if any tuple gets a -1 then -1 gets returned as the 'sum', even if other tuples return other values. Tim.
Re: more on execute_array not complying with the specification
On 01/02/2011 10:44 AM, Tim Bunce wrote: On Sun, Jan 30, 2011 at 05:46:49PM +, Martin J. Evans wrote: push @$tuple_status, [ $sth->err, $sth->errstr, $sth->state ]; so I guess the pod should say: "If the execution of a tuple causes an error, then the corresponding status array element will be set to a reference to an array containing the err, errstr and state set by the failed execution. If that is the case let me know and I'll amend the pod. Otherwise, I'll need to know what was intended. Yes, the state should be included and mentioned in the docs. Thanks. # NOTE: I'd like to do the following test but it requires Multiple # Active Statements and although I can find ODBC drivers which do this # it is not easy (if at all possible) to know if an ODBC driver can # handle MAS or not. Perhaps try to run it and if it gets an error, and the error indicates lack of MAS, then skip the test. Tim. p.s. Sorry for the delay. Ok I will have to add that into DBD::Oracle, What exactly should go into the "State" I am sort of unclear on what should be there?
Re: more on execute_array not complying with the specification
One other thing I was picking up on is the case if you have autocommit on and RaiseError off I this case it if there was a an error while running in list mode it will allways return undef. Where looking at the spec one might expect to see undef and XX which would be the # ot records effected? Am I reading that wrong?? ie with 20 items in your tuples ($tuples, $rows) = $sth->execute_array( and there is are two errors one might expect $tuples=undef and $rows=18? I think right now you are just getting $tuples=undef and nothing is being sent back for $rows Cheers John On Sun, Jan 30, 2011 at 12:46 PM, Martin J. Evans wrote: > > Hi, > > I'm still working on and off with execute_array problems hoping to get DBI > and the spec to match and also investigating if the current implementation > covers all possibilities. I've now written a simple test to see if it > complies with the specification (and will include it in DBD::ODBC) and I > don't think it does. The main point is: > > the pod says: > > "The ArrayTupleStatus attribute can be used to specify a reference to an > array which will receive the execute status of each executed parameter tuple. > Note the ArrayTupleStatus attribute was mandatory until DBI 1.38. > > For tuples which are successfully executed, the element at the same ordinal > position in the status array is the resulting rowcount. If the execution of a > tuple causes an error, then the corresponding status array element will be > set to a reference to an array containing the error code and error string set > by the failed execution." > > but the test shows the ArrayTupleStatus contains 3 elements on an error and > not "an array containing the error code and error string set by the failed > execution". > > e.g.,: > > # $VAR1 = [ > # 1, > # 1, > # 1, > # [ > # 1, > # '[unixODBC][Easysoft][SQL Server Driver 10.0][SQL > Server]Violation > of PRIMARY KEY constraint \'PK__PERL_DBD__3BD0198E526429B0\'. Cannot insert > dup > licate key in object \'dbo.PERL_DBD_execute_array\'. (SQL-23000) [state was > 2300 > 0 now 01000] > # [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]The statement has > been > terminated. (SQL-01000)', > # '01000' > # ], > # 1 > # ]; > > It is down to the following line of code: > > push @$tuple_status, [ $sth->err, $sth->errstr, $sth->state ]; > > > so I guess the pod should say: > > "If the execution of a tuple causes an error, then the corresponding status > array element will be set to a reference to an array containing the err, > errstr and state set by the failed execution. If that is the case let me know > and I'll amend the pod. Otherwise, I'll need to know what was intended. > > Attached is my current test code but I'm still working on it. In particular, > I've not found an ODBC driver which aborts on the first insert failure yet. > > Martin -- The best compliment you could give Pythian for our service is a referral.
Re: Clarification sought on execute_array
This behaviour is also inconsistent with versions of Oracle 11. Some patch sets show it other do not.I will have an answer for if later today Did you try it with the full 11 client. Cheers John On Fri, Jan 21, 2011 at 6:02 AM, Martin J. Evans wrote: > On 20/01/11 09:55, Tim Bunce wrote: > > > I wonder if we could run an older DBD::Oracle against an Oracle 9 (say) > > database to recreate the (presumably) original behaviour. > > I kept my Oracle 11.1.0.6.0 database and tried various combinations of > DBD::Oracle (back to 1.19) and instant clients - non work as documented. > > I then tried instant client 10.2.0.5.0 to an oracle database 10.2.0.5.0 > (with DBD::Oracle 1.19) and it works: > > DB Version: 10.2.0.1.0 > ** do_it ** > AutoCommit = 1 > execute_array = undef > total affected rows = undef > Error from execute_array - errstr=ORA-24381: error(s) in array DML (DBD > SUCCESS_WITH_INFO: OCIStmtExecute), err=0, state='' > $tuple_status = [ > -1, > [ > 1, >'ORA-1: unique constraint (SYSTEM.SYS_C005544) > violated (DBD SUCCESS_WITH_INFO)' > ], > -1, > -1 >]; > > Error captured in handler: undef > Warning captured in SIGWARN handler: undef > $select * from mytest = [ > [ >'1', >'onetwothree ' > ], > [ >'51', >'fiftyone' > ], > [ >'52', >'fiftythree ' > ], > [ >'53', >'one ' > ] >]; > > Notice, the successful rows are committed and the unsuccessful row is not. > > After trying various other combinations it seems any version of instant > client or DBD::Oracle works so long as Oracle database is less than 11. I > cannot say as yet if it is a bug in Oracle or some change DBD::Oracle didn't > follow. > > So it would appear DBD::Oracle/Oracle has always issued a warning and not > an error when a tuple fails. I think this is really dangerous if someone is > relying on RaiseError so I've changed the DBD::Oracle we use internally to > raise an error. > > Martin > -- > Martin J. Evans > Easysoft Limited > http://www.easysoft.com > -- The best compliment you could give Pythian for our service is a referral.
Re: Clarification sought on execute_array
On 20/01/2011 12:20 PM, Martin J. Evans wrote: On 20/01/11 12:31, John Scoles wrote: On 19/01/2011 5:25 PM, Martin J. Evans wrote: Great stuff Martin I am sure we can clear this up. One thing I did notice was you never set the 'ora_array_chunk_size' which is defaulting to 1000 I wonder if running this with this value set to say 2 or 3 will see a change in behaviour. No doubt there is some thing awry in there That we will have to get to the bottom of. Cheers John Just to jog memories. John, you document a bug in 11g and execute_array in your blog: http://www.pythian.com/news/1125/bug-in-dbdoracles-execute_array-with-11g/ "If you tell DBD::Oracle to autocommit, it seems that in 11g this commit will not take place when an error occurs during the processing of one of the tuples that you passed into execute_array" "The workaround is to set AutoCommit => 0 and then use $dbh-->commit() after the statement." which does not work for me by the way. and a link to the forum you sought advice on is here http://forums.oracle.com/forums/thread.jspa?messageID=2663832#2663832 There is no answer. Martin If memory served me correctly the root of the above bug has something to do with the comparing of two integers. One was coming back something like 6. while it was looking for 7 when working with 11g on 64bit hardware and 32bit perl. I will check tomorrow to see if I can recreate it again. Who knows it might illustrate a change in expected behaviour between the differing ORacle platforms and clients? I remember I have a fix for it someplace at home which was on my list to look at tomorrow Cheers John On 19/01/2011 11:47, Tim Bunce wrote: I'm sorry to arrive here late. Seems I've been missing all the fun! Thought you were in Paris so I didn't expect you to see this. It has not been a lot of fun ;-) Some observations: - I think it's reasonable for execute_array() and execute_for_fetch() to return an error (ie err() true and so trigger RaiseError etc.) if execution of any of the tuples encountered an error. The way I see it is that if some of the rows in the batch end up in the table and some don't I'd expect a warning. However, if 1 or more rows fail and no rows end up in the table I don't see any success so I'd expect an error. In my example code, 1 row fails but no rows end up successful so it is the latter. It may be a change/bug in the Oracle libraries as I thought when you execute a batch all the rows are executed no matter if some fail and all successful ones end up in the table. - That'll need a spec clarification and a clear warning in Changes. - We should avoid breaking existing batch apps that use DBD::Oracle. Agreed, but we've not got any working examples of anyone using execute_array other than the one which started this off (and mine) which was DBIx::Class and that was broken by DBD::Oracle/Oracle's current behaviour. The big problem with the DBIx::Class code was checking $sth->err which was 0 in this case because 0 indicates a warning. However, DBD::Oracle contradicted itself since execute_array returned undef (error) but then set a warning on err. - I'm hopeful that the above change wouldn't. (John?) So am I. If we could get the examples John has seen via his DBD::Oracle maintainership or via Pythian customers I would be happy to create test cases. We are running blind at the moment as we've not got those solid examples of supposedly working code. - We should review other database APIs that provide batch execution in order to spec a reasonable common subset behaviour for the DBI. Obviously ODBC and JDBC do batched statements. I might provide a JDBC example but for now I've done an ODBC example (slightly more familiar to me) - see below. - Clearly we should bring DBD::Oracle, the DBI default behaviour, and the DBI spec into agreement with each other. Exactly. This is really my main point. As it stands (and given it is not a bug in Oracle) I see a nightmare for anyone trying to use execute_array in a database neutral way as with DBI, all successful rows are inserted and we know which ones failed and with DBD::Oracle no rows are inserted (including the ones where there is no error) and you cannot commit the good ones and it is difficult to know (if not impossible) what really happened. This is not a dig at anyone in particular as I added the array context execute_array to DBI/DBD::Oracle but this is also why I suspect something has changed in DBD::Oracle/Oracle. - We *really* need a way to share tests across drivers. Perhaps something like a separate DBI::TestSuite distro that the DBI and drivers could have as a prerequisite. That would contain tests in modules. The DBI and DBDs would have a test file that uses the DBI::TestSuite module and calls a fu
Re: $SIG{INT} tampering during DBI->connect on Oracle
On 20/01/2011 9:16 AM, Brian Phillips wrote: On Dec 1 2010, 9:59 am, sco...@pythian.com (John Scoles) wrote: It will be in 1.28 as 1.27 is reserved for now. Look for it in the next few weeks Any idea when 1.28 will be released? I want to have it out by the end of the Month. Remind me which fix was this for. Is there an RT for it? cheers John
Re: Clarification sought on execute_array
On 19/01/2011 5:25 PM, Martin J. Evans wrote: Great stuff Martin I am sure we can clear this up. One thing I did notice was you never set the 'ora_array_chunk_size' which is defaulting to 1000 I wonder if running this with this value set to say 2 or 3 will see a change in behaviour. No doubt there is some thing awry in there That we will have to get to the bottom of. Cheers John On 19/01/2011 11:47, Tim Bunce wrote: I'm sorry to arrive here late. Seems I've been missing all the fun! Thought you were in Paris so I didn't expect you to see this. It has not been a lot of fun ;-) Some observations: - I think it's reasonable for execute_array() and execute_for_fetch() to return an error (ie err() true and so trigger RaiseError etc.) if execution of any of the tuples encountered an error. The way I see it is that if some of the rows in the batch end up in the table and some don't I'd expect a warning. However, if 1 or more rows fail and no rows end up in the table I don't see any success so I'd expect an error. In my example code, 1 row fails but no rows end up successful so it is the latter. It may be a change/bug in the Oracle libraries as I thought when you execute a batch all the rows are executed no matter if some fail and all successful ones end up in the table. - That'll need a spec clarification and a clear warning in Changes. - We should avoid breaking existing batch apps that use DBD::Oracle. Agreed, but we've not got any working examples of anyone using execute_array other than the one which started this off (and mine) which was DBIx::Class and that was broken by DBD::Oracle/Oracle's current behaviour. The big problem with the DBIx::Class code was checking $sth->err which was 0 in this case because 0 indicates a warning. However, DBD::Oracle contradicted itself since execute_array returned undef (error) but then set a warning on err. - I'm hopeful that the above change wouldn't. (John?) So am I. If we could get the examples John has seen via his DBD::Oracle maintainership or via Pythian customers I would be happy to create test cases. We are running blind at the moment as we've not got those solid examples of supposedly working code. - We should review other database APIs that provide batch execution in order to spec a reasonable common subset behaviour for the DBI. Obviously ODBC and JDBC do batched statements. I might provide a JDBC example but for now I've done an ODBC example (slightly more familiar to me) - see below. - Clearly we should bring DBD::Oracle, the DBI default behaviour, and the DBI spec into agreement with each other. Exactly. This is really my main point. As it stands (and given it is not a bug in Oracle) I see a nightmare for anyone trying to use execute_array in a database neutral way as with DBI, all successful rows are inserted and we know which ones failed and with DBD::Oracle no rows are inserted (including the ones where there is no error) and you cannot commit the good ones and it is difficult to know (if not impossible) what really happened. This is not a dig at anyone in particular as I added the array context execute_array to DBI/DBD::Oracle but this is also why I suspect something has changed in DBD::Oracle/Oracle. - We *really* need a way to share tests across drivers. Perhaps something like a separate DBI::TestSuite distro that the DBI and drivers could have as a prerequisite. That would contain tests in modules. The DBI and DBDs would have a test file that uses the DBI::TestSuite module and calls a function that runs the tests. This issue could provide the first test. Tim. I agree and I seem to remember a project to do something like this - was it perhaps a google summer of code suggestion? But it is pretty difficult and I think that puts a lot of people off. I briefly looked at Test::Database so I could get more realistic test results for DBD::ODBC but I ran in to a load of problems as Test::Database needs some DBD methods writing and expects to be able to create a database and in ODBC (via dozens of ODBC drivers) there is not single way to do this. The gain was just not worth the pain for me. I'd be happy to help someone do this but only in a minor way as right now I cannot find the time to satisfy even half of my OS commitments (as an example, I REALLY want to be able to set handle attributes on methods in DBI [post from a week back] but I just cannot find time to do it - something else is always cropping up). Attached is a very rough and ready bit of C code (with little error checking) that does batch inserts. You run it with something like: ./a.out 'DSN=mydsn;UID=username;PWD=password' and it does batch inserts into a table called xtest that is defined as: create table xtest(ky integer primary key, txt varchar(20)) It has 4 tests: 1. insert a batch successfully reading the parameter status array (ArrayTupleStatus) to s
Re: Clarification sought on execute_array
On 19/01/2011 6:47 AM, Tim Bunce wrote: As usual very sage advice Tim. I'm sorry to arrive here late. Seems I've been missing all the fun! Some observations: - I think it's reasonable for execute_array() and execute_for_fetch() to return an error (ie err() true and so trigger RaiseError etc.) if execution of any of the tuples encountered an error. It looking more and more that this should of been the case in DBD::Oracle still have some reading to do on it though. - That'll need a spec clarification and a clear warning in Changes. - We should avoid breaking existing batch apps that use DBD::Oracle. - I'm hopeful that the above change wouldn't. (John?) - We should review other database APIs that provide batch execution in order to spec a reasonable common subset behaviour for the DBI. I have yet to find one but I haven't look very hard - Clearly we should bring DBD::Oracle, the DBI default behaviour, and the DBI spec into agreement with each other. The question being is it the default behaviour from 1999 Oracle 8 or today's 11g ? ;) - We *really* need a way to share tests across drivers. Perhaps something like a separate DBI::TestSuite distro that the DBI and drivers could have as a prerequisite. That would contain tests in modules. The DBI and DBDs would have a test file that uses the DBI::TestSuite module and calls a function that runs the tests. This issue could provide the first test. Tim.
Re: Clarification sought on execute_array
On 18/01/2011 9:47 AM, Martin J. Evans wrote:
On 18/01/11 14:11, John Scoles wrote:
On 18/01/2011 8:35 AM, Martin J. Evans wrote:
John,
I slightly reformatted you reply as you added comments on the end of lines I
wrote which made it look like I said them.
On 18/01/11 12:40, John Scoles wrote:
On 17/01/2011 3:34 PM, Martin J. Evans wrote:
There appear to be differences between DBDs which do not handle
execute_array (so DBI does it for them) and DBDs which do handle
execute_array (e.g., DBD::Oracle). The main ones discussed on
#dbix-class which I investigated are whether the driver sets the
err and errstr or even raises an error. Some of the guys writing
DBIx::Class think execute_array should raise and error and fail on
the first error but I explained since execute_array may send the
entire batch to the server and it is server dependent when it stops
this is beyond definition by DBI. Never the less the following
script seems to show some large differences between DBI's
execute_array and DBD::Oracle's:
The first question is do any other DBIs utilize a native
array_execute??
Anyway
Not that I know of but if DBD::Oracle does not match what happens with a DBI
execute_array then that is a problem for anyone writing DBD neutral code and it
should be clearly documented so you can write DBD neutral code.
Well lets go back to DBI and see what it says
When called in scalar context the execute_array() method returns the
number of tuples executed, or |undef| if an error occurred.
Like
execute(), a successful execute_array() always returns true
regardless of the number of tuples executed, even if it's zero.
I think you have misread this bit. It means (like execute) it is not an error
to do nothing or something like;
update mytable set mycol = 1 where mycol = 2
where no mycol = 2 i.e., it will return success even though no change occurred.
I don't think it means execute_array always returns success no matter what
happens just because it is a batch.
If
there were any errors the ArrayTupleStatus array can be used to
discover which tuples failed and with what errors.
In DBD::Oracle you will never get 'undef' returned as the execute
will always be successful even though all of your tuples may fail.
and yet, you do get an undef back in my example so you we already have a
contradiction.
See:
Error from execute_array - ORA-24381: error(s) in array DML (DBD
SUCCESS_WITH_INFO: OCIStmtExecute),0
which is output because execute_array returned undef!
my (@tuple_status, $inserted);
$inserted = 99;
eval {
$inserted = $sth->execute_array(
{ ArrayTupleStatus => \@tuple_status } );
};
if ($@) {
print "Exception: $@\n";
}
print "inserted = ", DBI::neat($inserted), "\n";
print "Error from execute_array - " . $sth->errstr . ",", $sth->err ."\n"
if (!$inserted);
outputs (for Oracle):
The following is due to PrintWarn => 1
DBD::Oracle::st execute_array warning: ORA-24381: error(s) in array DML (DBD
SUCCESS_WITH_INFO: OCIStmtExecute) [for Statement "insert into mytest values
(?,?)"] at rt_data/execute_array/execute_array.pl line 44.
The following is undef from execute_array:
inserted = undef
The following is because execute_array returned undef:
Error from execute_array - ORA-24381: error(s) in array DML (DBD
SUCCESS_WITH_INFO: OCIStmtExecute),0
Note the errstr value is set but not err (0) - that cannot be right surely.
So It agrees with the first para and works in scalar.
Funnily enough, it does agree with the first paragraph since an error occurred and it
returned undef (unlike you reasoning) BUT it only set the error state to a warning and
did not set "err".
I have no issue it is a batch and executed in the server as one operation but
DBD::Oracle does know something failed as it stands.
To get the extra info that comes out in a non-DBD specific
array_execute we would have to build in an extra iteration over the
results to give a count of the Failed/Pass. As some of my customers
use this with batch loads of 5meg plus of inserts the iteration may
take some time and sort of defeat the purpose of a quick way to do
bulk inserts.
but John, DBD::Oracle already knows an error occurred.
I think (you will have to ask Tim to verify) that the Idea behind
array_execute is a 'Batch' processor. ie send a Batch, to the server
then figure out what to with what is returned.
so with
a) even though RaiseError was set, no error was raised although a
warning was.
JS replied:
We know there was a problem so we have to fail the batch or at lease report
on it is what the warning is telling us
I'm in danger of repeating myself - an error did occur, DBD::Oracle knows this
but it was not raised as an error.
b) execute_array returned undef (correct)
JS rep
Re: Clarification sought on execute_array
On 18/01/2011 9:47 AM, Martin J. Evans wrote:
Just as a Side note seems execute_array was added well after the first
DBI spec was written.
DBI 1.24,4th June 2002 seems to be the correct date rather a late
addition.
Cheers
John
On 18/01/11 14:11, John Scoles wrote:
On 18/01/2011 8:35 AM, Martin J. Evans wrote:
John,
I slightly reformatted you reply as you added comments on the end of lines I
wrote which made it look like I said them.
On 18/01/11 12:40, John Scoles wrote:
On 17/01/2011 3:34 PM, Martin J. Evans wrote:
There appear to be differences between DBDs which do not handle
execute_array (so DBI does it for them) and DBDs which do handle
execute_array (e.g., DBD::Oracle). The main ones discussed on
#dbix-class which I investigated are whether the driver sets the
err and errstr or even raises an error. Some of the guys writing
DBIx::Class think execute_array should raise and error and fail on
the first error but I explained since execute_array may send the
entire batch to the server and it is server dependent when it stops
this is beyond definition by DBI. Never the less the following
script seems to show some large differences between DBI's
execute_array and DBD::Oracle's:
The first question is do any other DBIs utilize a native
array_execute??
Anyway
Not that I know of but if DBD::Oracle does not match what happens with a DBI
execute_array then that is a problem for anyone writing DBD neutral code and it
should be clearly documented so you can write DBD neutral code.
Well lets go back to DBI and see what it says
When called in scalar context the execute_array() method returns the
number of tuples executed, or |undef| if an error occurred.
Like
execute(), a successful execute_array() always returns true
regardless of the number of tuples executed, even if it's zero.
I think you have misread this bit. It means (like execute) it is not an error
to do nothing or something like;
update mytable set mycol = 1 where mycol = 2
where no mycol = 2 i.e., it will return success even though no change occurred.
I don't think it means execute_array always returns success no matter what
happens just because it is a batch.
If
there were any errors the ArrayTupleStatus array can be used to
discover which tuples failed and with what errors.
In DBD::Oracle you will never get 'undef' returned as the execute
will always be successful even though all of your tuples may fail.
and yet, you do get an undef back in my example so you we already have a
contradiction.
See:
Error from execute_array - ORA-24381: error(s) in array DML (DBD
SUCCESS_WITH_INFO: OCIStmtExecute),0
which is output because execute_array returned undef!
my (@tuple_status, $inserted);
$inserted = 99;
eval {
$inserted = $sth->execute_array(
{ ArrayTupleStatus => \@tuple_status } );
};
if ($@) {
print "Exception: $@\n";
}
print "inserted = ", DBI::neat($inserted), "\n";
print "Error from execute_array - " . $sth->errstr . ",", $sth->err ."\n"
if (!$inserted);
outputs (for Oracle):
The following is due to PrintWarn => 1
DBD::Oracle::st execute_array warning: ORA-24381: error(s) in array DML (DBD
SUCCESS_WITH_INFO: OCIStmtExecute) [for Statement "insert into mytest values
(?,?)"] at rt_data/execute_array/execute_array.pl line 44.
The following is undef from execute_array:
inserted = undef
The following is because execute_array returned undef:
Error from execute_array - ORA-24381: error(s) in array DML (DBD
SUCCESS_WITH_INFO: OCIStmtExecute),0
Note the errstr value is set but not err (0) - that cannot be right surely.
So It agrees with the first para and works in scalar.
Funnily enough, it does agree with the first paragraph since an error occurred and it
returned undef (unlike you reasoning) BUT it only set the error state to a warning and
did not set "err".
I have no issue it is a batch and executed in the server as one operation but
DBD::Oracle does know something failed as it stands.
To get the extra info that comes out in a non-DBD specific
array_execute we would have to build in an extra iteration over the
results to give a count of the Failed/Pass. As some of my customers
use this with batch loads of 5meg plus of inserts the iteration may
take some time and sort of defeat the purpose of a quick way to do
bulk inserts.
but John, DBD::Oracle already knows an error occurred.
I think (you will have to ask Tim to verify) that the Idea behind
array_execute is a 'Batch' processor. ie send a Batch, to the server
then figure out what to with what is returned.
so with
a) even though RaiseError was set, no error was raised although a
warning was.
JS replied:
We know there was a problem so we have to fail the batch or at lease report
on it is what the w
Re: Clarification sought on execute_array
On 18/01/2011 9:47 AM, Martin J. Evans wrote:
On 18/01/11 14:11, John Scoles wrote:
On 18/01/2011 8:35 AM, Martin J. Evans wrote:
John,
I slightly reformatted you reply as you added comments on the end of lines I
wrote which made it look like I said them.
On 18/01/11 12:40, John Scoles wrote:
On 17/01/2011 3:34 PM, Martin J. Evans wrote:
There appear to be differences between DBDs which do not handle
execute_array (so DBI does it for them) and DBDs which do handle
execute_array (e.g., DBD::Oracle). The main ones discussed on
#dbix-class which I investigated are whether the driver sets the
err and errstr or even raises an error. Some of the guys writing
DBIx::Class think execute_array should raise and error and fail on
the first error but I explained since execute_array may send the
entire batch to the server and it is server dependent when it stops
this is beyond definition by DBI. Never the less the following
script seems to show some large differences between DBI's
execute_array and DBD::Oracle's:
The first question is do any other DBIs utilize a native
array_execute??
Anyway
Not that I know of but if DBD::Oracle does not match what happens with a DBI
execute_array then that is a problem for anyone writing DBD neutral code and it
should be clearly documented so you can write DBD neutral code.
Well lets go back to DBI and see what it says
When called in scalar context the execute_array() method returns the
number of tuples executed, or |undef| if an error occurred.
Like
execute(), a successful execute_array() always returns true
regardless of the number of tuples executed, even if it's zero.
I think you have misread this bit. It means (like execute) it is not an error
to do nothing or something like;
update mytable set mycol = 1 where mycol = 2
where no mycol = 2 i.e., it will return success even though no change occurred.
I don't think it means execute_array always returns success no matter what
happens just because it is a batch.
If
there were any errors the ArrayTupleStatus array can be used to
discover which tuples failed and with what errors.
In DBD::Oracle you will never get 'undef' returned as the execute
will always be successful even though all of your tuples may fail.
and yet, you do get an undef back in my example so you we already have a
contradiction.
See:
Error from execute_array - ORA-24381: error(s) in array DML (DBD
SUCCESS_WITH_INFO: OCIStmtExecute),0
which is output because execute_array returned undef!
my (@tuple_status, $inserted);
$inserted = 99;
eval {
$inserted = $sth->execute_array(
{ ArrayTupleStatus => \@tuple_status } );
};
if ($@) {
print "Exception: $@\n";
}
print "inserted = ", DBI::neat($inserted), "\n";
print "Error from execute_array - " . $sth->errstr . ",", $sth->err ."\n"
if (!$inserted);
outputs (for Oracle):
The following is due to PrintWarn => 1
DBD::Oracle::st execute_array warning: ORA-24381: error(s) in array DML (DBD
SUCCESS_WITH_INFO: OCIStmtExecute) [for Statement "insert into mytest values
(?,?)"] at rt_data/execute_array/execute_array.pl line 44.
The following is undef from execute_array:
inserted = undef
The following is because execute_array returned undef:
Error from execute_array - ORA-24381: error(s) in array DML (DBD
SUCCESS_WITH_INFO: OCIStmtExecute),0
Note the errstr value is set but not err (0) - that cannot be right surely.
So It agrees with the first para and works in scalar.
Funnily enough, it does agree with the first paragraph since an error occurred and it
returned undef (unlike you reasoning) BUT it only set the error state to a warning and
did not set "err".
I have no issue it is a batch and executed in the server as one operation but
DBD::Oracle does know something failed as it stands.
To get the extra info that comes out in a non-DBD specific
array_execute we would have to build in an extra iteration over the
results to give a count of the Failed/Pass. As some of my customers
use this with batch loads of 5meg plus of inserts the iteration may
take some time and sort of defeat the purpose of a quick way to do
bulk inserts.
but John, DBD::Oracle already knows an error occurred.
I think (you will have to ask Tim to verify) that the Idea behind
array_execute is a 'Batch' processor. ie send a Batch, to the server
then figure out what to with what is returned.
so with
a) even though RaiseError was set, no error was raised although a
warning was.
JS replied:
We know there was a problem so we have to fail the batch or at lease report
on it is what the warning is telling us
I'm in danger of repeating myself - an error did occur, DBD::Oracle knows this
but it was not raised as an error.
b) execute_array returned undef (correct)
JS rep
Re: Clarification sought on execute_array
On 18/01/2011 8:35 AM, Martin J. Evans wrote:
John,
I slightly reformatted you reply as you added comments on the end of lines I
wrote which made it look like I said them.
On 18/01/11 12:40, John Scoles wrote:
On 17/01/2011 3:34 PM, Martin J. Evans wrote:
There appear to be differences between DBDs which do not handle
execute_array (so DBI does it for them) and DBDs which do handle
execute_array (e.g., DBD::Oracle). The main ones discussed on
#dbix-class which I investigated are whether the driver sets the
err and errstr or even raises an error. Some of the guys writing
DBIx::Class think execute_array should raise and error and fail on
the first error but I explained since execute_array may send the
entire batch to the server and it is server dependent when it stops
this is beyond definition by DBI. Never the less the following
script seems to show some large differences between DBI's
execute_array and DBD::Oracle's:
The first question is do any other DBIs utilize a native
array_execute??
Anyway
Not that I know of but if DBD::Oracle does not match what happens with a DBI
execute_array then that is a problem for anyone writing DBD neutral code and it
should be clearly documented so you can write DBD neutral code.
Well lets go back to DBI and see what it says
When called in scalar context the execute_array() method returns the
number of tuples executed, or |undef| if an error occurred.
Like
execute(), a successful execute_array() always returns true
regardless of the number of tuples executed, even if it's zero.
I think you have misread this bit. It means (like execute) it is not an error
to do nothing or something like;
update mytable set mycol = 1 where mycol = 2
where no mycol = 2 i.e., it will return success even though no change occurred.
I don't think it means execute_array always returns success no matter what
happens just because it is a batch.
If
there were any errors the ArrayTupleStatus array can be used to
discover which tuples failed and with what errors.
In DBD::Oracle you will never get 'undef' returned as the execute
will always be successful even though all of your tuples may fail.
and yet, you do get an undef back in my example so you we already have a
contradiction.
See:
Error from execute_array - ORA-24381: error(s) in array DML (DBD
SUCCESS_WITH_INFO: OCIStmtExecute),0
which is output because execute_array returned undef!
my (@tuple_status, $inserted);
$inserted = 99;
eval {
$inserted = $sth->execute_array(
{ ArrayTupleStatus => \@tuple_status } );
};
if ($@) {
print "Exception: $@\n";
}
print "inserted = ", DBI::neat($inserted), "\n";
print "Error from execute_array - " . $sth->errstr . ",", $sth->err ."\n"
if (!$inserted);
outputs (for Oracle):
The following is due to PrintWarn => 1
DBD::Oracle::st execute_array warning: ORA-24381: error(s) in array DML (DBD
SUCCESS_WITH_INFO: OCIStmtExecute) [for Statement "insert into mytest values
(?,?)"] at rt_data/execute_array/execute_array.pl line 44.
The following is undef from execute_array:
inserted = undef
The following is because execute_array returned undef:
Error from execute_array - ORA-24381: error(s) in array DML (DBD
SUCCESS_WITH_INFO: OCIStmtExecute),0
Note the errstr value is set but not err (0) - that cannot be right surely.
So It agrees with the first para and works in scalar.
Funnily enough, it does agree with the first paragraph since an error occurred and it
returned undef (unlike you reasoning) BUT it only set the error state to a warning and
did not set "err".
I have no issue it is a batch and executed in the server as one operation but
DBD::Oracle does know something failed as it stands.
To get the extra info that comes out in a non-DBD specific
array_execute we would have to build in an extra iteration over the
results to give a count of the Failed/Pass. As some of my customers
use this with batch loads of 5meg plus of inserts the iteration may
take some time and sort of defeat the purpose of a quick way to do
bulk inserts.
but John, DBD::Oracle already knows an error occurred.
I think (you will have to ask Tim to verify) that the Idea behind
array_execute is a 'Batch' processor. ie send a Batch, to the server
then figure out what to with what is returned.
so with
a) even though RaiseError was set, no error was raised although a
warning was.
JS replied:
We know there was a problem so we have to fail the batch or at lease report
on it is what the warning is telling us
I'm in danger of repeating myself - an error did occur, DBD::Oracle knows this
but it was not raised as an error.
b) execute_array returned undef (correct)
JS replied:
Well at least that is a good thing
You said "In DBD::Oracle you will never get 'undef&#x
Re: Clarification sought on execute_array
On 18/01/2011 7:52 AM, H.Merijn Brand wrote:
On Tue, 18 Jan 2011 07:40:25 -0500, John Scoles
wrote:
On 17/01/2011 3:34 PM, Martin J. Evans wrote:
There appear to be differences between DBDs which do not handle
execute_array (so DBI does it for them) and DBDs which do handle
execute_array (e.g., DBD::Oracle). The main ones discussed on
#dbix-class which I investigated are whether the driver sets the err
and errstr or even raises an error. Some of the guys writing
DBIx::Class think execute_array should raise and error and fail on the
first error but I explained since execute_array may send the entire
batch to the server and it is server dependent when it stops this is
beyond definition by DBI. Never the less the following script seems to
show some large differences between DBI's execute_array and
DBD::Oracle's:
The first question is do any other DBIs utilize a native array_execute??
DBD::Unify and DBD::CSV do not
Looking more and more that only DBD::Oracle has a native exe_array
Anyway
Well lets go back to DBI and see what it says
When called in scalar context the execute_array() method returns
the number of tuples executed, or |undef| if an error occurred. Like
execute(), a successful execute_array() always returns true regardless
of the number of tuples executed, even if it's zero. If there were any
errors the ArrayTupleStatus array can be used to discover which tuples
failed and with what errors.
In DBD::Oracle you will never get 'undef' returned as the execute will
always be successful even though all of your tuples may fail.
So It agrees with the first para and works in scalar.
To get the extra info that comes out in a non-DBD specific array_execute
we would have to build in an extra iteration over the results to give a
count of the Failed/Pass. As some of my customers use this with batch
loads of 5meg plus of inserts the iteration may take some time and sort
of defeat the purpose of a quick way to do bulk inserts.
I think (you will have to ask Tim to verify) that the Idea behind
array_execute is a 'Batch' processor. ie send a Batch, to the server
then figure out what to with what is returned.
so with
a) even though RaiseError was set, no error was raised although a
warning was.
We know there was a problem so we have to fail the batch or at
least report on it is what the warning is telling us
b) execute_array returned undef (correct)
Well at least that is a good thing
c) errstr is set but err is not (0)
d) the HandleError routine was not called - due to (a)?
Which is correct as is did do exactly what was expected. ie
'execute a bath and report back'
The batch will take long enough to have a comfortable bath? :)
Dislexia stick again?
You see we do not believe in Dog!
As a side point one client who uses the exe_array takes 2h and 45m to
run so just enough time for a good bath :). To put it in perspective it
use to take 19~25 hours (DBD::Oracle 1.17) and she now has almost twice
the number of records than at the 29 hour mark.
cheers
e) the count of rows affected is -1 for all rows which worked - I
believe this is permissible
I will have to check on that.
In the end I do not think this should ever error
eval {
$inserted = $sth->execute_array(
{ ArrayTupleStatus => \@tuple_status } );
};
It is the wrong way to process a batch job. JMHO though
I guess the real sort of problem is that in the normal DBI array fetch
it is just iterating over array and doing the insert one at a time so
you get your good and error counts as you go. As well as each iteration
is a separate execute you will get a raise_error with it which is think
is suppressed but I would have to look at the code.
I think you are right that the the chaps at DBIx have it wrong. It
should be a batch job and they would have to handle in that way.
1) bind
2) exe
3) commit if all successful or process if an error is returned.
Anyway lets see what Tim has to say.
We could add in the list context for DBD::Oracle and do some of this
processing with the caveat that it will take longer than the scalar context
Cheers
John
use DBI;
use strict;
use Data::Dumper;
sub fred
{
print "Error Handler called\n";
print Dumper(\@_);
my ($msg, $handle, $val) = @_;
print "handle_error: $msg\nhandle: $handle\nval=$val\n";
0;
}
my $dbh = DBI->connect(
'DBI:Oracle:host=xxx;sid=devel', 'xxx', 'xxx',
{ RaiseError => 1, PrintError => 0, HandleError => \&fred
});
do_it($dbh);
my $dbh = DBI->connect(
'DBI:ODBC:DSN=xxx', 'xxx', 'xxx',
{ RaiseError => 1, PrintError => 0, HandleError => \&fred
});
do_it($dbh);
sub do_it {
my $dbh = shift;
eval {$dbh->do(q/drop table mytest/);}
Re: Clarification sought on execute_array
On 17/01/2011 3:34 PM, Martin J. Evans wrote:
There appear to be differences between DBDs which do not handle
execute_array (so DBI does it for them) and DBDs which do handle
execute_array (e.g., DBD::Oracle). The main ones discussed on
#dbix-class which I investigated are whether the driver sets the err
and errstr or even raises an error. Some of the guys writing
DBIx::Class think execute_array should raise and error and fail on the
first error but I explained since execute_array may send the entire
batch to the server and it is server dependent when it stops this is
beyond definition by DBI. Never the less the following script seems to
show some large differences between DBI's execute_array and
DBD::Oracle's:
The first question is do any other DBIs utilize a native array_execute??
Anyway
Well lets go back to DBI and see what it says
When called in scalar context the execute_array() method returns
the number of tuples executed, or |undef| if an
error occurred. Like execute(), a successful execute_array() always
returns true regardless of the number of tuples
executed, even if it's zero. If there were any errors the
ArrayTupleStatus array can be used to discover which tuples
failed and with what errors.
In DBD::Oracle you will never get 'undef' returned as the execute will
always be successful even though all of your tuples may fail.
So It agrees with the first para and works in scalar.
To get the extra info that comes out in a non-DBD specific array_execute
we would have to build in an extra iteration over the results to give a
count of the Failed/Pass. As some of my customers use this with batch
loads of 5meg plus of inserts the iteration may take some time and sort
of defeat the purpose of a quick way to do bulk inserts.
I think (you will have to ask Tim to verify) that the Idea behind
array_execute is a 'Batch' processor. ie send a Batch, to the server
then figure out what to with what is returned.
so with
a) even though RaiseError was set, no error was raised although a
warning was.
We know there was a problem so we have to fail the batch or at lease
report on it is what the warning is telling us
b) execute_array returned undef (correct)
Well at least that is a good thing
c) errstr is set but err is not (0)
d) the HandleError routine was not called - due to (a)?
Which is correct as is did do exactly what was expected. ie 'execute a
bath and report back'
e) the count of rows affected is -1 for all rows which worked - I
believe this is permissible
I will have to check on that.
In the end I do not think this should ever error
eval {
$inserted = $sth->execute_array(
{ ArrayTupleStatus => \@tuple_status } );
};
It is the wrong way to process a batch job. JMHO though
I guess the real sort of problem is that in the normal DBI array fetch
it is just iterating over array and doing the insert one at a time so
you get your good and error counts as you go. As well as each iteration
is a separate execute you will get a raise_error with it which is think
is suppressed but I would have to look at the code.
I think you are right that the the chaps at DBIx have it wrong. It
should be a batch job and they would have to handle in that way.
1) bind
2) exe
3) commit if all successful or process if an error is returned.
Anyway lets see what Tim has to say.
We could add in the list context for DBD::Oracle and do some of this
processing with the caveat that it will take longer than the scalar context
Cheers
John
use DBI;
use strict;
use Data::Dumper;
sub fred
{
print "Error Handler called\n";
print Dumper(\@_);
my ($msg, $handle, $val) = @_;
print "handle_error: $msg\nhandle: $handle\nval=$val\n";
0;
}
my $dbh = DBI->connect(
'DBI:Oracle:host=xxx;sid=devel', 'xxx', 'xxx',
{ RaiseError => 1, PrintError => 0, HandleError => \&fred
});
do_it($dbh);
my $dbh = DBI->connect(
'DBI:ODBC:DSN=xxx', 'xxx', 'xxx',
{ RaiseError => 1, PrintError => 0, HandleError => \&fred
});
do_it($dbh);
sub do_it {
my $dbh = shift;
eval {$dbh->do(q/drop table mytest/);};
$dbh->do(q/create table mytest (a int primary key, b char(20))/);
my $sth = $dbh->prepare(q/insert into mytest values (?,?)/);
$sth->bind_param(1, 1);
$sth->bind_param(2, 'onetwothree');
$sth->execute;
$sth->bind_param_array(1, [51,1,52,53]);
$sth->bind_param_array(2, ['fiftyone', 'fiftytwo', 'fiftythree',
'one']);
my (@tuple_status, $inserted);
eval {
$inserted = $sth->execute_array(
{ ArrayTupleStatus => \@tuple_status } );
};
if ($@) {
print "Exception: $@\n";
}
print "Error from execute_array - " . $sth->errstr . ",",
$sth->err ."\n"
if (!$inserted);
for (@tuple_status) {
print Dumper($_), "\n";
}
}
which outputs for the DBD::Oracle part:
$ perl execute_array/execute_array.pl
DBD::Oracle::st execute_
Re: DBD::Oracle - credentials
On 14/01/2011 10:21 AM, Martin J. Evans wrote: On 14/01/11 15:01, H.Merijn Brand wrote: On Fri, 14 Jan 2011 14:56:46 +, "Martin J. Evans" wrote: On 14/01/11 14:30, H.Merijn Brand wrote: Maybe this is a feature request, but if I have ORACLE_USERID=john/sekrit DBI_USER=pablo DBI_PASS=neruda I *do* expect that DBD::Oracle uses DBI_USER and DBI_PASS *instead of* the ORACLE_USERID. Anyone can come up with a good reason why this os not happening at the moment? How do other DBD's set precedence if environment variable are allowed for login credentials? Just a minor point (not saying it stops the discussion) but I don't think ORACLE_USERID is something DBD::Oracle defines (other than in test code in t/*). So is your point to do with running tests in t/* or in general? I ran into this in the test suite indeed. Your findings below just confirm my expectation :) I posted it here because I saw no generic docs about this It is in the README. The supplied tests will connect to the database using the value of the ORACLE_USERID environment variable to supply the username/password. Don't know why it cannot fall back on DBI_USER/DBI_PASS. $ export ORACLE_USERID=wrong/wrong $ export DBI_USER=real_user $ export DBI_PASS=valid_password $ export DBI_DSN='dbi:Oracle:host=betoracle.easysoft.local;sid=devel' $ perl -le 'use DBI; my $h = DBI->connect();' works for me. i.e., ORACLE_USERID contains invalid username/password and DBI_USER/DBI_PASS contain valid username/password and it connects fine. Perhaps I misunderstood you Merijn. Martin Martin Why not. Seems like a good Idea and easy to implement I guess we are just carrying on an old tradition nothing says we have to use ORACLE_USERID which is a little out dated. Time to update the POD and a tests I guess. Might be best for 1.29 as 1.28 is rather full right now. Cheers John
Re: DBD::Oracle - credentials
On 14/01/2011 9:30 AM, H.Merijn Brand wrote: Maybe this is a feature request, but if I have ORACLE_USERID=john/sekrit DBI_USER=pablo DBI_PASS=neruda I *do* expect that DBD::Oracle uses DBI_USER and DBI_PASS *instead of* the ORACLE_USERID. Anyone can come up with a good reason why this os not happening at the moment? How do other DBD's set precedence if environment variable are allowed for login credentials? Funny the question never came up before. I guess we can have a look at it. I do not know about the other drivers?
Re: DBD-Oracle stored proc with array bug
Ok I got it to work 100% with the test seems you gave a little too much SP
You will find it in trunk of DBD::Oracle
which is here
http://svn.perl.org/modules/dbd-oracle/trunk
Thanks again
Cheers
John Scoles
On Tue, Dec 14, 2010 at 10:39 AM, Tim Oertel wrote:
>
> Hi,
>
> We were recently trying to pass an array to a stored procedure.
> It worked fine the first call, but subsequent calls were receiving
> the wrong array size, and with old array entries.
>
> After some investigation, it looks like once an array parameter
> is used with a given size array, it ignores any future array length.
>
> i.e.
>
> @array_of_something = (1, 2, 3, 4, 5);
>
> $sth->bind_param(1, \...@array_of_something, {
> ora_type => ORA_NUMBER_TABLE,
> ora_internal_type => SQLT_INT });
> $sth->execute();
>
> @array_of_something = (6, 7, 8);
>
> $sth->bind_param(1, \...@array_of_something, {
> ora_type => ORA_NUMBER_TABLE,
> ora_internal_type => SQLT_INT });
> $sth->execute();
>
> ...doesn't do what is expected. The stored proceedure
> receives an array of (6, 7, 8, 4, 5), in the second execute().
>
>
> In the process of debugging that, we grabbed newer versions of DBD::Oracle,
> to check if a more recent version had a fix, and lacking that, creating
> a patch against svn.
>
> Turned out we ran into a second bug, our dev system has recent Oracle
> libraries on it, but our db is Oracle 9.2, and (after investigation) it
> looks like there is a known issue with Oracle 10.2 libs, doing a ping
> against 9.2, which causes a listener crash (I think).
>
> The fix for that was to revert to the server version check if the server
> is too old.
>
>
> So, I've attached a patch, plus a test case for the first problem.
> The patch is a patch against svn, 14564. It was tested on a Linux
> box, with 10.2.0.3 client libs and 9.2.0.8.0 server, also running on
> Linux.
>
>
> If I've screwed up formatting, or need to do this differently, let me
> know, and I'll try to fix it. If the fixes are wrong, I'm all ears
> on that too.
>
>
> --
> Tim Oertel |"Why should I be content to simply
> VP Engineering | live in this world, when I, as a
> Ashergroup, Inc. | human being, can CREATE it?!"
> 585-586-0020 | IM via google/XMPP: ma...@jabber.com
>
--
>From the Pythian family to yours, Happy Holidays and all the best in 2011!
Re: DBD::Oracle 1.26 new_tablename / synonyms issue
I have applied this patch to trunk you can find it here
http://svn.perl.org/modules/dbd-oracle/trunk
It will be part of Release 1.28 sometime before the end of the month.
Cheers
John Scoles
On Tue, Nov 16, 2010 at 10:28 AM, Jan Pazdziora wrote:
>
> Hello,
>
> The DBD::Oracle 1.26 code
>
>http://cpansearch.perl.org/src/TIMB/DBD-Oracle-1.26/oci8.c
>
> declares
>
>char new_tablename[100];
>
> in the if branch in init_lob_refetch, and then assigns
>
>tablename=new_tablename;
>
> and then uses the tablename outside of that if block. So the
> tablename pointer points to a random address on the stack.
>
> It causes problems described at
>
>http://rt.cpan.org/Public/Bug/Display.html?id=61511
>https://bugzilla.redhat.com/show_bug.cgi?id=548489
>
> Note that it's kinda related to
>
>http://rt.cpan.org/Public/Bug/Display.html?id=36069#txn-464638
>
> where it's said
>
>Used some of the code form the patch there was no need to
>create a char [100] outside the if statement though.
>
> I'd claim that it is actually necessary to declare it outside of
> that block because even if the name goes out of scope at the end
> of the block, the tablename pointer still points to it.
>
> Please apply
>
> --- oci8.c.orig 2010-11-16 16:26:42.459208675 +0100
> +++ oci8.c 2010-11-16 16:27:27.194207404 +0100
> @@ -4030,6 +4030,7 @@
>ub2 numcols = 0;
>imp_fbh_t *fbh;
>int unmatched_params;
> + char new_tablename[100];
>I32 i,j;
>char *p;
>lob_refetch_t *lr = NULL;
> @@ -4067,7 +4068,6 @@
>if (status == OCI_SUCCESS) { /* There is a synonym, get the schema
> */
>char *syn_schema=NULL;
>char syn_name[100];
> - char new_tablename[100];
>ub4 tn_len = 0, syn_schema_len = 0;
>
>strncpy(syn_name,tablename,strlen(tablename));
>
> to DBD::Oracle.
>
> Thank you,
>
> --
> Jan Pazdziora
>
--
>From the Pythian family to yours, Happy Holidays and all the best in 2011!
ANNOUNCE: DBD::Oracle 1.27 Release Candidate 1
Sorry no funny nick name for version 1.27 but here is the scoop on it anyway You can find the Zip file here http://www.pythian.com/news/wp-content/uploads/DBD-Oracle-1.27-RC1.zip This version removes 'PERL_POLLUTE' and adds in PL_ so it will be fully compatible with Perl 5.13. So in a nutshell 1.27 is a single issue maintenance release. For those interested in the whole story you can have a look at this thread http://www.nntp.perl.org/group/perl.dbi.dev/2010/08/msg6217.html Any an all testing with differing Perls would be much appreciated Cheers John Scoles
Re: DBD-Oracle stored proc with array bug
Ok thanks for that.
At first glance this looks like it might fix another reported bug and it
is doing some work for me as I wanted to add in
OCIServerRelease_log for my own selfish reasons so thanks for that:)
It will be going into 1.28 as 1.27 is reserved for a PERL_POLLUTE release which
should be out soon
Cheers
John Scoles
On 14/12/2010 10:39 AM, Tim Oertel wrote:
Hi,
We were recently trying to pass an array to a stored procedure.
It worked fine the first call, but subsequent calls were receiving
the wrong array size, and with old array entries.
After some investigation, it looks like once an array parameter
is used with a given size array, it ignores any future array length.
i.e.
@array_of_something = (1, 2, 3, 4, 5);
$sth->bind_param(1, \...@array_of_something, {
ora_type => ORA_NUMBER_TABLE,
ora_internal_type => SQLT_INT });
$sth->execute();
@array_of_something = (6, 7, 8);
$sth->bind_param(1, \...@array_of_something, {
ora_type => ORA_NUMBER_TABLE,
ora_internal_type => SQLT_INT });
$sth->execute();
...doesn't do what is expected. The stored proceedure
receives an array of (6, 7, 8, 4, 5), in the second execute().
In the process of debugging that, we grabbed newer versions of
DBD::Oracle,
to check if a more recent version had a fix, and lacking that, creating
a patch against svn.
Turned out we ran into a second bug, our dev system has recent Oracle
libraries on it, but our db is Oracle 9.2, and (after investigation) it
looks like there is a known issue with Oracle 10.2 libs, doing a ping
against 9.2, which causes a listener crash (I think).
The fix for that was to revert to the server version check if the server
is too old.
So, I've attached a patch, plus a test case for the first problem.
The patch is a patch against svn, 14564. It was tested on a Linux
box, with 10.2.0.3 client libs and 9.2.0.8.0 server, also running on
Linux.
If I've screwed up formatting, or need to do this differently, let me
know, and I'll try to fix it. If the fixes are wrong, I'm all ears
on that too.
Re: $SIG{INT} tampering during DBI->connect on Oracle
On 01/12/2010 9:22 AM, Brian Phillips wrote: On Nov 30, 8:37 am, sco...@pythian.com (John Scoles) wrote: Just wanting to make sure this doesn't get lost in the shuffle... Depends is it a specific OS patch for DBD::Oracle or a general one DBI? I am keeping an eye on it. I'm not sure why this matters or perhaps I'm not understanding your question/concern. If this is a feature turned off by default (which is the way the patch is coded), users can selectively enable it only if they see the need. Are you wondering if it should be enabled by default for certain OS's? Or are you wondering if this should be a general DBI patch? I would say "no" to both since there's been no definitive proof as to what is triggering the (undesirable) behavior. Ok I will put it in the way Tim suggests It will be in 1.28 as 1.27 is reserved for now. Look for it in the next few weeks cheers John
Re: $SIG{INT} tampering during DBI->connect on Oracle
On 30/11/2010 10:28 AM, Tim Bunce wrote:
On Mon, Nov 15, 2010 at 10:04:56AM -0800, Brian Phillips wrote:
- DBD::Oracle::db::_login($dbh, $dbname, $user, $auth, $attr)
- or return undef;
+{
+my @local_signals = @{ $attr->{ora_local_signals} || [] };
+local @s...@local_signals} if @local_signals;
+DBD::Oracle::db::_login($dbh, $dbname, $user, $auth, $attr)
+or return undef;
+}
Two minor nit-picks...
The name ora_local_signals doesn't convey what it does.
ora_connect_with_default_signals is long but descriptive.
Performance wise, some apps connect frequently so keeping the default
path lightweight seems worthwhile:
local @SIG{ @{ $attr->{ora_local_signals}||[] } }
if $attr->{ora_local_signals};
Tim
Thy shall be done.
Re: $SIG{INT} tampering during DBI->connect on Oracle
On 30/11/2010 9:28 AM, Brian Phillips wrote: On Nov 15, 12:04 pm, bpphill...@gmail.com (Brian Phillips) wrote: See below for a patch that does not change the default behavior but allows the user to specify a list of signals to be localized during the connect phase. This could easily be default to localize the "INT" handler but since there's some concern over breaking existing applications, I've left the default behavior alone. So... can I expect this patch to be pulled in at some point? Just wanting to make sure this doesn't get lost in the shuffle... Depends is it a specific OS patch for DBD::Oracle or a general one DBI? I am keeping an eye on it. cheers John
Re: $SIG{INT} tampering during DBI->connect on Oracle
On 15/11/2010 9:15 AM, Brian Phillips wrote: On Nov 9, 9:43 am, bpphill...@gmail.com (Brian Phillips) wrote: In my opinion, adding an option to change the current behavior is low risk and completely satisfactory for resolving my issue while leaving current behavior unchanged. If there are concerns that this may be affecting others, perhaps it would make sense to change the default behavior but this is not something I'm asking for. So, can I expect anything from DBD::Oracle in this regard? Can we provide a DBI->connect attribute to enable the functionality I'm requesting? I will supply a patch if that would help. My only other options is to start lobbying for changes in higher-level modules like DBIx::Class or to maintain a CPAN Distroprefs patch file for our installation of DBD::Oracle here at $WORK, neither of which is as clean of a solution as getting this added into DBD::Oracle. I can add in an 'ora_' type attribute that could take care of it. I have done it for other little things as well. I do not think this will go against the spec in any way. You can send me the patch and I will apply it. It would be nice to have a generic solution to this problem but I do not see how this is possible as any solution would have to cover so many OS and client versions we would never get them all? So I am in favour. one thumbs up:) I would appreciate a clear up/down vote from someone in authority so that I know how to proceed. Thanks, Brian
ANNOUNCE DBD Oracle 1.27 Release Candidate 1
You can find Release Candidate 1 for DBD::Oracle 1.27 here http://svn.perl.org/modules/dbd-oracle/branches/polute/DBD_ORACLE_1_27_RC_1.tar.zip This release is limited to cleaning up Perl globals within the C and XS code. Short version PERL_POLLUTE is being dropped from perl 5.13.3 so drivers that have been around for a while (like DBD::Oracle) may break!! Check out this thread http://www.nntp.perl.org/group/perl.dbi.dev/2010/08/msg6217.html for the long version I am announcing this on dbi-users as well to expand the group of people who may be using 5.13.3. Cheers John Scoles -- The best compliment you could give Pythian for our service is a referral.
Re: $SIG{INT} tampering during DBI->connect on Oracle
On 11/2/2010 2:49 PM, Brian Phillips wrote:
Hello - I recently discovered that when we do a DBI->connect to an Oracle
database, the process no longer responds to SIGINT signals. I'm not sure if
it's something in DBD::Oracle (I can't find anything messing with $SIG{INT})
or something in the Oracle client itself but the fix is relatively simple
and I'm hoping it can be included in DBD::Oracle (unless there's a downside
I'm not seeing).
Here's some code that illustrates the problem:
http://gist.github.com/660058#file_dbd_oracle_sigint_fail.pl
Basically, the forked child process discards the SIGINT signal once it's
done a DBI->connect.
The workaround seems fairly simple (a single-line change from the above:
http://gist.github.com/660058#file_dbd_oracle_sigint_works.pl):
{
local $SIG{INT}; # make sure $SIG{INT} returns to normal after we
leave this block
$dbh = DBI->connect('dbi:Oracle:mydb', 'user', 'password');
warn "connected in child ($$)\n";
}
Is this something that would be appropriate to go in the
DBD::Oracle::dr::connect
sub? I have not checked any other signal handlers besides INT and TERM (TERM
seems to work fine) but perhaps it'd be better to localize the entire %SIG
hash:
local @SIG{ keys %SIG };
Thoughts?
Brian
Sounds like a good Idea. I could include it but is it 'safe' We will
have to here from types that know something more
about SIGINT signals than I do.
My one question is how OS transportable is this?
Just for example If I am working on windows does SIGINT have any effect?
Like to here what the other Maintainers say?
Cheers
John
Re: Perl 5.13.3+ MAY BREAK COMPILED DRIVERS
Got bored just looking for the vars that do not use PL_ so I wrote up this little script. It takes all the PL_ vars I could find (minus the PL_ part) in Perl 5.13 and the looks for them in .C .H .XS and .XSI files and reports back if it found any. It does give a few false positives but it is much easier then trying to pick them all out yourself or by recompiling and fixing as you go. Cheers John Scoles file_PL_vars.pl Description: Perl program
Re: Perl 5.13.3+ MAY BREAK COMPILED DRIVERS - Please test DBI 1.613_71!
On 10/25/2010 1:52 PM, Martin J. Evans wrote: On 25/10/2010 18:57, John Scoles wrote: On 10/25/2010 1:42 PM, Martin J. Evans wrote: On 25/10/2010 18:04, John Scoles wrote: On 10/25/2010 10:33 AM, Tim Bunce wrote: On Mon, Aug 30, 2010 at 07:28:31AM -0400, John Scoles wrote: Tim Bunce wrote: Looks like DBD::Oracle as PERL_POLLUTE as well. Lucy!!! You have some renaming to do!!! A project of 1.26 perhaps As 5.13.x comes closer to being 5.14 this issue is becoming more urgent. John, I've asked s...@perl.org to give commit access for the dbd-orcle repro to Martin so he can work on this for you. I'd appreciate it if you could work on a release as soon as he's done. Thanks! Actully I have done all the work on it it is just testing I have to go at now. Which should take place in a day or so I created a new branch http://svn.perl.org/modules/dbd-oracle/branches/polute for it. It would be best if he uses that branch to start with. Looks like you have it under control so that is fine by me. There are a few missed "na"s, "dowarn", "dirty" etc in that branch in dbdimp.c and oci8.c. Perhaps others, I did not check that carefully. Yep there might be I have yet go a chance to fully 'Test' is on perl 5.13 on Lunix yet. Just with windows and I an not 100% on it. I you want to patch and check into that branch that is fine. I want to keep 'trunk' as clean as possiable until I get at least 2 or three release candidates out there Cheers John I have no commit bit as yet so it will have to wait until then. I have tried my changes on Perl 5.13.6 and they tested ok. I just had to check there was no code which I would not compile (e.g., in Oracle 8 compiler directives). You can just send me a diff and I can get them in today Cheers John Martin Martin I have have a new release ready within a few days What is the drop dead date for this. Cheers John Scoles Michael, will you be able to make a fixed release of DBD::Sybase soonish? The work required is pretty trivial. It's mostly just adding a "PL_" prefix to instances of the sv_undef, sv_yes, and sv_no variables. For both of you I suggest testing with an actual recent build of 5.13.x (rather than fiddle around with the special 1.613_71 DBI build). Tim. cheers John Short version: Please download build test *and install* DBI 1.613_71, then download build and test any compiled drivers you use to check they work with DBI 1.613_71. Let us know about any failures *and* successes. Also grep the source code of the driver to see if it defines PERL_POLUTE. If it does, let us know. Long version: Perl 5.13.3+ removes support for PERL_POLUTE. PERL_POLUTE enables use of old-style variables names, without the PL_ prefix (e.g. sv_undef instead of PL_sv_undef). The DBI has, for many years, enabled PERL_POLUTE mode in DBIXS.h, so it's likely that compiled drivers are use some old-style variables names. These drivers won't work with Perl 5.13.3+. To aid testing for this, the DBI 1.613_71 doesn't enabled PERL_POLUTE mode. So please test compiled drivers against DBI 1.613_71. Thanks! Tim.
Re: Perl 5.13.3+ MAY BREAK COMPILED DRIVERS - Please test DBI 1.613_71!
On 10/25/2010 1:42 PM, Martin J. Evans wrote: On 25/10/2010 18:04, John Scoles wrote: On 10/25/2010 10:33 AM, Tim Bunce wrote: On Mon, Aug 30, 2010 at 07:28:31AM -0400, John Scoles wrote: Tim Bunce wrote: Looks like DBD::Oracle as PERL_POLLUTE as well. Lucy!!! You have some renaming to do!!! A project of 1.26 perhaps As 5.13.x comes closer to being 5.14 this issue is becoming more urgent. John, I've asked s...@perl.org to give commit access for the dbd-orcle repro to Martin so he can work on this for you. I'd appreciate it if you could work on a release as soon as he's done. Thanks! Actully I have done all the work on it it is just testing I have to go at now. Which should take place in a day or so I created a new branch http://svn.perl.org/modules/dbd-oracle/branches/polute for it. It would be best if he uses that branch to start with. Looks like you have it under control so that is fine by me. There are a few missed "na"s, "dowarn", "dirty" etc in that branch in dbdimp.c and oci8.c. Perhaps others, I did not check that carefully. Yep there might be I have yet go a chance to fully 'Test' is on perl 5.13 on Lunix yet. Just with windows and I an not 100% on it. I you want to patch and check into that branch that is fine. I want to keep 'trunk' as clean as possiable until I get at least 2 or three release candidates out there Cheers John Martin I have have a new release ready within a few days What is the drop dead date for this. Cheers John Scoles Michael, will you be able to make a fixed release of DBD::Sybase soonish? The work required is pretty trivial. It's mostly just adding a "PL_" prefix to instances of the sv_undef, sv_yes, and sv_no variables. For both of you I suggest testing with an actual recent build of 5.13.x (rather than fiddle around with the special 1.613_71 DBI build). Tim. cheers John Short version: Please download build test *and install* DBI 1.613_71, then download build and test any compiled drivers you use to check they work with DBI 1.613_71. Let us know about any failures *and* successes. Also grep the source code of the driver to see if it defines PERL_POLUTE. If it does, let us know. Long version: Perl 5.13.3+ removes support for PERL_POLUTE. PERL_POLUTE enables use of old-style variables names, without the PL_ prefix (e.g. sv_undef instead of PL_sv_undef). The DBI has, for many years, enabled PERL_POLUTE mode in DBIXS.h, so it's likely that compiled drivers are use some old-style variables names. These drivers won't work with Perl 5.13.3+. To aid testing for this, the DBI 1.613_71 doesn't enabled PERL_POLUTE mode. So please test compiled drivers against DBI 1.613_71. Thanks! Tim.
Re: Perl 5.13.3+ MAY BREAK COMPILED DRIVERS - Please test DBI 1.613_71!
On 10/25/2010 10:33 AM, Tim Bunce wrote: On Mon, Aug 30, 2010 at 07:28:31AM -0400, John Scoles wrote: Tim Bunce wrote: Looks like DBD::Oracle as PERL_POLLUTE as well. Lucy!!! You have some renaming to do!!! A project of 1.26 perhaps As 5.13.x comes closer to being 5.14 this issue is becoming more urgent. John, I've asked s...@perl.org to give commit access for the dbd-orcle repro to Martin so he can work on this for you. I'd appreciate it if you could work on a release as soon as he's done. Thanks! Actully I have done all the work on it it is just testing I have to go at now. Which should take place in a day or so I created a new branch http://svn.perl.org/modules/dbd-oracle/branches/polute for it. It would be best if he uses that branch to start with. I have have a new release ready within a few days What is the drop dead date for this. Cheers John Scoles Michael, will you be able to make a fixed release of DBD::Sybase soonish? The work required is pretty trivial. It's mostly just adding a "PL_" prefix to instances of the sv_undef, sv_yes, and sv_no variables. For both of you I suggest testing with an actual recent build of 5.13.x (rather than fiddle around with the special 1.613_71 DBI build). Tim. cheers John Short version: Please download build test *and install* DBI 1.613_71, then download build and test any compiled drivers you use to check they work with DBI 1.613_71. Let us know about any failures *and* successes. Also grep the source code of the driver to see if it defines PERL_POLUTE. If it does, let us know. Long version: Perl 5.13.3+ removes support for PERL_POLUTE. PERL_POLUTE enables use of old-style variables names, without the PL_ prefix (e.g. sv_undef instead of PL_sv_undef). The DBI has, for many years, enabled PERL_POLUTE mode in DBIXS.h, so it's likely that compiled drivers are use some old-style variables names. These drivers won't work with Perl 5.13.3+. To aid testing for this, the DBI 1.613_71 doesn't enabled PERL_POLUTE mode. So please test compiled drivers against DBI 1.613_71. Thanks! Tim.
Re: ANNOUNCE: DBD::Oracle 1.25 Release Candidate 4
Not getting this on windows and 32 bit linux Hmm!!! having a look at it at in oci8.c on line 2046 textstr_buf[200]; doublednum; size_tstr_len; dnum is set up as a double. later on 2174 I where I return the value (void) OCINumberToReal(fbh->imp_sth->errhp, (CONST OCINumber *) attr_value, (uword) sizeof(dnum), (dvoid *) &dnum); perhaps the (uword) is causing the problem?? can you give it few tests for me and see what you come up with?? Cheers John On Wed, Sep 15, 2010 at 2:35 PM, H.Merijn Brand wrote: > On Wed, 15 Sep 2010 10:35:03 -0400, John Scoles > wrote: > > > Ok here I am again for the fourth time :( > > > > You can find it here > > > > http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.25-RC4.tar > > Please use the correct extension: .tar.gz > > > This one has some more warning fixes and a little add to the Pod. Small > > but annoying. > > > > I wonder if I will break a record this time?? > > > > I think it was 5?? > > Longdouble causes tests to fail? > > > Using DBI 1.613 (for perl 5.012000 on i686-linux-64int-ld) > ^^^ > Configuring DBD::Oracle for perl 5.012000 on linux (i686-linux-64int-ld) > > Installing on a linux, Ver#2.6 > Using Oracle in /pro/oracle/v11.2 > DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR) > Oracle version 11.2.0.1 (11.2) > > > System: perl5.012000 linux nb09 2.6.31.12-0.2-default #1 smp 2010-03-16 > 21:25:39 +0100 i686 i686 i386 gnulinux > Compiler: cc -O2 -DDEBUGGING -fno-strict-aliasing -pipe -fstack-protector > -I/pro/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 > Linker: /usr/bin/ld > Sysliblist: > Oracle makefiles would have used these definitions but we override them: > CC: /usr/bin/gcc > LDFLAGS: -g $(MTYPE) > sh: shell: command not found > [-g ] > Linking with -L../../ -locci -lclntsh -lpthread [from $(CCLIB)] > > /pro/bin/perl -p -e "s/~DRIVER~/Oracle/g" > /pro/lib/perl5/site_perl/5.12.0/i686-linux-64int-ld/auto/DBI/Driver.xst > > Oracle.xsi > /pro/bin/perl /pro/lib/perl5/5.12.0/ExtUtils/xsubpp -typemap > /pro/lib/perl5/5.12.0/ExtUtils/typemap -typemap typemap Oracle.xs > > Oracle.xsc && mv Oracle.xsc Oracle.c > cc -c -I/usr/include/oracle/11.2/client > -I/pro/lib/perl5/site_perl/5.12.0/i686-linux-64int-ld/auto/DBI -DDEBUGGING > -fno-strict-aliasing -pipe -fstack-protector -I/pro/local/include > -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O2 -DVERSION=\"1.25\" > -DXS_VERSION=\"1.25\" -fPIC > "-I/pro/lib/perl5/5.12.0/i686-linux-64int-ld/CORE" -Wall -Wno-comment > -DUTF8_SUPPORT -DORA_OCI_VERSION=\"11.2.0.1\" -DORA_OCI_102 -DORA_OCI_112 > Oracle.c > cc -c -I/usr/include/oracle/11.2/client > -I/pro/lib/perl5/site_perl/5.12.0/i686-linux-64int-ld/auto/DBI -DDEBUGGING > -fno-strict-aliasing -pipe -fstack-protector -I/pro/local/include > -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O2 -DVERSION=\"1.25\" > -DXS_VERSION=\"1.25\" -fPIC > "-I/pro/lib/perl5/5.12.0/i686-linux-64int-ld/CORE" -Wall -Wno-comment > -DUTF8_SUPPORT -DORA_OCI_VERSION=\"11.2.0.1\" -DORA_OCI_102 -DORA_OCI_112 > dbdimp.c > dbdimp.c: In function ‘dbd_rebind_ph_char’: > dbdimp.c:2513:20: warning: cast from pointer to integer of different size > dbdimp.c: In function ‘dbd_rebind_ph_xml’: > dbdimp.c:2711:17: warning: cast to pointer from integer of different size > dbdimp.c: In function ‘ora_bind_ph’: > dbdimp.c:3004:6: warning: format ‘%ld’ expects type ‘long int’, but > argument 2 has type ‘IV’ > dbdimp.c:3013:5: warning: format ‘%ld’ expects type ‘long int’, but > argument 2 has type ‘IV’ > dbdimp.c:3047:4: warning: format ‘%ld’ expects type ‘long int’, but > argument 3 has type ‘IV’ > cc -c -I/usr/include/oracle/11.2/client > -I/pro/lib/perl5/site_perl/5.12.0/i686-linux-64int-ld/auto/DBI -DDEBUGGING > -fno-strict-aliasing -pipe -fstack-protector -I/pro/local/include > -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O2 -DVERSION=\"1.25\" > -DXS_VERSION=\"1.25\" -fPIC > "-I/pro/lib/perl5/5.12.0/i686-linux-64int-ld/CORE" -Wall -Wno-comment > -DUTF8_SUPPORT -DORA_OCI_VERSION=\"11.2.0.1\" -DORA_OCI_102 -DORA_OCI_112 > oci8.c > oci8.c: In function ‘ora_blob_read_mb_piece’: > oci8.c:1645:4: warning: format ‘%ld’ expects type ‘long int’, but argument > 6 has type ‘UV’ > oci8.c: In function ‘ora_st_fetch’: > oci8.c:3802:9: warning: format ‘%ld’ expects type ‘long int’, but argument > 4 has type ‘IV’ > oci8.c:3810:9: warning: format ‘%ld’ expects type ‘long i
ANNOUNCE: DBD::Oracle 1.25 Release Candidate 4
Ok here I am again for the fourth time :( You can find it here http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.25-RC4.tar This one has some more warning fixes and a little add to the Pod. Small but annoying. I wonder if I will break a record this time?? I think it was 5?? Please Enjoy John Scoles
ANNOUNCE: DBD::Oracle 1.25 Release Candidate 3
Well the third time is a charm!! We hope for the 'two bit' version of DBD::Oracle 1.25 You can find release candidate 3 here http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.25-RC3.tar I got rid of the warning error in the .xs file for OCILobLocatorIsInit_log_stat seems it was a typo on my part. As well got rid of some harmless compiler warnings Cheers John Scoles -- The best compliment you could give Pythian for our service is a referral.
Re: Perl 5.13.3+ MAY BREAK COMPILED DRIVERS - Please test DBI 1.613_71!
Tim Bunce wrote: Looks like DBD::Oracle as PERL_POLLUTE as well. Lucy!!! You have some renaming to do!!! A project of 1.26 perhaps cheers John Short version: Please download build test *and install* DBI 1.613_71, then download build and test any compiled drivers you use to check they work with DBI 1.613_71. Let us know about any failures *and* successes. Also grep the source code of the driver to see if it defines PERL_POLUTE. If it does, let us know. Long version: Perl 5.13.3+ removes support for PERL_POLUTE. PERL_POLUTE enables use of old-style variables names, without the PL_ prefix (e.g. sv_undef instead of PL_sv_undef). The DBI has, for many years, enabled PERL_POLUTE mode in DBIXS.h, so it's likely that compiled drivers are use some old-style variables names. These drivers won't work with Perl 5.13.3+. To aid testing for this, the DBI 1.613_71 doesn't enabled PERL_POLUTE mode. So please test compiled drivers against DBI 1.613_71. Thanks! Tim.
Re: ANNOUNCE: DBD::Oracle 1.25 Release Candidate
Martin J. Evans wrote: Rather than blasting out a whole new RC for this can you make this quick fix to ocitrace.h #define OCILobLocatorIsInit_log_stat(envhp,errhp,loc,is_initp,stat)\ --stat =OCILobLocatorIsInit (envhp,errhp,loc,is_init );\ ++stat =OCILobLocatorIsInit (envhp,errhp,loc,is_initp );\ Hopefully that will get it Cheers John Scoles John Scoles wrote: Well story two of the 'two bit' version of DBD::Oracle 1.25 You can find release candidate 2 here http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.25-RC2.tar.gz This one has some minor changes from Martin Evans, though I could not find any issues with the Makfile.PL myself. and hopefully I got rid of the warning error in the .xs file for OCILobLocatorIsInit_log_stat And of course the usual bunch of POD changes. Cheers John Scoles -- New! Learn why & how to love your data with Pythian's new webinar series. Topics, details & register: http://www.pythian.com/webinars Sorry for taking so long to try this RC. The gzipped tar file you reference appears to be doubly gzipped BTW. OCILobLocatorIsInit problem still seems to exist for me: cc -c -I/home/oracle/instantclient_11_1//sdk/include -I/usr/local/lib/perl/5.10.0/auto/DBI -D_REENTRANT -D_GNU_SOURCE -DDEBIAN -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O2 -g -DVERSION=\"1.25\" -DXS_VERSION=\"1.25\" -fPIC "-I/usr/lib/perl/5.10/CORE" -Wall -Wno-comment -DUTF8_SUPPORT -DORA_OCI_VERSION=\"11.1.0.6\" -DORA_OCI_102 Oracle.c Oracle.xs: In function ‘XS_DBD__Oracle__db_ora_lob_is_init’: Oracle.xs:486: warning: passing argument 4 of ‘OCILobLocatorIsInit’ makes pointer from integer without a cast /home/oracle/instantclient_11_1//sdk/include/ociap.h:7297: note: expected ‘boolean *’ but argument is of type ‘boolean’ Other than the usual 26exe_array test this seems to work ok here. Hope you enjoyed YAPC::EU 2010. Martin
Re: Please Test: AutoInactiveDestroy
David E. Wheeler wrote:
Ah what do you want that for It will just mess up your email ;)
t/zvp_16destroy.t .. 1/20 -> DBI->install_driver(Test) for
MSWin32 perl=5.0100
01 pid=2200 ruid=0 euid=0
install_driver: DBD::Test version undef loaded from dummy
New DBI::dr (for DBD::Test::dr, parent=, id=77)
_setup_handle(DBI::dr=HASH(0x18df61c) DBD::Test::dr 77)
<- install_driver= DBI::dr=HASH(0x18df61c)
> connect in DBD::Test::dr ('DBI::dr=HASH(0x1a459b4)') []
New DBI::db (for DBD::Test::db, parent=DBI::dr=HASH(0x1a459b4), id=)
_setup_handle(DBI::db=HASH(0x19428b4) DBD::Test::db
DBI::dr=HASH(0x1a459b4) )
> STORE in DBD::Test::db ('DBI::db=HASH(0x1938f7c)' 'Active' 1) []
< STORE= 1 at ./t/16destroy.t line 44
> STORE in DBD::Test::db ('DBI::db=HASH(0x1938f7c)' 'AutoCommit' 1) []
< STORE= 1 at ./t/16destroy.t line 45
< connect= 'DBI::db=HASH(0x19428b4)' at ./t/16destroy.t line 93
> FETCH in DBD::Test::db ('DBI::db=HASH(0x1938f7c)' 'Active') []
< FETCH= 1 at C:/Perl/lib/Test/More.pm line 292
> DESTROY in DBD::Test::db ('DBI::db=HASH(0x1938f7c)') []
> FETCH in DBD::Test::db ('DBI::db=HASH(0x1938f7c)' 'Active') []
< FETCH= 1 at ./t/16destroy.t line 82
< DESTROY= 1 at ./t/16destroy.t line 95
> connect in DBD::Test::dr ('DBI::dr=HASH(0x1a459b4)'
'dbi:ExampleP:dummy' '' '' 'HASH
(0x19428b4)') []
New DBI::db (for DBD::Test::db, parent=DBI::dr=HASH(0x1a459b4), id=)
_setup_handle(DBI::db=HASH(0x1fbaab4) DBD::Test::db
DBI::dr=HASH(0x1a459b4) )
> STORE in DBD::Test::db ('DBI::db=HASH(0x1b009a4)' 'Active' 1) []
< STORE= 1 at ./t/16destroy.t line 44
> STORE in DBD::Test::db ('DBI::db=HASH(0x1b009a4)' 'AutoCommit' 1) []
< STORE= 1 at ./t/16destroy.t line 45
> STORE in DBD::Test::db ('DBI::db=HASH(0x1b009a4)'
'InactiveDestroy' 1) []
< STORE= 1 at ./t/16destroy.t line 46
< connect= 'DBI::db=HASH(0x1fbaab4)' at ./t/16destroy.t line 102
> FETCH in DBD::Test::db ('DBI::db=HASH(0x1b009a4)'
'InactiveDestroy') []
< FETCH= 1 at C:/Perl/lib/Test/More.pm line 292
> FETCH in DBD::Test::db ('DBI::db=HASH(0x1b009a4)' 'Active') []
< FETCH= 1 at C:/Perl/lib/Test/More.pm line 292
> DESTROY in DBD::Test::db ('DBI::db=HASH(0x1b009a4)') []
> FETCH in DBD::Test::db ('DBI::db=HASH(0x1b009a4)' 'Active') []
< FETCH= 0 at ./t/16destroy.t line 82
< DESTROY= 1 at ./t/16destroy.t line 102
> connect in DBD::Test::dr ('DBI::dr=HASH(0x1a459b4)'
'dbi:ExampleP:dummy' '' '' 'HASH
(0x1fbaab4)') []
New DBI::db (for DBD::Test::db, parent=DBI::dr=HASH(0x1a459b4), id=)
_setup_handle(DBI::db=HASH(0x1fe004c) DBD::Test::db
DBI::dr=HASH(0x1a459b4) )
> STORE in DBD::Test::db ('DBI::db=HASH(0x1fbaaf4)' 'Active' 1) []
< STORE= 1 at ./t/16destroy.t line 44
> STORE in DBD::Test::db ('DBI::db=HASH(0x1fbaaf4)' 'AutoCommit' 1) []
< STORE= 1 at ./t/16destroy.t line 45
> STORE in DBD::Test::db ('DBI::db=HASH(0x1fbaaf4)'
'AutoInactiveDestroy' 1) []
< STORE= 1 at ./t/16destroy.t line 46
< connect= 'DBI::db=HASH(0x1fe004c)' at ./t/16destroy.t line 111
> FETCH in DBD::Test::db ('DBI::db=HASH(0x1fbaaf4)'
'AutoInactiveDestroy') []
< FETCH= 1 at C:/Perl/lib/Test/More.pm line 292
> FETCH in DBD::Test::db ('DBI::db=HASH(0x1fbaaf4)' 'Active') []
< FETCH= 1 at C:/Perl/lib/Test/More.pm line 292
> DESTROY in DBD::Test::db ('DBI::db=HASH(0x1fbaaf4)') []
> FETCH in DBD::Test::db ('DBI::db=HASH(0x1fbaaf4)' 'Active') []
< FETCH= 1 at ./t/16destroy.t line 82
< DESTROY= 1 at ./t/16destroy.t line 111
> connect in DBD::Test::dr ('DBI::dr=HASH(0x1a459b4)'
'dbi:ExampleP:dummy' '' '' 'HASH
(0x1fe004c)') []
New DBI::db (for DBD::Test::db, parent=DBI::dr=HASH(0x1a459b4), id=)
_setup_handle(DBI::db=HASH(0x1a45934) DBD::Test::db
DBI::dr=HASH(0x1a459b4) )
> STORE in DBD::Test::db ('DBI::db=HASH(0x1fe033c)' 'Active' 1) []
< STORE= 1 at ./t/16destroy.t line 44
> STORE in DBD::Test::db ('DBI::db=HASH(0x1fe033c)' 'AutoCommit' 1) []
< STORE= 1 at ./t/16destroy.t line 45
> STORE in DBD::Test::db ('DBI::db=HASH(0x1fe033c)'
'AutoInactiveDestroy' 1) []
< STORE= 1 at ./t/16destroy.t line 46
< connect= 'DBI::db=HASH(0x
Re: Please Test: AutoInactiveDestroy
David E. Wheeler wrote: now we are getting t/zvp_16destroy.t(Wstat: 0 Tests: 18 Failed: 0) Parse errors: Bad plan. You planned 20 tests but ran 18. t/zvxgp_16destroy.t (Wstat: 0 Tests: 18 Failed: 0) Parse errors: Bad plan. You planned 20 tests but ran 18. Files=171, Tests=6280, 223 wallclock secs ( 3.73 usr + 0.70 sys = 4.44 CPU) Result: FAIL Failed 2/171 test programs. 0/6280 subtests failed. NMAKE : U1077: Stop. On Jul 27, 2010, at 9:42 AM, Martin J. Evans wrote: Test has no driver CLONE() function so is unsafe threaded FAILED tests 19-20 Failed 2/20 tests, 90.00% okay Failed Test Stat Wstat Total Fail List of Failed --- t\zvp_16destroy.t 202 19-20 Failed 1/1 test scripts. 2/20 subtests failed. Files=1, Tests=20, 1 wallclock secs ( 0.00 cusr + 0.00 csys = 0.00 CPU) Failed 1/1 test programs. 2/20 subtests failed. C:\perlbuild_xxx\dbi_trunk> As a wild guess, I've just added a CLONE() function in r14289. Would you mind trying again? I'm around on #dbi (as mje) tomorrow if that helps. Might be around this evening also but I'm not I've got a Windows machine available tonight. As am I. I'm "theory". Best, David
Re: Please Test: AutoInactiveDestroy
nactiveDestroy' 1) []
< STORE= 1 at ./t/16destroy.t line 44
< connect= 'DBI::db=HASH(0x1a457c4)' at ./t/16destroy.t line 118
> FETCH in DBD::Test::db ('DBI::db=HASH(0x1fe0234)'
'AutoInactiveDestroy') []
< FETCH= 1 at C:/Perl/lib/Test/More.pm line 292
> FETCH in DBD::Test::db ('DBI::db=HASH(0x1fe0234)' 'Active') []
< FETCH= 1 at C:/Perl/lib/Test/More.pm line 292
CLONE DBI for new thread
Test has no driver CLONE() function so is unsafe threaded
> DESTROY in DBD::Test::db ('DBI::db=HASH(0x202027c)') []
> FETCH in DBD::Test::db ('DBI::db=HASH(0x202027c)' 'Active') []
< FETCH= 0 at ./t/16destroy.t line 77
-- DBI::END ($@: , $!: )
> DESTROY in DBD::Test::db ('DBI::db=HASH(0x202027c)') []
> FETCH in DBD::Test::db ('DBI::db=HASH(0x202027c)' 'Active') []
< FETCH= 0 at ./t/16destroy.t line 77
t/zvp_16destroy.t .. Failed 2/20 subtests
On Jul 27, 2010, at 5:01 AM, John Scoles wrote:
Which test is it exactly??
t/16destroy.t
Ok here you go
Windblows XP home SP3
Visual C++ 2003
here is the output from the nmake test
Thanks!
t/01basics.t ... ok
t/02dbidrv.t ... ok
t/03handle.t ... ok
t/04mods.t . ok
t/05concathash.t ... ok
t/06attrs.t ok
t/07kids.t . ok
t/08keeperr.t .. ok
t/09trace.t ok
t/10examp.t ok
t/11fetch.t ok
t/12quote.t ok
t/13taint.t ok
t/14utf8.t . ok
t/15array.t ok
t/16destroy.t .. 1/20 Test has no driver CLONE() function so is
unsafe threade
d
Tim, is that something we should add to that test driver?
t/zvp_16destroy.t .. 1/20 Test has no driver CLONE() function so is
unsafe threaded
t/zvp_16destroy.t .. Failed 2/20 subtests
Hrm, can you run that one verbose, please?
Thanks,
David
Re: Please Test: AutoInactiveDestroy
David E. Wheeler wrote:
Fellow DBIers,
With a bit of help from me, Tim added a new feature to the DBI yesterday:
AutoInactiveDestroy. I wrote the test for it. The test, however, forks. We need
to make sure that it doesn't die an ugly death on systems without fork (and on
Win32, where it's emulated). If you have such a system, would you minding
checking out the repository and building and testing DBI?
Which test is it exactly??
svn co http://svn.perl.org/modules/dbi/trunk
Thanks!
David
Ok here you go
Windblows XP home SP3
Visual C++ 2003
here is the output from the nmake test
t/01basics.t ... ok
t/02dbidrv.t ... ok
t/03handle.t ... ok
t/04mods.t . ok
t/05concathash.t ... ok
t/06attrs.t ok
t/07kids.t . ok
t/08keeperr.t .. ok
t/09trace.t ok
t/10examp.t ok
t/11fetch.t ok
t/12quote.t ok
t/13taint.t ok
t/14utf8.t . ok
t/15array.t ok
t/16destroy.t .. 1/20 Test has no driver CLONE() function so
is unsafe threade
d
t/16destroy.t .. ok
t/19fhtrace.t .. ok
t/20meta.t . ok
t/30subclass.t . ok
t/35thrclone.t . ok
t/40profile.t .. ok
t/41prof_dump.t ok
t/42prof_data.t ok
t/43prof_env.t . ok
t/49dbd_file.t . ok
t/50dbm_simple.t ... ok
t/51dbm_file.t . ok
t/52dbm_complex.t .. skipped: Not running with SQL::Statement
t/60preparse.t . ok
t/65transact.t . ok
t/70callbacks.t ok
t/72childhandles.t . ok
t/80proxy.t skipped: modules required for proxy are
probably not installe
d (e.g., RPC/PlClient.pm)
t/85gofer.t ok
t/86gofer_fail.t ... ok
t/87gofer_cache.t .. ok
t/90sql_type_cast.t ok
t/pod-cm.t . skipped: Pod::Spell::CommonMistakes
required for testing POD
spelling
t/pod-coverage.t ... skipped: Currently FAILS FOR MANY MODULES!
t/pod.t ok
t/zvg_01basics.t ... ok
t/zvg_02dbidrv.t ... ok
t/zvg_03handle.t ... ok
t/zvg_04mods.t . ok
t/zvg_05concathash.t ... ok
t/zvg_06attrs.t ok
t/zvg_07kids.t . ok
t/zvg_08keeperr.t .. ok
t/zvg_09trace.t ok
t/zvg_10examp.t ok
t/zvg_11fetch.t ok
t/zvg_12quote.t ok
t/zvg_13taint.t skipped: Taint attribute tests not
functional with DBI_AUTOPR
OXY
t/zvg_14utf8.t . ok
t/zvg_15array.t ok
t/zvg_16destroy.t .. 1/20 Test has no driver CLONE() function so
is unsafe threaded
t/zvg_16destroy.t .. ok
t/zvg_19fhtrace.t .. ok
t/zvg_20meta.t . ok
t/zvg_30subclass.t . ok
t/zvg_35thrclone.t . ok
t/zvg_40profile.t .. ok
t/zvg_41prof_dump.t ok
t/zvg_42prof_data.t ok
t/zvg_43prof_env.t . ok
t/zvg_49dbd_file.t . ok
t/zvg_50dbm_simple.t ... ok
t/zvg_51dbm_file.t . ok
t/zvg_52dbm_complex.t .. skipped: Not running with SQL::Statement
t/zvg_60preparse.t . ok
t/zvg_65transact.t . skipped: Transactions not supported by
DBD::Gofer
t/zvg_70callbacks.t ok
t/zvg_72childhandles.t . ok
t/zvg_80proxy.t skipped: modules required for proxy are
probably not installe
d (e.g., RPC/PlClient.pm)
t/zvg_85gofer.t ok
t/zvg_86gofer_fail.t ... skipped: Gofer DBI_AUTOPROXY
t/zvg_87gofer_cache.t .. skipped: Gofer DBI_AUTOPROXY
t/zvg_90sql_type_cast.t ok
t/zvn_49dbd_file.t . ok
t/zvn_50dbm_simple.t ... ok
t/zvn_51dbm_file.t . ok
t/zvn_52dbm_complex.t .. skipped: Not running with SQL::Statement
t/zvn_85gofer.t ok
t/zvp_01basics.t ... ok
t/zvp_02dbidrv.t ... ok
t/zvp_03handle.t ... ok
t/zvp_04mods.t . ok
t/zvp_05concathash.t ... ok
t/zvp_06attrs.t ok
t/zvp_07kids.t . skipped: $h->{Kids} attribute not supported
for DBI::PurePerl
t/zvp_08keeperr.t .. ok
t/zvp_09trace.t ok
t/zvp_10examp.t ok
t/zvp_11fetch.t ok
t/zvp_12quote.t ok
t/zvp_13taint.t skipped: Taint attributes not supported
with DBI::PurePerl
t/zvp_14utf8.t . ok
t/zvp_15array.t ok
t/zvp_16destroy.t .. 1/20 Test has no driver CLONE() function so
is unsafe threaded
t/zvp_16destroy.t .. Failed 2/20 subtests
t/zvp_19fhtrace.t .. ok
t/zvp_20meta.t . ok
t/zvp_30subclass.t . ok
t/zvp_35thrclone.t . ok
t/zvp_40profile.t .. skipped: profiling not supported for
DBI::PurePerl
t/zvp_41prof_dump.t skipped: profiling not supported
Re: ANNOUNCE: DBD::Oracle 1.25 Release Candidate
Make sure you double check the MANIFEST if that is important for you system. I have yet to make sure it is 100% complete yet. Jens Rehsack wrote: 2010/7/23 John Scoles : Well story two of the 'two bit' version of DBD::Oracle 1.25 You can find release candidate 2 here http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.25-RC2.tar.gz Great, it hasn't the issue from 1.24a (double packaged) and I can use it to update the pkgsrc package when uploaded to CPAN. This one has some minor changes from Martin Evans, though I could not find any issues with the Makfile.PL myself. and hopefully I got rid of the warning error in the .xs file for OCILobLocatorIsInit_log_stat And of course the usual bunch of POD changes. Cheers John Scoles Best regards and nice weekend, Jens
ANNOUNCE: DBD::Oracle 1.25 Release Candidate
Well story two of the 'two bit' version of DBD::Oracle 1.25 You can find release candidate 2 here http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.25-RC2.tar.gz This one has some minor changes from Martin Evans, though I could not find any issues with the Makfile.PL myself. and hopefully I got rid of the warning error in the .xs file for OCILobLocatorIsInit_log_stat And of course the usual bunch of POD changes. Cheers John Scoles -- New! Learn why & how to love your data with Pythian's new webinar series. Topics, details & register: http://www.pythian.com/webinars
Re: ANNOUNCE: DBD::Oracle 1.25 Release Candidate 1
have him read this http://www.oracle.com/technology/tech/oci/pdf/oracledrcp11g.pdf takes about 5 mins to set up with I think only 2 psql command and an equal number to shut down cheers :) John On Fri, Jul 16, 2010 at 4:33 PM, Scott T. Hildreth < shild...@scotth.emsphone.com> wrote: > On Fri, 2010-07-16 at 13:04 -0400, John Scoles wrote: > > John Scoles wrote: > > > > Nobody wants to play with my new toy? > > > > I will, waiting for our dba to setup the DRCP for me. > > > :( > > > > > Well it is finally here the 'two bit' version of DBD::Oracle 1.25 > > > > > > You can find the release candidate here > > > > > > > http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.25-RC1.tar.gz > > > > > > Any and all testing will be most welcome! > > > > > > The big add in this time is support for DRCP (Database Resident > Connection > > > Pool) if you happen to be using 11g or later > > > > > > As well 'get_info' has been greatly expanded > > > > > > Plus the the usual hodgepodge of bug fixes detailed below > > > > > > Added support for the OCIPing by John Scoles > > > Spell checked the pod (the first time in a while me thinks) updated > the > > > todo By John Scoles > > > Added support for DCRP (Database Resident Connection Pooling) by John > > > Scoles with Luben Karavelov > > > Fix for odd error with Ping from Tom Payerle > > > Removed the NEW_OCI_INIT compile directive and the deprecated > > > OCIInitialize calls > > > Fix for rt.cpan.org Ticket #=57256 : Double free problem in > dbdimp.c by > > > John Scoles > > > Fix for invalid format in trace of OCILobLocatorIsInit_log_stat > reported > > > by Martin Evans Fixed by John Scoles > > > Fix for very odd UNKNOWN OCI STATUS 1041 (OCILobFreeTemporary) on > > > disconnect reported by John Parker and Bob Mcgowan fixed by John Scoles > > > Fix for rt.cpan.org Ticket #=55445: get_info(28) SQL_IDENTIFIER_CASE > seems > > > to return the wrong value from Martin J Evans and a bunch of re jigging > from > > > John Scoles > > > Patch for PL/SQL: numeric or value error: character string buffer too > > > small from Scott T. Hildreth > > > Fix for rt.cpan.org Ticket #=51594 type_info and type_info_all miss > vital > > > information from John Scoles > > > Added ora_lob_is_init function by John Scoles > > > Fix for rt.cpan.org Ticket #=55031 Ubuntu Server Building with > Oracle XE > > > under 32-bit from Brian Candler > > > Fix for rt.cpan.org Ticket #=56810 bug with multiple nested cursor > from > > > John Scoles > > > Fix for bug found only on Big-Endian hardware reported by Timothy > Everett > > > and others from Charles Jardine > > > Fix for memory leak when using prepared_cached and lobs reported by > Mark > > > Bobak and Martin Evans found and fixed by John Scoles and a test from > Martin > > > Evans > > > Added more entries to the Readmes from John Scoles > > > > > > > > > Cheers > > > John Scoles > > > > > > -- > > > New! Learn why & how to love your data with Pythian's new webinar > series. > > > Topics, details & register: http://www.pythian.com/webinars > > > > > > > > -- New! Learn why & how to love your data with Pythian's new webinar series. Topics, details & register: http://www.pythian.com/webinars
Re: ANNOUNCE: DBD::Oracle 1.25 Release Candidate 1
John Scoles wrote: Nobody wants to play with my new toy? :( Well it is finally here the 'two bit' version of DBD::Oracle 1.25 You can find the release candidate here http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.25-RC1.tar.gz Any and all testing will be most welcome! The big add in this time is support for DRCP (Database Resident Connection Pool) if you happen to be using 11g or later As well 'get_info' has been greatly expanded Plus the the usual hodgepodge of bug fixes detailed below Added support for the OCIPing by John Scoles Spell checked the pod (the first time in a while me thinks) updated the todo By John Scoles Added support for DCRP (Database Resident Connection Pooling) by John Scoles with Luben Karavelov Fix for odd error with Ping from Tom Payerle Removed the NEW_OCI_INIT compile directive and the deprecated OCIInitialize calls Fix for rt.cpan.org Ticket #=57256 : Double free problem in dbdimp.c by John Scoles Fix for invalid format in trace of OCILobLocatorIsInit_log_stat reported by Martin Evans Fixed by John Scoles Fix for very odd UNKNOWN OCI STATUS 1041 (OCILobFreeTemporary) on disconnect reported by John Parker and Bob Mcgowan fixed by John Scoles Fix for rt.cpan.org Ticket #=55445: get_info(28) SQL_IDENTIFIER_CASE seems to return the wrong value from Martin J Evans and a bunch of re jigging from John Scoles Patch for PL/SQL: numeric or value error: character string buffer too small from Scott T. Hildreth Fix for rt.cpan.org Ticket #=51594 type_info and type_info_all miss vital information from John Scoles Added ora_lob_is_init function by John Scoles Fix for rt.cpan.org Ticket #=55031 Ubuntu Server Building with Oracle XE under 32-bit from Brian Candler Fix for rt.cpan.org Ticket #=56810 bug with multiple nested cursor from John Scoles Fix for bug found only on Big-Endian hardware reported by Timothy Everett and others from Charles Jardine Fix for memory leak when using prepared_cached and lobs reported by Mark Bobak and Martin Evans found and fixed by John Scoles and a test from Martin Evans Added more entries to the Readmes from John Scoles Cheers John Scoles -- New! Learn why & how to love your data with Pythian's new webinar series. Topics, details & register: http://www.pythian.com/webinars
ANNOUNCE: DBD::Oracle 1.25 Release Candidate 1
Well it is finally here the 'two bit' version of DBD::Oracle 1.25 You can find the release candidate here http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.25-RC1.tar.gz Any and all testing will be most welcome! The big add in this time is support for DRCP (Database Resident Connection Pool) if you happen to be using 11g or later As well 'get_info' has been greatly expanded Plus the the usual hodgepodge of bug fixes detailed below Added support for the OCIPing by John Scoles Spell checked the pod (the first time in a while me thinks) updated the todo By John Scoles Added support for DCRP (Database Resident Connection Pooling) by John Scoles with Luben Karavelov Fix for odd error with Ping from Tom Payerle Removed the NEW_OCI_INIT compile directive and the deprecated OCIInitialize calls Fix for rt.cpan.org Ticket #=57256 : Double free problem in dbdimp.c by John Scoles Fix for invalid format in trace of OCILobLocatorIsInit_log_stat reported by Martin Evans Fixed by John Scoles Fix for very odd UNKNOWN OCI STATUS 1041 (OCILobFreeTemporary) on disconnect reported by John Parker and Bob Mcgowan fixed by John Scoles Fix for rt.cpan.org Ticket #=55445: get_info(28) SQL_IDENTIFIER_CASE seems to return the wrong value from Martin J Evans and a bunch of re jigging from John Scoles Patch for PL/SQL: numeric or value error: character string buffer too small from Scott T. Hildreth Fix for rt.cpan.org Ticket #=51594 type_info and type_info_all miss vital information from John Scoles Added ora_lob_is_init function by John Scoles Fix for rt.cpan.org Ticket #=55031 Ubuntu Server Building with Oracle XE under 32-bit from Brian Candler Fix for rt.cpan.org Ticket #=56810 bug with multiple nested cursor from John Scoles Fix for bug found only on Big-Endian hardware reported by Timothy Everett and others from Charles Jardine Fix for memory leak when using prepared_cached and lobs reported by Mark Bobak and Martin Evans found and fixed by John Scoles and a test from Martin Evans Added more entries to the Readmes from John Scoles Cheers John Scoles -- New! Learn why & how to love your data with Pythian's new webinar series. Topics, details & register: http://www.pythian.com/webinars
Re: DBD::Oracle DRCP_1.25
Hi Luben I have incorporated your patch into the DRCP branch and I have also merged that branch back into trunk for any testing that you will be doing I would try it with the Trunk which you can find here http://svn.perl.org/modules/dbd-oracle/trunk I went with the ORA_DRCP_* for the attribute names rather than POOL. as well I made ORA_DRCP_CLASS an optional value. Some users my not want to use it. Thanks again John Scoles On Wed, Jul 7, 2010 at 4:34 PM, luben karavelov wrote: > As I have promissed, here comes the documentation patch. I am not native > speeker, so may be it will need an edit. > > Also, I have added processing of environment valiables ORA_POOL_CLASS, > ORA_POOL_MIN, ORA_POOL_MAX, ORA_POOL_INCR if there is ORA_DRCP env set. > > Best regards > luben > -- New! Learn why & how to love your data with Pythian's new webinar series. Topics, details & register: http://www.pythian.com/webinars
Re: DBD::Oracle DRCP_1.25
luben wrote: Gee didn't know anyone else was even interested in DRCP. good to see there is some interest Just to let you know that branch is in the very Beta stages so expect a few things to be missing Hello, I have noticed and installed DRCP_1.25 branch from DBD::Oracle repository. It seems to work fine but it's missing some essential functionality in order to make it usefull. So I have made local modifications (some of them are not essential) and all seems to work fine here. What I have done: 1. essential - added ora_pool_class connection attribute that sets OCI_ATTR_CONNECTION_CLASS on the obtained connection. It is essential because without setting this attribute the pooled sessions are not shared between processes. From oracle documentation: > If no connection class is specified, the OCISessionPool’s name is implicitly used > as the connection class by all the requests using that OCISessionPool, resulting in > sharing only within a session pool, becoming equivalent to a plain client-side > session pooling application. Ok would like to see the code for that/ 2.non-essential - Added ora_pool_min, ora_pool_max, ora_pool_incr connections attributes that are used to set corresponding values for OCISessionPoolCreate. - Set default values for the above parameters to follow default values of DBMS_CONNECTION_POOL package (principle of least surprise) I have those stubbed in for now as I was deliberately using small min and max for testing and will check them in soon I most likely. Part of the plan was to use the default values. I have some questions: 1. Do you have suggestions for different names of attributes? I was considering ora_drcp_class etc. but I am not sure what will be the best. ora_drcp_conn_class would be better so as not to get it confused with any other thing that might be a 'class' of some sort 2. If my work is interesting for the further development of DBD::Oracle, whom I should send a patch? Just post it here on this thread or on DBI-users just make sure it is patched from the DRCP branch. Finally, if this work is accepted I will add proper documentation for the new attributes. That would save me some trouble. Now I have a little favour to ask of you. Seems I am getting a problem with 31lob.t test 8 the sql "BEGIN ? := DBMS_LOB.GETLENGTH( ? ); END;"; does not return the correct length when used with DRCP If you have half a moment and can take a look at that one that would be great. Cheers John Scoles Best regards luben karavelov
Re: Minor issue with ping
opps didn't see the patch attachment.as it was hidden until I looked for it (stupid google mail) That should work as it is only on the perl side. Thanks Martin On Fri, Jun 18, 2010 at 8:53 AM, Martin Evans wrote: > John Scoles wrote: > > The way Ping works is different is depending on the version of > DBD::Oracle > > you are using. Which version of DBD::Oracle are you using?? > > > > cheers > > John Scoles > > The patch looked to be against the subversion trunk John. > > Martin > > > On Thu, Jun 17, 2010 at 2:34 PM, Thomas M. Payerle > wrote: > > > >> Hi, > >> > >> My colleagues and I encountered a problem in some code which seems > >> to be due to some impolite behavior on the part of the ping routine > >> in DBD::Oracle. > >> > >> Basically, we had an eval block with a locally declared (my) > CGI::Session > >> object using Oracle DB for storing session info. When we raise an > >> exception, > >> the CGI::Session is destroyed, which somewhere results in DBD::Oracle > ping > >> being called. ping() does not localize $@ for its eval block, thereby > >> clobbering the exception text in $...@. > >> > >> I believe adding a "local $@" in the ping routine resolves this issue > >> without any ill effect on the routine, as shown in attached patch. > >> Not really a bug, but I believe this is better behavior. > >> > >> Tom Payerle > >> OIT-TSS-DCS paye...@umd.edu > >> University of Maryland (301) 405-6135 > >> College Park, MD 20742-4111 > >> > >> PS: I just wanted to offer my gratitude to the DBD::Oracle developers > for > >> their fine work on this module. > > > > -- > > Catch Alex & Sheeri at ODTUG/Kaleidoscope - June 27 - July 1. > > Hear Sheeri speak or email eve...@pythian.com to meet with Pythian. > > > -- Catch Alex & Sheeri at ODTUG/Kaleidoscope - June 27 - July 1. Hear Sheeri speak or email eve...@pythian.com to meet with Pythian.
Re: Minor issue with ping
The way Ping works is different is depending on the version of DBD::Oracle you are using. Which version of DBD::Oracle are you using?? cheers John Scoles On Thu, Jun 17, 2010 at 2:34 PM, Thomas M. Payerle wrote: > Hi, > > My colleagues and I encountered a problem in some code which seems > to be due to some impolite behavior on the part of the ping routine > in DBD::Oracle. > > Basically, we had an eval block with a locally declared (my) CGI::Session > object using Oracle DB for storing session info. When we raise an > exception, > the CGI::Session is destroyed, which somewhere results in DBD::Oracle ping > being called. ping() does not localize $@ for its eval block, thereby > clobbering the exception text in $...@. > > I believe adding a "local $@" in the ping routine resolves this issue > without any ill effect on the routine, as shown in attached patch. > Not really a bug, but I believe this is better behavior. > > Tom Payerle > OIT-TSS-DCS paye...@umd.edu > University of Maryland (301) 405-6135 > College Park, MD 20742-4111 > > PS: I just wanted to offer my gratitude to the DBD::Oracle developers for > their fine work on this module. -- Catch Alex & Sheeri at ODTUG/Kaleidoscope - June 27 - July 1. Hear Sheeri speak or email eve...@pythian.com to meet with Pythian.
Re: Spelling
On Tue, Jun 8, 2010 at 7:31 AM, Tim Bunce wrote: > On Mon, Jun 07, 2010 at 09:52:52PM +0200, H.Merijn Brand wrote: > > On Mon, 07 Jun 2010 13:51:26 -0400, John Scoles > > wrote: > > > > Summary: > > > > ☑ unicode => Unicode > > ☐ DBDs<= DBD's > > ☐ DSNs<= DSN's > > ☑ unlikey => unlikely > > ☑ abreviated => abbreviated > > ☐ NULLs <= NULL's > > Yes to all (assuming the arrow direction indicates the intended change). > Thanks! > > > Things I also noted: > > > > # 'thru' => (Thur thrum Thu thou) > > isn't it "through" in English? > > My Mac OS X dictionary says: > >thru >preposition, adverb, & adjective >informal spelling of through > > I like it. > > Cogreve, Boucicault Shaw, Wilde, Becket, Freil, Behan, Swift, Sterne, Stoker, Moore, Joyce and O'Faolain are all rolling over in their graves after hearing that Tim. Shame!! :( [In case anyone's using OS X and doesn't know about this: try tapping the d key while holding down the control and command keys. > Keep the control and command keys help down afterwards. > Then, as you move the cursor about you'll see a little dictionary window > appear over any words you hover over. Very handy. Works in almost all OS > X applications including Terminal and Safari.] > > > # 'piggback' => (piggyback piggybacks piggyback's piggybacked) > > piggyback? > > piggyback. > > > # 'scaleable' => (scale able scale-able scalable saleable salable > callable) > > I prefer scaleable over scalable, but not by much. > > > I fixed "a subtile difference" to "a subtle difference". Unless sub-tile > > has some weird meaning, that looked s weird. > > subtle is right. > > > I have no idea how to change "ommiting" in: > > 'You can put every SQL-statement you like in simply ommiting > >"sql => ...", but the more important thing is to restrict the > >connection so that only allowed queries are possible.' > > I don't know what that's saying either. > > > WTF does 'Pern' mean in: > > > > But you'll note that there is only one call to > > DBD::_::db::selectrow_arrayref but another 99 to > > DBD::mysql::db::selectrow_arrayref. Currently the first > > call Pern't record the true location. That may change. > > s/Pern't/doesn't/ - I think. > > > lib/DBD/Multiplex has different/wrong line endings :( > > Currently DBD::Multiplex isn't part of the distribution. > Feel free to hack. > > > I have committed fixes to all the obvious errors. > > Thanks! > > Tim. > > p.s. Parts of the DBI docs that are unchanged since version 1.14 were > copy-edited by O'Reilly for the DBI book. > -- Catch Alex & Sheeri at ODTUG/Kaleidoscope - June 27 - July 1. Hear Sheeri speak or email eve...@pythian.com to meet with Pythian.
Re: Spelling
H.Merijn Brand wrote: On Mon, 07 Jun 2010 13:51:26 -0400, John Scoles wrote: H.Merijn Brand wrote: For my own projects (which includes two DBD's), I have been working on spell-check issues. I'm not born in an English-speaking country, nor was I raised in one, so I make errors. Probably quite a few. spell-checkers help a lot, but most work on en_US, not en_EN, and I try to at least be consistent inside a project. When I was done with my own projects, I threw my newly built utility at the perl source tree itself, and found a few mistakes as well. Then I implemented Text::Aspell into it and fixed all that it found that was obviously wrong. It supports reading local aspell lists of words that are considered to be correct for the given project. DBI documentation is written in en_EN instead of en_US, so the spell-checker will see "behaviour" as wrong and suggests "behavior". Same for "ACKNOWLEDGEMENT" vs "ACKNOWLEDGMENT". So it spells it correctly good thing. No keep the correct Canadian spelling :) 'iour' Huh? "it"? So you want to move everything to en_US? I'm really trying to be serious here (and learn). Consistency is VERY high in my goals, so IMHO we should stick to en_EN for DBI. That was my trigger to implement project specific language support. Done. Before I try to get deeper into DBI docs and its spelling, would it be considered good-work? As an example to start (this part DOES contain real errors, like abreviate (one b) and unlikey (instead of unlikely)): Summary: ☑ unicode => Unicode ☐ DBDs<= DBD's DBDs ☐ DSNs<= DSN's DSNs ☑ unlikey => unlikely ☑ abreviated => abbreviated ☐ NULLs <= NULL's NULLs are the correct ones. the 's are for possessive nouns such as " Merijn's bicycle has a flat tire" @@ -2303,7 +2303,7 @@ use by the DBI. Extensions and related modules use the C namespace (see L<http://www.perl.com/CPAN/modules/by-module/DBIx/>). Package names beginning with C are reserved for use by DBI database drivers. All environment variables used by the DBI -or by individual DBDs begin with "C" or "C". +or by individual DBD's begin with "C" or "C". the first one is correct. As you are referring to many DBDs not something that belongs to a DBD Seems like your spell checker cannot tell or (does not know) the correct use of "s" in its plural, possessive, and plural possessive. My spell checker is a perl script using Text::Aspell and doesn't know any context at all. Most likely just taking a guess based on wheather of not the first letter is capitalized. Welcome to the wonderful world of English. have a go at this http://www.meredith.edu/grammar/plural.htm : We could play this game for a long time as we here in Canada have some of our own funny ways to spell things?? Thanks for the insightful remarks. Things I also noted: # 'DEFERABILITY' => (DEFER ABILITY DEFER-ABILITY DESIRABILITY DURABILITY DIVISIBILITY) # 'deferrability' => (desirability durability divisibility) # 'DEFERRABILITY' => (DESIRABILITY DURABILITY DIVISIBILITY) The correct spelling is DEFERRABILITY though strictly speaking it is not an English word that should be used in a sentence. It is one of those messy ones where you have taken a verb 'defer' added 'able' to it (doubled the 'r' to keep the 'e' short) to get deferrable and then added 'ity' to turn it into an adjective. I'm blank on this: one or two 'r's? My "Collins Cobuild" English Language Dictionary doesn't know the word, but spells alle deferr... with two 'r's # 'implementors' => (implementers implements implementer's implement's impalement's implementer) # 'thru' => (Thur thrum Thu thou) isn't it "through" in English? yes it is even in US English it is through # 'piggback' => (piggyback piggybacks piggyback's piggybacked) piggyback? piggyback is slang and is correct with piggyback # 'scaleable' => (scale able scale-able scalable saleable salable callable) again scale is a noun so it cannot have 'able' on the end (as that should only be user on a verb) to try and turn it into a verb. One should say 'scales' or 'scale' as in does the web service scale it scales correctly is the web service able to scale up I fixed "a subtile difference" to "a subtle difference". Unless sub-tile has some weird meaning, that looked s weird. subtile is a word but it means a 'tile' under s
Re: Spelling
H.Merijn Brand wrote:
For my own projects (which includes two DBD's), I have been working on
spell-check issues. I'm not born in an English-speaking country, nor
was I raised in one, so I make errors. Probably quite a few.
spell-checkers help a lot, but most work on en_US, not en_EN, and I try
to at least be consistent inside a project.
When I was done with my own projects, I threw my newly built utility at
the perl source tree itself, and found a few mistakes as well. Then I
implemented Text::Aspell into it and fixed all that it found that was
obviously wrong. It supports reading local aspell lists of words that
are considered to be correct for the given project.
DBI documentation is written in en_EN instead of en_US, so the
spell-checker will see "behaviour" as wrong and suggests "behavior".
Same for "ACKNOWLEDGEMENT" vs "ACKNOWLEDGMENT".
So it spells it correctly good thing.
That was my trigger to implement project specific language support.
Done.
Before I try to get deeper into DBI docs and its spelling, would it be
considered good-work?
As an example to start (this part DOES contain real errors, like
abreviate (one b) and unlikey (instead of unlikely)):
--8<---
diff --git a/DBI.pm b/DBI.pm
index ad14fed..0cf59e9 100644
--- a/DBI.pm
+++ b/DBI.pm
@@ -1101,7 +1101,7 @@ sub data_diff {
sub data_string_diff {
# Compares 'logical' characters, not bytes, so a latin1 string and an
-# an equivalent unicode string will compare as equal even though their
+# an equivalent Unicode string will compare as equal even though their
# byte encodings are different.
my ($a, $b) = @_;
unless (defined $a and defined $b) { # one undef
@@ -2267,7 +2267,7 @@ Perl supports binary data in Perl strings, and the DBI
will pass binary
data to and from the driver without change. It is up to the driver
implementors to decide how they wish to handle such binary data.
-Perl supports two kinds of strings: unicode (utf8 internally) and non-unicode
+Perl supports two kinds of strings: Unicode (utf8 internally) and non-Unicode
(defaults to iso-8859-1 if forced to assume an encoding). Drivers should
accept both kinds of strings and, if required, convert them to the character
set of the database being used. Similarly, when fetching from the database
@@ -2303,7 +2303,7 @@ use by the DBI. Extensions and related modules use the
C
namespace (see L
Re: DBD::Oracle 11gr2 & ORA-38909
Had a quick look at it today give it a try agian but this time make sure you
have autocommit off
AutoCommit=>1
on the connection method
cheers
On Fri, May 21, 2010 at 11:20 AM, John Scoles wrote:
> Ok I guess it is back to square 1 on this.
>
> Unfortunetly no time to look at it today
>
> Will write up a small test script and see if my patch actually does
> anything
>
> You may hear from me in a few days
>
> Cheers
> John
>
>
>
> Scott T. Hildreth wrote:
>
>> On Fri, 2010-05-21 at 09:23 +0100, Martin Evans wrote:
>>
>>
>>> John Scoles wrote:
>>>
>>>
>>>> Ok I have patched up a solution I think will work across the board and
>>>> you
>>>> can find it here
>>>>
>>>> http://svn.perl.org/modules/dbd-oracle/branches/oci_batch
>>>>
>>>> here are the details
>>>>
>>>> ora_oci_batch
>>>>
>>>> For 11g users you may encounter an error while using the execute_array
>>>> in
>>>> that it does not
>>>> return a full list of tuples. This seems to be a result in that a
>>>> statement
>>>> can only
>>>> have 'LOG ERRORS' or 'SAVE EXCEPTIONS'set, By setting this flag to a
>>>> value
>>>> should stop this
>>>> problem error.
>>>>
>>>> For convenience I have added support for a 'ORA_DBD_OCI_BATCH'
>>>> environment variable that you can use at the OS level to set this
>>>> value. It can also be set as an attribute on both the Connect and
>>>> Prepare.
>>>>
>>>> Unfortunately I can't test it (do not have an 11g box yet) so It will
>>>> stay
>>>> in the above branch until it is tested hopefully by you Scott
>>>>
>>>> Cheers
>>>> John Scoles
>>>>
>>>> --
>>>> See Pythian's Alex Gorbachev, co-author of "Expert Oracle Practices" at
>>>> NoCOUG Spring Conference May 20th.
>>>> Details, interview & book chapter in the May NoCOUG Journal:
>>>> http://bit.ly/alexnocoug
>>>>
>>>>
>>>>
>>> I'm not sure why I seem to have ignored your mail but I just noticed it
>>> again - sorry for the delay.
>>>
>>> I checked out the branch you mentioned and
>>>
>>> export ORA_DBD_OCI_BATCH=1
>>>
>>> but 26exe_array still seems to fail for me:
>>>
>>>
>>
>> Sorry John, I meant to test earlier but its been a busy week.
>>
>> Fails for me as well.
>>
>> DB<7> x $dbh->{'ora_oci_batch'}
>> 0 1
>> DB<8> n
>> main::(../tst_exec_ary.pl:13): my $sth = $dbh->prepare("Insert into
>> TestArray Values(?, ?, ?) LOG ERRORS INTO ERR_TESTARRAY");
>> DB<8> n
>> main::(../tst_exec_ary.pl:15): $sth->bind_param_array(1, [ qw(One Uno Il
>> oNe) ]);
>> DB<8> n
>> main::(../tst_exec_ary.pl:16): $sth->bind_param_array(2, [ 2, 22, 0, 222
>> ]);
>> DB<8> n
>> main::(../tst_exec_ary.pl:17): $sth->bind_param_array(3, [ qw(20070101
>> 20080101 20090101 20060101) ]);
>> DB<8> n
>> main::(../tst_exec_ary.pl:21): $sth->execute_array({});
>> DB<8> n
>> DBD::Oracle::st execute_array failed: ORA-38909: DML Error logging is not
>> supported with BATCH ERROR mode (DBD ERROR: OCIStmtExecute) [for Statement
>> "Insert into TestArray Values(?, ?, ?) LOG ERRORS INTO ERR_TESTARRAY"] at
>> ../tst_exec_ary.pl line 21.
>> at ../tst_exec_ary.pl line 21
>>
>>
>>
>>> mar...@bragi:~/svn/dbd-oracle/branches/oci_batch$ prove -vb
>>> t/26exe_array.t
>>> t/26exe_array.t ..
>>> 1..17
>>> ok 1 - use DBI;
>>> ok 2 - The object isa DBI::db
>>> ok 3 - ... execute_array should return true
>>> ok 4 - ... we should have 10 tuple_status
>>> ok 5 - ... execute_array should return false
>>> ok 6 - ... we should have 10 tuple_status
>>> ok 7 - ... we should get text
>>> ok 8 - ... we should get -1
>>> ok 9 - ... we should get a warning
>>> ok 10 - ... execute_for_fetch should return true
>>> not ok 11 - ... we should have 19 tuple_status
>>>
>>> # Failed test '... we should have 19 tuple_status'
>>> # at t/26exe_array.t line 128.
>>> # got: 10
>>> # expected: 19
>>> ok 12 - ... execute_array should return flase
>>> ok 13 - ... we should have 10 tuple_status
>>> not ok 14 - ... we should have 48 rows
>>>
>>> # Failed test '... we should have 48 rows'
>>> # at t/26exe_array.t line 154.
>>> # got: 30
>>> # expected: 48
>>> ok 15 - ... execute_array should return true
>>> ok 16 - ... \#5 should be a warning
>>> ok 17 - ... we should have 10 tuple_status
>>> # Looks like you failed 2 tests of 17.
>>> Dubious, test returned 2 (wstat 512, 0x200)
>>> Failed 2/17 subtests
>>>
>>> Test Summary Report
>>> ---
>>> t/26exe_array.t (Wstat: 512 Tests: 17 Failed: 2)
>>> Failed tests: 11, 14
>>> Non-zero exit status: 2
>>> Files=1, Tests=17, 0 wallclock secs ( 0.02 usr 0.01 sys + 0.05 cusr
>>> 0.01 csys = 0.09 CPU)
>>> Result: FAIL
>>>
>>> This was using oracle 11.1 server and 11.1 instant client.
>>>
>>> If I've not set the right thing let me know.
>>>
>>> Martin
>>>
>>>
>>
>>
>>
>
>
>
--
Catch Alex & Sheeri at ODTUG/Kaleidoscope - June 27 - July 1.
Hear Sheeri speak or email eve...@pythian.com to meet with Pythian.
Re: DBD::Oracle 11gr2 & ORA-38909
Ok I guess it is back to square 1 on this.
Unfortunetly no time to look at it today
Will write up a small test script and see if my patch actually does anything
You may hear from me in a few days
Cheers
John
Scott T. Hildreth wrote:
On Fri, 2010-05-21 at 09:23 +0100, Martin Evans wrote:
John Scoles wrote:
Ok I have patched up a solution I think will work across the board and you
can find it here
http://svn.perl.org/modules/dbd-oracle/branches/oci_batch
here are the details
ora_oci_batch
For 11g users you may encounter an error while using the execute_array in
that it does not
return a full list of tuples. This seems to be a result in that a statement
can only
have 'LOG ERRORS' or 'SAVE EXCEPTIONS'set, By setting this flag to a value
should stop this
problem error.
For convenience I have added support for a 'ORA_DBD_OCI_BATCH'
environment variable that you can use at the OS level to set this
value. It can also be set as an attribute on both the Connect and Prepare.
Unfortunately I can't test it (do not have an 11g box yet) so It will stay
in the above branch until it is tested hopefully by you Scott
Cheers
John Scoles
--
See Pythian's Alex Gorbachev, co-author of "Expert Oracle Practices" at NoCOUG
Spring Conference May 20th.
Details, interview & book chapter in the May NoCOUG Journal:
http://bit.ly/alexnocoug
I'm not sure why I seem to have ignored your mail but I just noticed it
again - sorry for the delay.
I checked out the branch you mentioned and
export ORA_DBD_OCI_BATCH=1
but 26exe_array still seems to fail for me:
Sorry John, I meant to test earlier but its been a busy week.
Fails for me as well.
DB<7> x $dbh->{'ora_oci_batch'}
0 1
DB<8> n
main::(../tst_exec_ary.pl:13): my $sth = $dbh->prepare("Insert into TestArray
Values(?, ?, ?) LOG ERRORS INTO ERR_TESTARRAY");
DB<8> n
main::(../tst_exec_ary.pl:15): $sth->bind_param_array(1, [ qw(One Uno Il oNe)
]);
DB<8> n
main::(../tst_exec_ary.pl:16): $sth->bind_param_array(2, [ 2, 22, 0, 222 ]);
DB<8> n
main::(../tst_exec_ary.pl:17): $sth->bind_param_array(3, [ qw(20070101
20080101 20090101 20060101) ]);
DB<8> n
main::(../tst_exec_ary.pl:21): $sth->execute_array({});
DB<8> n
DBD::Oracle::st execute_array failed: ORA-38909: DML Error logging is not supported with
BATCH ERROR mode (DBD ERROR: OCIStmtExecute) [for Statement "Insert into TestArray
Values(?, ?, ?) LOG ERRORS INTO ERR_TESTARRAY"] at ../tst_exec_ary.pl line 21.
at ../tst_exec_ary.pl line 21
mar...@bragi:~/svn/dbd-oracle/branches/oci_batch$ prove -vb t/26exe_array.t
t/26exe_array.t ..
1..17
ok 1 - use DBI;
ok 2 - The object isa DBI::db
ok 3 - ... execute_array should return true
ok 4 - ... we should have 10 tuple_status
ok 5 - ... execute_array should return false
ok 6 - ... we should have 10 tuple_status
ok 7 - ... we should get text
ok 8 - ... we should get -1
ok 9 - ... we should get a warning
ok 10 - ... execute_for_fetch should return true
not ok 11 - ... we should have 19 tuple_status
# Failed test '... we should have 19 tuple_status'
# at t/26exe_array.t line 128.
# got: 10
# expected: 19
ok 12 - ... execute_array should return flase
ok 13 - ... we should have 10 tuple_status
not ok 14 - ... we should have 48 rows
# Failed test '... we should have 48 rows'
# at t/26exe_array.t line 154.
# got: 30
# expected: 48
ok 15 - ... execute_array should return true
ok 16 - ... \#5 should be a warning
ok 17 - ... we should have 10 tuple_status
# Looks like you failed 2 tests of 17.
Dubious, test returned 2 (wstat 512, 0x200)
Failed 2/17 subtests
Test Summary Report
---
t/26exe_array.t (Wstat: 512 Tests: 17 Failed: 2)
Failed tests: 11, 14
Non-zero exit status: 2
Files=1, Tests=17, 0 wallclock secs ( 0.02 usr 0.01 sys + 0.05 cusr
0.01 csys = 0.09 CPU)
Result: FAIL
This was using oracle 11.1 server and 11.1 instant client.
If I've not set the right thing let me know.
Martin
Re: DBD::Oracle 11gr2 & ORA-38909
Ok I have patched up a solution I think will work across the board and you can find it here http://svn.perl.org/modules/dbd-oracle/branches/oci_batch here are the details ora_oci_batch For 11g users you may encounter an error while using the execute_array in that it does not return a full list of tuples. This seems to be a result in that a statement can only have 'LOG ERRORS' or 'SAVE EXCEPTIONS'set, By setting this flag to a value should stop this problem error. For convenience I have added support for a 'ORA_DBD_OCI_BATCH' environment variable that you can use at the OS level to set this value. It can also be set as an attribute on both the Connect and Prepare. Unfortunately I can't test it (do not have an 11g box yet) so It will stay in the above branch until it is tested hopefully by you Scott Cheers John Scoles -- See Pythian's Alex Gorbachev, co-author of "Expert Oracle Practices" at NoCOUG Spring Conference May 20th. Details, interview & book chapter in the May NoCOUG Journal: http://bit.ly/alexnocoug
Re: DBD::Oracle 11gr2 & ORA-38909
On Tue, Apr 6, 2010 at 4:51 AM, Martin Evans wrote:
> I haven't seen a reply to this yet but I've been on holiday so might
> have missed it:
>
> Scott T. Hildreth wrote:
> > On Wed, 2010-03-31 at 12:20 -0500, Scott T. Hildreth wrote:
> >> We have run into an issue with array processing in 11g. The developer
> >> was using execute_array and his sql statement had 'LOG ERRORS' in it.
> >> This did not error out until we switched to 11g. The issue is that only
> >> one is allowed, either 'LOG ERRORS' or 'SAVE EXCEPTIONS'. Our DBA
> >> logged and error report with Oracle and after several posts back and
> >> forth this is what they concluded,
> >>
> >> ==
> >> After investigation and discussion, development has closed the bug as
> >> 'Not a Bug' with the following reason:
> >>
> >> "this is an expected behavior in 11g and the user needs to specify
> >> either of 'SAVE EXCEPTIONS' clause or the 'DML error logging', but NOT
> >> both together.
> >> The batch error mode, in the context of this bug, is basically referring
> >> to the SAVE EXCEPTIONS clause.
> >> It seems the code is trying to use both dml error logging and batch
> >> error handling for the same insert. In that case, this is not a bug.
> >>
> >> For INSERT, the data errors are logged in an error logging table (when
> >> the dml error logging feature is used) or returned in batch error
> >> handles (when using batch mode).
> >> Since the error messages are available to the user in either case, there
> >> is no need to both log the error in the error logging table and return
> >> the errors in batch error handles,
> >> and we require the user to specify one option or the other but not both
> >> in 11G.
> >>
> >> Both features exist in 10.x. For 11.x, users should change their
> >> application to avoid the error.
> >> ==
> >>
> >> So basically we need a way to turn off the 'SAVE EXCEPTIONS' for the
> >> batch mode. I found in dbdimp.c that the oci_mode is being set to
> >> OCI_BATCH_ERRORS in the ora_st_execute_array function. I was planning
> >> on setting it to OCI_BATCH_MODE and running a test to see if this will
> >> not error out. I report back when I have run the test, but I was
> >> wondering what would be the best way to give the user the ability to
> >> override the oci_mode.
> >
> > Setting oci_mode to OCI_BATCH_MODE works. So I want to add a prepare
> > attribute that will turn off the SAVE EXCEPTIONS. I'm looking for some
> > direction on how to add it to dbdimp.c. I haven't thought of a name yet,
> > but something like
> >
> > my $sth = $dbh->prepare($SQL,{ora_oci_err_mode => 0});
> >
> > I assume I would have to add it to dbd_db_FETCH_attrib() and would I do
> > something like this in ora_st_execute_array(),
>
> Don't you mean dbd_st_FETCH_attrib as it is a statement level attribute
> not a connection one? Anyway, I don't think it is required unless you
> really want to get it back out in a Perl script.
>
> I don't even think you need to add it to a statements
> private_attribute_info but then when I checked Oracle.pm it appears a
> load of prepare flags have been added. I might be wrong here but since
> there is no way to get ora_parse_lang etc (prepare attributes) I don't
> think they should be in private_attribute_info.
>
> Well it should be in there along with the other ones I forgot to add;)
if one looks at the dbi spec
private_attribute_info <#___top>
Returns a reference to a hash whose keys are the names of driver-private
attributes available for the kind of handle (driver, database, statement)
that the method was called on.
The values should be undef. Meanings may be assigned to particular values in
future.
> perl -e 'use DBI;$h =
> DBI->connect("dbi:Oracle:host=xxx;sid=yyy","xxx","yyy"); $s =
> $h->prepare("select 1 from dual", {ora_parse_lang => 2}); print
> $s->{ora_parse_lang};'
>
> prints nothing as you'd expect as there is no way to get ora_parse_lang.
>
>
as it should be since you are setting these why would you want to get their
values??
> > if (DBD_ATTRIB_TRUE(attr,"ora_oci_err_mode",16,svp))
> > DBD_ATTRIB_GET_IV( attr, "ora_oci_err_m
Re: DBI and do
Knoerl, Thomas wrote:
Hello folks,
Is there are reason why the 'select(all|col)_*' methods support the handover of
statement handles, but 'do' doesn't?
$dbh->do() is what I would call a 'short hand' or 'short cut' method.
It was included for the CUD (Creation Update Delete) operations of SQL
as any of these operations do not involve the return of a 'Result Set'.
All it is doing is wrapping the the 'prepare' and 'execute' into the
'do' and it does not bother to return a 'Statement' handle. It is not a
'Magic' function the eliminates a step or two.
In fact there is a big performance hit when using 'do' repeatedly, say
in the insertion of may row into a db, You would be doing a prepare many
hundreds of times over when you could just
do it once and just change the params ie
Very slow
foreach my $i (1...100){
$dbh->do("insert into bla (id) values ($i)");
}
much faster
my $sth =$dbh->prepare("insert into bla (id) values (?)");
foreach my $i (1...100){
$sth->execute($i);
}
Even faster the execute_array
my @in_values=(1...1000)
my @status;
my $sth = $dbh->prepare(qq{ INSERT INTO foo (id, bar) VALUES ( ?) });
$sth->bind_param_array(1,\...@in_values);
$sth->execute_array({ArrayTupleStatus=>\...@status}) or die "error inserting";
Is it possible to change the statement assignment?
Current:
my $sth = $dbh->prepare($statement, $attr) or return undef;
Suggested:
my $sth = (ref $stmt) ? $stmt : $dbh->prepare($stmt, $attr) or return;
The suggested solution makes it possible to work with cached statements and
would therefore, in my opinion, improve performance.
You will have to ask Tim Bunce or maybe Alligator Descartes for that
change but I do not see any use in having it.
A statement handle without 'SQL' ( a statement) would be meaningless,
something like a sentence with only punctuation.
cheers
John Scoles
With best regards,
Thomas Knoerl
Siemens AG
Siemens IT Solutions and Services
SIS IT SM CP
Wuerzburger Str. 121
90766 Fuerth, Germany
Tel.: +49 (911) 978-3034
Fax: +49 (911) 978-2037
Mobile: +49 (1522) 2795353
mailto:thomas.kno...@siemens.com
Siemens Aktiengesellschaft: Chairman of the Supervisory Board: Gerhard Cromme;
Managing Board: Peter Loescher, Chairman, President and Chief Executive
Officer; Wolfgang Dehen, Heinrich Hiesinger, Joe Kaeser, Barbara Kux, Hermann
Requardt, Siegfried Russwurm, Peter Y. Solmssen; Registered offices: Berlin and
Munich, Germany; Commercial registries: Berlin Charlottenburg, HRB 12300,
Munich, HRB 6684; WEEE-Reg.-No. DE 23691322
Re: Comments on this patch for dbms_output_get() ?
simple enough I will put it in the 1.25 release
I created this patch because of this issue (which we have run into),
==
Parameter Description
line Returns a single line of buffered information,
excluding a final newline character. You should
declare the actual for this parameter as VARCHAR2 (32767)
to avoid the risk of "ORA-06502: PL/SQL: numeric or value error:
character string buffer too small".
status If the call completes successfully, then the status returns as 0.
If there are no more lines in the buffer, then the status is 1.
==
--- Oracle.pm.orig 2010-03-31 15:27:16.0 -0500
+++ Oracle.pm 2010-03-31 16:09:37.0 -0500
@@ -766,8 +766,11 @@
my $sth = $dbh->prepare_cached("begin
dbms_output.get_line(:l, :s); end;")
or return;
my ($line, $status, @lines);
+ my $version = join ".", @{ ora_server_version($dbh) }[0..1];
+ my $len = $version >= 10.2 ? 32767 : 400;
+
# line can be greater that 255 (e.g. 7 byte date is expanded on
output)
- $sth->bind_param_inout(':l', \$line, 400, { ora_type => 1 });
+ $sth->bind_param_inout(':l', \$line, $len, { ora_type => 1 });
$sth->bind_param_inout(':s', \$status, 20, { ora_type => 1 });
if (!wantarray) {
$sth->execute or return undef;
Thanks,
Scott
Re: ANNOUNCE: DBD::Oracle 1.24 Release Candidate 5
Great looks like a release on Monday cheers John Scoles wrote: Horray. Just one more thing has come up in like the last 15min can you redo your the make test with the following file http://svn.perl.org/modules/dbd-oracle/trunk/ocitrace.h It is a trivial change but just want to make sure it does not break anything It still compiles without warnings and passes all its tests. if is a change to precision for OCIDateTimeToText to 6 instead of 0 for varrays of timestamps needed to conduct a large scale experiment of some sort cheers John Scoles Charles Jardine wrote: On 28/01/10 15:59, John Scoles wrote: Well here comes the big #5 It can be found at the usual place http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.24-RC5.tar My environment is Linux x86-64, Perl 5.10.1 (64 bit), DBI 1.609, Oracle 10.2.0.4.2 (64 bit). Database charset UTF8, national charset AL16UTF16. RC5 compiles without warnings and passes all its tests, including the regression tests for my object patches. I have run some sample work at trace level 15 - there are no segfaults. In short, I can't find anything wrong with it.
Re: ANNOUNCE: DBD::Oracle 1.24 Release Candidate 5
Horray. Just one more thing has come up in like the last 15min can you redo your the make test with the following file http://svn.perl.org/modules/dbd-oracle/trunk/ocitrace.h It is a trivial change but just want to make sure it does not break anything if is a change to precision for OCIDateTimeToText to 6 instead of 0 for varrays of timestamps needed to conduct a large scale experiment of some sort cheers John Scoles Charles Jardine wrote: On 28/01/10 15:59, John Scoles wrote: Well here comes the big #5 It can be found at the usual place http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.24-RC5.tar My environment is Linux x86-64, Perl 5.10.1 (64 bit), DBI 1.609, Oracle 10.2.0.4.2 (64 bit). Database charset UTF8, national charset AL16UTF16. RC5 compiles without warnings and passes all its tests, including the regression tests for my object patches. I have run some sample work at trace level 15 - there are no segfaults. In short, I can't find anything wrong with it.
ANNOUNCE: DBD::Oracle 1.24 Release Candidate 5
Well here comes the big #5 It can be found at the usual place http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.24-RC5.tar This time round was a patch to fix a patch for some warnings that was causing a seg-fault on some 32 bit boxes Cheers and thanks for all the testing John Scoles
ANNOUNCE: DBD::Oracle 1.24 Release Candidate 4
Well here comes #4 It can be found at the usual place http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.24-RC4.tar This time round I added a little patch from Charles Jardine fro objects and some fixes for warnings I have changed the ora_ncs_buff_mtpl default value back to 4 so it doesn't muck anyone up and have made it so it reports better and have updated the Pod to reflect this Hopefully this will get most of them this time round Cheers and thanks for all the testing John S
Re: ANNOUNCE: DBD::Oracle 1.24 Release Candidate 3
At a first look it most likely is my new
ora_ncs_buff_mtpl /ORA_DBD_NCS_BUFFER might be the source of the problem
byt default it is 1 which may be too small.
Can you set the ORA_DBD_NCS_BUFFER to 2 then 3 and 4 to see if it
cleans up the problem
My guess is that 1 is too small and I might have to make it 2 to cover
more bases.
cheers
John Scoles
Martin Evans wrote:
John Scoles wrote:
Thank Charles that is really good stuff
I have not investigated the
NLS_LANG=.WE8ISO8859P1, but the tests 30long.t and 31lob_extended.t
still fail badly if NLS_LANG=.AL32UTF8.
bug yet as my local test box is just US7ASCII.
BTW can you tell me what the '
NLS_CHARACTERSET and
NLS_NCHAR_CHARACTERSET
setting of your Oralcle DB you are testing on
I will have to do that later today or tonight as I have to install a
different version or Oracle to get that to fail (I hope)
If you can set $dbh->{dbd_verbose}=15 just before the test start to fail in
30long.t and 31lob_extended.t
and send me the results I will have something more to go on.
Look for another RC in the next day or two.
Cheers
Jardine wrote:
On 14/01/10 12:19, Charles Jardine wrote:
On 12/01/10 12:07, John Scoles wrote:
Ok third time is a Charm
The Third RC of the beer edition of DBD::Oracle 1.24 can be found at
http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.24-RC3.tar
This round has a few little patches from Martin Evans on it.
Please test and enjoy
My environment is Linux x86-64, Perl 5.10.1 (64 bit), DBI 1.609,
Oracle 10.2.0.4.2 (64 bit). Database charset UTF8, national
charset AL16UTF16
Three things:
[snip]
3. Here is a patch which removes the remaining warnings detected by
gcc in 64-bit mode.
[snip]
I realise that something has wrapped the very long lines in the patch,
so I am trying again, sending the patch as an attachment.
For me tests 40long.t and 30lob_extended.t also fail when $NLS_LANG is
AMERICAN_AMERICA.AL32UTF8. The values of NLS_CHARACTERSET and
NLS_NCHAR_CHARACTERSET in my database are:
AL32UTF8 and
UTF8
as the test output below shows.
The errors I get are:
prove -vb t/30long.t
t/30long.t ..
1..479
# ora_server_version: 11 1 0 6 0
# Database 11.1.0.6.0 CHAR set is AL32UTF8 (Unicode), NCHAR set is UTF8
(Unicode)
# Client 11.1.0.6 NLS_LANG is 'AMERICAN_AMERICA.AL32UTF8', NLS_NCHAR is
''
#
#
=
# Running long test for LONG (0) use_utf8_data=0
# create table dbd_ora__drop_me ( idx integer, lng LONG, dt date )
# long_data[0] length 10240
# long_data[1] length 81920
# long_data[2] length 71680
# --- insert some LONG data (ora_type 0)
ok 1 - prepare: insert into dbd_ora__drop_me values (?, ?, SYSDATE)
ok 2 - insert long data 40
ok 3 - insert long data 41
ok 4 - insert long data 42
ok 5 - insert long data undef 43
# --- fetch LONG data back again -- truncated - LongTruncOk == 1
# LongReadLen 20, LongTruncOk 1
ok 6 - prepare: select * from dbd_ora__drop_me order by idx
ok 7 - execute: select * from dbd_ora__drop_me order by idx
ok 8 - fetch_arrayref for select * from dbd_ora__drop_me order by idx
ok 9 - four rows
ok 10 - byte_string test of truncated to LongReadLen 20
ok 11 - nice_string test of truncated to LongReadLen 20
ok 12 - LONG UTF8 setting
ok 13 - byte_string test of truncated to LongReadLen 20
ok 14 - nice_string test of truncated to LongReadLen 20
ok 15 - LONG UTF8 setting
ok 16 - byte_string test of truncated to LongReadLen 20
ok 17 - nice_string test of truncated to LongReadLen 20
ok 18 - LONG UTF8 setting
ok 19 - last row undefined
ok 20 - prepare select * from dbd_ora__drop_me order by idx
# --- fetch LONG data back again -- truncated - LongTruncOk == 0
# LongReadLen 81910, LongTruncOk
ok 21 - execute select * from dbd_ora__drop_me order by idx
ok 22 - fetchrow_arrayref select * from dbd_ora__drop_me order by idx
ok 23 - length tmp->[1] 10240
ok 24 - truncation error not triggered (LongReadLen 81910, data 10240)
ok 25 - tmp==1406 || tmp==24345 tmp actually=24345
# --- fetch LONG data back again -- complete - LongTruncOk == 0
# LongReadLen 82920, LongTruncOk
ok 26 - prepare: select * from dbd_ora__drop_me order by idx
ok 27 - execute select * from dbd_ora__drop_me order by idx
ok 28 - fetchrow_arrayref select * from dbd_ora__drop_me order by idx
ok 29 - Strings are identical, Len 10240
ok 30 - fetchrow_arrayref select * from dbd_ora__drop_me order by idx
ok 31 - Strings are identical, Len 10240
ok 32 - fetchrow_arrayref select * from dbd_ora__drop_me order by idx
ok 33 - Strings are identical, Len 10240
ok 34 # skip blob_read tests for LONGs - not currently supported
ok 94 # skip ora_auto_lob tests for LONGs - not supported
#
#
=
# Running long test for LONG RAW (24) use_utf8_data=0
ok 95 - prepare: insert into dbd_ora
Re: ANNOUNCE: DBD::Oracle 1.24 Release Candidate 3
Thank Charles that is really good stuff
I have not investigated the
NLS_LANG=.WE8ISO8859P1, but the tests 30long.t and 31lob_extended.t
still fail badly if NLS_LANG=.AL32UTF8.
bug yet as my local test box is just US7ASCII.
BTW can you tell me what the '
NLS_CHARACTERSET and
NLS_NCHAR_CHARACTERSET
setting of your Oralcle DB you are testing on
I will have to do that later today or tonight as I have to install a
different version or Oracle to get that to fail (I hope)
If you can set $dbh->{dbd_verbose}=15 just before the test start to fail in
30long.t and 31lob_extended.t
and send me the results I will have something more to go on.
Look for another RC in the next day or two.
Cheers
Jardine wrote:
On 14/01/10 12:19, Charles Jardine wrote:
On 12/01/10 12:07, John Scoles wrote:
Ok third time is a Charm
The Third RC of the beer edition of DBD::Oracle 1.24 can be found at
http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.24-RC3.tar
This round has a few little patches from Martin Evans on it.
Please test and enjoy
My environment is Linux x86-64, Perl 5.10.1 (64 bit), DBI 1.609,
Oracle 10.2.0.4.2 (64 bit). Database charset UTF8, national
charset AL16UTF16
Three things:
[snip]
3. Here is a patch which removes the remaining warnings detected by
gcc in 64-bit mode.
[snip]
I realise that something has wrapped the very long lines in the patch,
so I am trying again, sending the patch as an attachment.
ANNOUNCE: DBD::Oracle 1.24 Release Candidate 2
Ok third time is a Charm The Third RC of the beer edition of DBD::Oracle 1.24 can be found at http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.24-RC3.tar This round has a few little patches from Martin Evans on it. Please test and enjoy Cheers John Scoles
ANNOUNCE: DBD::Oracle 1.24 Release Candidate 2
Well here it is the second crack at 1.24 Beer version of DBD::ORACLE You can find the release candidate here http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.24-RC2.tar this time out I have hopefully fixed most of the warnings I have also updated the pod to explain what ora_ncs_buff_mtpl and var ORA_DBD_NCS_BUFFER do I have also added a few other little patches from H.Merijn Brand The big one this RC is that I think I finally got the RowsInCache and RowCacheSize working according to spec RowCacheSize can now be set on the statement handle (in the prepare) and RowsInCache has been implemented as a Read only value off the statement handle as well Both the Statement and the DB handle versions of RowsInCache also now decrement correctly (nothing fancy in the change just moved the decrement later on in the code path) Hope this cleans any bugs up. cheers and thanks for the testing
ANNOUNCE: DBD::Oracle 1.24 Release Candidate 1
Well here it is the long awaited 1.24 Beer version of DBD::ORACLE http://sctvguide.ca/images/bd_two-four.jpg You can find the release candidate here http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.24-RC1.tar Any and all testing will be most welcome! Well a big load of stuff this time. A number of patches and bug fixes plus with this RC I am introducing a two really big features 1) Full support for a multiple records from a single Fetch. This should really speed things up as it cuts down on round trips to the server 2) I have added ora_ncs_buff_mtpl or environment var ORA_DBD_NCS_BUFFER so you can control the size of the byte buffer for lobs. So rather than a default buffer 4* the Long_Read_Length or it is now 1. This should free up great hoards of memory for your LOB Fetches anyway here is a complete list Added extended support for 64 bit clients in Makefile.PL from Ralph Doncaster Added extended nvarchar support from Jan Mach Added support for the TYPE attribute on bind_col and the new DBI bind_col attributes StrictlyTyped and DiscardString from Martin J. Evans Added ora_ncs_buff_mtpl and environment var ORA_DBD_NCS_BUFFER so we can control the size of the buffer when doing nclob reads Fix for bug in for changes to row fetch buffer mostly lobs and object fetches Fix for rt.cpan.org Ticket #=49741 Oracle.h has commented out params in OCIXMLTypeCreateFromSrc from Kartik Thakore Added from rt.cpan.org Ticket #=49436 Patch to add support for a few Oracle data types to type_info_all from David Hull Added from rt.cpan.org Ticket #=49435 Patch to add support for a few Oracle data types to dbd_describe from David Hull Fix for rt.cpan.org Ticket #=49331 Bad code example in POD from John Scoles Added support for looking up OCI_DTYPE_PARAM Attributes Added support for looking up csform values Fix for rt.cpan.org Ticket #=46763,46998 enhancement -Rowcache size is now being properly implemented with row fetch buffer from John Scoles Fix for rt.cpan.org Ticket #=46448 enhancement -Errors returned by procedures are now unicode strings from Martin Evans, John Scoles and Tim Bunce Fix for rt.cpan.org Ticket #=47503 bugfix - using more than 1 LOB in insert broken from APLA Fix for rt.cpan.org Ticket #=46613 bugfix - sig-abort on nested objects with ora_objects=1 from TomasP Fix for rt.cpan.org Ticket #=46661 DBD::Oracle hungs when insert/update with LOB and quoted table name from APLA Fix for rt.cpan.org Ticket #=46246 fetching from nested cursor (returned from procedure) leads to application crash (abort) from John Scoles Fix for rt.cpan.org Ticket #=46016 LOBs bound with ora_field broken from RKITOVER Fix for bug in 58object.t when test run as externally identified user from Charles Jardine
Re: Patch to DBD::Oracle 1.23
Ok that will be the next one to get done thanks Jan Mach wrote: We have published len_char_size property of statement handle. Why? That's because of nvarchar type. You have column nvarchar(20) and you get length 40 using PRECISION (it's right because is BYTE length). But we need for our application CHAR length and it's done usign len_char_size. Regards, Jan Mach DERS s.r.o.
Re: DBD-Oracle-1.23 Makefile.PL patch
Of you could give it a try on 64 bit power solaris it you want to tug all of your hair out. H.Merijn Brand wrote: On Fri, 27 Nov 2009 10:41:19 -0400, Ralph Doncaster wrote: On 11/27/09, H.Merijn Brand wrote: On Fri, 27 Nov 2009 10:21:05 -0400, Ralph Doncaster wrote: While you're at it, I'd like to see some more changes: * make /usr/share/oracle not hardcoded but variable We're running Oracle & perl on a few Unix boxes without a /usr/share/oracle, so it seems it's not required in some cases. Our install is in /usr/oracle, and there's no oracle link in /usr/share. There is a (huge) difference between a full Oracle install (server) or an Instant Client only. I prefer the latter if the box is to be client only, because it is much smaller. * make Instant Client work on windows too (Windows instant client works fine with the current Makefile.PL if installed on C:\Oracle\instantclient_11_1, but it does NOT work at all when installed in C:\Program Files\Oracle\... due to the space (I tried yesterday with the newest Strawberry) Sorry, I only do perl on Unix. So want I, but those silly customers ... That being said, I think Oracle on Windows is still easier than Oracle on AIX, which is even worse. That being said, Postgres is easier on *any* platform (and faster) than Oracle, but again, those silly customers ...
