DBD::mysql and mysql_use_result

I want to use the DBD::mysql 'mysql_use_result' attribute to make the
driver use mysql_use_result instead of mysql_store_result, and I want to
do it for all the queries I'm submitting against the database. Having
to set it on each and every statement handle that I generate is a real
pain, I'd like to be able to set the default on the database handle and
have it inherited by all the statement handles that are generated from
the database handle.

I'm not sure of the best way to go about this, there are two options:

1. Store the value in the DBH and copy into the STH when the STH is
created, unless the STD constructor is given an explicit value.

2. Store the value in the DBH and each time the STH is executed, look up
the value in the parent DBH if the STH doesn't have an explicit value.

e.g. given:

$dbh->{mysql_use_result} = 1;
my $sth = $dbh->prepare(...);
$dbh->{mysql_use_result} = 0;

under option 1, when $sth is executed the value of the mysql_use_result
attribute will be 1, and under option 2 it will be 0.

One other question - how do I get the parent DBH from a STH from within
the XS code for a DBD driver?

--
Alan Burlison
--

Re: DBD::mysql and mysql_use_result

Alan Burlison wrote:

> One other question - how do I get the parent DBH from a STH from
> within the XS code for a DBD driver?


There's a DBI macro for that, I do not remember the name, but most
probably its DBIxxx_parent or similar.


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql and mysql_use_result

Alan Burlison wrote:

> One other question - how do I get the parent DBH from a STH from
> within the XS code for a DBD driver?


There's a DBI macro for that, I do not remember the name, but most
probably its DBIxxx_parent or similar.


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql and mysql_use_result

On Fri, 6 Aug 2004, Alan Burlison wrote:

> I'm not sure of the best way to go about this, there are two options:
>
> 1. Store the value in the DBH and copy into the STH when the STH is
> created, unless the STD constructor is given an explicit value.
>
> 2. Store the value in the DBH and each time the STH is executed, look up
> the value in the parent DBH if the STH doesn't have an explicit value.
>

I'd go with option #1, and I would not let the value of mysqLstore_results be
changed after prepare. This is the planned behaviour with prepared statements.

> One other question - how do I get the parent DBH from a STH from within
> the XS code for a DBD driver?
>

Assuming SV *sth:

D_imp_dbh_from_sth;


Rudy

Re: DBD::mysql and mysql_use_result

On Fri, 6 Aug 2004, Alan Burlison wrote:

> I'm not sure of the best way to go about this, there are two options:
>
> 1. Store the value in the DBH and copy into the STH when the STH is
> created, unless the STD constructor is given an explicit value.
>
> 2. Store the value in the DBH and each time the STH is executed, look up
> the value in the parent DBH if the STH doesn't have an explicit value.
>

I'd go with option #1, and I would not let the value of mysqLstore_results be
changed after prepare. This is the planned behaviour with prepared statements.

> One other question - how do I get the parent DBH from a STH from within
> the XS code for a DBD driver?
>

Assuming SV *sth:

D_imp_dbh_from_sth;


Rudy

Re: DBD::mysql and mysql_use_result

On Fri, Aug 06, 2004 at 01:38:48PM +0100, Alan Burlison wrote:
> I want to use the DBD::mysql 'mysql_use_result' attribute to make the
> driver use mysql_use_result instead of mysql_store_result, and I want to
> do it for all the queries I'm submitting against the database. Having
> to set it on each and every statement handle that I generate is a real
> pain, I'd like to be able to set the default on the database handle and
> have it inherited by all the statement handles that are generated from
> the database handle.
>
> I'm not sure of the best way to go about this, there are two options:
>
> 1. Store the value in the DBH and copy into the STH when the STH is
> created, unless the STD constructor is given an explicit value.
>
> 2. Store the value in the DBH and each time the STH is executed, look up
> the value in the parent DBH if the STH doesn't have an explicit value.
>
> e.g. given:
>
> $dbh->{mysql_use_result} = 1;
> my $sth = $dbh->prepare(...);
> $dbh->{mysql_use_result} = 0;
>
> under option 1, when $sth is executed the value of the mysql_use_result
> attribute will be 1, and under option 2 it will be 0.

