Re: Newbie: Statement handle inactive after successful execute of SELECT

[Alan M. Carroll]

"Michael A Chase" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> On Tue, 4 Nov 2003 14:24:35 -0600 "Alan M. Carroll"
> <[EMAIL PROTECTED]> wrote:
>
> > I'm working with a problem in Movable Type (a PERL web application)
> > where certain operations fail.  One place where it fails is in some
> > code to create an iterator which walks a set of rows in a MySQL 3.23.58
> > DB using DBI 1.21.  The code looks like this:
> >
> >my $sth = $dbh->prepare($sql) or return sub { };
> >$sth->execute(@$bind) or return sub { };
> >$sth->bind_columns(undef, @bind);
>
> It would be helpful to print the contents of $DBI::errstr whenever any of
> these statements fail.  Otherwise, even when you know one failed, you
> have no idea what went wrong.

I tried that but $DBI::errstr was undefined. That's consistent with other
indicators that after the execute, nothing has gone wrong despite the fact
that the statement handle is not active.

> The SELECT rows are moderately wide, but there should be plenty of room
> in memory for that many rows unless some columns are a couple thousand
> characters wide.

Perhaps. The SELECT in this case will select almost all of the table, which
is 32M. That's a lot to fit in the cache.

> Try adding a second argument to trace() to specify a file in a directory
> where you know the process has write permission.

That was a very helpful suggestion. I managed to get some traces of the
activity. My current theory is that the query results are too large to be
brought over to the client and this is indicated by the inactive statement
handle and the execute return value of "-1". To test this theory, I modified
the prepare call to be "($sql, { mysql_use_result => 1 })". This seemed to
fix the local problem and the checks I had for an inactive statement didn't
go off. However, there was a later crash because the outer code looks like

my $entries = getEntryIter();
while (my $entry = $entries->()) {
# do some output
my $weblog = LoadWeblogFromDb();
}

The load from the DB fails, almost certainly because it is forbidden to do
additional DB queries on the same connection while a mysql_use_result is
unfinished. However, this still represents excellent progress because now I
have a PERL problem instead of a DB problem. Your suggestions are much
appreciated.

I do have a couple of other questions.

1) Is this a plausible theory?
2) What is the best way to deal with this issue? Surely I'm not the first to
encounter it!
  A) Detect the -1 return from execute / inactive statement and redo the
query with mysql_use_result?
  B) Always use mysql_use_result and modify the rest of the application to
deal with the effects of that?
  C) Is it possible to recover after the execute?
  D) Use LIMIT inside the iterator to bring the data over in client
controlled chunks?

Thanks!

P.S. My "bad rows" seem to have disappeared. Perhaps I tweaked something
while debugging or maybe it was a system load transient. Whatever the
reason, I can't duplicate the problem now. There is an RPC I used to walk
over the rows in chunks which would fail even for very small chunk sizes and
would always fail on specific rows. Now it works fine everywhere. Oh well.
If it comes back I have a chance of debugging it now.

Re: Newbie: Statement handle inactive after successful execute of SELECT

[Alan M. Carroll]

"Michael A Chase" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> On Tue, 4 Nov 2003 14:24:35 -0600 "Alan M. Carroll"
> <[EMAIL PROTECTED]> wrote:
>
> > I'm working with a problem in Movable Type (a PERL web application)
> > where certain operations fail.  One place where it fails is in some
> > code to create an iterator which walks a set of rows in a MySQL 3.23.58
> > DB using DBI 1.21.  The code looks like this:
> >
> >my $sth = $dbh->prepare($sql) or return sub { };
> >$sth->execute(@$bind) or return sub { };
> >$sth->bind_columns(undef, @bind);
>
> It would be helpful to print the contents of $DBI::errstr whenever any of
> these statements fail.  Otherwise, even when you know one failed, you
> have no idea what went wrong.

$DBI::errstr is undefined between bind and execute. Also, I think RaiseError
is set because without the debugging code the error about "Statement has no
result columns to bind" does come back (I suspect that's the value of
DBI::errstr after the bind_column goes wrong).

> Try adding a second argument to trace() to specify a file in a directory
> where you know the process has write permission.

