Re: Question about $sth-finish;
On Tue, Aug 15, 2000 at 03:26:03PM +0400, Vladislav Safronov wrote:
Hi,
Could you have a look at the lines and answer the question ..
---
sub foo {
my $dbh = shift;
my $sql = ...
my $sth = $dbh-prepare($sql);
$sth-execute;
$sth-finish;
}
===
Do I always need to call $sth-finish?
You *never* need to call finish on non-select statements.
(If you do, it's a driver bug.)
Wouldn't it be automaticly called when
sub foo ends (when my variable $sth get destroyed)?
Finish marks the end of *fetching*, not the end of life of the handle.
Reread the DBI 1.14 docs on finish and tell me if anything is unclear.
Tim.
p.s. If someone asked me what I'd change about the DBI I was to rewrite
it, I'd probably just say "rename finish to cancel_select".
Re: Question about $sth-finish;
On Tue, Aug 15, 2000 at 12:22:46PM -0500, Jay Jacobs wrote: On Tue, 15 Aug 2000, Tom Mornini wrote: It is my understanding of the DBI docs that you only need to call $sth-finish when you DON'T fetch all the rows that the $sth has ready to return. From "Writing Apache Modules with Perl and C": "You should still call finish() at the end of each series of fetches, even though you are going to reuse the statement handler. Failure to do so can lead to memory leaks." Not true. Unless there's a driver bug, in which case anything can happen. If I remember correctly, it also frees up any resources used by the database (depending on db) for the query, like for sorting, joining, etc. But I can't quote a source for that one. Me. But it's only relevant if you're _not_ fetching _all_ rows. From my point of view, it never hurts to call finish()... True. Tim.
Re: Question about $sth-finish;
On Wed, Aug 16, 2000 at 08:26:09AM +0200, Henrik Tougaard wrote: From: Jay Jacobs [mailto:[EMAIL PROTECTED]] On Tue, 15 Aug 2000, Tom Mornini wrote: It is my understanding of the DBI docs that you only need to call $sth-finish when you DON'T fetch all the rows that the $sth has ready to return. From "Writing Apache Modules with Perl and C": "You should still call finish() at the end of each series of fetches, even though you are going to reuse the statement handler. Failure to do so can lead to memory leaks." You picked the wrong authority for this! The right place (tm) to look when discussing DBI is 'perldoc DBI'. The relevant quote is: If I remember correctly, it also frees up any resources used by the database (depending on db) for the query, like for sorting, joining, etc. But I can't quote a source for that one. There is no authoritative source for that fallacy - I hope! The right place (tm) to look when discussing DBI is 'perldoc DBI'. The relevant quote is: $rc = $sth-finish; [...] Consider a query like: SELECT foo FROM table WHERE bar=? ORDER BY foo where you want to select just the first (smallest) "foo" value from a very large table. When executed, the database server will have to use temporary buffer space to store the sorted rows. If, after executing the handle and selecting one row, the handle won't be re-executed for some time and won't be destroyed, the Cfinish method can be used to tell the server that the buffer space can be freed. :-) Tim.
RE: Question about $sth-finish;
On Tue, Aug 15, 2000 at 03:26:03PM +0400, Vladislav Safronov wrote:
Hi,
Could you have a look at the lines and answer the question ..
---
sub foo {
my $dbh = shift;
my $sql = ...
my $sth = $dbh-prepare($sql);
$sth-execute;
$sth-finish;
}
===
Do I always need to call $sth-finish?
You *never* need to call finish on non-select statements.
(If you do, it's a driver bug.)
Wouldn't it be automaticly called when
sub foo ends (when my variable $sth get destroyed)?
Finish marks the end of *fetching*, not the end of life of the handle.
So I can freely overwrite the handle with new one, since it's not the end
of life of the handle, can't I?
==
my $sql = "select .."
my $sth = $dbh-prepare($sql);
$sth-execute;
.. fetch just some (not all) data
my $newsql = "select .."
$sth = $dbh-prepare($newsql);
$sth-execute;
==
and this code should work with troubles ...
Vlad.
RE: Question about $sth-finish;
From: Jay Jacobs [mailto:[EMAIL PROTECTED]] On Tue, 15 Aug 2000, Tom Mornini wrote: It is my understanding of the DBI docs that you only need to call $sth-finish when you DON'T fetch all the rows that the $sth has ready to return. From "Writing Apache Modules with Perl and C": "You should still call finish() at the end of each series of fetches, even though you are going to reuse the statement handler. Failure to do so can lead to memory leaks." You picked the wrong authority for this! The right place (tm) to look when discussing DBI is 'perldoc DBI'. The relevant quote is: finish $rc = $sth-finish; Indicates that no more data will be fetched from this statement handle before it is either executed again or destroyed. It is rarely needed but can sometimes be helpful in very specific situations in order to allow the server to free up resources currently being held (such as sort buffers). When all the data has been fetched from a select statement the driver should automatically call finish for you. So you should not normally need to call it explicitly. Note the last sentence! If I remember correctly, it also frees up any resources used by the database (depending on db) for the query, like for sorting, joining, etc. But I can't quote a source for that one. There is no authoritative source for that fallacy - I hope! From my point of view, it never hurts to call finish()... Quite true. Do you also undef all your variables just before they go out of scope? Thats comparable. There are a *few* situations where finish is needed (Michael Peppler has shown one, the DBI docs list another) but must DBI programmers won't need finish ever. Henrik
RE: Question about $sth-finish;
From: Vladislav Safronov [mailto:[EMAIL PROTECTED]]
What can you say about this code? is it ok (overwriting
previous handle)?
==
sub foo {
my $dbh = shift;
my $sql1 = "select *...
my $sql2 = "select *...
my $sth = $dbh-prepare($sql1);
$sth-execute;
.. fetch some data.
# should be $sth-finish inserted??
$sth = $dbh-prepare($sql2); # we overwrite previous
handle saved in $sth
..
$sth-execute;
.. fetch some data.
return;
}
==
$sth-finish should be inserted if (and ONLY if) the C...fetch some data
does NOT fetch ALL data in the select. If you do some thing like:
while (my $r=$sth-fetchrow_arrayref) {
.. handle data;
}
there is no reason to call finish, but if you do
while (...$sth-fetch..) {
last if some condition;
}
you will have to call finish, but I would reccomend using
another name for the second statement (that would help
the poor sod who will try to understand this in a years time :)
Henrik
RE: Question about $sth-finish;
Well, summarizing all the answers and assuming using Mysql 1. $sth-finish should be used if (and ONLY if) the the returned data (any SELECT, but not INSERT, UPDATE?) has not been fetched ALL and $sth is going to be overwritten.. 2. $sth (defined as 'my') should not call finish before it gets out of scope.. Vlad/
RE: Question about $sth-finish;
as written in the manpage, this is rarely used, it will be called for you
when the handle is going out of scope, but if something is still left in the
buffer some warnings will be generated.
-Original Message-
From: Vladislav Safronov
To: [EMAIL PROTECTED]
Sent: 8/15/00 7:26 PM
Subject: Question about $sth-finish;
Hi,
Could you have a look at the lines and answer the question ..
---
sub foo {
my $dbh = shift;
my $sql = ...
my $sth = $dbh-prepare($sql);
$sth-execute;
$sth-finish;
}
===
Do I always need to call $sth-finish? Wouldn't it be automaticly called
when
sub foo ends (when my variable $sth get destroyed)?
Vlad.
Re: Question about $sth-finish;
On Tue, 15 Aug 2000, Vladislav Safronov wrote:
Hi,
Could you have a look at the lines and answer the question ..
---
sub foo {
my $dbh = shift;
my $sql = ...
my $sth = $dbh-prepare($sql);
$sth-execute;
$sth-finish;
}
===
Do I always need to call $sth-finish? Wouldn't it be automaticly called
when
sub foo ends (when my variable $sth get destroyed)?
$sth doesn't always get destroyed when foo ends (due to a bug in all
perls). But otherwise, yes.
--
Matt/
Fastnet Software Ltd. High Performance Web Specialists
Providing mod_perl, XML, Sybase and Oracle solutions
Email for training and consultancy availability.
http://sergeant.org | AxKit: http://axkit.org
RE: Question about $sth-finish;
From: Vladislav Safronov [mailto:[EMAIL PROTECTED]]
sub foo {
my $dbh = shift;
my $sql = ...
my $sth = $dbh-prepare($sql);
$sth-execute;
$sth-finish;
}
===
Do I always need to call $sth-finish? Wouldn't it be
automaticly called when
sub foo ends (when my variable $sth get destroyed)?
You do *NOT* need to call $sth-finish - never, never, never.
[OK. not quite true: you need to call $sth-finish when you end a
select statement before reading the last data in the cursor and
want to reexecute the select statement. So the answer is:
NEVER!!!]
If you worry about the statement handle still being defined I
would reccomend using Cundef $sth instead. That will
certainly release all resources owned by the statement.
$sth-finish is just for very special cases - and this is
not one of them.
--
Henrik Tougaard, [EMAIL PROTECTED]
RE: Question about $sth-finish;
On Tue, 15 Aug 2000, Vladislav Safronov wrote:
Hi,
Could you have a look at the lines and answer the question ..
---
sub foo {
my $dbh = shift;
my $sql = ...
my $sth = $dbh-prepare($sql);
$sth-execute;
$sth-finish;
}
===
Do I always need to call $sth-finish? Wouldn't it be
automaticly called
when
sub foo ends (when my variable $sth get destroyed)?
$sth doesn't always get destroyed when foo ends (due to a bug in all
perls). But otherwise, yes.
"my" (perl's my) variables doesn't always get destoyed, does it Perl's
documentation say that "my" vars are the most safe since they get destroyed
when they get out of scope ...
Vlad.
RE: Question about $sth-finish;
On Tue, 15 Aug 2000, Vladislav Safronov wrote:
"my" (perl's my) variables doesn't always get destoyed, does it Perl's
documentation say that "my" vars are the most safe since they get destroyed
when they get out of scope ...
I said this was a bug in Perl, although I don't think that 5.6.1 is fixing
it (due out "soon"), because its quite hard to track down. It occurs in
conditionals:
if (my $rec = foo()) {
# lexicals in foo() not destroyed here
}
# lexicals in foo() destroyed here.
This can be demonstrated with a very simple object class with a DESTROY
method. There's a message somewhere in the p5p archives about this from
me.
(and I don't mean the lexicals that might get returned and assigned to
$rec, for anyone assuming I don't know what I'm talking about)...
--
Matt/
Fastnet Software Ltd. High Performance Web Specialists
Providing mod_perl, XML, Sybase and Oracle solutions
Email for training and consultancy availability.
http://sergeant.org | AxKit: http://axkit.org
RE: Question about $sth-finish;
Matt Sergeant [EMAIL PROTECTED] wrote: This can be demonstrated with a very simple object class with a DESTROY method. There's a message somewhere in the p5p archives about this from me. That's http://www.xray.mpe.mpg.de/mailing-lists/perl5-porters/2000-03/msg00604.html to save anyone else having to look :-)
RE: Question about $sth-finish;
Ok. I think, the answers clear the problem, but I have yet more question.
What can you say about this code? is it ok (overwriting previous handle)?
==
sub foo {
my $dbh = shift;
my $sql1 = "select *...
my $sql2 = "select *...
my $sth = $dbh-prepare($sql1);
$sth-execute;
.. fetch some data.
# should be $sth-finish inserted??
$sth = $dbh-prepare($sql2); # we overwrite previous handle saved in $sth
..
$sth-execute;
.. fetch some data.
return;
}
==
Vlad.
RE: Question about $sth-finish;
On Tue, 15 Aug 2000, Vladislav Safronov wrote: Ok. I think, the answers clear the problem, but I have yet more question. What can you say about this code? is it ok (overwriting previous handle)? [snip] Well it depends on the DBMS. For example Sybase might not like it if you haven't read everything from the $sth first - it will bite you in the bum with locks. Others will be fine. So just be careful... -- Matt/ Fastnet Software Ltd. High Performance Web Specialists Providing mod_perl, XML, Sybase and Oracle solutions Email for training and consultancy availability. http://sergeant.org | AxKit: http://axkit.org
Re: Question about $sth-finish;
Matt Sergeant wrote:
On Tue, 15 Aug 2000, Vladislav Safronov wrote:
Hi,
Could you have a look at the lines and answer the question ..
---
sub foo {
my $dbh = shift;
my $sql = ...
my $sth = $dbh-prepare($sql);
$sth-execute;
$sth-finish;
}
===
Do I always need to call $sth-finish? Wouldn't it be automaticly called
when
sub foo ends (when my variable $sth get destroyed)?
$sth doesn't always get destroyed when foo ends (due to a bug in all
perls).
(Boggle) Really? 'My' variables going out of scope don't always get
freed up? Or is this strictly an object thing with DESTROY?
Re: Question about $sth-finish;
On Tue, 15 Aug 2000, Keith G. Murphy wrote: (Boggle) Really? 'My' variables going out of scope don't always get freed up? Or is this strictly an object thing with DESTROY? Well why would you care if my $str = "hello world" didn't get freed via this bug? It only matters for objects that do something in DESTROY... -- Matt/ Fastnet Software Ltd. High Performance Web Specialists Providing mod_perl, XML, Sybase and Oracle solutions Email for training and consultancy availability. http://sergeant.org | AxKit: http://axkit.org
RE: Question about $sth-finish;
Matt Sergeant writes: On Tue, 15 Aug 2000, Vladislav Safronov wrote: Ok. I think, the answers clear the problem, but I have yet more question. What can you say about this code? is it ok (overwriting previous handle)? [snip] Well it depends on the DBMS. For example Sybase might not like it if you haven't read everything from the $sth first - it will bite you in the bum with locks. Others will be fine. So just be careful... Actually what happens with Sybase is this: If $sth has pending results when prepare() is called DBD::Sybase opens a new connection (because it sees that the $dbh already has an active $sth). When the return value from prepare() is assigned to $sth the DESTROY method for the old $sth is called, which cancels the previous query. So I think that you *should* be safe from deadlocks, but the problem will be getting additional connections created, which is not really optimal. Michael -- Michael Peppler -||- Data Migrations Inc. [EMAIL PROTECTED]-||- http://www.mbay.net/~mpeppler Int. Sybase User Group -||- http://www.isug.com Sybase on Linux mailing list: [EMAIL PROTECTED]
RE: Question about $sth-finish;
On Tue, 15 Aug 2000, Michael Peppler wrote: Matt Sergeant writes: On Tue, 15 Aug 2000, Vladislav Safronov wrote: Ok. I think, the answers clear the problem, but I have yet more question. What can you say about this code? is it ok (overwriting previous handle)? [snip] Well it depends on the DBMS. For example Sybase might not like it if you haven't read everything from the $sth first - it will bite you in the bum with locks. Others will be fine. So just be careful... Actually what happens with Sybase is this: If $sth has pending results when prepare() is called DBD::Sybase opens a new connection (because it sees that the $dbh already has an active $sth). When the return value from prepare() is assigned to $sth the DESTROY method for the old $sth is called, which cancels the previous query. So I think that you *should* be safe from deadlocks, but the problem will be getting additional connections created, which is not really optimal. Isn't there something different happening when AutoCommit = 0 though? -- Matt/ Fastnet Software Ltd. High Performance Web Specialists Providing mod_perl, XML, Sybase and Oracle solutions Email for training and consultancy availability. http://sergeant.org | AxKit: http://axkit.org
RE: Question about $sth-finish;
Matt Sergeant writes: On Tue, 15 Aug 2000, Michael Peppler wrote: Matt Sergeant writes: On Tue, 15 Aug 2000, Vladislav Safronov wrote: Ok. I think, the answers clear the problem, but I have yet more question. What can you say about this code? is it ok (overwriting previous handle)? [snip] Well it depends on the DBMS. For example Sybase might not like it if you haven't read everything from the $sth first - it will bite you in the bum with locks. Others will be fine. So just be careful... Actually what happens with Sybase is this: If $sth has pending results when prepare() is called DBD::Sybase opens a new connection (because it sees that the $dbh already has an active $sth). When the return value from prepare() is assigned to $sth the DESTROY method for the old $sth is called, which cancels the previous query. So I think that you *should* be safe from deadlocks, but the problem will be getting additional connections created, which is not really optimal. Isn't there something different happening when AutoCommit = 0 though? If AutoCommit = 0 then you'll get a fatal error because DBD::Sybase can't guarantee consistent rollback behavior accross multiple connections (which is what happens in this case, even though the first $sth gets destroyed before the second one gets executed). Michael -- Michael Peppler -||- Data Migrations Inc. [EMAIL PROTECTED]-||- http://www.mbay.net/~mpeppler Int. Sybase User Group -||- http://www.isug.com Sybase on Linux mailing list: [EMAIL PROTECTED]
RE: Question about $sth-finish;
On Tue, 15 Aug 2000, Tom Mornini wrote: It is my understanding of the DBI docs that you only need to call $sth-finish when you DON'T fetch all the rows that the $sth has ready to return. From "Writing Apache Modules with Perl and C": "You should still call finish() at the end of each series of fetches, even though you are going to reuse the statement handler. Failure to do so can lead to memory leaks." If I remember correctly, it also frees up any resources used by the database (depending on db) for the query, like for sorting, joining, etc. But I can't quote a source for that one. From my point of view, it never hurts to call finish()... Jay Jacobs
RE: Question about $sth-finish;
On Tue, 15 Aug 2000, Vladislav Safronov wrote:
Ok. I think, the answers clear the problem, but I have yet more question.
What can you say about this code? is it ok (overwriting previous handle)?
==
sub foo {
my $dbh = shift;
my $sql1 = "select *...
my $sql2 = "select *...
my $sth = $dbh-prepare($sql1);
$sth-execute;
.. fetch some data.
# should be $sth-finish inserted??
$sth = $dbh-prepare($sql2); # we overwrite previous handle saved in $sth
..
$sth-execute;
.. fetch some data.
return;
}
It is my understanding of the DBI docs that you only need to call
$sth-finish when you DON'T fetch all the rows that the $sth has ready to
return.
--
-- Tom Mornini
-- InfoMania Printing and Prepress