Use option 1.

So the code in dbd_st_prepare would look like:

imp_sth->use_mysql_use_result = svp ? SvTRUE(*svp) : imp_dbh->use_mysql_use_result

> One other question - how do I get the parent DBH from a STH from within
> the XS code for a DBD driver?

Anywhere there's an imp_sth in scope you can declare a imp_dbh this way:

D_imp_dbh_from_sth;

That's a macro that expands to D_imp_from_child(imp_dbh, imp_dbh_t, imp_sth).
[Which expands to imp_dbh_t *imp_dbh = (imp_dbh_t*)(DBIc_PARENT_COM(imp_sth))]

Tim

p.s. Questions like this would be better directed to dbi-dev.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql and mysql_use_result

On Fri, Aug 06, 2004 at 01:38:48PM +0100, Alan Burlison wrote:
> I want to use the DBD::mysql 'mysql_use_result' attribute to make the
> driver use mysql_use_result instead of mysql_store_result, and I want to
> do it for all the queries I'm submitting against the database. Having
> to set it on each and every statement handle that I generate is a real
> pain, I'd like to be able to set the default on the database handle and
> have it inherited by all the statement handles that are generated from
> the database handle.
>
> I'm not sure of the best way to go about this, there are two options:
>
> 1. Store the value in the DBH and copy into the STH when the STH is
> created, unless the STD constructor is given an explicit value.
>
> 2. Store the value in the DBH and each time the STH is executed, look up
> the value in the parent DBH if the STH doesn't have an explicit value.
>
> e.g. given:
>
> $dbh->{mysql_use_result} = 1;
> my $sth = $dbh->prepare(...);
> $dbh->{mysql_use_result} = 0;
>
> under option 1, when $sth is executed the value of the mysql_use_result
> attribute will be 1, and under option 2 it will be 0.

Use option 1.

So the code in dbd_st_prepare would look like:

imp_sth->use_mysql_use_result = svp ? SvTRUE(*svp) : imp_dbh->use_mysql_use_result

> One other question - how do I get the parent DBH from a STH from within
> the XS code for a DBD driver?

Anywhere there's an imp_sth in scope you can declare a imp_dbh this way:

D_imp_dbh_from_sth;

That's a macro that expands to D_imp_from_child(imp_dbh, imp_dbh_t, imp_sth).
[Which expands to imp_dbh_t *imp_dbh = (imp_dbh_t*)(DBIc_PARENT_COM(imp_sth))]

Tim

p.s. Questions like this would be better directed to dbi-dev.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql and mysql_use_result

Alexey Stroganov wrote:

> It was already implemented in my tree with the same behaviour as for
> 'mysql_server_prepare' attribute:
>
> - attribute 'mysql_use_result' on dbh level, this allows to
> enable/disable mode for all new statements:
>
> - DBI->connect("DBI:mysql:test;mysql_use_result=1", "root", "");
>
> - $dbh->{'mysql_use_result'}=0; #disable
> $dbh->{'mysql_use_result'}=1; #enable
>
> When new sth is created we set default value of attribute
> 'mysql_use_result' from $dbh. One can override the default
> value:
>
> - $sth=$dbh->prepare(statement, {'mysql_use_result' => 1});
>
> - $sth->{'mysql_use_result'}=0; #disable
> $sth->{'mysql_use_result'}=1; #enable

Oooh, I'd *love* a copy, pretty please ;-)

What does 'mysql_server_prepare' do BTW, I can't find it in the docs?

--
Alan Burlison
--

Re: DBD::mysql and mysql_use_result

Alexey Stroganov wrote:

