Re: Oracle's support of PreparedStatement

Alain Kreienbuhl wrote:
>
> Hello Joe,
>
> This is my hope. How can I ensure such behavior.
>
> We're planning to use the app-server provided PreparedStatementCache
> (sorry we're not using WebLogic) ;)

You have my condolences. :-)

>with an oracle RDBMS.

Even more condolences! ;-) ( Wahoo, do I crack myself up!)

>it that setting If P (ex: 100) is the size of the
> preparedStatement pool and C (ex: 20) the average number of connection in the Connection
> pool, the total PreparedStatement object in the cache is P*C. That means 2000
> PreparedStatement object in the pool. Now if Oracle is not actually really preparing
> statement, the preparedStatement cache is like having a Nitrogen charged - Prosche with an
> Elephan as side-passanger and 25 Mph speed limit !

No need to worry. The point is that usually the DBMS and driver will cooperate to provide you the benefit of prepared statements as soon as you re-use a particular statement. Make sure you ask some more questions about this cache, because if you're doing any transactional work, every statement you use has to be associated with a single connection. It seems random and unfair if some connections have more cached prepared statements than others...
Joe

>
> Alain.
>
> Joseph Weinstein a écrit :
>
> > Alain Kreienbuhl wrote:
> > >
> > > Thanks for your reply.
> > >
> > > I'm very much aware of the JDBC Developper's guide and reference (540 pages).
> > > There is nothing mentioning that preparedStatement are really repared once.
> > > The reason my asking this is because I Merant mentioned that :
> > > con.prepareStatement("invalid sql code")
> > >
> > > does not throw any SQLException nor SQLWarning. Therefore I'm wondering if
> > > preparedStatement are really being prepared once.
> > >
> > > Alain.
> >
> > Hi Alain. It is possible that driver caches the SQL and maybe any subsequent
> > parameters, and sends them all at one time to the DBMS in response to the
> > first execute() call. At that time the DBMS will parse the SQL. It would be then
> > that it would throw an exception for bad SQL. Subsequent execute()s would use
> > the query plan from the first parse/exec of the SQL, and the driver might only
> > have to send the new parameter values.
> > Joe
> >
> > >
> > > Sybrand Bakker a écrit :
> > >
> > > > "Alain Kreienbuhl" <alain.kreienbuhl_at_fisystem.com> wrote in message
> > > > news:3B27945B.5144F725_at_fisystem.com...
> > > > > Hello,
> > > > >
> > > > > Are preparedStatement supported in the following configuration:
> > > > > - JDBC Driver 8.1.(6-7)
> > > > > - RDMS Oracle 8.0.5
> > > > >
> > > > > By supported I mean, are the query really prepared once (for a given
> > > > > scope, method scope or longer if connection pool + preparedStatement
> > > > > pool are used).
> > > > >
> > > > > Where can I found official information about this.
> > > > >
> > > > > Thanks .
> > > > >
> > > > > Alain.
> > > > >
> > > > >
> > > >
> > > > In Oracle's jdbc documentation, obviously.
> > > > Although running a higher version driver against a lower version database
> > > > (which has also been desupported) is just asking for problems.
> > > >
> > > > Hth,
> > > >
> > > > Sybrand Bakker, Oracle DBA
> >
> > --
> >
> > PS: Folks: BEA WebLogic is expanding rapidly, with both entry and advanced positions
> > for people who want to work with Java, XML, SOAP and E-Commerce infrastructure products.
> > We have jobs at Nashua NH, Liberty Corner NJ, San Francisco and San Jose CA.
> > Send resumes to joe_at_bea.com
 

-- 

PS: Folks: BEA WebLogic is expanding rapidly, with both entry and advanced positions
for people who want to work with Java, XML, SOAP and E-Commerce infrastructure products.
We have jobs at Nashua NH, Liberty Corner NJ, San Francisco and San Jose CA.
Send resumes to joe_at_bea.com