That was very helpful. Based on the log information, it looks like the
problem is that if execute returns -1, it means that you should have used
"mysql_use_result" instead of "mysql_store_result".  So in this case, at
least, it does look like purely a size problem (the total size of the
returned query in this particular case is something like 30-40M because it
selects almost every row in the table and that's how big the table is). I
changed the prepare to "($sql, { mysql_use_result => 1} )" and it got past
this section of code. Sadly, it crashed later because the outer code looks
like this:

my $iter = getEntryIter();
while (my $entry = $iter->()) {
   # work on some output
   my $blog = loadBlogFromDb();
}

The load fails because it does a query to the DB while the mysql_use_result
is still pending, which is forbidden. But this is excellent progress,
because now I have a PERL problem instead of a DB problem. Thanks!

My questions now are:

1) Does this sound plausible?
2)
  A) Is it expected that one would look for the -1 return and redo the query
with mysql_use_result?
  B) Is redoing too expensive and if it's possible to have this issue one
should use mysql_use_result all the time?
  C) Is there a way to recover after the execute? My solution requires
knowing what to do when calling prepare.
  D) What's the standard pattern for handling this issue? Surely others have
run in to it.

Thanks!

P.S. I still have the "bad row" problem, which occurs even when I am
selecting a single row. But now I know some things to look for and how to do
tracing. I may come back to pester you once I have better isolation on that
one, but I don't have a good description right now and I've got some shiny
new tools with which to attack it, which are much appreciated.

looking for driver for DB2

[Anderson, James H [IT]]

As the subject says, I'm looking for a DB2 DBD (I'm running on Mac OS X).
I'm also looking for the java DB2 connector.

Thanks,

jim

possible memory leak in either DBI or DBD:Oracle (or maybe oci??)

[Roman Hochleitner]

Hi!

We are using Oracle 8.1.7 server/client under Redhat 7.1 perl 5.6.0 with 
DBD::Oracle V1.14 and DBI V1.37.

With the attached script I can reproduce a memory leak growing at a rate
of about 12MB/min. We use scripts simmiliar to that for monitoring 
(nocol/snips client).
At first I searched the leak in nocol/snips but after a while i stripped 
the monitors down until I got this remainder.

The funny thing is: if you connect/disconnect to only one and the same
database, there is no memory leak, but if you alternate between at least
two databases, it starts growing.

Can anybody reproduce the same memory leak with this little script?

Thanks for your time

PS: please reply to my email address too (I am not subscribed)

mfg  

Roman Hochleitner
Unix System Programmer


ecetra Central European e-Finance AG

Neutorgasse 2 | A-1010 Vienna
Tel:+43-1-536 89-17725
http://www.ecetra.com
#! /usr/bin/perl -w

use DBI;
use strict;


sub connect_and_disconnect()
{  
   my $dbh;
   my $db_str;

   my %StdAttr = (
   PrintError => 1,
   AutoCommit => 0,
   RaiseError => 1
   );

   my %attr = (
  PrintError => 0,
  RaiseError => 0
  );

$dbh = DBI->connect('dbi:Oracle:DBONE.ACPT','emerald',
"d0gf00d", \%StdAttr);
if (!defined $dbh) { print "Error"; exit 1; }
$db_str = $dbh->prepare("select * from dual",\%attr);
$db_str->execute();
$db_str->finish();


   $dbh->disconnect;
   undef $dbh;
}

sub connect_and_disconnect2()
{  
   my $dbh;
   my $db_str;

   my %StdAttr = (
   PrintError => 1,
   AutoCommit => 0,
   RaiseError => 1
   );

   my %attr = (
  PrintError => 0,
  RaiseError => 0
  );

$dbh = DBI->connect('dbi:Oracle:DBTWO.DEV','gizmo',
"catf00d", \%StdAttr);
if (!defined $dbh) { print "Error"; exit 1; }
$db_str = $dbh->prepare("select * from dual",\%attr);
$db_str->execute();
$db_str->finish();


   $dbh->disconnect;
   undef $dbh;
}



my $loops=1;

while($loops < 1)
{  
   $loops++;
   if ( ($loops % 100) == 0 ) {
 print ".\n" ;
   }
   connect_and_disconnect();
   connect_and_disconnect2();
}

Re: ActivePerl And Sybase

[Chuck Fox]

Ramez,

In the future please send your questions to the mailing list.  It has a 
much wider audience than my lonely ( and overly full ) mail box.  My 
comments are in italics.

From the CPAN FAQ:

 Where can I find Perl modules?

   * http://www.cpan.org/modules/00modlist.long.html
   * http://search.cpan.org/
   * http://kobesearch.cpan.org/
Due to the ever increasing number of modules on CPAN, the CPAN search 
engine is possibly a better starting point in your quest for code, 
especially if you already know exactly what you are looking for.