> It was already implemented in my tree with the same behaviour as for
> 'mysql_server_prepare' attribute:
>
> - attribute 'mysql_use_result' on dbh level, this allows to
> enable/disable mode for all new statements:
>
> - DBI->connect("DBI:mysql:test;mysql_use_result=1", "root", "");
>
> - $dbh->{'mysql_use_result'}=0; #disable
> $dbh->{'mysql_use_result'}=1; #enable
>
> When new sth is created we set default value of attribute
> 'mysql_use_result' from $dbh. One can override the default
> value:
>
> - $sth=$dbh->prepare(statement, {'mysql_use_result' => 1});
>
> - $sth->{'mysql_use_result'}=0; #disable
> $sth->{'mysql_use_result'}=1; #enable

Oooh, I'd *love* a copy, pretty please ;-)

What does 'mysql_server_prepare' do BTW, I can't find it in the docs?

--
Alan Burlison
--

Re: DBD::mysql and mysql_use_result

On Fri, Aug 06, 2004 at 01:38:48PM +0100, Alan Burlison wrote:
> I want to use the DBD::mysql 'mysql_use_result' attribute to make the
> driver use mysql_use_result instead of mysql_store_result, and I want to
> do it for all the queries I'm submitting against the database. Having
> to set it on each and every statement handle that I generate is a real
> pain, I'd like to be able to set the default on the database handle and
> have it inherited by all the statement handles that are generated from
> the database handle.
>
> I'm not sure of the best way to go about this, there are two options:
>
> 1. Store the value in the DBH and copy into the STH when the STH is
> created, unless the STD constructor is given an explicit value.
>
> 2. Store the value in the DBH and each time the STH is executed, look up
> the value in the parent DBH if the STH doesn't have an explicit value.


It was already implemented in my tree with the same behaviour as for
'mysql_server_prepare' attribute:

- attribute 'mysql_use_result' on dbh level, this allows to
enable/disable mode for all new statements:

- DBI->connect("DBI:mysql:test;mysql_use_result=1", "root", "");

- $dbh->{'mysql_use_result'}=0; #disable
$dbh->{'mysql_use_result'}=1; #enable

When new sth is created we set default value of attribute
'mysql_use_result' from $dbh. One can override the default
value:

- $sth=$dbh->prepare(statement, {'mysql_use_result' => 1});

- $sth->{'mysql_use_result'}=0; #disable
$sth->{'mysql_use_result'}=1; #enable


--
Mr. Alexey Stroganov
Full-Time Benchmarks Developer
MySQL AB, www.mysql.com

Re: DBD::mysql and mysql_use_result

On Fri, Aug 06, 2004 at 01:38:48PM +0100, Alan Burlison wrote:
> I want to use the DBD::mysql 'mysql_use_result' attribute to make the
> driver use mysql_use_result instead of mysql_store_result, and I want to
> do it for all the queries I'm submitting against the database. Having
> to set it on each and every statement handle that I generate is a real
> pain, I'd like to be able to set the default on the database handle and
> have it inherited by all the statement handles that are generated from
> the database handle.
>
> I'm not sure of the best way to go about this, there are two options:
>
> 1. Store the value in the DBH and copy into the STH when the STH is
> created, unless the STD constructor is given an explicit value.
>
> 2. Store the value in the DBH and each time the STH is executed, look up
> the value in the parent DBH if the STH doesn't have an explicit value.


It was already implemented in my tree with the same behaviour as for
'mysql_server_prepare' attribute:

- attribute 'mysql_use_result' on dbh level, this allows to
enable/disable mode for all new statements:

- DBI->connect("DBI:mysql:test;mysql_use_result=1", "root", "");

- $dbh->{'mysql_use_result'}=0; #disable
$dbh->{'mysql_use_result'}=1; #enable

When new sth is created we set default value of attribute
'mysql_use_result' from $dbh. One can override the default
value:

- $sth=$dbh->prepare(statement, {'mysql_use_result' => 1});

- $sth->{'mysql_use_result'}=0; #disable
$sth->{'mysql_use_result'}=1; #enable


