RE: Prepared Statements

Ben

MySQL supports only one type of prepared statement and that
is server side.
The second URL you included just describes the SQL syntax
for the PS.  Frank
is also right when he says that there are currently some
issues with PS and
we have provided a connection string option to disable them
(this is on by
default).

Reggie

> 
> Dear MySql.NET,
> 
> I have been using MySql.NET on Mono and find it easy to
use and fast.
> 
> I read that the MySql.NET API uses prepared statements.
 I'm trying to
> find out a bit more about this.  I know MySQL has two
types of Server
> Side Prepared Statement.  There are also Client Side
prepared
> statements
> which are external to MySQL.
> 
> One Server Site Prepared Statement uses a custom binary
protocol for
> fast low bandwidth data transfer:
> 
> http://dev.mysql.com/doc/refman/5.0/en/c-ap
i-prepared-statements.html
> 
> The other is formed in SQL and uses the standard text
protocol:
> 
> htt
p://dev.mysql.com/doc/refman/5.0/en/sqlps.html
> 
> But both have a limitation that they doesn't use the
Query Cache:
> 
> http://dev.mysql.com/doc/refman/5.0/en/c-api-pre
pared-statement-
> problems.html
> 
> My question is, which does .NET use?  Can I switch
between them
> depending on the behaviour I want?
> 
> Thanks for any help you can give me,
> 
> Ben Clewett.
> 
> 
> 
> 
> --
> MySQL on .NET Mailing List
> For list archives: http://lists.mysql.com/
dotnet
> To unsubscribe:    http://lis
ts.mysql.com/dotnet?unsub=reggiemysql.com



-- 
MySQL on .NET Mailing List
For list archives: http://lists.mysql.com/
dotnet
To unsubscribe:    http://lists
.mysql.com/dotnet?unsub=bondyahoo.com

Hi Reggie,

I understand about the problem.

Oddly when I went on the MySql 4-day tuning course, the
impression I got 
was that there were definitely two kinds of server-side
prepared statement.

One in SQL syntax which is a half-way implementation.  Using
the 
standard text transport protocol and standard SQL.

The other which uses a fast binary protocol and is the full

implementation...

I believe you fully when you say there is only one, but I am
interested 
to know where my miss-information came from, and what
MySql.NET is....

Anyway, I greatly look forward to trying the c# when
available, and hope 
this will be available for .NET 1.1...

Regards,

Ben


Reggie Burnett wrote:
> Ben
> 
> MySQL supports only one type of prepared statement and
that is server side.
> The second URL you included just describes the SQL
syntax for the PS.  Frank
> is also right when he says that there are currently
some issues with PS and
> we have provided a connection string option to disable
them (this is on by
> default).
> 
> Reggie
> 
>> Dear MySql.NET,
>>
>> I have been using MySql.NET on Mono and find it
easy to use and fast.
>>
>> I read that the MySql.NET API uses prepared
statements.  I'm trying to
>> find out a bit more about this.  I know MySQL has
two types of Server
>> Side Prepared Statement.  There are also Client
Side prepared
>> statements
>> which are external to MySQL.
>>
>> One Server Site Prepared Statement uses a custom
binary protocol for
>> fast low bandwidth data transfer:
>>
>> http://dev.mysql.com/doc/refman/5.0/en/c-ap
i-prepared-statements.html
>>
>> The other is formed in SQL and uses the standard
text protocol:
>>
>> htt
p://dev.mysql.com/doc/refman/5.0/en/sqlps.html
>>
>> But both have a limitation that they doesn't use
the Query Cache:
>>
>> http://dev.mysql.com/doc/refman/5.0/en/c-api-pre
pared-statement-
>> problems.html
>>
>> My question is, which does .NET use?  Can I switch
between them
>> depending on the behaviour I want?
>>
>> Thanks for any help you can give me,
>>
>> Ben Clewett.
>>
>>
>>
>>
>> --
>> MySQL on .NET Mailing List
>> For list archives: http://lists.mysql.com/
dotnet
>> To unsubscribe:    http://lis
ts.mysql.com/dotnet?unsub=reggiemysql.com
> 
> 
> 


-- 
MySQL on .NET Mailing List
For list archives: http://lists.mysql.com/
dotnet
To unsubscribe:    http://lists
.mysql.com/dotnet?unsub=bondyahoo.com

