sybperl-l Archive

From: michael dot peppler at bnpparibas dot com
Subject: Re: About prepared statements asnd transactions....
Date: Oct 11 2005 1:31PM

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.

Michael




Internet
yves.orton@de.mci.com@peppler.org - 11/10/2005 12:10


Sent by:    owner-sybperl-l@peppler.org

To:    sybperl-l

cc:


Subject:    About prepared statements asnd transactions....




Hi All,

Im wondering what people think about having the option to have client side
statement handles that arent translated to server side temporary stored
procs.

This idea came to mind when I was using a large number of prepared queries
and discovered that doing so can silently lead to inadverdant transactional
violations.

I was thinking basically that in some cases the advantage of stored procs
is the simplicity of use and not the fact that the query is being created
as a temporary stored proc on the server that can be called via RPC. If it
were possible to have statement handles that were client side and just
responsible for appropriately quoting their placeholders then there would
be no issues with transasctional problems and stored proc creation or
whatever.

IIUIC this would allow using multiple statement handles in transasctions,
and if triggered by the presence of AutoCommit would make it much more
difficult to transasctionally blow your foot off.

Maybe this mail reveals a serious flaw in understanding of how DBD::Sybase
works, if so please straighten me out.

Cheers,
Yves







This message and any attachments (the "message") is
intended solely for the addressees and is confidential. 
If you receive this message in error, please delete it and 
immediately notify the sender. Any use not in accord with 
its purpose, any dissemination or disclosure, either whole 
or partial, is prohibited except formal approval. The internet
can not guarantee the integrity of this message. 
BNP PARIBAS (and its subsidiaries) shall (will) not 
therefore be liable for the message if modified. 

                ---------------------------------------------

Ce message et toutes les pieces jointes (ci-apres le 
"message") sont etablis a l'intention exclusive de ses 
destinataires et sont confidentiels. Si vous recevez ce 
message par erreur, merci de le detruire et d'en avertir 
immediatement l'expediteur. Toute utilisation de ce 
message non conforme a sa destination, toute diffusion 
ou toute publication, totale ou partielle, est interdite, sauf 
autorisation expresse. L'internet ne permettant pas 
d'assurer l'integrite de ce message, BNP PARIBAS (et ses
filiales) decline(nt) toute responsabilite au titre de ce 
message, dans l'hypothese ou il aurait ete modifie.