--
Mr. Alexey Stroganov
Full-Time Benchmarks Developer
MySQL AB, www.mysql.com

Re: DBD::mysql and mysql_use_result

On Fri, Aug 06, 2004 at 07:39:11PM +0300, Alexey Stroganov wrote:
>
> It was already implemented in my tree with the same behaviour as for
> 'mysql_server_prepare' attribute:

How are you intending to ensure that the DBD::mysql source at mysql.com
doesn not diverge from that on CPAN?

Tim.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql and mysql_use_result

On Fri, Aug 06, 2004 at 07:39:11PM +0300, Alexey Stroganov wrote:
>
> It was already implemented in my tree with the same behaviour as for
> 'mysql_server_prepare' attribute:

How are you intending to ensure that the DBD::mysql source at mysql.com
doesn not diverge from that on CPAN?

Tim.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql and mysql_use_result

On Aug 6, 2004, at 9:19 AM, Alan Burlison wrote:

> Alexey Stroganov wrote:
>
>> It was already implemented in my tree with the same behaviour as for
>> 'mysql_server_prepare' attribute:
>> - attribute 'mysql_use_result' on dbh level, this allows to
>> enable/disable mode for all new statements:
>> - DBI->connect("DBI:mysql:test;mysql_use_result=1", "root", "");
>> - $dbh->{'mysql_use_result'}=0; #disable
>> $dbh->{'mysql_use_result'}=1; #enable
>> When new sth is created we set default value of attribute
>> 'mysql_use_result' from $dbh. One can override the default
>> value:
>> - $sth=$dbh->prepare(statement, {'mysql_use_result' => 1});
>> - $sth->{'mysql_use_result'}=0; #disable
>> $sth->{'mysql_use_result'}=1; #enable
>
> Oooh, I'd *love* a copy, pretty please ;-)
>
> What does 'mysql_server_prepare' do BTW, I can't find it in the docs?

It's not public yet, and therefore not documented as such, but
mysql_server_prepare enables the server to prepare the statement as
opposed to emulating the prepare in the driver. Prior to mysql 4.1,
there was no support for server prepare statements, so it was up to the
driver to parse placeholders and then substitute values upon execution.
The current DBD::mysql that is public does this. With the latest
changes, the server will prepare the statement. This can bring some
performance increase, especially if you are dealing with a lot of
inserts.

As far as use_result vs. store result, the server prepared statements
(mysql 4.1 and greater) will always use 'store result', as this has no
affect on performance as per the API documentation.

I will make my OSCON slides public which explain how this works, and
also be glad to answer anyone's questions about this.

regards

Patrick
>
> --
> Alan Burlison
> --
>
Patrick Galbraith Senior Software Developer
patg@mysql.com http://www.mysql.com

"Whatever action a great man performs, common men follow. Whatever
standards he sets by exemplary acts, all the world pursues" --
Bhagavad Gita

Re: DBD::mysql and mysql_use_result

On Aug 6, 2004, at 9:19 AM, Alan Burlison wrote:

> Alexey Stroganov wrote:
>
>> It was already implemented in my tree with the same behaviour as for
>> 'mysql_server_prepare' attribute:
>> - attribute 'mysql_use_result' on dbh level, this allows to
>> enable/disable mode for all new statements:
>> - DBI->connect("DBI:mysql:test;mysql_use_result=1", "root", "");
>> - $dbh->{'mysql_use_result'}=0; #disable
>> $dbh->{'mysql_use_result'}=1; #enable
>> When new sth is created we set default value of attribute
>> 'mysql_use_result' from $dbh. One can override the default
>> value:
>> - $sth=$dbh->prepare(statement, {'mysql_use_result' => 1});
>> - $sth->{'mysql_use_result'}=0; #disable
>> $sth->{'mysql_use_result'}=1; #enable
>
> Oooh, I'd *love* a copy, pretty please ;-)
>
> What does 'mysql_server_prepare' do BTW, I can't find it in the docs?

It's not public yet, and therefore not documented as such, but
mysql_server_prepare enables the server to prepare the statement as
opposed to emulating the prepare in the driver. Prior to mysql 4.1,
there was no support for server prepare statements, so it was up to the
driver to parse placeholders and then substitute values upon execution.
The current DBD::mysql that is public does this. With the latest
changes, the server will prepare the statement. This can bring some
performance increase, especially if you are dealing with a lot of
inserts.

As far as use_result vs. store result, the server prepared statements
(mysql 4.1 and greater) will always use 'store result', as this has no
affect on performance as per the API documentation.

I will make my OSCON slides public which explain how this works, and
also be glad to answer anyone's questions about this.

regards

Patrick
>
> --
> Alan Burlison
> --
>
Patrick Galbraith Senior Software Developer
patg@mysql.com http://www.mysql.com

"Whatever action a great man performs, common men follow. Whatever
standards he sets by exemplary acts, all the world pursues" --
Bhagavad Gita

Re: DBD::mysql and mysql_use_result

Alan,

The macro is D_imp_dbh_from_sth,

regards,

Patrick

On Aug 6, 2004, at 5:51 AM, Jochen Wiedmann wrote:

> Alan Burlison wrote:
>
>> One other question - how do I get the parent DBH from a STH from
>> within the XS code for a DBD driver?
>
>
> There's a DBI macro for that, I do not remember the name, but most
> probably its DBIxxx_parent or similar.
>
Patrick Galbraith Senior Software Developer
patg@mysql.com http://www.mysql.com

"Whatever action a great man performs, common men follow. Whatever
standards he sets by exemplary acts, all the world pursues" --
Bhagavad Gita

Re: DBD::mysql and mysql_use_result

Alan,

The macro is D_imp_dbh_from_sth,

regards,

Patrick

On Aug 6, 2004, at 5:51 AM, Jochen Wiedmann wrote:

> Alan Burlison wrote:
>
>> One other question - how do I get the parent DBH from a STH from
>> within the XS code for a DBD driver?
>
>
> There's a DBI macro for that, I do not remember the name, but most
> probably its DBIxxx_parent or similar.
>
Patrick Galbraith Senior Software Developer
patg@mysql.com http://www.mysql.com

"Whatever action a great man performs, common men follow. Whatever
standards he sets by exemplary acts, all the world pursues" --
Bhagavad Gita

Re: DBD::mysql and mysql_use_result

Tim,

It won't. We will work with the maintainer to ensure that CPAN and
mysql.com's source is the same. No matter how we internally manage
DBD::mysql code internally, we will go through CPAN/cvs to merge our
work.

regards,

Patrick

On Aug 7, 2004, at 12:54 PM, Tim Bunce wrote:

> On Fri, Aug 06, 2004 at 07:39:11PM +0300, Alexey Stroganov wrote:
>>
>> It was already implemented in my tree with the same behaviour as for
>> 'mysql_server_prepare' attribute:
>
> How are you intending to ensure that the DBD::mysql source at mysql.com
> doesn not diverge from that on CPAN?
>
> Tim.
>
Patrick Galbraith Senior Software Developer
patg@mysql.com http://www.mysql.com

"Whatever action a great man performs, common men follow. Whatever
standards he sets by exemplary acts, all the world pursues" --
Bhagavad Gita

Re: DBD::mysql and mysql_use_result

Tim,

It won't. We will work with the maintainer to ensure that CPAN and
mysql.com's source is the same. No matter how we internally manage
DBD::mysql code internally, we will go through CPAN/cvs to merge our
work.

regards,

Patrick

On Aug 7, 2004, at 12:54 PM, Tim Bunce wrote:

> On Fri, Aug 06, 2004 at 07:39:11PM +0300, Alexey Stroganov wrote:
>>
>> It was already implemented in my tree with the same behaviour as for
>> 'mysql_server_prepare' attribute:
>
> How are you intending to ensure that the DBD::mysql source at mysql.com
> doesn not diverge from that on CPAN?
>
> Tim.
>
Patrick Galbraith Senior Software Developer
patg@mysql.com http://www.mysql.com

