prepared statements

[Amir Michail <am...@gmail.com>]

Hi,

Why is it that prepared statements are created with respect to a connection?

Why can't you share a prepared statement across connections?

Amir

RE: prepared statements

[de...@segel.com]

> -----Original Message-----
> From: Amir Michail [mailto:amichail@gmail.com]
> Sent: Monday, February 26, 2007 7:16 PM
> To: Derby Discussion
> Subject: Re: prepared statements
> 
> On 2/26/07, Dag H. Wanvik <Da...@sun.com> wrote:
> > "Bernt M. Johnsen" <Be...@Sun.COM> writes:
> >
> > > Hi,
> > >
> > >
> > > This behaviour is defined in the JDBC specification.
> >
> > But Derby's statement cache works across connections, which alleviates
> > redundant statement compilation.
> >
> 
> I ran into a connection lost exception and I had no idea why. It
> wasn't obvious that this was the reason from the error given. Also,
> it's not obvious why you would want to prevent sharing of prepared
> statements across connections.
> 
> Amir
> 
>
This isn't a derby thing but a *java* thing. As well, it's a database thing.

In fact, think about it from a database perspective. If you could
disassociate a prepared statement from the connection, how do you control
the prepared statement. Meaning how do you know when you no longer need a
prepared statement.

Re: prepared statements

[Amir Michail <am...@gmail.com>]

On 2/26/07, Dag H. Wanvik <Da...@sun.com> wrote:
> "Bernt M. Johnsen" <Be...@Sun.COM> writes:
>
> > Hi,
> >
> >
> > This behaviour is defined in the JDBC specification.
>
> But Derby's statement cache works across connections, which alleviates
> redundant statement compilation.
>

I ran into a connection lost exception and I had no idea why. It
wasn't obvious that this was the reason from the error given. Also,
it's not obvious why you would want to prevent sharing of prepared
statements across connections.

Amir

> Please see http://db.apache.org/derby/docs/10.2/tuning/ctundepth29804.html
>
> Dag
>

Re: prepared statements

[Mike Matrigali <mi...@sbcglobal.net>]

Dag H. Wanvik wrote:
> "Bernt M. Johnsen" <Be...@Sun.COM> writes:
> 
> 
>>Hi,
>>
>>
>>This behaviour is defined in the JDBC specification.
> 
> 
> But Derby's statement cache works across connections, which alleviates
> redundant statement compilation.
> 
> Please see http://db.apache.org/derby/docs/10.2/tuning/ctundepth29804.html
> 
As Dag points out, Derby caches an internal object that represents all 
the work necessary to compile a statement and it matches that object up
with executions of "prepared statements".  This object does not go back
to the user.  So while derby does cache this across connections to save
recompilation, and does share read-only parts of the plan, it does not
really cache the prepared statement object returned to the user.

The prepared statement object in jdbc is used to pass info back and 
forth from the app and engine and thus is specific to the app and not
shared.

> 
> 

Re: prepared statements

["Dag H. Wanvik" <Da...@Sun.COM>]

"Bernt M. Johnsen" <Be...@Sun.COM> writes:

> Hi,
>
>
> This behaviour is defined in the JDBC specification.

But Derby's statement cache works across connections, which alleviates
redundant statement compilation.

Please see http://db.apache.org/derby/docs/10.2/tuning/ctundepth29804.html

Dag

Re: prepared statements

["Bernt M. Johnsen" <Be...@Sun.COM>]

Hi,


This behaviour is defined in the JDBC specification.

>>>>>>>>>>>> Amir Michail wrote (2007-02-26 14:46:33):
> Hi,
> 
> Why is it that prepared statements are created with respect to a
> connection?
> 
> Why can't you share a prepared statement across connections?
> 
> Amir

-- 
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway