Buck's Journal http://use.perl.org/~Buck/journal/ Buck's use Perl Journal en-us use Perl; is Copyright 1998-2006, Chris Nandor. Stories, comments, journals, and other submissions posted on use Perl; are Copyright their respective owners. 2012-01-25T02:38:14+00:00 pudge pudge@perl.org Technology hourly 1 1970-01-01T00:00+00:00 Buck's Journal http://use.perl.org/images/topics/useperl.gif http://use.perl.org/~Buck/journal/ Frustration with DBD::ADO and Oracle http://use.perl.org/~Buck/journal/18811?from=rss <p>I've been trying to use DBD::ADO 2.84 (the latest available via ActiveState PPM). I wrote up a simple script to perform a SELECT from Oracle 8.1.7:</p><blockquote><div><p> <tt>use strict;<br>use DBI qw(:sql_types);<br> <br>my $userid = "****";<br>my $password = "****";<br>my $sql = 'SELECT SYSDATE FROM DUAL';<br>my $dsn = "Provider=OraOLEDB.Oracle;Data Source=****;";<br> <br>unlink 'dbitrace.log' if -e 'dbitrace.log';<br>DBI-&gt;trace(1, 'dbitrace.log');<br> <br>my $dbh = DBI-&gt;connect("dbi:ADO:$dsn", $userid, $password,<br>&nbsp; &nbsp; { RaiseError =&gt; 1, AutoCommit =&gt; 0 });<br> <br>my $sth = $dbh-&gt;prepare($sql);<br>$sth-&gt;execute();<br>$sth-&gt;dump_results();<br>$sth-&gt;finish(); <br> $dbh-&gt;disconnect();</tt></p></div> </blockquote><p>Up to the execute(), it's OK. I then get the error:</p><blockquote><div><p> <tt>&gt;perl -w OneWorld_Table_Description.pl<br>Use of uninitialized value in subroutine entry at E:/Perl/site/lib/DBI.pm line 587.<br>DBD::ADO::st execute failed: Can't execute statement 'SELECT SYSDATE FROM DUAL':<br>&nbsp; &nbsp; Description : Multiple-step operation completed with one or more errors. Check each status value.<br>&nbsp; &nbsp; HelpContext<nobr> <wbr></nobr>:<br>&nbsp; &nbsp; &nbsp; &nbsp;HelpFile<nobr> <wbr></nobr>:<br>&nbsp; &nbsp; NativeError<nobr> <wbr></nobr>:<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Number : 265946<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Source : OraOLEDB<br>&nbsp; &nbsp; &nbsp; &nbsp;SQLState<nobr> <wbr></nobr>:<br>&nbsp; &nbsp; &nbsp;at OneWorld_Table_Description.pl line 42.<br>DBD::ADO::st execute failed: Can't execute statement 'SELECT SYSDATE FROM DUAL':<br>&nbsp; &nbsp; Description : Multiple-step operation completed with one or more errors. Check each status value.<br>&nbsp; &nbsp; HelpContext<nobr> <wbr></nobr>:<br>&nbsp; &nbsp; &nbsp; &nbsp;HelpFile<nobr> <wbr></nobr>:<br>&nbsp; &nbsp; NativeError<nobr> <wbr></nobr>:<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Number : 265946<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Source : OraOLEDB<br>&nbsp; &nbsp; &nbsp; &nbsp;SQLState<nobr> <wbr></nobr>:<br>&nbsp; &nbsp; &nbsp;at OneWorld_Table_Description.pl line 42.<br>&gt;Exit code: 255</tt></p></div> </blockquote><p>The output from the DBI trace (level 1):</p><blockquote><div><p> <tt>&nbsp; &nbsp; DBI 1.42-ithread default trace level set to Ox1/0 (in pid 1876)<br>&nbsp; &nbsp; -&gt; DBI-&gt;connect(dbi:ADO:Provider=OraOLEDB.Oracle;Data Source=****;, ****, ****, HASH(0x15d5700))<br>&nbsp; &nbsp; -&gt; DBI-&gt;install_driver(ADO) for MSWin32 perl=5.008003 pid=1876 ruid=0 euid=0<br>&nbsp; &nbsp; &nbsp; &nbsp;install_driver: DBD::ADO version 2.84 loaded from E:/Perl/site/lib/DBD/ADO.pm<br>&nbsp; &nbsp; &lt;- install_driver= DBI::dr=HASH(0x1b57384)<br>-&gt;ADO Connection:&nbsp; &nbsp; &nbsp;&lt;- STORE('Provider' 'OraOLEDB.Oracle')= 'OraOLEDB.Oracle' at ADO.pm line 146<br>-&gt;&gt; Storing Provider OraOLEDB.Oracle<br>-&gt;&gt; Open ADO connection using Data Source=****<br>-&gt;&gt; Transaction support: 2 Transactions can contain DML statements.&nbsp; DDL statements within a transaction cause the transaction to be committed.<br>1&nbsp; &nbsp;&lt;&gt; FETCH('Warn')= 0 ('Warn' from cache) at ADO.pm line 1290<br>&nbsp; &nbsp; &lt;- STORE('Active' 1)= 1 at ADO.pm line 208<br>1&nbsp; &nbsp;&lt;&gt; FETCH('AutoCommit')= 1 ('AutoCommit' from cache) at ADO.pm line 1301<br>1&nbsp; &nbsp;&lt;&gt; FETCH('Warn')= 0 ('Warn' from cache) at ADO.pm line 1290<br>&nbsp; &nbsp; &lt;- connect= DBI::db=HASH(0x1cc814c)<br>1&nbsp; &nbsp;&lt;&gt; FETCH('Warn')= 0 ('Warn' from cache) at ADO.pm line 1290<br>&nbsp; &nbsp; &lt;- STORE('LongReadLen' 0)= 1 at ADO.pm line 450<br>&nbsp; &nbsp; &lt;- STORE('LongTruncOk' 0)= 1 at ADO.pm line 451<br>&nbsp; &nbsp; &lt;- STORE('RowsInCache' 0)= 0 at ADO.pm line 456<br>&nbsp; &nbsp; &lt;&gt; FETCH('ado_conn')= Win32::OLE=HASH(0x1cc847c) ('ado_conn' from cache) at ADO.pm line 1384<br>&nbsp; &nbsp; &lt;&gt; FETCH('ado_comm')= Win32::OLE=HASH(0x15d57c0) ('ado_comm' from cache) at ADO.pm line 1385<br>&nbsp; &nbsp; &lt;&gt; FETCH('Statement')= ( 'SELECT SYSDATE FROM DUAL' ) [1 items] ('Statement' from cache) at ADO.pm line 1387<br>&nbsp; &nbsp; &lt;- STORE('NUM_OF_PARAMS' 0)= 1 at ADO.pm line 1404<br>1&nbsp; &nbsp;&lt;&gt; FETCH('Statement')= 'SELECT SYSDATE FROM DUAL' ('Statement' from cache) at ADO.pm line 1459<br>&nbsp; &nbsp; !! ERROR: -1 'Can't execute statement 'SELECT SYSDATE FROM DUAL':<br>&nbsp; &nbsp; Description : Multiple-step operation completed with one or more errors. Check each status value.<br>&nbsp; &nbsp; HelpContext<nobr> <wbr></nobr>:<br>&nbsp; &nbsp; &nbsp; &nbsp;HelpFile<nobr> <wbr></nobr>:<br>&nbsp; &nbsp; NativeError<nobr> <wbr></nobr>:<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Number : 265946<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Source : OraOLEDB<br>&nbsp; &nbsp; &nbsp; &nbsp;SQLState<nobr> <wbr></nobr>:<br>&nbsp; &nbsp; ' (err#1)<br>1&nbsp; &nbsp;&lt;- set_err(-1 'Can't execute statement 'SELECT SYSDATE FROM DUAL':<br>&nbsp; &nbsp; Description : Multiple-step operation completed with one or more errors. Check each status value.<br>&nbsp; &nbsp; HelpContext<nobr> <wbr></nobr>:<br>&nbsp; &nbsp; &nbsp; &nbsp;HelpFile<nobr> <wbr></nobr>:<br>&nbsp; &nbsp; NativeError<nobr> <wbr></nobr>:<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Number : 265946<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Source : OraOLEDB<br>&nbsp; &nbsp; &nbsp; &nbsp;SQLState<nobr> <wbr></nobr>:<br>&nbsp; &nbsp; ')= undef at ADO.pm line 1568<br>&lt;- destroy statement handler<br>-&gt; destroy statement handler<br>&nbsp; &nbsp; -- State: 1<br>&nbsp; &nbsp; -- Modified ADO Connection Attributes: 0<br>&nbsp; &nbsp; -- AutoCommit: 0, Provider Support: 2, Comments: Transactions can contain DML statements.&nbsp; DDL statements within a transaction cause the transaction to be committed.</tt></p></div> </blockquote><p>I'm running 5.8.3.809 of ActiveState's Perl. I looked on perl.dbi.users with no luck. I'd post this to the list, but my company's spam filter would snarf it.<nobr> <wbr></nobr>:( Any ideas?</p><p> <b>Update (21-May-2004):</b> I tried to build DBD::Oracle via CPAN. It failed tests on long and ph_types which included a Memory Violation. Long story short, I downgraded to ActiveState's 5.6.1 build 638, and installed the latest available DBI amd DBD::Oracle via PPM. Now, I'm "shittin' in tall cotton".<nobr> <wbr></nobr>:) Thanks, to everyone who commented.</p> Buck 2004-05-18T20:49:21+00:00 journal LLLWF! http://use.perl.org/~Buck/journal/16520?from=rss Today's my birthday as well as <a href="http://use.perl.org/~KM/journal/16517">KM's</a>. Mine happens to be the Big Four-O; hard to believe I made it this far. So, what did you get me? Where's my present, dammit?<nobr> <wbr></nobr>:) Buck 2003-12-27T21:11:26+00:00 journal Perl, Ruby and Python (Part Deux) http://use.perl.org/~Buck/journal/9979?from=rss <p>Continuing with my comments made <a href="http://use.perl.org/~Ovid/journal/9867">here</a>, here's the source for the Perl and Ruby scripts I used to test with. The Python version is included for<nobr> <wbr></nobr>... completeness (I'm being polite, here<nobr> <wbr></nobr>:).</p><p> <code> #!/usr/bin/perl<br> <br> my $target = shift;<br> for (my $iter = 0; $iter &lt; 100; $iter++)<br> {<br> &nbsp;&nbsp;my $count = 0;<br> &nbsp;&nbsp;open(INDEX, "/usr/ports/INDEX");<br> &nbsp;&nbsp;while (&lt;INDEX&gt;)<br> &nbsp;&nbsp;{<br> &nbsp;&nbsp;&nbsp;&nbsp;chomp;<br> &nbsp;&nbsp;&nbsp;&nbsp;my @fields = split/\|/;<br> &nbsp;&nbsp;&nbsp;&nbsp;if ($fields[0] =~ m{$target} || $fields[6] =~ m{$target} || <br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$fields[3] =~ m{$target} || $fields[7] =~ m{$target} || <br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$fields[8] =~ m{$target})<br> &nbsp;&nbsp;&nbsp;&nbsp;{<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $count++;<br> &nbsp;&nbsp;&nbsp;&nbsp;}<br> &nbsp;&nbsp;}<br> &nbsp;&nbsp;close INDEX;<br> }<br> </code> <br> <br> <code> #!/usr/local/bin/ruby<br> # distribution-name|port-path|installation-prefix|comment| \<br> # description-file|maintainer|categories|build deps|run deps|www site <br> target = ARGV[0]<br> 100.times do |iter|<br> &nbsp;&nbsp;index = File.new("/usr/ports/INDEX")<br> &nbsp;&nbsp;count = 0<br> &nbsp;&nbsp;index.each do |line|<br> &nbsp;&nbsp;&nbsp;&nbsp;fields = line.chomp.split('|')<br> &nbsp;&nbsp;&nbsp;&nbsp;if fields[0] =~<nobr> <wbr></nobr>/#{target}/ || fields[6] =~<nobr> <wbr></nobr>/#{target}/ || \<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;fields[3] =~<nobr> <wbr></nobr>/#{target}/ || fields[7] =~<nobr> <wbr></nobr>/#{target}/ || \<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;fields[8] =~<nobr> <wbr></nobr>/#{target}/<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;count += 1<br> &nbsp;&nbsp;&nbsp;&nbsp;end<br> &nbsp;&nbsp;end<br> &nbsp;&nbsp;index.close<br> end<br> </code> <br> <br> <code> #!/usr/local/bin/python<br> <br> import sys<br> import string<br> import re<br> target = re.compile(sys.argv[1])<br> <br> for iter in range(100):<br> &nbsp;&nbsp;&nbsp;&nbsp;index = open('/usr/ports/INDEX', 'r')<br> &nbsp;&nbsp;&nbsp;&nbsp;count = 0<br> &nbsp;&nbsp;&nbsp;&nbsp;line = index.readline()<br> &nbsp;&nbsp;&nbsp;&nbsp;while line &lt;&gt; ''<nobr> <wbr></nobr>:<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;fields = line.strip().split('|')<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if target.search(fields[0]) or target.search(fields[6]) \<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;or target.search(fields[3]) or target.search(fields[7]) \<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;or target.search(fields[8]):<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;count = count + 1<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;line = index.readline()<br> &nbsp;&nbsp;&nbsp;&nbsp;index.close()<br> </code></p><p>An example of the data used (from<nobr> <wbr></nobr>/usr/ports/INDEX in FreeBSD)<br> <code> # distribution-name|port-path|installation-prefix|comment| \<br> # description-file|maintainer|categories|build deps|run deps|www site <br> 9e-1.0|/usr/ports/archivers/9e|/usr/local|Explode Plan9 archives|/usr/ports/archivers/9e/pkg-descr|ports@FreeBSD.Org|archivers|||http:/<nobr>/<wbr></nobr> www.eecs.harvard.edu/~wkj/Software/9e/<br> arc-5.21e.8_1|/usr/ports/archivers/arc|/usr/local|Create &amp; extract files from DOS<nobr> <wbr></nobr>.ARC files|/usr/ports/archivers/arc/pkg-descr|ache@FreeBSD.org|archivers|||<br> arj-3.10b|/usr/ports/archivers/arj|/usr/local|Open-source ARJ|/usr/ports/archivers/arj/pkg-descr|kot@premierbank.dp.ua|archivers|autoconf<nobr>2<wbr></nobr> 13-2.13.000227_5 expat-1.95.5 gettext-0.11.5_1 gmake-3.80 libiconv-1.8_2 m4-1.4_1||http://arj.sourceforge.net/<br> bzip-0.21|/usr/ports/archivers/bzip|/usr/local|A block-sorting file compressor|/usr/ports/archivers/bzip/pkg-descr|ports@FreeBSD.org|archivers|||ht<nobr>t<wbr></nobr> p://www.muraroa.demon.co.uk/<br> bzip2-1.0.2|/usr/ports/archivers/bzip2|/usr/local|A block-sorting file compressor|/usr/ports/archivers/bzip2/pkg-descr|jharris@widomaker.com|archivers<nobr>|<wbr></nobr> ||http://sources.redhat.com/bzip2/<br> cabextract-0.6|/usr/ports/archivers/cabextract|/usr/local|A program to extract Microsoft cabinet (.CAB) files|/usr/ports/archivers/cabextract/pkg-descr|sobomax@FreeBSD.org|archivers|l<nobr>i<wbr></nobr> bgnugetopt-1.2|libgnugetopt-1.2|http://www.kyz.uklinux.net/cabextract.php3 </code> </p><p>The average runtimes, minutes and seconds, have been (for running each of the above scripts once):<br> Perl: 0:35<br> Python: 0:38<br> Ruby: 2:29</p> Buck 2003-01-15T03:48:40+00:00 journal