"Whatever action a great man performs, common men follow. Whatever
standards he sets by exemplary acts, all the world pursues" --
Bhagavad Gita

Re: DBD::mysql and mysql_use_result

Patrick Galbraith wrote:

> It's not public yet, and therefore not documented as such, but
> mysql_server_prepare enables the server to prepare the statement as
> opposed to emulating the prepare in the driver. Prior to mysql 4.1,
> there was no support for server prepare statements, so it was up to the
> driver to parse placeholders and then substitute values upon execution.
> The current DBD::mysql that is public does this. With the latest
> changes, the server will prepare the statement. This can bring some
> performance increase, especially if you are dealing with a lot of inserts.

I am, and lots of updates as well.

> As far as use_result vs. store result, the server prepared statements
> (mysql 4.1 and greater) will always use 'store result', as this has no
> affect on performance as per the API documentation.

Umm, I thought store_result meant that the data was transferred over to
the client in one big wodge? In my case that constitutes nearly 100Mb
of memory, which most certainly *does* have an effect on performance, so
I can't see how server prepared statements are going to help - and I
also don't understand how server prepared statements mandates the use of
store_result...

--
Alan Burlison
--

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql and mysql_use_result

Patrick Galbraith wrote:

> It's not public yet, and therefore not documented as such, but
> mysql_server_prepare enables the server to prepare the statement as
> opposed to emulating the prepare in the driver. Prior to mysql 4.1,
> there was no support for server prepare statements, so it was up to the
> driver to parse placeholders and then substitute values upon execution.
> The current DBD::mysql that is public does this. With the latest
> changes, the server will prepare the statement. This can bring some
> performance increase, especially if you are dealing with a lot of inserts.

I am, and lots of updates as well.

> As far as use_result vs. store result, the server prepared statements
> (mysql 4.1 and greater) will always use 'store result', as this has no
> affect on performance as per the API documentation.

Umm, I thought store_result meant that the data was transferred over to
the client in one big wodge? In my case that constitutes nearly 100Mb
of memory, which most certainly *does* have an effect on performance, so
I can't see how server prepared statements are going to help - and I
also don't understand how server prepared statements mandates the use of
store_result...

--
Alan Burlison
--

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql and mysql_use_result

On Sat, 7 Aug 2004, Patrick Galbraith wrote:

> As far as use_result vs. store result, the server prepared statements
> (mysql 4.1 and greater) will always use 'store result', as this has no
> affect on performance as per the API documentation.
>

I plan, if possible, to make "use result" the default with prepared statements,
my thinking here is this: if you are going to be using parepared statements, you
will more than likly be running in a persistant envionment, in which case,
memory usage becomes more of an issue. esp. in something like a mod_perl
envionment because when you use "store result" you will more than likely use 2x
the memory (or more) -- Think of an application that munges the result result
set and pases the munged data off to a templating system for a total of three
times memory usage of just the result set.

And from my reading of the docs (though I have not had a chance to test this
yet), mysql_stmt_store_result() need not be called mysql_stmt_fetch(). Is this
the case?


Rudy


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql and mysql_use_result

On Sat, 7 Aug 2004, Patrick Galbraith wrote:

> As far as use_result vs. store result, the server prepared statements
> (mysql 4.1 and greater) will always use 'store result', as this has no
> affect on performance as per the API documentation.
>

I plan, if possible, to make "use result" the default with prepared statements,
my thinking here is this: if you are going to be using parepared statements, you
will more than likly be running in a persistant envionment, in which case,
memory usage becomes more of an issue. esp. in something like a mod_perl
envionment because when you use "store result" you will more than likely use 2x
the memory (or more) -- Think of an application that munges the result result
set and pases the munged data off to a templating system for a total of three
times memory usage of just the result set.