So jump out to one of these links and search up DBI and DBD::Sybase



 How do I install Perl modules?

Installing a new module can be as simple as typing perl -MCPAN -e 
'install Chocolate::Belgian'. The CPAN.pm documentation 
 has more 
complete instructions on how to use this convenient tool. If you are 
uncomfortable with having something take that much control over your 
software installation, or it otherwise doesn't work for you, the 
perlmodinstall 
 
documentation covers module installation for UNIX, Windows and Macintosh 
in more familiar terms.

Finally, if you're using ActivePerl on Windows, the PPM 
 
(Perl Package Manager) has much of the same functionality as CPAN.pm.

Additionally, you will need a C compiler to build the DBD for Sybase.  
So in your case you want to "perl -MCPAN -e 'install DBI'" followed by 
"perl -MCPAN -e 'install DBD::Sybase'" .  Another option for you is to 
get the source from one of the above links and then manually install 
it.  Manual installation involves unzipping/untarring the source into 
some directory.

Assume you have unloaded DBI into \tmp\DBI
cd \tmp\DBI
perl Makefile.PL
make
make test
make install
If everything is setup correctly, ie. perl installed correctly, 
environment correct, etc. then you should have a working copy of DBI 
installed into your Perl lib.  Installing DBD::Sybase is quite similar.

Regards,

Chuck

[EMAIL PROTECTED] wrote:

i have already Sybase  Open Client in my computer...
but what is the CPAN interface and where can i find it...
please can you tell me the way to install it in details

and thanks

- Original Message -
From: "Chuck Fox" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, November 04, 2003 8:17 PM
Subject: Re: ActivePerl And Sybase
 

In addition to the DBD and DBI, you will also need to install Sybase
Open Client or FreeTDS.  In terms of installing perl, use the CPAN
interface to download and install.
Chuck

[EMAIL PROTECTED] wrote:

   

I have a WinXP OS and IIS http server.
I have download and install ActivePerl 5.8.
And I want To connect to Database In Sybase Server.

i read some notes and i figure that i should install DBI-1.38 then
 

DBD-Sybase-1.01
 

Is that right

and how can i install it
should i extract these two file into the perl directory(C:/Perl) or
 

what
 





 

   



 

Re: looking for driver for DB2

[johnnnnnn]

On Wed, Nov 05, 2003 at 08:55:16AM -0500, Anderson, James H [IT] wrote:
> As the subject says, I'm looking for a DB2 DBD (I'm running on Mac OS X).

Check with IBM:

http://www-3.ibm.com/software/data/db2/perl/

It should work wherever you can get perl and DBI to work, as long as
you can also get the DB2 client libraries to work. I don't think OS X
is supported.

Actually, according to this:

http://www-3.ibm.com/software/data/db2/udb/ad/index.html

The supported OSes are AIX, HP-UX, Linux, Solaris, and Windows. You
may be better off trying to get a proxyserver or DBD::ODBC working.

> I'm also looking for the java DB2 connector.

Amazing the amount of stuff on IBM's website:

http://www-3.ibm.com/software/data/db2/udb/ad/v8/java/

-johnnn

RE: possible memory leak in either DBI or DBD:Oracle (or maybe oci??)

[shildreth]

I was just debugging this problem for co-workers, on RedHat 7.3 perl 5.6.1,
DBI 1.37 & DBD::Oracle 1.14.  I am going to upgrade the DBI to 1.38 and the
perl 5.8.1(not that this is affecting the memory leak).  If you search the 
archives, DBI 1.37 seems to had memory leak problems with other drivers as
well.  So I would say you might want to upgrade to the latest DBI(1.38).



On 05-Nov-2003 Roman Hochleitner wrote:
> 
> Hi!
> 
> We are using Oracle 8.1.7 server/client under Redhat 7.1 perl 5.6.0 with 
> DBD::Oracle V1.14 and DBI V1.37.
> 
> With the attached script I can reproduce a memory leak growing at a rate
> of about 12MB/min. We use scripts simmiliar to that for monitoring 
> (nocol/snips client).
> At first I searched the leak in nocol/snips but after a while i stripped 
> the monitors down until I got this remainder.
> 
> The funny thing is: if you connect/disconnect to only one and the same
> database, there is no memory leak, but if you alternate between at least
> two databases, it starts growing.
> 
> Can anybody reproduce the same memory leak with this little script?
> 
> Thanks for your time
> 
> PS: please reply to my email address too (I am not subscribed)
> 
> mfg  
> 
> Roman Hochleitner
> Unix System Programmer
> 
> 
> ecetra Central European e-Finance AG
> 
> Neutorgasse 2 | A-1010 Vienna
> Tel:  +43-1-536 89-17725
> http://www.ecetra.com