> 
> Hi Reggie,
> 
> I understand about the problem.
> 
> Oddly when I went on the MySql 4-day tuning course, the
impression I
> got
> was that there were definitely two kinds of server-side
prepared
> statement.
> 
> One in SQL syntax which is a half-way implementation. 
Using the
> standard text transport protocol and standard SQL.
> 
> The other which uses a fast binary protocol and is the
full
> implementation...
> 
> I believe you fully when you say there is only one, but
I am interested
> to know where my miss-information came from, and what
MySql.NET is....

I'm not sure where you misunderstood.  The SQL syntax part
you referred to
is just that, SQL syntax.  It's the same prepared statements
under the hood.


> 
> Anyway, I greatly look forward to trying the c# when
available, and
> hope
> this will be available for .NET 1.1...

Not sure what you mean by 'trying the C# when available'. 
We've had a .NET
connector out for > 2 years now.  We have one that is
built with .NET 1.1
and one that is targeted at .NET 2.0

Reggie


-- 
MySQL on .NET Mailing List
For list archives: http://lists.mysql.com/
dotnet
To unsubscribe:    http://lists
.mysql.com/dotnet?unsub=bondyahoo.com

> I'm not sure where you misunderstood.  The SQL syntax
part you referred to
> is just that, SQL syntax.  It's the same prepared
statements under the hood.


I understand that it's the same 'under the hood'.  That's
really not 
what I asked.

