Re: [sqlite] Long lived prepared statements

[Mark Lawrence]

> > By the way, the last call to fetchrow_array() (that returns
> > 'undef') implicitly calls finish() internally.

> I assume this is the same finish of the handle obtained from my
> prepare.  But because I have a reference to the handle holed away it
> is not really "finished" and that is why I am able to continue to use
> the prepared statement? Is this assumption correct?

I suspect you are mixing a couple of different concepts together,
although I can only what they are exactly.

As the DBI documentation mentions, the finish() method should more
accurately be called discard_pending_rows(). It does not actually do
very much - more a way of saying "I don't need any more information
from this handle."

That method is unrelated to Perl's reference counting which keeps track
of objects and their memory, and which you usually don't need to worry
about. If you keep a reference to the statement handle somewhere then
you can run execute() on it again, and when all the references are
gone Perl will do what it needs to do memory wise which *may* involve
calls to the underlying SQLite C API as needed.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Long lived prepared statements

[Jim Dodgen]

Thanks Mark,

That confirms what I have been observing.
I have been running a test for about 12 hours doing the reused prepare and
I detect no leakage.

A question about your reply about the automatic finish at the last
fetchrow.  I assume this is the same finish of the handle obtained from my
prepare.  But because I have a reference to the handle holed away it is not
really "finished" and that is why I am able to continue to use the prepared
statement? Is this assumption correct?

*Jim Dodgen*







On Mon, Oct 6, 2014 at 12:08 AM, Mark Lawrence  wrote:

> > I think I know the answer to this so I'm looking for some
> > conformation.  What I do is to prepare a relatively large and complex
> > query. Then I run the same query forever  never doing a finalize. My
> > assumption is I will have no memory leakage.
>
> In principle this should be fine, and if it isn't then it would be a
> bug somewhere, either in the SQLite code (very unlikely) or the
> DBD::SQLite driver for Perl's DBI interface (also unlikely).
>
> Simon wrote:
>
> > When you are finished with the query you should either end on a
> > _reset() or do a _finalize().  Or (harmlessly) do both.  If you do
> > not do one of those, you may find that when you _close() some memory
> > is not released for the statement and/or the database (I'm not sure
> > which, but either way it's bad).
>
> On the Perl/DBI side of things these actions are usually be taken care
> of automatically when the associated objects holding the relevant
> resources go out of scope.
>
> On Sat Oct 04, 2014 at 03:16:23PM -0700, Jim Dodgen wrote:
> > It might be I need more of a Perl DBI question the order of the
> > statements I do are as follows
> >
> > prepare  >> - Done once
> >
> > execute  << done many times followed by: fetchrow_array << until
> > exhausted
> >
> > finish << never done
> >
> > I just don't see that the Execute/Fetchrow-array activity is going to
> > leave a handle or some resource dangling
>
> The above steps are exactly (but not exclusively) what the Perl DBI was
> intended to support. Although I haven't specifically measured the
> memory use, I do the above quite a lot without a problem.
>
> By the way, the last call to fetchrow_array() (that returns 'undef')
> implicitly calls finish() internally.
>
> --
> Mark Lawrence
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Long lived prepared statements

[Mark Lawrence]

> I think I know the answer to this so I'm looking for some
> conformation.  What I do is to prepare a relatively large and complex
> query. Then I run the same query forever  never doing a finalize. My
> assumption is I will have no memory leakage.

In principle this should be fine, and if it isn't then it would be a
bug somewhere, either in the SQLite code (very unlikely) or the
DBD::SQLite driver for Perl's DBI interface (also unlikely).

Simon wrote:

> When you are finished with the query you should either end on a
> _reset() or do a _finalize().  Or (harmlessly) do both.  If you do
> not do one of those, you may find that when you _close() some memory
> is not released for the statement and/or the database (I'm not sure
> which, but either way it's bad).

On the Perl/DBI side of things these actions are usually be taken care
of automatically when the associated objects holding the relevant
resources go out of scope.

On Sat Oct 04, 2014 at 03:16:23PM -0700, Jim Dodgen wrote:
> It might be I need more of a Perl DBI question the order of the
> statements I do are as follows
> 
> prepare  >> - Done once
> 
> execute  << done many times followed by: fetchrow_array << until
> exhausted
> 
> finish << never done
>
> I just don't see that the Execute/Fetchrow-array activity is going to
> leave a handle or some resource dangling

The above steps are exactly (but not exclusively) what the Perl DBI was
intended to support. Although I haven't specifically measured the
memory use, I do the above quite a lot without a problem.

By the way, the last call to fetchrow_array() (that returns 'undef')
implicitly calls finish() internally.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Long lived prepared statements

[Simon Slavin]

On 4 Oct 2014, at 11:16pm, Jim Dodgen  wrote:

> It might be I need more of a Perl DBI question

Whoops.  Yes, my answer was geared to users of the C API and thin shims to it.  
I have no idea how Perl DBI works.  Sorry about that, and I hope you can get a 
response from a Perl user.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Long lived prepared statements

[Jim Dodgen]

It might be I need more of a Perl DBI question the order of the statements
I do are as follows

prepare  >> - Done once

execute  << done many times
followed by:
fetchrow_array << until exhausted

 finish << never done

I just don't see that the Execute/Fetchrow-array activity is going to leave
a handle or some resource dangling


*Jim Dodgen*







On Sat, Oct 4, 2014 at 2:34 PM, Simon Slavin  wrote:

>
> On 4 Oct 2014, at 10:08pm, Jim Dodgen  wrote:
>
> > What I do is to prepare a relatively large and complex query. Then I run
> > the same query forever  never doing a finalize. My assumption is I will
> > have no memory leakage.
>
> You can do _step() and _reset() and _bind_() as many times as you want in
> any order.  You should not get memory (or any other resource) leaks unless
> you've messed up in your own programming or supplied the wrong value for
> the fifth parameter to a _bind_().
>
> When you are finished with the query you should either end on a _reset()
> or do a _finalize().  Or (harmlessly) do both.  If you do not do one of
> those, you may find that when you _close() some memory is not released for
> the statement and/or the database (I'm not sure which, but either way it's
> bad).
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Long lived prepared statements

[Simon Slavin]

On 4 Oct 2014, at 10:08pm, Jim Dodgen  wrote:

> What I do is to prepare a relatively large and complex query. Then I run
> the same query forever  never doing a finalize. My assumption is I will
> have no memory leakage.

You can do _step() and _reset() and _bind_() as many times as you want in any 
order.  You should not get memory (or any other resource) leaks unless you've 
messed up in your own programming or supplied the wrong value for the fifth 
parameter to a _bind_().

When you are finished with the query you should either end on a _reset() or do 
a _finalize().  Or (harmlessly) do both.  If you do not do one of those, you 
may find that when you _close() some memory is not released for the statement 
and/or the database (I'm not sure which, but either way it's bad).

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users