-- 
--
E-Mail: [EMAIL PROTECTED]
Date: 05-Nov-2003
Time: 10:19:57
--

RE: possible memory leak in either DBI or DBD:Oracle (or maybe oci??)

[Roman Hochleitner]

On Wed, 5 Nov 2003 [EMAIL PROTECTED] wrote:

> 
> I was just debugging this problem for co-workers, on RedHat 7.3 perl 5.6.1,
> DBI 1.37 & DBD::Oracle 1.14.  I am going to upgrade the DBI to 1.38 and the
> perl 5.8.1(not that this is affecting the memory leak).  If you search the 
> archives, DBI 1.37 seems to had memory leak problems with other drivers as
> well.  So I would say you might want to upgrade to the latest DBI(1.38).

Thank you,

If upgrading fixes this problem, then I will do that. It's just that I 
would like to have confirmation that exactly the script that I attached 
(db connections/user/pwd edited of course and 2 different databases)
runs without leaking, with the setup you suggested, because I got the 
answer to upgrade very often (for different problems) and it often didn't
help at all.

So, does the script I attached run smothely with your setup without 
leaking?


Thanks
 
mfg  

Roman Hochleitner
Unix System Programmer


ecetra Central European e-Finance AG

Neutorgasse 2 | A-1010 Vienna
Tel:+43-1-536 89-17725
Fax:+43-1-536 89-17719
http://www.ecetra.com

RE: possible memory leak in either DBI or DBD:Oracle (or maybe oci??)

[shildreth]

I don't see the script?

On 05-Nov-2003 Roman Hochleitner wrote:
> On Wed, 5 Nov 2003 [EMAIL PROTECTED] wrote:
> 
>> 
>> I was just debugging this problem for co-workers, on RedHat 7.3 perl 5.6.1,
>> DBI 1.37 & DBD::Oracle 1.14.  I am going to upgrade the DBI to 1.38 and the
>> perl 5.8.1(not that this is affecting the memory leak).  If you search the 
>> archives, DBI 1.37 seems to had memory leak problems with other drivers as
>> well.  So I would say you might want to upgrade to the latest DBI(1.38).
> 
> Thank you,
> 
> If upgrading fixes this problem, then I will do that. It's just that I 
> would like to have confirmation that exactly the script that I attached 
> (db connections/user/pwd edited of course and 2 different databases)
> runs without leaking, with the setup you suggested, because I got the 
> answer to upgrade very often (for different problems) and it often didn't
> help at all.
> 
> So, does the script I attached run smothely with your setup without 
> leaking?
> 
> 
> Thanks
>  
> mfg  
> 
> Roman Hochleitner
> Unix System Programmer
> 
> 
> ecetra Central European e-Finance AG
> 
> Neutorgasse 2 | A-1010 Vienna
> Tel:  +43-1-536 89-17725
> Fax:  +43-1-536 89-17719
> http://www.ecetra.com
> 

-- 
--
E-Mail: [EMAIL PROTECTED]
Date: 05-Nov-2003
Time: 16:23:02
--

Sleepycat DBXML

[Bob X]

Has anyone created a module for AS Perl 5.8.1 that works with the new DBXML
from Sleepycat?

Bob

Field terminated by

[W. Bauer]

Dear all

I have a problem using special characters in the Perl DBI. The following
SQL code works without problem with mysql:

load data local infile "orders.txt"
into table orders
fields terminated by ";" optionally enclosed by '"'
lines terminated by "\r\n"
(ID);


When I try to execute it from perl as follows,

my @sqlcommands=(qq{load data local infile \"orders.txt\"
into table orders
fields terminated by  \";\"
optionally enclosed by \'\"\'
lines terminated by \"\\r\\n\"
(ID);});

foreach (@sqlcommands){
 my $temp = $_;
 $sth = $dbh->prepare($temp);
 $sth->execute() or die "Couldn't execute statement: " . $sth->errstr;
}
I get the following error message:

DBD::mysql::st execute failed: The used command is not allowed with
this MySQL version. 

I guess this problem is related to the field terminated by ";" part of the command. 
How do I need to specify the command?

Best regards, Wolfgang Bauer

