Prepared stmt vs Connection Pooling
Roedy Green
PreparedStatement, unless you are doing several similar queries is a
burst. Is that so?
--
Roedy Green Canadian Mind Products
http://mindprod.com
Nothing is so good as it seems beforehand.
~ George Eliot (born: 1819-11-22 died: 1880-12-22 at age: 61) (Mary Ann Evans)
Donkey Hottie
> It seems to me, if you use connection pooling, you must give up
> PreparedStatement, unless you are doing several similar queries is a
> burst. Is that so?
During the lifetime of one Connection you can and should use
PreparedStatements.
But of course, you can not save the PreparedStatement e.g. into a
Servlet state for use in later invocations with another Connection.
But one Connection is just an ordinary JDBC Connection. It can do what a
Connection can do.
--
You're at the end of the road again.
Lew
> It seems to me, if you use connection pooling, you must give up
> PreparedStatement, unless you are doing several similar queries is a
> burst. Is that so?
No.
--
Lew
Arne Vajhøj
> It seems to me, if you use connection pooling, you must give up
> PreparedStatement, unless you are doing several similar queries is a
> burst. Is that so?
No.
Connection pooling gives much better performance because connection
to the database is very costly.
Prepared statement will always protect both against Scottish names
and malicious SQL injection.
To still get the performance benefits from prepared statement, then
your connection pool software needs to be smart enough to keep
the existing prepared statements around together with the connections.
Smart connection pool software do exist !
Arne
joe.we...@gmail.com
>
> Smart connection pool software do exist !
>
> Arne
Yes. WebLogic's pooling is an example of transparently
pooling PreparedStatements (a configurable number)
along with the connection, repeated use-patterns re-use
the same statements indefinitely.
Joe Weinstein at Oracle
Roedy Green
wrote, quoted or indirectly quoted someone who said :
>
>Prepared statement will always protect both against Scottish names
>and malicious SQL injection.
what are "Scottish names"
Roedy Green
<joe.we...@gmail.com> wrote, quoted or indirectly quoted someone
who said :
>
>Yes. WebLogic's pooling is an example of transparently
>pooling PreparedStatements (a configurable number)
>along with the connection, repeated use-patterns re-use
>the same statements indefinitely.
So the way this works is you create the PreparedStatement afresh each
time you get a new connection from the pool, but internally it notices
this is same as a previous PreparedStatement and recycles an old
PreparedStatment which may have been built on a different connection
in the pool, rather than completely building a new one??
I just learned yesterday that sometimes the Preparsed statement lives
in the driver and sometimes in the SQL engine.
Arne Vajhøj
> On Fri, 23 Oct 2009 09:59:44 -0400, Arne Vajh�j <ar...@vajhoej.dk>
> wrote, quoted or indirectly quoted someone who said :
>
>> Prepared statement will always protect both against Scottish names
>> and malicious SQL injection.
>
> what are "Scottish names"
O'Toole, O'Malley etc..
Arne
John B. Matthews
Roedy Green <see_w...@mindprod.com.invalid> wrote:
> On Fri, 23 Oct 2009 09:59:44 -0400, Arne Vajhøj <ar...@vajhoej.dk>
> wrote, quoted or indirectly quoted someone who said :
>
> >Prepared statement will always protect both against Scottish names
> >and malicious SQL injection.
>
> what are "Scottish names"
I interpreted this to mean strings containing characters that could be
misinterpreted if taken literally, e.g. the SQL string delimiter
appearing in a name.
See also, <http://en.wikipedia.org/wiki/SQL_injection>.
--
Sean O';Drop table users
Donkey Hottie
Those are Irish names. Scottish names are like McScrooge and McKay.
--
Try to relax and enjoy the crisis.
-- Ashleigh Brilliant
Lew
>>> what are "Scottish names"
>
Arne Vajhøj kirjoitti:
>> O'Toole, O'Malley etc..
>
Donkey Hottie wrote:
> Those are Irish names. Scottish names are like McScrooge and McKay.
>
OK, *now* it makes sense. I was extremely puzzled trying to figure
out how "MacTavish" might hurt an SQL database.
More common for Scottish surnames as well as Irish is the prefix
"Mac" ("son"). In the U.S., at least, "Mc" is common among those of
Irish ancestry rather than Scottish. Wikipedia shows "Mc" as an
Anglicization of "Mac".
<http://en.wikipedia.org/wiki/Irish_name#List_of_surnames>
<http://en.wikipedia.org/wiki/List_of_Scottish_Gaelic_surnames>
The latter link does show a couple of Scottish surnames that begin
with "O'" ("O' Luingeachain").
--
Lew
Arne Vajhøj
Non malicious occurrences of ' that causes SQL errors.
Arne
Arne Vajhøj
> 23.10.2009 21:54, Arne Vajh�j kirjoitti:
>> Roedy Green wrote:
>>> On Fri, 23 Oct 2009 09:59:44 -0400, Arne Vajh�j <ar...@vajhoej.dk>
>>> wrote, quoted or indirectly quoted someone who said :
>>>
>>>> Prepared statement will always protect both against Scottish names
>>>> and malicious SQL injection.
>>> what are "Scottish names"
>> O'Toole, O'Malley etc..
>
Ooops.
My apologies to both the Scottish and Irish.
Arne
Donkey Hottie
As long as you remember this lesson, no worries.
I'm a Finn, and our names are all like *nen.
Just don't define your SQL function as
FUNCTION safe_name(name IN STRING)
RETURNS STRING
BEGIN
RETURN REPLACE(name,'nen','') ;
END ;
--
Q: How many Zen masters does it take to screw in a light bulb?
A: None. The Universe spins the bulb, and the Zen master stays out
of the way.
joe.we...@gmail.com
wrote:
> On Fri, 23 Oct 2009 07:31:06 -0700 (PDT), "joeNOS...@BEA.com"
> <joe.weinst...@gmail.com> wrote, quoted or indirectly quoted someone
> who said :
>
>
>
> >Yes. WebLogic's pooling is an example of transparently
> >pooling PreparedStatements (a configurable number)
> >along with the connection, repeated use-patterns re-use
> >the same statements indefinitely.
>
> So the way this works is you create the PreparedStatement afresh each
> time you get a new connection from the pool, but internally it notices
> this is same as a previous PreparedStatement and recycles an old
> PreparedStatment which may have been built on a different connection
> in the pool, rather than completely building a new one??
>
> I just learned yesterday that sometimes the Preparsed statement lives
> in the driver and sometimes in the SQL engine.
> --
>
> Nothing is so good as it seems beforehand.
> ~ George Eliot (born: 1819-11-22 died: 1880-12-22 at age: 61) (Mary Ann Evans)
Correct. The SQL and any arguments to the different sorts of
prepareStatement() or prepareCall() methods will be matched to locate/
store a statement to be re-used. This saves a clientside object, and
(more importantly) any DBMS-side components, such as cursors and
execution plans etc. Ei: It avoids the DBMS having to parse the SQL
and verify the existence of the DBMS objects mentioned in the SQL
every time...
Joe Weinstein at Oracle
Roedy Green
<nos...@nospam.invalid> wrote, quoted or indirectly quoted someone who
said :
>> >Prepared statement will always protect both against Scottish names
>> >and malicious SQL injection.
>>
>> what are "Scottish names"
>
>I interpreted this to mean strings containing characters that could be
>misinterpreted if taken literally, e.g. the SQL string delimiter
>appearing in a name.
Since Prepared statements protect against BOTH Scottish names AND
malicious SQL injection, I take it Scottish names refer to some other
problem.
Maybe he means Irish names which contain 's which are SQL delimiters,
which would screw things up without PreparedStatement.
John B. Matthews
Roedy Green <see_w...@mindprod.com.invalid> wrote:
> On Fri, 23 Oct 2009 14:58:42 -0400, "John B. Matthews"
> <nos...@nospam.invalid> wrote, quoted or indirectly quoted someone who
> said :
>
> >> >Prepared statement will always protect both against Scottish names
> >> >and malicious SQL injection.
> >>
> >> what are "Scottish names"
> >
> >I interpreted this to mean strings containing characters that could
> >be misinterpreted if taken literally, e.g. the SQL string delimiter
> >appearing in a name.
>
> Since Prepared statements protect against BOTH Scottish names AND
> malicious SQL injection, I take it Scottish names refer to some other
> problem.
>
> Maybe he means Irish names which contain 's which are SQL delimiters,
> which would screw things up without PreparedStatement.
I'm not sure I'd pick a particular nationality; apostrophal surnames are
ubiquitous.
--
John B. Matthews
trashgod at gmail dot com
<http://sites.google.com/site/drjohnbmatthews>
Arne Vajhøj
> On Oct 23, 6:59 am, Arne Vajh�j <a...@vajhoej.dk> wrote:
>> Smart connection pool software do exist !
>
> pooling PreparedStatements (a configurable number)
> along with the connection, repeated use-patterns re-use
> the same statements indefinitely.
We were not expecting anything less.
:-)
Arne
Arne Vajhøj
> I just learned yesterday that sometimes the Preparsed statement lives
> in the driver and sometimes in the SQL engine.
There will always exist something within the client.
If the database supports prepared statements in the SQL sense,
then the driver should use that feature.
Most databases does. But JDBC spec does not required it.
Arne
Lew
> I'm not sure I'd pick a particular nationality; apostrophal surnames are
> ubiquitous.
The fourth Musketeer agrees with you.
See also
<http://xkcd.com/327/>
--
Lew
jlp
> On Fri, 23 Oct 2009 07:31:06 -0700 (PDT), "joeN...@BEA.com"
> <joe.we...@gmail.com> wrote, quoted or indirectly quoted someone
> who said :
>
>
>>Yes. WebLogic's pooling is an example of transparently
>>pooling PreparedStatements (a configurable number)
>>along with the connection, repeated use-patterns re-use
>>the same statements indefinitely.
>
>
> So the way this works is you create the PreparedStatement afresh each
> time you get a new connection from the pool, but internally it notices
> this is same as a previous PreparedStatement and recycles an old
> PreparedStatment which may have been built on a different connection
> in the pool, rather than completely building a new one??
>
> I just learned yesterday that sometimes the Preparsed statement lives
> in the driver and sometimes in the SQL engine.
PreparedStatement ( like DBCP or C3P0 for example on the open source,
Weblogic has it own JDBC pool).
It is important to size correctly this cache, to avoid unusefull parsing
on the database side. The most popular J2EE servers, have JMX attributes
that follow these caches ( you can retrieve the hit ratio of the use of
the ps cache). If the JDBC pool permit it, choose a cache strategy per
connection and not for the whole pool itself.
Size of the cache ( per connection) >= number of different
PreparedStatements used by the application