And from my reading of the docs (though I have not had a chance to test this
yet), mysql_stmt_store_result() need not be called mysql_stmt_fetch(). Is this
the case?


Rudy


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql and mysql_use_result

On Mon, Aug 09, 2004 at 11:01:12PM -0400, Rudy Lippan wrote:
> On Sat, 7 Aug 2004, Patrick Galbraith wrote:
>
> > As far as use_result vs. store result, the server prepared statements
> > (mysql 4.1 and greater) will always use 'store result', as this has no
> > affect on performance as per the API documentation.
> >
>
> I plan, if possible, to make "use result" the default with prepared statements,
> my thinking here is this: if you are going to be using parepared statements, you
> will more than likly be running in a persistant envionment, in which case,
> memory usage becomes more of an issue. esp. in something like a mod_perl
> envionment because when you use "store result" you will more than likely use 2x
> the memory (or more) -- Think of an application that munges the result result
> set and pases the munged data off to a templating system for a total of three
> times memory usage of just the result set.

Does the binary protocol allow the link to be used for other actions while
there are still rows being sent to the client?

If not "use result" can't be the default as too much existing code would break.

Tim.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql and mysql_use_result

On Mon, Aug 09, 2004 at 11:01:12PM -0400, Rudy Lippan wrote:
> On Sat, 7 Aug 2004, Patrick Galbraith wrote:
>
> > As far as use_result vs. store result, the server prepared statements
> > (mysql 4.1 and greater) will always use 'store result', as this has no
> > affect on performance as per the API documentation.
> >
>
> I plan, if possible, to make "use result" the default with prepared statements,
> my thinking here is this: if you are going to be using parepared statements, you
> will more than likly be running in a persistant envionment, in which case,
> memory usage becomes more of an issue. esp. in something like a mod_perl
> envionment because when you use "store result" you will more than likely use 2x
> the memory (or more) -- Think of an application that munges the result result
> set and pases the munged data off to a templating system for a total of three
> times memory usage of just the result set.

Does the binary protocol allow the link to be used for other actions while
there are still rows being sent to the client?

If not "use result" can't be the default as too much existing code would break.

Tim.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql and mysql_use_result

Tim Bunce wrote:

> Does the binary protocol allow the link to be used for other actions while
> there are still rows being sent to the client?
>
> If not "use result" can't be the default as too much existing code would break.

My experementation would suggest that it doesn't - if you turn
use_result on with the existing driver than then try to do a nested
prepare/execute/fetch within the outer loop, to get an error - I can't
remember the exact wording, but the gist is 'You can't do that now'.

--
Alan Burlison
--

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql and mysql_use_result

Tim Bunce wrote:

> Does the binary protocol allow the link to be used for other actions while
> there are still rows being sent to the client?
>
> If not "use result" can't be the default as too much existing code would break.

My experementation would suggest that it doesn't - if you turn
use_result on with the existing driver than then try to do a nested
prepare/execute/fetch within the outer loop, to get an error - I can't
remember the exact wording, but the gist is 'You can't do that now'.

--
Alan Burlison
--

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql and mysql_use_result

On Fri, Aug 13, 2004 at 11:17:51PM +0100, Alan Burlison wrote:
> Tim Bunce wrote:
>
> >Does the binary protocol allow the link to be used for other actions while
> >there are still rows being sent to the client?
> >
> >If not "use result" can't be the default as too much existing code would
> >break.
>
> My experementation would suggest that it doesn't - if you turn
> use_result on with the existing driver than then try to do a nested
> prepare/execute/fetch within the outer loop, to get an error - I can't
> remember the exact wording, but the gist is 'You can't do that now'.

I thought so. Thanks for confirming it. (The standard workaround of using
multiple dbh applies.)

Tim.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql and mysql_use_result