ppm for DBD::Oracle

[paul . boutros]

I know this keeps coming up but... does anyone know of a location of a ppm for 
DBD::Oracle for 5.8.0?  None of the repositories I've checked (Jeff's, Randy 
Kobe's, the one at PerlMonks) seem to have it available.

I ran into a computer on which ODBC just won't work, and I was hoping to by-
pass in a reasonably easy manner with DBD::Oracle

Thanks for any suggestions,
Paul

DBD::Oracle installation problem

[Thomas Porschberg]

I tried to install the DBD::Oracle module at a
HPUX machine (HP-UX hp-sport B.11.00 U 9000/856).
There is the following perl available:
perl -v 
This is perl, v5.6.0 built for PA-RISC1.1

In a first step I installed the DBI module in version 1.38.
Then I did a "perl Makefile.PL" and "make" for 
DBD-Oracle-1.14.

Then I did a "make test" which gives the following error:
hp-sport(pberg):/home/pberg/DBD-Oracle-1.14> make test
 /bin/sh -c true
 /bin/sh -c true
PERL_DL_NONLAZY=1 /usr/bin/perl -Iblib/arch -Iblib/lib 
-I/opt/perl5/lib/5.6.0/PA-RISC1.1 -I/opt/perl5/lib/5.6.0 -e 'use Test::Harness 
qw(&runtests $verbose); $verbose=0; runtests @ARGV;' t/*.t
t/base..Failed to load Oracle extension and/or shared libraries:
install_driver(Oracle) failed: DBD::Oracle object version 1.06 does not match 
bootstrap parameter 1.14 at blib/lib/DBD/Oracle.pm line 39.
Compilation failed in require at (eval 1) line 3.
 at t/base.t line 19
The remaining tests will probably also fail with the same error.

.
.
.

I think I had already installed an older version of DBD installed at this machine.
After getting this error I deleted all the files regarding DBI/DBD under 

/opt/perl5/lib/site_perl/5.6.0/PA-RISC1.1
/opt/perl5/lib/site_perl/5.6.0/PA-RISC1.1/DBI
/opt/perl5/lib/site_perl/5.6.0/PA-RISC1.1/DBD
/opt/perl5/lib/site_perl/5.6.0/PA-RISC1.1/auto

and repeated the installtion.

Nothing changed. 

What is the problem here and how can I fix it ?

Thomas



-- 
Please avoid sending me Word or PowerPoint attachments.
See http://www.fsf.org/philosophy/no-word-attachments.html

Re: ppm for DBD::Oracle (solved)

[paul . boutros]

Please ignore, sorry for the stupid question.  I added the repositories, but 
searched via ppm.  Instead, all you need to do is from a DOS prompt:

C:\>ppm install ftp://ftp.esoftmatic.com/outgoing/DBI/DBD-Oracle.ppd

Install 'DBD-Oracle' version 1.13 in ActivePerl 5.8.0.806.

Installing G:\Perl\site\lib\auto\DBD\Oracle\dbdimp.h
Installing G:\Perl\site\lib\auto\DBD\Oracle\mk.pm
Installing G:\Perl\site\lib\auto\DBD\Oracle\ocitrace.h
Installing G:\Perl\site\lib\auto\DBD\Oracle\Oracle.bs
Installing G:\Perl\site\lib\auto\DBD\Oracle\Oracle.dll
Installing G:\Perl\site\lib\auto\DBD\Oracle\Oracle.exp
Installing G:\Perl\site\lib\auto\DBD\Oracle\Oracle.h
Installing G:\Perl\site\lib\auto\DBD\Oracle\Oracle.lib
Installing G:\Perl\site\lib\oraperl.ph
Installing G:\Perl\site\lib\Oraperl.pm
Installing G:\Perl\site\lib\DBD\Oracle.pm
Installing G:\Perl\bin\ora_explain
Installing G:\Perl\bin\ora_explain.bat
Successfully installed DBD-Oracle version 1.13 in ActivePerl 5.8.0.806.

Quoting [EMAIL PROTECTED]:

> I know this keeps coming up but... does anyone know of a location of a ppm
> for 
> DBD::Oracle for 5.8.0?  None of the repositories I've checked (Jeff's, Randy
> 
> Kobe's, the one at PerlMonks) seem to have it available.
> 
> I ran into a computer on which ODBC just won't work, and I was hoping to
> by-
> pass in a reasonably easy manner with DBD::Oracle
> 
> Thanks for any suggestions,
> Paul
>