The difference between the two is that one is prepared in a
text string 
using the standard protocol (here in c):

   mysql_query(mysql, "PREPARE stmt1 FROM 'SELECT
....'");
   mysql_query(mysql, "SET a=1");
   mysql_query(mysql, "SET b=2");
   mysql_query(mysql, "EXECUTE stmt1 USING a, b");

As an SQL text string.

The binary version uses a binary channel, as called by:

   mysql_stmt_prepare(stmt, "SELECT ...", 	//
Prepare statement
   mysql_stmt_bind_param(stmt, bind);		// Send variables
binary

The two forms have different SQL syntax, and the two forms
use different 
channels in the MySql transport protocol, the first text,
the second binary.

As the second uses a binary protocol, it is far more
efficient at 
getting data into MySql fast.

Again when returning data, the first version uses the
textual transport 
protocol, the second uses the binary transport protocol. 
Which is 
obviously far faster.

So although they are the same 'under the hood'. there is a
vast difference.

Which is what I was trying to ask this mailing list on my
first email, 
which version does MySql.NET use?


> Not sure what you mean by 'trying the C# when
available'.  We've had a .NET
> connector out for > 2 years now.  We have one that
is built with .NET 1.1
> and one that is targeted at .NET 2.0


Not the connector, I have been using that since first
released.  It's an 
excelent piece of software.  Works just as well on Mono as
MS.

I was talking about prepared statements.  The part of the
.NET which is 
not working currently.  I was asking whether the developers
are still 
planning support for .NET 1.1 in the future and when this is
fixed. 
Which I am hoping as we are stuck with 1.1.

Thanks for you answers,

Ben


-- 
MySQL on .NET Mailing List
For list archives: http://lists.mysql.com/
dotnet
To unsubscribe:    http://lists
.mysql.com/dotnet?unsub=bondyahoo.com

Ben,

> > I'm not sure where you misunderstood.  The SQL
syntax part you
> referred to
> > is just that, SQL syntax.  It's the same prepared
statements under
> the hood.
> 
> 
> I understand that it's the same 'under the hood'. 
That's really not
> what I asked.
> 
> The difference between the two is that one is prepared
in a text string
> using the standard protocol (here in c):
> 
>    mysql_query(mysql, "PREPARE stmt1 FROM 'SELECT
....'");
>    mysql_query(mysql, "SET a=1");
>    mysql_query(mysql, "SET b=2");
>    mysql_query(mysql, "EXECUTE stmt1 USING a, b");
> 
> As an SQL text string.
> 
> The binary version uses a binary channel, as called
by:
> 
>    mysql_stmt_prepare(stmt, "SELECT ...", 	//
Prepare statement
>    mysql_stmt_bind_param(stmt, bind);		// Send
variables
> binary
> 
> The two forms have different SQL syntax, and the two
forms use
> different
> channels in the MySql transport protocol, the first
text, the second
> binary.
> 
> As the second uses a binary protocol, it is far more
efficient at
> getting data into MySql fast.
> 
> Again when returning data, the first version uses the
textual transport
> protocol, the second uses the binary transport
protocol.  Which is
> obviously far faster.
> 
> So although they are the same 'under the hood'. there
is a vast
> difference.
> 
> Which is what I was trying to ask this mailing list on
my first email,
> which version does MySql.NET use?

Sorry about the confusion.  The connector uses the binary
protocol to
execute PS.


> 
> 
> > Not sure what you mean by 'trying the C# when
available'.  We've had
> a .NET
> > connector out for > 2 years now.  We have one
that is built with .NET
> 1.1
> > and one that is targeted at .NET 2.0
> 
> 
> Not the connector, I have been using that since first
released.  It's
> an
> excelent piece of software.  Works just as well on Mono
as MS.
> 
> I was talking about prepared statements.  The part of
the .NET which is
> not working currently.  I was asking whether the
developers are still
> planning support for .NET 1.1 in the future and when
this is fixed.
> Which I am hoping as we are stuck with 1.1.

The problem with PS is not in the connector.  The problem we
found was in
the server.  The server was not always executing PS
correctly.  All the
connectors added options to disable PS at the users request.
 When the PS
subsystem on the server becomes more robust, then users can
remove the
connection string option to disable PS and they will start
working again.

Reggie





-- 
MySQL on .NET Mailing List
For list archives: http://lists.mysql.com/
dotnet
To unsubscribe:    http://lists
.mysql.com/dotnet?unsub=bondyahoo.com

Reggie,

I understand, thank for the info.

Is the PS problem on all versions of MySql?

Ben


Reggie Burnett wrote:
> Ben,
> 
>>> I'm not sure where you misunderstood.  The SQL
syntax part you
>> referred to
>>> is just that, SQL syntax.  It's the same
prepared statements under
>> the hood.
>>
>>
>> I understand that it's the same 'under the hood'. 
That's really not
>> what I asked.
>>
>> The difference between the two is that one is
prepared in a text string
>> using the standard protocol (here in c):
>>
>>    mysql_query(mysql, "PREPARE stmt1 FROM
'SELECT ....'");
>>    mysql_query(mysql, "SET a=1");
>>    mysql_query(mysql, "SET b=2");
>>    mysql_query(mysql, "EXECUTE stmt1 USING
a,
b");
>>
>> As an SQL text string.
>>
>> The binary version uses a binary channel, as called
by:
>>
>>    mysql_stmt_prepare(stmt, "SELECT ...",
	// Prepare statement
>>    mysql_stmt_bind_param(stmt, bind);		// Send
variables
>> binary
>>
>> The two forms have different SQL syntax, and the
two forms use
>> different
>> channels in the MySql transport protocol, the first
text, the second
>> binary.
>>
>> As the second uses a binary protocol, it is far
more efficient at
>> getting data into MySql fast.
>>
>> Again when returning data, the first version uses
the textual transport
>> protocol, the second uses the binary transport
protocol.  Which is
>> obviously far faster.
>>
>> So although they are the same 'under the hood'.
there is a vast
>> difference.
>>
>> Which is what I was trying to ask this mailing list
on my first email,
>> which version does MySql.NET use?
> 
> Sorry about the confusion.  The connector uses the
binary protocol to
> execute PS.
> 
> 
>>
>>> Not sure what you mean by 'trying the C# when
available'.  We've had
>> a .NET
>>> connector out for > 2 years now.  We have
one that is built with .NET
>> 1.1
>>> and one that is targeted at .NET 2.0
>>
>> Not the connector, I have been using that since
first released.  It's
>> an
>> excelent piece of software.  Works just as well on
Mono as MS.
>>
>> I was talking about prepared statements.  The part
of the .NET which is
>> not working currently.  I was asking whether the
developers are still
>> planning support for .NET 1.1 in the future and
when this is fixed.
>> Which I am hoping as we are stuck with 1.1.
> 
> The problem with PS is not in the connector.  The
problem we found was in
> the server.  The server was not always executing PS
correctly.  All the
> connectors added options to disable PS at the users
request.  When the PS
> subsystem on the server becomes more robust, then users
can remove the
> connection string option to disable PS and they will
start working again.
> 
> Reggie
> 
> 
> 
> 
> 


-- 
MySQL on .NET Mailing List
For list archives: http://lists.mysql.com/
dotnet
To unsubscribe:    http://lists
.mysql.com/dotnet?unsub=bondyahoo.com

All versions that support PS, yes.

> -----Original Message-----
> From: Ben Clewett [mailto:benroadrunner.uk.com]
> Sent: Friday, February 23, 2007 2:08 AM
> To: Reggie Burnett
> Cc: dotnetlists.mysql.com
> Subject: Re: Prepared Statements
> 
> Reggie,
> 
> I understand, thank for the info.
> 
> Is the PS problem on all versions of MySql?
> 
> Ben
> 
> 
> Reggie Burnett wrote:
> > Ben,
> >
> >>> I'm not sure where you misunderstood.  The
SQL syntax part you
> >> referred to
> >>> is just that, SQL syntax.  It's the same
prepared statements under
> >> the hood.
> >>
> >>
> >> I understand that it's the same 'under the
hood'.  That's really not
> >> what I asked.
> >>
> >> The difference between the two is that one is
prepared in a text
> string
> >> using the standard protocol (here in c):
> >>
> >>    mysql_query(mysql, "PREPARE stmt1 FROM
'SELECT ....'");
> >>    mysql_query(mysql, "SET a=1");
> >>    mysql_query(mysql, "SET b=2");
> >>    mysql_query(mysql, "EXECUTE stmt1
USING a, b");
> >>
> >> As an SQL text string.
> >>
> >> The binary version uses a binary channel, as
called by:
> >>
> >>    mysql_stmt_prepare(stmt, "SELECT
...", 	// Prepare statement
> >>    mysql_stmt_bind_param(stmt, bind);		// Send
variables
> >> binary
> >>
> >> The two forms have different SQL syntax, and
the two forms use
> >> different
> >> channels in the MySql transport protocol, the
first text, the second
> >> binary.
> >>
> >> As the second uses a binary protocol, it is
far more efficient at
> >> getting data into MySql fast.
> >>
> >> Again when returning data, the first version
uses the textual
> transport
> >> protocol, the second uses the binary transport
protocol.  Which is
> >> obviously far faster.
> >>
> >> So although they are the same 'under the
hood'. there is a vast
> >> difference.
> >>
> >> Which is what I was trying to ask this mailing
list on my first
> email,
> >> which version does MySql.NET use?
> >
> > Sorry about the confusion.  The connector uses the
binary protocol to
> > execute PS.
> >
> >
> >>
> >>> Not sure what you mean by 'trying the C#
when available'.  We've
> had
> >> a .NET
> >>> connector out for > 2 years now.  We
have one that is built with
> .NET
> >> 1.1
> >>> and one that is targeted at .NET 2.0
> >>
> >> Not the connector, I have been using that
since first released.
> It's
> >> an
> >> excelent piece of software.  Works just as
well on Mono as MS.
> >>
> >> I was talking about prepared statements.  The
part of the .NET which
> is
> >> not working currently.  I was asking whether
the developers are
> still
> >> planning support for .NET 1.1 in the future
and when this is fixed.
> >> Which I am hoping as we are stuck with 1.1.
> >
> > The problem with PS is not in the connector.  The
problem we found
> was in
> > the server.  The server was not always executing
PS correctly.  All
> the
> > connectors added options to disable PS at the
users request.  When
> the PS
> > subsystem on the server becomes more robust, then
users can remove
> the
> > connection string option to disable PS and they
will start working
> again.
> >
> > Reggie
> >
> >
> >
> >
> >
> 
> 
> --
> MySQL on .NET Mailing List
> For list archives: http://lists.mysql.com/
dotnet
> To unsubscribe:    http://lis
ts.mysql.com/dotnet?unsub=reggiemysql.com



-- 
MySQL on .NET Mailing List
For list archives: http://lists.mysql.com/
dotnet
To unsubscribe:    http://lists
.mysql.com/dotnet?unsub=bondyahoo.com