On Fri, Aug 13, 2004 at 11:17:51PM +0100, Alan Burlison wrote:
> Tim Bunce wrote:
>
> >Does the binary protocol allow the link to be used for other actions while
> >there are still rows being sent to the client?
> >
> >If not "use result" can't be the default as too much existing code would
> >break.
>
> My experementation would suggest that it doesn't - if you turn
> use_result on with the existing driver than then try to do a nested
> prepare/execute/fetch within the outer loop, to get an error - I can't
> remember the exact wording, but the gist is 'You can't do that now'.

I thought so. Thanks for confirming it. (The standard workaround of using
multiple dbh applies.)

Tim.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql and mysql_use_result

On Aug 9, 2004, at 8:01 PM, Rudy Lippan wrote:

> On Sat, 7 Aug 2004, Patrick Galbraith wrote:
>
>> As far as use_result vs. store result, the server prepared statements
>> (mysql 4.1 and greater) will always use 'store result', as this has no
>> affect on performance as per the API documentation.
>>
>
> I plan, if possible, to make "use result" the default with prepared
> statements,
> my thinking here is this: if you are going to be using parepared
> statements, you
> will more than likly be running in a persistant envionment, in which
> case,
> memory usage becomes more of an issue. esp. in something like a
> mod_perl
> envionment because when you use "store result" you will more than
> likely use 2x
> the memory (or more) -- Think of an application that munges the result
> result
> set and pases the munged data off to a templating system for a total
> of three
> times memory usage of just the result set.
>

Rudy,

Hmm... I can see that point. Is there something similar to this in
other database drivers? What setting do they default to? I would like
to bring this up too with the fellow who implemented the server prepare
statements in the server and see what he thinks.


> And from my reading of the docs (though I have not had a chance to
> test this
> yet), mysql_stmt_store_result() need not be called mysql_stmt_fetch().
> Is this
> the case?
>

Yes, an I just tested this with the latest code I've worked on, and it
works without store_result. I'm curious to see what my benchmarking
will show ;)

As far as the question about use_result, I'll have to test this.

regards,

Patrick


>
> Rudy
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=patg@mysql.com
>
Patrick Galbraith Senior Software Developer
patg@mysql.com http://www.mysql.com

"Whatever action a great man performs, common men follow. Whatever
standards he sets by exemplary acts, all the world pursues" --
Bhagavad Gita


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql and mysql_use_result

On Aug 9, 2004, at 8:01 PM, Rudy Lippan wrote:

> On Sat, 7 Aug 2004, Patrick Galbraith wrote:
>
>> As far as use_result vs. store result, the server prepared statements
>> (mysql 4.1 and greater) will always use 'store result', as this has no
>> affect on performance as per the API documentation.
>>
>
> I plan, if possible, to make "use result" the default with prepared
> statements,
> my thinking here is this: if you are going to be using parepared
> statements, you
> will more than likly be running in a persistant envionment, in which
> case,
> memory usage becomes more of an issue. esp. in something like a
> mod_perl
> envionment because when you use "store result" you will more than
> likely use 2x
> the memory (or more) -- Think of an application that munges the result
> result
> set and pases the munged data off to a templating system for a total
> of three
> times memory usage of just the result set.
>

Rudy,

Hmm... I can see that point. Is there something similar to this in
other database drivers? What setting do they default to? I would like
to bring this up too with the fellow who implemented the server prepare
statements in the server and see what he thinks.


> And from my reading of the docs (though I have not had a chance to
> test this
> yet), mysql_stmt_store_result() need not be called mysql_stmt_fetch().
> Is this
> the case?
>

Yes, an I just tested this with the latest code I've worked on, and it
works without store_result. I'm curious to see what my benchmarking
will show ;)

As far as the question about use_result, I'll have to test this.

regards,

Patrick


>
> Rudy
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=patg@mysql.com
>
Patrick Galbraith Senior Software Developer
patg@mysql.com http://www.mysql.com

"Whatever action a great man performs, common men follow. Whatever
standards he sets by exemplary acts, all the world pursues" --
Bhagavad Gita


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org