------=_NextPart_000_0005_01C50BC7.39769F80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello,=20

I have used prepared statements almost exclusively when invoking JDBC =
calls (because of the putative performance advantages).=20

But I've spent some time perusing the code (3.1.6) and this forum and =
I'm wondering whether I've been using them correctly.=20

It looks like any PreparedStatement instance (created via =
connection.prepareStatement("SQL . . .")) is only "re-usable" on the =
"same" connection instance, and in fact is only re-usable by client code =
maintaining a reference to the original PreparedStatement instance. Is =
this correct?=20

Example:=20
final String SQL_SELECT =3D "SELECT * FROM Table WHERE x =3D ?";=20
PreparedStatement prep =3D conn.prepareStatement(SQL_SELECT);=20
prep.setInt(32);=20
Resultset results =3D prep.executeQuery();=20
////////Process resultset=20
///////Use again with same references=20
prep.setInt(33);=20
results =3D prep.executeQuery();=20
////////Process new resultset=20
My question is, is this the only way to gain the performance benefit =
from a PreparedStatement?i.e. there is no connection-level or =
(even-better) DataSource-level "cache" of PreparedStatements?=20
so that conn.prepareStatement(SQL_SELECT) checks the client-side cache =
to see if there is a com.mysql.jdbc.PreparedStatement instance already =
associated with the "key" SQL_SELECT?=20

I had posted this on the Connectors>>jdbc forum.
****Digression--feel free to ignore*******
After spending some more time looking at all relevant posts, I found =
something where Mark mentioned that the kind of "client-side" cache that =
I'm talking about is only provided by connection pools. This is =
interesting, as the mysql PreparedStatement sources are associated with =
a single Connection (at instantiation time) and there is no mutator =
method to (re)set the connection.
This means that calling PreparedStatement.close() essentially renders =
the PreparedStatement instance unusable (because this.connection =3D =
null)--and I would think this is the usual usage. So even if the pool =
maintains a reference to the PreparedStatement instance, there is no =
possibility to call execute() upon it. No? Perhaps connection pools =
implement PreparedStatement wrappers as they do for Connections, but =
what's the contract for notifying the "physical" PreparedStatement that =
it's wrapper has had close() called?
------=_NextPart_000_0005_01C50BC7.39769F80--

tnabe wrote:

Read the documentation for the JDBC connection URL:

http://dev.mysql.com/doc/connector/j...classname.html

Specifically cachePrepStmts

.... Kevin

--

Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an
invite! Also see irc.freenode.net #rojo if you want to chat.

Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html

If you're interested in RSS, Weblogs, Social Networking, etc... then you
should work for Rojo! If you recommend someone and we hire them you'll
get a free iPod!

Kevin A. Burton, Location - San Francisco, CA
AIM/YIM - sfburtonator, Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412


--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe: http://lists.mysql.com/java?unsub=my...ie.nctu.edu.tw