PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: "Orton, Yves" <yves dot orton at de dot mci dot com>
Subject: RE: About prepared statements asnd transactions....
Date: Oct 11 2005 1:39PM

> At the Sybase API level there are two types of "prepared" statements.
> 
> The one that DBD::Sybase uses is the "dynamic" prepare: this sends the
> statement to be prepared to the server, where it is analyzed 
> and converted
> to a lightweight stored proc. This has a couple of positive 
> effects: the
> data types for each parameter is determined by the server, and the
> statement is precompiled and optimized, so execution is very fast. The
> downside is that you may have a situation when AutoCommit is 
> off that could
> be problematic when you have several prepared statements active.
> 
> The second type of prepared statement is the one used by jConnect by
> default: this replaces the ? placeholders with @p1, @p2, etc, 
> and then does
> a dynamic execution (sends the query for each execution, with the
> parameters). Each execution is of course independant of the 
> others, which
> may be beneficial in the situation where you want to have a 
> lot of stuff
> prepared in advance (note however that in this situation there is NO
> performance advantage to having the statements prepared in advance!)
> 
> I've never tried to use the second method in C, but I could 
> maybe look at this if there is enough interest.

Id love to hear about it, especially if it made using multiple prepared
statements within a transaction possible.

I think the general issue is that more often than not prepared queries are
recommended to ease issues like proper quoting of arguments, not because
they are faster. Inside of a transaction id happily get rid of the speed to
be able to simplify my code to use multiple prepared statements.

How its done doesn't bother me much. Id be happy if they were client side
like MySql but the jConnect idea sounds interesting as well.

The main issue for me is that currently prepared handles + transasctions are
dangerous. I don't think that prepared handles should be dangerous ever,
especially given the general advice about using placeholders as often as
possible.

Cheers and thanks for the reply
Yves