You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@empire-db.apache.org by Rainer Döbele <do...@esteam.de> on 2010/12/03 09:30:14 UTC
All "prepared" now!
Hi Kenji,
good news for you: it's all "prepared" now :-)
Most of it was already there, but a few bits and pieces were missing.
However you have to take the latest sources directly from our SVN repository.
In DBDatabase you now have a property called "preparedStatementsEnabled" which you can enable or disable.
If enabled Empire-db will use prepared statements for all database operations on DBRecord.
If you create your own commands using DBCommand you will have to explicitly declare the parameters using DBCommand.addCmdParam() which will return a DBCommandParam (I have renamed this inner class from DBCmdParameter!) except for set() operations.
I have added an example to the empire-db-example-advanced (SampleAdvApp.java). The commandParamsSample() method shows how to use command params.
By additionally setting preparedStatementsEnabled to enabled, also all operations performed by Empire-db itself will use prepared statements.
You can easily see it in the log.
And the good thing is, you can easily switch between prepared and non-prepared (non-prepared statements are still better for debugging).
Let me know, if you have problems or need any more help.
Best regards,
Rainer
Kenji Nakamura wrote:
> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
> to: empire-db-user@incubator.apache.org
> re: Re: Prepared statement support?
>
> Hi Rainer,
>
> Yes, this is exactly what I was looking for.
> Regarding statement pooling, it is a part of JDBC 3.0 spec and I think
> it is a job of connection pool utility.
> We use c3p0 and it has statement pooling capability. It is highly
> configurable and has lots of features.
>
> http://www.mchange.com/projects/c3p0/index.html#configuring_statement_po
> oling
>
> I really appreciate if you can include the bug fix of DBReader in the
> next release as this is crucial feature to persuade DBAs and security
> auditors.
>
> Thanks a lot!
>
> Kenji Nakamura
>
> On Dec 2, 2010, at 19:29, Rainer Döbele <do...@esteam.de> wrote:
>
> > Hi everyone,
> >
> > thanks for your comment Matt.
> >
> > To my own surprise I have overlooked that there is already substantial
> support for prepared statement generation in Empire-db now, but you have
> to explicitly declare the parameters.
> > Here is an example of how to generate a prepared statement phrase and
> execute it with the corresponding parameters:
> >
> > // Define the query
> > DBCommand cmd = db.createCommand();
> >
> > // Create parameters
> > DBCmdParameter depIdParam = cmd.addCmdParam(1);
> > DBCmdParameter genderParam = cmd.addCmdParam('F');
> >
> > // create statement
> > cmd.select(EMP.getColumns());
> > cmd.where(EMP.DEPARTMENT_ID.is(depIdParam));
> > cmd.where(EMP.GENDER.is(genderParam));
> >
> > // First execution
> > String sql = cmd.getSelect();
> > ResultSet r = db.executeQuery(sql, cmd.getCmdParams(), false,
> conn);
> > // do something
> > r.close();
> >
> > // Modify command parameters
> > depIdParam.setValue(2);
> > genderParam.setValue('M');
> >
> > // Second execution
> > r = db.executeQuery(sql, cmd.getCmdParams(), false, conn);
> > // do something
> > r.close();
> >
> > This will result in the following SQL:
> >
> > SELECT t2.EMPLOYEE_ID, t2...
> > FROM EMPLOYEES t2
> > WHERE t2.DEPARTMENT_ID=? AND t2.GENDER=?
> >
> > And set the parameter to 1 and 'F' for the first query and to 2 and
> 'M' for the second.
> >
> > Unfortunately there is a bug in DBReader so that cmd params are not
> properly set.
> > This is the reason why I used db.executeQuery(..) instead of a
> DBReader in the example above.
> > I will fix this bug as soon as possible.
> >
> > Another thing we should do is to use the prepared statements for
> DBRecord.read (which in turn uses DBRowSet.readRecord(...)).
> >
> > As far as the pooling of prepared statements is concerned, if it's not
> done by the data source already it can also be done by subclassing the
> DBDatabaseDriver and overriding executeQuery() and / or executeSQL() and
> do it yourself. But it is not necessary for Empire-db to provide this.
> >
> > Kenji will this satisfy your needs?
> >
> > Regards,
> > Rainer
> >
> >
> >
> > Matthew Bond wrote:
> >> from: Matthew Bond [mailto:bond@bond-it.de]
> >> to: empire-db-dev@incubator.apache.org; empire-db-
> >> re: AW: Prepared statement support?
> >>
> >> Hi Rainer, Hi Kenji,
> >>
> >> Rainer's comments are true in a Web Application scenario where the
> >> connection if got for a short time and then released again. Empire DB
> >> can also be used in other scenarios, like a Fat Clients or Command
> Line
> >> Utility tools, where a connection will probably be held for the whole
> >> duration of the application lifetime and PooledStatements could
> bring
> >> more performance. So it really depends on what you application type
> you
> >> are programming.
> >>
> >> FYI: WebSphere too pools prepared statements (see page 2 of
> http://www-
> >>
> 03.ibm.com/systems/resources/systems_i_advantages_perfmgmt_pdf_stmntcach
> >> e.pdf "WebSphere, however, will do the caching automatically. When
> you
> >> execute a query, WebSphere determines if the SQL text is already in
> the
> >> cache and if so, it will use that cached statement instead of
> preparing
> >> a new one." ). So if EmpireDB was extended to make more use of
> Prepared
> >> Statements it would be advantageous.
> >>
> >> However as Rainer describes, the big benefit of using EmpireDB is
> that
> >> the selects are going to be way better than other ORM's as the
> developer
> >> hand crafts the "SQL" statement.
> >>
> >> The great thing is that it is Open Source so if you feel strongly
> about
> >> the use of PreparedStatements, you could submit a Patch adding this
> >> functionality.
> >>
> >> Cheers
> >> Matt
> >>
> >> -----Ursprüngliche Nachricht-----
> >> Von: Rainer Döbele [mailto:doebele@esteam.de]
> >> Gesendet: Donnerstag, 2. Dezember 2010 00:11
> >> An: empire-db-user@incubator.apache.org; empire-db-
> >> dev@incubator.apache.org
> >> Betreff: re: Prepared statement support?
> >>
> >> Dear Kenji,
> >>
> >> I have reviewed our code and thought about this subject again.
> >> As you mentioned there is both a performance and a security issue to
> >> consider.
> >> For the moment I would like to focus on the performance issue as
> >> security can as well be established by other measures.
> >>
> >> It's pretty obvious to understand that creating a prepared statement
> and
> >> executing it multiple times with varying parameters is superior over
> >> creating a normal statement each time. But as far as I understand it,
> >> the advantage of a ps exists only as long as the statement lives, and
> >> ends when you close it.
> >>
> >> The problem is, that a prepared statement is created for a particular
> >> connection. In a web-application we usually use a connection pool and
> >> the connection is fetched for a particular request. It is extremely
> >> rare, that the same statement is executed multiple times within a
> single
> >> request - whereas it is very likely that the same statement needs to
> be
> >> executed by other users' requests. As those other users have
> different
> >> connections they cannot share the same prepared statement.
> >>
> >> Here is a thread discussing this issue:
> >> http://www.velocityreviews.com/forums/t644638-jdbc-preparedstatement-
> in-
> >> a-multi-threaded-environment.html
> >>
> >> As Empire-db does not store or maintain a connection, it is not
> sensible
> >> for us to store the actual JDBC prepared statement object. But this
> >> might not be necessary as it could be done on another level. Possibly
> >> the solution lies just in another Apache Project: Apache Commons
> DBCP.
> >> http://commons.apache.org/dbcp/index.html
> >>
> >> From my understanding it should be possible to use a commons-dbcp
> >> connection pool that will also pool prepared statements. The
> connections
> >> returned by the pool can be used with Empire db just like a normal
> JDBC
> >> connection.
> >> Of course we still need to enforce and extend the generation of
> prepared
> >> statement phrases beyond the CUD operations.
> >>
> >> Still we must keep in mind, that probably for most real world
> >> applications the performance benefit of prepared statements over
> simple
> >> statements is negligible, and it is our primary goal to maintain
> >> simplicity and transparency.
> >> It is IMO far more important to be able to create efficient
> statements -
> >> and avoid the problem of OR-Mappers that usually work with lots of
> >> simple operations. After all, one clever statement with server side
> db
> >> logic will still execute a lot faster than 10 prepared statements
> with
> >> trailed Java logic.
> >> (Still the gloal is to have it all of course)
> >>
> >> Any more suggestions or remarks on this topic?
> >>
> >> Regards
> >> Rainer
> >>
> >>
> >> Kenji Nakamura wrote:
> >>> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
> >>> to: empire-db-user@incubator.apache.org
> >>> re Re: Prepared statement support?
> >>>
> >>> Rainer,
> >>>
> >>> Thank you for your reply. My comment are inline.
> >>>
> >>> On Wed, Dec 1, 2010 at 2:14 AM, Rainer Döbele <do...@esteam.de>
> >>> wrote:
> >>>> Hi Kenji,
> >>>>
> >>>> thanks for your interesting links about this subject.
> >>>>
> >>>> It is certainly true, that the performance of a prepared statements
> >>> is better when you execute it multiple times with varying parameter
> >>> values.
> >>>> This is not always possible when varying statements with
> conditional
> >>> joins are created at runtime.
> >>>> For a one-time statement using a prepared statement does not
> execute
> >>> faster than a normal statement.
> >>>
> >>> I understand the issue that the use of PreparedStatement seems to
> have
> >>> overhead and actually it may take longer if we measure it with a
> >>> single execution from application developer's point of view, but the
> >>> compiled result of the statement is kept added to Oracle's cache and
> >>> it flushes the compiled results of the PreparedStatement invoked
> from
> >>> different applications as the cache is managed per SID in Oracle. So
> >>> it has negative impact from the DBA's point of view. It is not an
> >>> issue as long as the DB is used as the data storage of a web
> >>> application server and the performance of the app is only concern,
> but
> >>> the assumption is not true when the DB is also used in data
> >>> processing.
> >>>
> >>>> The inclusion of parameter values in the SQL text when assembling
> >>> statements is an advantage when it comes to logging (logging of
> >>> parameterized statements is not sufficient to track errors) or for
> the
> >>> creation of SQL scripts that are saved and executed later.
> >>>
> >>> I see your point.
> >>>
> >>>>
> >>>> Currently Empire-db uses prepared statements by default only for
> >>> statements with BLOB and CLOB fields.
> >>>>
> >>>> However at least as far as update and insert statements are
> >>>> concerned
> >>> you can override the method useCmdParam() in DBCommandOracle, but
> you
> >>> need to subclass the DBDatabaseDriverOracle and override
> createCommand
> >>> first. If you return true in useCmdParam(), then Empire-DB will use
> a
> >>> prepared statement and supply this value as a prepared statement
> >>> parameter.
> >>>
> >>> From the point of view of Oracle administrator, the primary interest
> >>> is how to reduce the # of hard parse and increase the hit rate of
> the
> >>> cache, and using PreparedStatement only for CUD operation is not
> >>> sufficient if the ratio of Select outweigh CUD operations. From
> >>> security point of view, Select statement with parameters embedding
> >>> user's input is as vulnerable as other DMLs, so the option to use
> >>> PreparedStatement for CUD operation doesn't address those concerns,
> >>> while it may be useful to improve the performance on iterative
> >>> operations.
> >>>
> >>>>
> >>>> Personally I have used Empire-DB in many projects and performance
> or
> >>> security have never been a problem. However, if you except to
> execute
> >>> 10.000 sql statements a minute then certainly this needs to be
> >>> thoroughly checked.
> >>>
> >>> It is nice to know the framework has been proven in production
> >>> environments. Our current performance test also doesn't show the
> hard
> >>> parse is the primary culprit of the performance bottleneck, so it is
> >>> not an urgent problem, but I'd like prepare to answer the questions
> >>> from our DB engineers.
> >>>
> >>>>
> >>>> I have created a new Jira (EMPIREDB-91) issue for us to check, how
> >>> and where we can increase and optimize the use of prepared
> statements.
> >>>
> >>> Thank you for the reaction. I registered myself to the watch list.
> Let
> >>> me know if I can do something to make this forward.
> >>>
> >>> Lastly, I really thank you to share the framework in public. I have
> >>> used Toplink, Hibernate, and iBatis, but I favor empire-db a lot
> >>> because of the simplicity and type-safe coding. It is very
> >>> straightforward to customize to fulfill our specific needs such as
> the
> >>> support of TableFunction in Oracle.
> >>>
> >>> Regards,
> >>>
> >>> Kenji
> >>>
> >>>>
> >>>> Regards
> >>>> Rainer
> >>>>
> >>>>
> >>>> Kenji Nakamura wrote:
> >>>>> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
> >>>>> to: empire-db-user@incubator.apache.org
> >>>>> re: Prepared statement support?
> >>>>>
> >>>>> Hi,
> >>>>>
> >>>>> I got a question from one of our DB engineer about the use of
> >>> prepared
> >>>>> statements.
> >>>>> According to him, or a thread in AskTom, it is always preferred to
> >>> use
> >>>>> PreparedStatement instead of Statement whenever possible.
> >>>>>
> >>>
> http://asktom.oracle.com/pls/asktom/f?p=100:11:7607696421577136::::P11
> >>> _
> >>> Q
> >>>>> UESTION_ID:1993620575194
> >>>>>
> >>>>> As far as I looked at the code, PreparedStatement is not used
> other
> >>>>> than DBDatabaseDriver class and the method is not used from other
> >>>>> code.
> >>>>>
> >>>>> My understanding is that creation of PreparedStatement has certain
> >>>>> overhead, but statement pooling introduced in JDBC 3.0 mitigates
> >>>>> the impact especially from application server point of view.
> >>>>> We use Oracle, and the DB engineer explained that the use of
> >>> statement
> >>>>> floods the library cache in SGA and reduce the hit rate of
> >>>>> pre-compiled statements so it has negative impact on entire db,
> and
> >>>>> using PreparedStatement simply reduces the cost of hard parse.
> >>>>>
> >>>>> Another aspect is about SQL injection prevention. I noticed single
> >>>>> quotes are escaped at DBDatabaseDriver#getValueString() method,
> but
> >>>>> the preferred way to prevent SQL injection is to use
> >>> PreparedStatement
> >>>>> according to OWASP website.
> >>>>>
> http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
> >>>>>
> >>>>> Would you tell me the design philosophy or reasons not to use or
> >>>>> provide the option to use prepared statement? Is it possible, or
> >>> have
> >>>>> a plan to support PreparedStatement?
> >>>>>
> >>>>> Thanks,
> >>>>>
> >>>>> Kenji Nakamura
> >>>>
AW: All "prepared" now!
Posted by Rainer Döbele <do...@esteam.de>.
Hi Kenji,
you're welcome, but actually I am glad you came up with it since we just had neglected this very important issue a bit.
And as I said: Most of it was already there and was waiting for getting completed.
I am thinking of tiding up a bit and I might rename a few functions (not sure yet) - just to make it simpler and more consistent.
It won't be anything serious or a change in the behavior so it can easily be applied to existing code.
If I do I let you know here.
BTW: I'd be happy to hear if everything works as desired (or not).
Regards
Rainer
Kenji Nakamura wrote:
> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
> to: empire-db-dev@incubator.apache.org
> re: Re: All "prepared" now!
>
> Rainer,
>
> Wow, it's fantastic!
> I'll give a try with the latest code.
>
> I feel like I got a bit early Christmas present!
>
> Thanks for all your effort,
>
> Kenji Nakamura
>
> On Dec 3, 2010, at 22:26, "Rainer Döbele" <do...@esteam.de> wrote:
>
> >
> > But then we should not wait too long with our 2.1. release.
> >
> > At least it will show, that we are a very active community :-)
> >
> > Regards
> > Rainer
> >
> >
> > Francis De Brabandere wrote:
> >> from: Francis De Brabandere [mailto:francisdb@gmail.com]
> >> to: empire-db-dev@incubator.apache.org
> >> re: Re: All "prepared" now!
> >>
> >> I would just continue with the release. That will be the last 2.0.x
> >> Afterwards we can directly cut the 2.1.0
> >>
> >> Cheers,
> >> Francis
> >>
> >> On Fri, Dec 3, 2010 at 11:25 AM, Rainer Döbele <do...@esteam.de>
> >> wrote:
> >>> Hi Francis,
> >>>
> >>> Yes indeed a hard night.
> >>> I had a problem with the parameter order that just took me some
> time
> >> to fix.
> >>>
> >>> I have seen that I missed a few places that sill generate simple
> >> statements.
> >>> Will submit another patch today after a few more tests.
> >>>
> >>> Yes it's a shame that this isn't part of the release - I think it
> is
> >> an important major feature.
> >>>
> >>> How about stopping the current rc and going for the next one?
> >>> I would probably safe us some unnecessary double work.
> >>>
> >>> Since I had to change a few things in the interface of DBCommand I
> >> would like to call the next release 2.1.0.
> >>>
> >>> What do you think?
> >>>
> >>> Regards
> >>> Rainer
> >>>
> >>>
> >>> Francis De Brabandere wrote:
> >>>> from: Francis De Brabandere [mailto:francisdb@gmail.com]
> >>>> to: empire-db-dev@incubator.apache.org
> >>>> re: Re: All "prepared" now!
> >>>>
> >>>> Busy night Rainer? Great to hear this is implemented :-) Too bad
> we
> >>>> just cut a release :-s
> >>>>
> >>>> On Fri, Dec 3, 2010 at 9:30 AM, Rainer Döbele <do...@esteam.de>
> >> wrote:
> >>>>> Hi Kenji,
> >>>>>
> >>>>> good news for you: it's all "prepared" now :-)
> >>>>> Most of it was already there, but a few bits and pieces were
> >> missing.
> >>>>> However you have to take the latest sources directly from our SVN
> >>>> repository.
> >>>>>
> >>>>> In DBDatabase you now have a property called
> >>>> "preparedStatementsEnabled" which you can enable or disable.
> >>>>> If enabled Empire-db will use prepared statements for all
> database
> >>>> operations on DBRecord.
> >>>>>
> >>>>> If you create your own commands using DBCommand you will have to
> >>>> explicitly declare the parameters using DBCommand.addCmdParam()
> which
> >>>> will return a DBCommandParam (I have renamed this inner class from
> >>>> DBCmdParameter!) except for set() operations.
> >>>>>
> >>>>> I have added an example to the empire-db-example-advanced
> >>>> (SampleAdvApp.java). The commandParamsSample() method shows how to
> >> use
> >>>> command params.
> >>>>> By additionally setting preparedStatementsEnabled to enabled,
> also
> >> all
> >>>> operations performed by Empire-db itself will use prepared
> >> statements.
> >>>>> You can easily see it in the log.
> >>>>> And the good thing is, you can easily switch between prepared and
> >> non-
> >>>> prepared (non-prepared statements are still better for debugging).
> >>>>>
> >>>>> Let me know, if you have problems or need any more help.
> >>>>> Best regards,
> >>>>>
> >>>>> Rainer
> >>>>>
> >>>>>
> >>>>> Kenji Nakamura wrote:
> >>>>>> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
> >>>>>> to: empire-db-user@incubator.apache.org
> >>>>>> re: Re: Prepared statement support?
> >>>>>>
> >>>>>> Hi Rainer,
> >>>>>>
> >>>>>> Yes, this is exactly what I was looking for.
> >>>>>> Regarding statement pooling, it is a part of JDBC 3.0 spec and I
> >>>> think
> >>>>>> it is a job of connection pool utility.
> >>>>>> We use c3p0 and it has statement pooling capability. It is
> highly
> >>>>>> configurable and has lots of features.
> >>>>>>
> >>>>>>
> >>>>
> >>
> http://www.mchange.com/projects/c3p0/index.html#configuring_statement_p
> o
> >>>>>> oling
> >>>>>>
> >>>>>> I really appreciate if you can include the bug fix of DBReader
> in
> >> the
> >>>>>> next release as this is crucial feature to persuade DBAs and
> >> security
> >>>>>> auditors.
> >>>>>>
> >>>>>> Thanks a lot!
> >>>>>>
> >>>>>> Kenji Nakamura
> >>>>>>
> >>>>>> On Dec 2, 2010, at 19:29, Rainer Döbele <do...@esteam.de>
> wrote:
> >>>>>>
> >>>>>>> Hi everyone,
> >>>>>>>
> >>>>>>> thanks for your comment Matt.
> >>>>>>>
> >>>>>>> To my own surprise I have overlooked that there is already
> >>>> substantial
> >>>>>> support for prepared statement generation in Empire-db now, but
> >> you
> >>>> have
> >>>>>> to explicitly declare the parameters.
> >>>>>>> Here is an example of how to generate a prepared statement
> >> phrase
> >>>> and
> >>>>>> execute it with the corresponding parameters:
> >>>>>>>
> >>>>>>> // Define the query
> >>>>>>> DBCommand cmd = db.createCommand();
> >>>>>>>
> >>>>>>> // Create parameters
> >>>>>>> DBCmdParameter depIdParam = cmd.addCmdParam(1);
> >>>>>>> DBCmdParameter genderParam = cmd.addCmdParam('F');
> >>>>>>>
> >>>>>>> // create statement
> >>>>>>> cmd.select(EMP.getColumns());
> >>>>>>> cmd.where(EMP.DEPARTMENT_ID.is(depIdParam));
> >>>>>>> cmd.where(EMP.GENDER.is(genderParam));
> >>>>>>>
> >>>>>>> // First execution
> >>>>>>> String sql = cmd.getSelect();
> >>>>>>> ResultSet r = db.executeQuery(sql, cmd.getCmdParams(),
> false,
> >>>>>> conn);
> >>>>>>> // do something
> >>>>>>> r.close();
> >>>>>>>
> >>>>>>> // Modify command parameters
> >>>>>>> depIdParam.setValue(2);
> >>>>>>> genderParam.setValue('M');
> >>>>>>>
> >>>>>>> // Second execution
> >>>>>>> r = db.executeQuery(sql, cmd.getCmdParams(), false, conn);
> >>>>>>> // do something
> >>>>>>> r.close();
> >>>>>>>
> >>>>>>> This will result in the following SQL:
> >>>>>>>
> >>>>>>> SELECT t2.EMPLOYEE_ID, t2...
> >>>>>>> FROM EMPLOYEES t2
> >>>>>>> WHERE t2.DEPARTMENT_ID=? AND t2.GENDER=?
> >>>>>>>
> >>>>>>> And set the parameter to 1 and 'F' for the first query and to 2
> >> and
> >>>>>> 'M' for the second.
> >>>>>>>
> >>>>>>> Unfortunately there is a bug in DBReader so that cmd params are
> >> not
> >>>>>> properly set.
> >>>>>>> This is the reason why I used db.executeQuery(..) instead of a
> >>>>>> DBReader in the example above.
> >>>>>>> I will fix this bug as soon as possible.
> >>>>>>>
> >>>>>>> Another thing we should do is to use the prepared statements
> for
> >>>>>> DBRecord.read (which in turn uses DBRowSet.readRecord(...)).
> >>>>>>>
> >>>>>>> As far as the pooling of prepared statements is concerned, if
> >> it's
> >>>> not
> >>>>>> done by the data source already it can also be done by
> subclassing
> >>>> the
> >>>>>> DBDatabaseDriver and overriding executeQuery() and / or
> >> executeSQL()
> >>>> and
> >>>>>> do it yourself. But it is not necessary for Empire-db to provide
> >>>> this.
> >>>>>>>
> >>>>>>> Kenji will this satisfy your needs?
> >>>>>>>
> >>>>>>> Regards,
> >>>>>>> Rainer
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>> Matthew Bond wrote:
> >>>>>>>> from: Matthew Bond [mailto:bond@bond-it.de]
> >>>>>>>> to: empire-db-dev@incubator.apache.org; empire-db-
> >>>>>>>> re: AW: Prepared statement support?
> >>>>>>>>
> >>>>>>>> Hi Rainer, Hi Kenji,
> >>>>>>>>
> >>>>>>>> Rainer's comments are true in a Web Application scenario where
> >> the
> >>>>>>>> connection if got for a short time and then released again.
> >> Empire
> >>>> DB
> >>>>>>>> can also be used in other scenarios, like a Fat Clients or
> >> Command
> >>>>>> Line
> >>>>>>>> Utility tools, where a connection will probably be held for
> the
> >>>> whole
> >>>>>>>> duration of the application lifetime and PooledStatements
> >> could
> >>>>>> bring
> >>>>>>>> more performance. So it really depends on what you application
> >>>> type
> >>>>>> you
> >>>>>>>> are programming.
> >>>>>>>>
> >>>>>>>> FYI: WebSphere too pools prepared statements (see page 2 of
> >>>>>> http://www-
> >>>>>>>>
> >>>>>>
> >>>>
> >>
> 03.ibm.com/systems/resources/systems_i_advantages_perfmgmt_pdf_stmntcac
> h
> >>>>>>>> e.pdf "WebSphere, however, will do the caching automatically.
> >>>> When
> >>>>>> you
> >>>>>>>> execute a query, WebSphere determines if the SQL text is
> >> already
> >>>> in
> >>>>>> the
> >>>>>>>> cache and if so, it will use that cached statement instead of
> >>>>>> preparing
> >>>>>>>> a new one." ). So if EmpireDB was extended to make more use of
> >>>>>> Prepared
> >>>>>>>> Statements it would be advantageous.
> >>>>>>>>
> >>>>>>>> However as Rainer describes, the big benefit of using
> EmpireDB
> >> is
> >>>>>> that
> >>>>>>>> the selects are going to be way better than other ORM's as the
> >>>>>> developer
> >>>>>>>> hand crafts the "SQL" statement.
> >>>>>>>>
> >>>>>>>> The great thing is that it is Open Source so if you feel
> >> strongly
> >>>>>> about
> >>>>>>>> the use of PreparedStatements, you could submit a Patch adding
> >>>> this
> >>>>>>>> functionality.
> >>>>>>>>
> >>>>>>>> Cheers
> >>>>>>>> Matt
> >>>>>>>>
> >>>>>>>> -----Ursprüngliche Nachricht-----
> >>>>>>>> Von: Rainer Döbele [mailto:doebele@esteam.de]
> >>>>>>>> Gesendet: Donnerstag, 2. Dezember 2010 00:11
> >>>>>>>> An: empire-db-user@incubator.apache.org; empire-db-
> >>>>>>>> dev@incubator.apache.org
> >>>>>>>> Betreff: re: Prepared statement support?
> >>>>>>>>
> >>>>>>>> Dear Kenji,
> >>>>>>>>
> >>>>>>>> I have reviewed our code and thought about this subject again.
> >>>>>>>> As you mentioned there is both a performance and a security
> >> issue
> >>>> to
> >>>>>>>> consider.
> >>>>>>>> For the moment I would like to focus on the performance issue
> >> as
> >>>>>>>> security can as well be established by other measures.
> >>>>>>>>
> >>>>>>>> It's pretty obvious to understand that creating a prepared
> >>>> statement
> >>>>>> and
> >>>>>>>> executing it multiple times with varying parameters is
> superior
> >>>> over
> >>>>>>>> creating a normal statement each time. But as far as I
> >> understand
> >>>> it,
> >>>>>>>> the advantage of a ps exists only as long as the statement
> >> lives,
> >>>> and
> >>>>>>>> ends when you close it.
> >>>>>>>>
> >>>>>>>> The problem is, that a prepared statement is created for a
> >>>> particular
> >>>>>>>> connection. In a web-application we usually use a connection
> >> pool
> >>>> and
> >>>>>>>> the connection is fetched for a particular request. It is
> >>>> extremely
> >>>>>>>> rare, that the same statement is executed multiple times
> within
> >> a
> >>>>>> single
> >>>>>>>> request - whereas it is very likely that the same statement
> >> needs
> >>>> to
> >>>>>> be
> >>>>>>>> executed by other users' requests. As those other users have
> >>>>>> different
> >>>>>>>> connections they cannot share the same prepared statement.
> >>>>>>>>
> >>>>>>>> Here is a thread discussing this issue:
> >>>>>>>> http://www.velocityreviews.com/forums/t644638-jdbc-
> >>>> preparedstatement-
> >>>>>> in-
> >>>>>>>> a-multi-threaded-environment.html
> >>>>>>>>
> >>>>>>>> As Empire-db does not store or maintain a connection, it is
> not
> >>>>>> sensible
> >>>>>>>> for us to store the actual JDBC prepared statement object. But
> >>>> this
> >>>>>>>> might not be necessary as it could be done on another level.
> >>>> Possibly
> >>>>>>>> the solution lies just in another Apache Project: Apache
> >> Commons
> >>>>>> DBCP.
> >>>>>>>> http://commons.apache.org/dbcp/index.html
> >>>>>>>>
> >>>>>>>> From my understanding it should be possible to use a commons-
> >> dbcp
> >>>>>>>> connection pool that will also pool prepared statements. The
> >>>>>> connections
> >>>>>>>> returned by the pool can be used with Empire db just like a
> >> normal
> >>>>>> JDBC
> >>>>>>>> connection.
> >>>>>>>> Of course we still need to enforce and extend the generation
> of
> >>>>>> prepared
> >>>>>>>> statement phrases beyond the CUD operations.
> >>>>>>>>
> >>>>>>>> Still we must keep in mind, that probably for most real world
> >>>>>>>> applications the performance benefit of prepared statements
> >> over
> >>>>>> simple
> >>>>>>>> statements is negligible, and it is our primary goal to
> >> maintain
> >>>>>>>> simplicity and transparency.
> >>>>>>>> It is IMO far more important to be able to create efficient
> >>>>>> statements -
> >>>>>>>> and avoid the problem of OR-Mappers that usually work with
> lots
> >> of
> >>>>>>>> simple operations. After all, one clever statement with server
> >>>> side
> >>>>>> db
> >>>>>>>> logic will still execute a lot faster than 10 prepared
> >> statements
> >>>>>> with
> >>>>>>>> trailed Java logic.
> >>>>>>>> (Still the gloal is to have it all of course)
> >>>>>>>>
> >>>>>>>> Any more suggestions or remarks on this topic?
> >>>>>>>>
> >>>>>>>> Regards
> >>>>>>>> Rainer
> >>>>>>>>
> >>>>>>>>
> >>>>>>>> Kenji Nakamura wrote:
> >>>>>>>>> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
> >>>>>>>>> to: empire-db-user@incubator.apache.org
> >>>>>>>>> re Re: Prepared statement support?
> >>>>>>>>>
> >>>>>>>>> Rainer,
> >>>>>>>>>
> >>>>>>>>> Thank you for your reply. My comment are inline.
> >>>>>>>>>
> >>>>>>>>> On Wed, Dec 1, 2010 at 2:14 AM, Rainer Döbele
> >> <do...@esteam.de>
> >>>>>>>>> wrote:
> >>>>>>>>>> Hi Kenji,
> >>>>>>>>>>
> >>>>>>>>>> thanks for your interesting links about this subject.
> >>>>>>>>>>
> >>>>>>>>>> It is certainly true, that the performance of a prepared
> >>>> statements
> >>>>>>>>> is better when you execute it multiple times with varying
> >>>> parameter
> >>>>>>>>> values.
> >>>>>>>>>> This is not always possible when varying statements with
> >>>>>> conditional
> >>>>>>>>> joins are created at runtime.
> >>>>>>>>>> For a one-time statement using a prepared statement does not
> >>>>>> execute
> >>>>>>>>> faster than a normal statement.
> >>>>>>>>>
> >>>>>>>>> I understand the issue that the use of PreparedStatement
> seems
> >> to
> >>>>>> have
> >>>>>>>>> overhead and actually it may take longer if we measure it
> with
> >> a
> >>>>>>>>> single execution from application developer's point of view,
> >> but
> >>>> the
> >>>>>>>>> compiled result of the statement is kept added to Oracle's
> >> cache
> >>>> and
> >>>>>>>>> it flushes the compiled results of the PreparedStatement
> >> invoked
> >>>>>> from
> >>>>>>>>> different applications as the cache is managed per SID in
> >> Oracle.
> >>>> So
> >>>>>>>>> it has negative impact from the DBA's point of view. It is
> >> not
> >>>> an
> >>>>>>>>> issue as long as the DB is used as the data storage of a web
> >>>>>>>>> application server and the performance of the app is only
> >>>> concern,
> >>>>>> but
> >>>>>>>>> the assumption is not true when the DB is also used in data
> >>>>>>>>> processing.
> >>>>>>>>>
> >>>>>>>>>> The inclusion of parameter values in the SQL text when
> >>>> assembling
> >>>>>>>>> statements is an advantage when it comes to logging (logging
> >> of
> >>>>>>>>> parameterized statements is not sufficient to track errors)
> or
> >>>> for
> >>>>>> the
> >>>>>>>>> creation of SQL scripts that are saved and executed later.
> >>>>>>>>>
> >>>>>>>>> I see your point.
> >>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>> Currently Empire-db uses prepared statements by default only
> >> for
> >>>>>>>>> statements with BLOB and CLOB fields.
> >>>>>>>>>>
> >>>>>>>>>> However at least as far as update and insert statements are
> >>>>>>>>>> concerned
> >>>>>>>>> you can override the method useCmdParam() in DBCommandOracle,
> >> but
> >>>>>> you
> >>>>>>>>> need to subclass the DBDatabaseDriverOracle and override
> >>>>>> createCommand
> >>>>>>>>> first. If you return true in useCmdParam(), then Empire-DB
> >> will
> >>>> use
> >>>>>> a
> >>>>>>>>> prepared statement and supply this value as a prepared
> >> statement
> >>>>>>>>> parameter.
> >>>>>>>>>
> >>>>>>>>> From the point of view of Oracle administrator, the primary
> >>>> interest
> >>>>>>>>> is how to reduce the # of hard parse and increase the hit
> rate
> >> of
> >>>>>> the
> >>>>>>>>> cache, and using PreparedStatement only for CUD operation is
> >> not
> >>>>>>>>> sufficient if the ratio of Select outweigh CUD operations.
> >> From
> >>>>>>>>> security point of view, Select statement with parameters
> >>>> embedding
> >>>>>>>>> user's input is as vulnerable as other DMLs, so the option to
> >> use
> >>>>>>>>> PreparedStatement for CUD operation doesn't address those
> >>>> concerns,
> >>>>>>>>> while it may be useful to improve the performance on
> iterative
> >>>>>>>>> operations.
> >>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>> Personally I have used Empire-DB in many projects and
> >>>> performance
> >>>>>> or
> >>>>>>>>> security have never been a problem. However, if you except to
> >>>>>> execute
> >>>>>>>>> 10.000 sql statements a minute then certainly this needs to
> be
> >>>>>>>>> thoroughly checked.
> >>>>>>>>>
> >>>>>>>>> It is nice to know the framework has been proven in
> production
> >>>>>>>>> environments. Our current performance test also doesn't show
> >> the
> >>>>>> hard
> >>>>>>>>> parse is the primary culprit of the performance bottleneck,
> so
> >> it
> >>>> is
> >>>>>>>>> not an urgent problem, but I'd like prepare to answer the
> >>>> questions
> >>>>>>>>> from our DB engineers.
> >>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>> I have created a new Jira (EMPIREDB-91) issue for us to
> >> check,
> >>>> how
> >>>>>>>>> and where we can increase and optimize the use of prepared
> >>>>>> statements.
> >>>>>>>>>
> >>>>>>>>> Thank you for the reaction. I registered myself to the watch
> >>>> list.
> >>>>>> Let
> >>>>>>>>> me know if I can do something to make this forward.
> >>>>>>>>>
> >>>>>>>>> Lastly, I really thank you to share the framework in public.
> I
> >>>> have
> >>>>>>>>> used Toplink, Hibernate, and iBatis, but I favor empire-db a
> >> lot
> >>>>>>>>> because of the simplicity and type-safe coding. It is very
> >>>>>>>>> straightforward to customize to fulfill our specific needs
> >> such
> >>>> as
> >>>>>> the
> >>>>>>>>> support of TableFunction in Oracle.
> >>>>>>>>>
> >>>>>>>>> Regards,
> >>>>>>>>>
> >>>>>>>>> Kenji
> >>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>> Regards
> >>>>>>>>>> Rainer
> >>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>> Kenji Nakamura wrote:
> >>>>>>>>>>> from: Kenji Nakamura [mailto:kenji_nakamura@diva-
> >> america.com]
> >>>>>>>>>>> to: empire-db-user@incubator.apache.org
> >>>>>>>>>>> re: Prepared statement support?
> >>>>>>>>>>>
> >>>>>>>>>>> Hi,
> >>>>>>>>>>>
> >>>>>>>>>>> I got a question from one of our DB engineer about the use
> >> of
> >>>>>>>>> prepared
> >>>>>>>>>>> statements.
> >>>>>>>>>>> According to him, or a thread in AskTom, it is always
> >> preferred
> >>>> to
> >>>>>>>>> use
> >>>>>>>>>>> PreparedStatement instead of Statement whenever possible.
> >>>>>>>>>>>
> >>>>>>>>>
> >>>>>>
> >>>>
> >>
> http://asktom.oracle.com/pls/asktom/f?p=100:11:7607696421577136::::P11
> >>>>>>>>> _
> >>>>>>>>> Q
> >>>>>>>>>>> UESTION_ID:1993620575194
> >>>>>>>>>>>
> >>>>>>>>>>> As far as I looked at the code, PreparedStatement is not
> >> used
> >>>>>> other
> >>>>>>>>>>> than DBDatabaseDriver class and the method is not used from
> >>>> other
> >>>>>>>>>>> code.
> >>>>>>>>>>>
> >>>>>>>>>>> My understanding is that creation of PreparedStatement has
> >>>> certain
> >>>>>>>>>>> overhead, but statement pooling introduced in JDBC 3.0
> >>>> mitigates
> >>>>>>>>>>> the impact especially from application server point of
> view.
> >>>>>>>>>>> We use Oracle, and the DB engineer explained that the use
> of
> >>>>>>>>> statement
> >>>>>>>>>>> floods the library cache in SGA and reduce the hit rate of
> >>>>>>>>>>> pre-compiled statements so it has negative impact on entire
> >> db,
> >>>>>> and
> >>>>>>>>>>> using PreparedStatement simply reduces the cost of hard
> >> parse.
> >>>>>>>>>>>
> >>>>>>>>>>> Another aspect is about SQL injection prevention. I noticed
> >>>> single
> >>>>>>>>>>> quotes are escaped at DBDatabaseDriver#getValueString()
> >> method,
> >>>>>> but
> >>>>>>>>>>> the preferred way to prevent SQL injection is to use
> >>>>>>>>> PreparedStatement
> >>>>>>>>>>> according to OWASP website.
> >>>>>>>>>>>
> >>>>>>
> >> http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
> >>>>>>>>>>>
> >>>>>>>>>>> Would you tell me the design philosophy or reasons not to
> >> use
> >>>> or
> >>>>>>>>>>> provide the option to use prepared statement? Is it
> >> possible,
> >>>> or
> >>>>>>>>> have
> >>>>>>>>>>> a plan to support PreparedStatement?
> >>>>>>>>>>>
> >>>>>>>>>>> Thanks,
> >>>>>>>>>>>
> >>>>>>>>>>> Kenji Nakamura
> >>>>>>>>>>
> >>>>>
> >>>>
> >>>>
> >>>>
> >>>> --
> >>>> http://www.somatik.be
> >>>> Microsoft gives you windows, Linux gives you the whole house.
> >>>
> >>
> >>
> >>
> >> --
> >> http://www.somatik.be
> >> Microsoft gives you windows, Linux gives you the whole house.
Re: All "prepared" now!
Posted by Kenji Nakamura <ke...@diva-america.com>.
Rainer,
Wow, it's fantastic!
I'll give a try with the latest code.
I feel like I got a bit early Christmas present!
Thanks for all your effort,
Kenji Nakamura
On Dec 3, 2010, at 22:26, "Rainer Döbele" <do...@esteam.de> wrote:
>
> But then we should not wait too long with our 2.1. release.
>
> At least it will show, that we are a very active community :-)
>
> Regards
> Rainer
>
>
> Francis De Brabandere wrote:
>> from: Francis De Brabandere [mailto:francisdb@gmail.com]
>> to: empire-db-dev@incubator.apache.org
>> re: Re: All "prepared" now!
>>
>> I would just continue with the release. That will be the last 2.0.x
>> Afterwards we can directly cut the 2.1.0
>>
>> Cheers,
>> Francis
>>
>> On Fri, Dec 3, 2010 at 11:25 AM, Rainer Döbele <do...@esteam.de>
>> wrote:
>>> Hi Francis,
>>>
>>> Yes indeed a hard night.
>>> I had a problem with the parameter order that just took me some time
>> to fix.
>>>
>>> I have seen that I missed a few places that sill generate simple
>> statements.
>>> Will submit another patch today after a few more tests.
>>>
>>> Yes it's a shame that this isn't part of the release - I think it is
>> an important major feature.
>>>
>>> How about stopping the current rc and going for the next one?
>>> I would probably safe us some unnecessary double work.
>>>
>>> Since I had to change a few things in the interface of DBCommand I
>> would like to call the next release 2.1.0.
>>>
>>> What do you think?
>>>
>>> Regards
>>> Rainer
>>>
>>>
>>> Francis De Brabandere wrote:
>>>> from: Francis De Brabandere [mailto:francisdb@gmail.com]
>>>> to: empire-db-dev@incubator.apache.org
>>>> re: Re: All "prepared" now!
>>>>
>>>> Busy night Rainer? Great to hear this is implemented :-) Too bad we
>>>> just cut a release :-s
>>>>
>>>> On Fri, Dec 3, 2010 at 9:30 AM, Rainer Döbele <do...@esteam.de>
>> wrote:
>>>>> Hi Kenji,
>>>>>
>>>>> good news for you: it's all "prepared" now :-)
>>>>> Most of it was already there, but a few bits and pieces were
>> missing.
>>>>> However you have to take the latest sources directly from our SVN
>>>> repository.
>>>>>
>>>>> In DBDatabase you now have a property called
>>>> "preparedStatementsEnabled" which you can enable or disable.
>>>>> If enabled Empire-db will use prepared statements for all database
>>>> operations on DBRecord.
>>>>>
>>>>> If you create your own commands using DBCommand you will have to
>>>> explicitly declare the parameters using DBCommand.addCmdParam() which
>>>> will return a DBCommandParam (I have renamed this inner class from
>>>> DBCmdParameter!) except for set() operations.
>>>>>
>>>>> I have added an example to the empire-db-example-advanced
>>>> (SampleAdvApp.java). The commandParamsSample() method shows how to
>> use
>>>> command params.
>>>>> By additionally setting preparedStatementsEnabled to enabled, also
>> all
>>>> operations performed by Empire-db itself will use prepared
>> statements.
>>>>> You can easily see it in the log.
>>>>> And the good thing is, you can easily switch between prepared and
>> non-
>>>> prepared (non-prepared statements are still better for debugging).
>>>>>
>>>>> Let me know, if you have problems or need any more help.
>>>>> Best regards,
>>>>>
>>>>> Rainer
>>>>>
>>>>>
>>>>> Kenji Nakamura wrote:
>>>>>> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
>>>>>> to: empire-db-user@incubator.apache.org
>>>>>> re: Re: Prepared statement support?
>>>>>>
>>>>>> Hi Rainer,
>>>>>>
>>>>>> Yes, this is exactly what I was looking for.
>>>>>> Regarding statement pooling, it is a part of JDBC 3.0 spec and I
>>>> think
>>>>>> it is a job of connection pool utility.
>>>>>> We use c3p0 and it has statement pooling capability. It is highly
>>>>>> configurable and has lots of features.
>>>>>>
>>>>>>
>>>>
>> http://www.mchange.com/projects/c3p0/index.html#configuring_statement_po
>>>>>> oling
>>>>>>
>>>>>> I really appreciate if you can include the bug fix of DBReader in
>> the
>>>>>> next release as this is crucial feature to persuade DBAs and
>> security
>>>>>> auditors.
>>>>>>
>>>>>> Thanks a lot!
>>>>>>
>>>>>> Kenji Nakamura
>>>>>>
>>>>>> On Dec 2, 2010, at 19:29, Rainer Döbele <do...@esteam.de> wrote:
>>>>>>
>>>>>>> Hi everyone,
>>>>>>>
>>>>>>> thanks for your comment Matt.
>>>>>>>
>>>>>>> To my own surprise I have overlooked that there is already
>>>> substantial
>>>>>> support for prepared statement generation in Empire-db now, but
>> you
>>>> have
>>>>>> to explicitly declare the parameters.
>>>>>>> Here is an example of how to generate a prepared statement
>> phrase
>>>> and
>>>>>> execute it with the corresponding parameters:
>>>>>>>
>>>>>>> // Define the query
>>>>>>> DBCommand cmd = db.createCommand();
>>>>>>>
>>>>>>> // Create parameters
>>>>>>> DBCmdParameter depIdParam = cmd.addCmdParam(1);
>>>>>>> DBCmdParameter genderParam = cmd.addCmdParam('F');
>>>>>>>
>>>>>>> // create statement
>>>>>>> cmd.select(EMP.getColumns());
>>>>>>> cmd.where(EMP.DEPARTMENT_ID.is(depIdParam));
>>>>>>> cmd.where(EMP.GENDER.is(genderParam));
>>>>>>>
>>>>>>> // First execution
>>>>>>> String sql = cmd.getSelect();
>>>>>>> ResultSet r = db.executeQuery(sql, cmd.getCmdParams(), false,
>>>>>> conn);
>>>>>>> // do something
>>>>>>> r.close();
>>>>>>>
>>>>>>> // Modify command parameters
>>>>>>> depIdParam.setValue(2);
>>>>>>> genderParam.setValue('M');
>>>>>>>
>>>>>>> // Second execution
>>>>>>> r = db.executeQuery(sql, cmd.getCmdParams(), false, conn);
>>>>>>> // do something
>>>>>>> r.close();
>>>>>>>
>>>>>>> This will result in the following SQL:
>>>>>>>
>>>>>>> SELECT t2.EMPLOYEE_ID, t2...
>>>>>>> FROM EMPLOYEES t2
>>>>>>> WHERE t2.DEPARTMENT_ID=? AND t2.GENDER=?
>>>>>>>
>>>>>>> And set the parameter to 1 and 'F' for the first query and to 2
>> and
>>>>>> 'M' for the second.
>>>>>>>
>>>>>>> Unfortunately there is a bug in DBReader so that cmd params are
>> not
>>>>>> properly set.
>>>>>>> This is the reason why I used db.executeQuery(..) instead of a
>>>>>> DBReader in the example above.
>>>>>>> I will fix this bug as soon as possible.
>>>>>>>
>>>>>>> Another thing we should do is to use the prepared statements for
>>>>>> DBRecord.read (which in turn uses DBRowSet.readRecord(...)).
>>>>>>>
>>>>>>> As far as the pooling of prepared statements is concerned, if
>> it's
>>>> not
>>>>>> done by the data source already it can also be done by subclassing
>>>> the
>>>>>> DBDatabaseDriver and overriding executeQuery() and / or
>> executeSQL()
>>>> and
>>>>>> do it yourself. But it is not necessary for Empire-db to provide
>>>> this.
>>>>>>>
>>>>>>> Kenji will this satisfy your needs?
>>>>>>>
>>>>>>> Regards,
>>>>>>> Rainer
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Matthew Bond wrote:
>>>>>>>> from: Matthew Bond [mailto:bond@bond-it.de]
>>>>>>>> to: empire-db-dev@incubator.apache.org; empire-db-
>>>>>>>> re: AW: Prepared statement support?
>>>>>>>>
>>>>>>>> Hi Rainer, Hi Kenji,
>>>>>>>>
>>>>>>>> Rainer's comments are true in a Web Application scenario where
>> the
>>>>>>>> connection if got for a short time and then released again.
>> Empire
>>>> DB
>>>>>>>> can also be used in other scenarios, like a Fat Clients or
>> Command
>>>>>> Line
>>>>>>>> Utility tools, where a connection will probably be held for the
>>>> whole
>>>>>>>> duration of the application lifetime and PooledStatements
>> could
>>>>>> bring
>>>>>>>> more performance. So it really depends on what you application
>>>> type
>>>>>> you
>>>>>>>> are programming.
>>>>>>>>
>>>>>>>> FYI: WebSphere too pools prepared statements (see page 2 of
>>>>>> http://www-
>>>>>>>>
>>>>>>
>>>>
>> 03.ibm.com/systems/resources/systems_i_advantages_perfmgmt_pdf_stmntcach
>>>>>>>> e.pdf "WebSphere, however, will do the caching automatically.
>>>> When
>>>>>> you
>>>>>>>> execute a query, WebSphere determines if the SQL text is
>> already
>>>> in
>>>>>> the
>>>>>>>> cache and if so, it will use that cached statement instead of
>>>>>> preparing
>>>>>>>> a new one." ). So if EmpireDB was extended to make more use of
>>>>>> Prepared
>>>>>>>> Statements it would be advantageous.
>>>>>>>>
>>>>>>>> However as Rainer describes, the big benefit of using EmpireDB
>> is
>>>>>> that
>>>>>>>> the selects are going to be way better than other ORM's as the
>>>>>> developer
>>>>>>>> hand crafts the "SQL" statement.
>>>>>>>>
>>>>>>>> The great thing is that it is Open Source so if you feel
>> strongly
>>>>>> about
>>>>>>>> the use of PreparedStatements, you could submit a Patch adding
>>>> this
>>>>>>>> functionality.
>>>>>>>>
>>>>>>>> Cheers
>>>>>>>> Matt
>>>>>>>>
>>>>>>>> -----Ursprüngliche Nachricht-----
>>>>>>>> Von: Rainer Döbele [mailto:doebele@esteam.de]
>>>>>>>> Gesendet: Donnerstag, 2. Dezember 2010 00:11
>>>>>>>> An: empire-db-user@incubator.apache.org; empire-db-
>>>>>>>> dev@incubator.apache.org
>>>>>>>> Betreff: re: Prepared statement support?
>>>>>>>>
>>>>>>>> Dear Kenji,
>>>>>>>>
>>>>>>>> I have reviewed our code and thought about this subject again.
>>>>>>>> As you mentioned there is both a performance and a security
>> issue
>>>> to
>>>>>>>> consider.
>>>>>>>> For the moment I would like to focus on the performance issue
>> as
>>>>>>>> security can as well be established by other measures.
>>>>>>>>
>>>>>>>> It's pretty obvious to understand that creating a prepared
>>>> statement
>>>>>> and
>>>>>>>> executing it multiple times with varying parameters is superior
>>>> over
>>>>>>>> creating a normal statement each time. But as far as I
>> understand
>>>> it,
>>>>>>>> the advantage of a ps exists only as long as the statement
>> lives,
>>>> and
>>>>>>>> ends when you close it.
>>>>>>>>
>>>>>>>> The problem is, that a prepared statement is created for a
>>>> particular
>>>>>>>> connection. In a web-application we usually use a connection
>> pool
>>>> and
>>>>>>>> the connection is fetched for a particular request. It is
>>>> extremely
>>>>>>>> rare, that the same statement is executed multiple times within
>> a
>>>>>> single
>>>>>>>> request - whereas it is very likely that the same statement
>> needs
>>>> to
>>>>>> be
>>>>>>>> executed by other users' requests. As those other users have
>>>>>> different
>>>>>>>> connections they cannot share the same prepared statement.
>>>>>>>>
>>>>>>>> Here is a thread discussing this issue:
>>>>>>>> http://www.velocityreviews.com/forums/t644638-jdbc-
>>>> preparedstatement-
>>>>>> in-
>>>>>>>> a-multi-threaded-environment.html
>>>>>>>>
>>>>>>>> As Empire-db does not store or maintain a connection, it is not
>>>>>> sensible
>>>>>>>> for us to store the actual JDBC prepared statement object. But
>>>> this
>>>>>>>> might not be necessary as it could be done on another level.
>>>> Possibly
>>>>>>>> the solution lies just in another Apache Project: Apache
>> Commons
>>>>>> DBCP.
>>>>>>>> http://commons.apache.org/dbcp/index.html
>>>>>>>>
>>>>>>>> From my understanding it should be possible to use a commons-
>> dbcp
>>>>>>>> connection pool that will also pool prepared statements. The
>>>>>> connections
>>>>>>>> returned by the pool can be used with Empire db just like a
>> normal
>>>>>> JDBC
>>>>>>>> connection.
>>>>>>>> Of course we still need to enforce and extend the generation of
>>>>>> prepared
>>>>>>>> statement phrases beyond the CUD operations.
>>>>>>>>
>>>>>>>> Still we must keep in mind, that probably for most real world
>>>>>>>> applications the performance benefit of prepared statements
>> over
>>>>>> simple
>>>>>>>> statements is negligible, and it is our primary goal to
>> maintain
>>>>>>>> simplicity and transparency.
>>>>>>>> It is IMO far more important to be able to create efficient
>>>>>> statements -
>>>>>>>> and avoid the problem of OR-Mappers that usually work with lots
>> of
>>>>>>>> simple operations. After all, one clever statement with server
>>>> side
>>>>>> db
>>>>>>>> logic will still execute a lot faster than 10 prepared
>> statements
>>>>>> with
>>>>>>>> trailed Java logic.
>>>>>>>> (Still the gloal is to have it all of course)
>>>>>>>>
>>>>>>>> Any more suggestions or remarks on this topic?
>>>>>>>>
>>>>>>>> Regards
>>>>>>>> Rainer
>>>>>>>>
>>>>>>>>
>>>>>>>> Kenji Nakamura wrote:
>>>>>>>>> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
>>>>>>>>> to: empire-db-user@incubator.apache.org
>>>>>>>>> re Re: Prepared statement support?
>>>>>>>>>
>>>>>>>>> Rainer,
>>>>>>>>>
>>>>>>>>> Thank you for your reply. My comment are inline.
>>>>>>>>>
>>>>>>>>> On Wed, Dec 1, 2010 at 2:14 AM, Rainer Döbele
>> <do...@esteam.de>
>>>>>>>>> wrote:
>>>>>>>>>> Hi Kenji,
>>>>>>>>>>
>>>>>>>>>> thanks for your interesting links about this subject.
>>>>>>>>>>
>>>>>>>>>> It is certainly true, that the performance of a prepared
>>>> statements
>>>>>>>>> is better when you execute it multiple times with varying
>>>> parameter
>>>>>>>>> values.
>>>>>>>>>> This is not always possible when varying statements with
>>>>>> conditional
>>>>>>>>> joins are created at runtime.
>>>>>>>>>> For a one-time statement using a prepared statement does not
>>>>>> execute
>>>>>>>>> faster than a normal statement.
>>>>>>>>>
>>>>>>>>> I understand the issue that the use of PreparedStatement seems
>> to
>>>>>> have
>>>>>>>>> overhead and actually it may take longer if we measure it with
>> a
>>>>>>>>> single execution from application developer's point of view,
>> but
>>>> the
>>>>>>>>> compiled result of the statement is kept added to Oracle's
>> cache
>>>> and
>>>>>>>>> it flushes the compiled results of the PreparedStatement
>> invoked
>>>>>> from
>>>>>>>>> different applications as the cache is managed per SID in
>> Oracle.
>>>> So
>>>>>>>>> it has negative impact from the DBA's point of view. It is
>> not
>>>> an
>>>>>>>>> issue as long as the DB is used as the data storage of a web
>>>>>>>>> application server and the performance of the app is only
>>>> concern,
>>>>>> but
>>>>>>>>> the assumption is not true when the DB is also used in data
>>>>>>>>> processing.
>>>>>>>>>
>>>>>>>>>> The inclusion of parameter values in the SQL text when
>>>> assembling
>>>>>>>>> statements is an advantage when it comes to logging (logging
>> of
>>>>>>>>> parameterized statements is not sufficient to track errors) or
>>>> for
>>>>>> the
>>>>>>>>> creation of SQL scripts that are saved and executed later.
>>>>>>>>>
>>>>>>>>> I see your point.
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Currently Empire-db uses prepared statements by default only
>> for
>>>>>>>>> statements with BLOB and CLOB fields.
>>>>>>>>>>
>>>>>>>>>> However at least as far as update and insert statements are
>>>>>>>>>> concerned
>>>>>>>>> you can override the method useCmdParam() in DBCommandOracle,
>> but
>>>>>> you
>>>>>>>>> need to subclass the DBDatabaseDriverOracle and override
>>>>>> createCommand
>>>>>>>>> first. If you return true in useCmdParam(), then Empire-DB
>> will
>>>> use
>>>>>> a
>>>>>>>>> prepared statement and supply this value as a prepared
>> statement
>>>>>>>>> parameter.
>>>>>>>>>
>>>>>>>>> From the point of view of Oracle administrator, the primary
>>>> interest
>>>>>>>>> is how to reduce the # of hard parse and increase the hit rate
>> of
>>>>>> the
>>>>>>>>> cache, and using PreparedStatement only for CUD operation is
>> not
>>>>>>>>> sufficient if the ratio of Select outweigh CUD operations.
>> From
>>>>>>>>> security point of view, Select statement with parameters
>>>> embedding
>>>>>>>>> user's input is as vulnerable as other DMLs, so the option to
>> use
>>>>>>>>> PreparedStatement for CUD operation doesn't address those
>>>> concerns,
>>>>>>>>> while it may be useful to improve the performance on iterative
>>>>>>>>> operations.
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Personally I have used Empire-DB in many projects and
>>>> performance
>>>>>> or
>>>>>>>>> security have never been a problem. However, if you except to
>>>>>> execute
>>>>>>>>> 10.000 sql statements a minute then certainly this needs to be
>>>>>>>>> thoroughly checked.
>>>>>>>>>
>>>>>>>>> It is nice to know the framework has been proven in production
>>>>>>>>> environments. Our current performance test also doesn't show
>> the
>>>>>> hard
>>>>>>>>> parse is the primary culprit of the performance bottleneck, so
>> it
>>>> is
>>>>>>>>> not an urgent problem, but I'd like prepare to answer the
>>>> questions
>>>>>>>>> from our DB engineers.
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> I have created a new Jira (EMPIREDB-91) issue for us to
>> check,
>>>> how
>>>>>>>>> and where we can increase and optimize the use of prepared
>>>>>> statements.
>>>>>>>>>
>>>>>>>>> Thank you for the reaction. I registered myself to the watch
>>>> list.
>>>>>> Let
>>>>>>>>> me know if I can do something to make this forward.
>>>>>>>>>
>>>>>>>>> Lastly, I really thank you to share the framework in public. I
>>>> have
>>>>>>>>> used Toplink, Hibernate, and iBatis, but I favor empire-db a
>> lot
>>>>>>>>> because of the simplicity and type-safe coding. It is very
>>>>>>>>> straightforward to customize to fulfill our specific needs
>> such
>>>> as
>>>>>> the
>>>>>>>>> support of TableFunction in Oracle.
>>>>>>>>>
>>>>>>>>> Regards,
>>>>>>>>>
>>>>>>>>> Kenji
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Regards
>>>>>>>>>> Rainer
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Kenji Nakamura wrote:
>>>>>>>>>>> from: Kenji Nakamura [mailto:kenji_nakamura@diva-
>> america.com]
>>>>>>>>>>> to: empire-db-user@incubator.apache.org
>>>>>>>>>>> re: Prepared statement support?
>>>>>>>>>>>
>>>>>>>>>>> Hi,
>>>>>>>>>>>
>>>>>>>>>>> I got a question from one of our DB engineer about the use
>> of
>>>>>>>>> prepared
>>>>>>>>>>> statements.
>>>>>>>>>>> According to him, or a thread in AskTom, it is always
>> preferred
>>>> to
>>>>>>>>> use
>>>>>>>>>>> PreparedStatement instead of Statement whenever possible.
>>>>>>>>>>>
>>>>>>>>>
>>>>>>
>>>>
>> http://asktom.oracle.com/pls/asktom/f?p=100:11:7607696421577136::::P11
>>>>>>>>> _
>>>>>>>>> Q
>>>>>>>>>>> UESTION_ID:1993620575194
>>>>>>>>>>>
>>>>>>>>>>> As far as I looked at the code, PreparedStatement is not
>> used
>>>>>> other
>>>>>>>>>>> than DBDatabaseDriver class and the method is not used from
>>>> other
>>>>>>>>>>> code.
>>>>>>>>>>>
>>>>>>>>>>> My understanding is that creation of PreparedStatement has
>>>> certain
>>>>>>>>>>> overhead, but statement pooling introduced in JDBC 3.0
>>>> mitigates
>>>>>>>>>>> the impact especially from application server point of view.
>>>>>>>>>>> We use Oracle, and the DB engineer explained that the use of
>>>>>>>>> statement
>>>>>>>>>>> floods the library cache in SGA and reduce the hit rate of
>>>>>>>>>>> pre-compiled statements so it has negative impact on entire
>> db,
>>>>>> and
>>>>>>>>>>> using PreparedStatement simply reduces the cost of hard
>> parse.
>>>>>>>>>>>
>>>>>>>>>>> Another aspect is about SQL injection prevention. I noticed
>>>> single
>>>>>>>>>>> quotes are escaped at DBDatabaseDriver#getValueString()
>> method,
>>>>>> but
>>>>>>>>>>> the preferred way to prevent SQL injection is to use
>>>>>>>>> PreparedStatement
>>>>>>>>>>> according to OWASP website.
>>>>>>>>>>>
>>>>>>
>> http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
>>>>>>>>>>>
>>>>>>>>>>> Would you tell me the design philosophy or reasons not to
>> use
>>>> or
>>>>>>>>>>> provide the option to use prepared statement? Is it
>> possible,
>>>> or
>>>>>>>>> have
>>>>>>>>>>> a plan to support PreparedStatement?
>>>>>>>>>>>
>>>>>>>>>>> Thanks,
>>>>>>>>>>>
>>>>>>>>>>> Kenji Nakamura
>>>>>>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> http://www.somatik.be
>>>> Microsoft gives you windows, Linux gives you the whole house.
>>>
>>
>>
>>
>> --
>> http://www.somatik.be
>> Microsoft gives you windows, Linux gives you the whole house.
re: All "prepared" now!
Posted by Rainer Döbele <do...@esteam.de>.
But then we should not wait too long with our 2.1. release.
At least it will show, that we are a very active community :-)
Regards
Rainer
Francis De Brabandere wrote:
> from: Francis De Brabandere [mailto:francisdb@gmail.com]
> to: empire-db-dev@incubator.apache.org
> re: Re: All "prepared" now!
>
> I would just continue with the release. That will be the last 2.0.x
> Afterwards we can directly cut the 2.1.0
>
> Cheers,
> Francis
>
> On Fri, Dec 3, 2010 at 11:25 AM, Rainer Döbele <do...@esteam.de>
> wrote:
> > Hi Francis,
> >
> > Yes indeed a hard night.
> > I had a problem with the parameter order that just took me some time
> to fix.
> >
> > I have seen that I missed a few places that sill generate simple
> statements.
> > Will submit another patch today after a few more tests.
> >
> > Yes it's a shame that this isn't part of the release - I think it is
> an important major feature.
> >
> > How about stopping the current rc and going for the next one?
> > I would probably safe us some unnecessary double work.
> >
> > Since I had to change a few things in the interface of DBCommand I
> would like to call the next release 2.1.0.
> >
> > What do you think?
> >
> > Regards
> > Rainer
> >
> >
> > Francis De Brabandere wrote:
> >> from: Francis De Brabandere [mailto:francisdb@gmail.com]
> >> to: empire-db-dev@incubator.apache.org
> >> re: Re: All "prepared" now!
> >>
> >> Busy night Rainer? Great to hear this is implemented :-) Too bad we
> >> just cut a release :-s
> >>
> >> On Fri, Dec 3, 2010 at 9:30 AM, Rainer Döbele <do...@esteam.de>
> wrote:
> >> > Hi Kenji,
> >> >
> >> > good news for you: it's all "prepared" now :-)
> >> > Most of it was already there, but a few bits and pieces were
> missing.
> >> > However you have to take the latest sources directly from our SVN
> >> repository.
> >> >
> >> > In DBDatabase you now have a property called
> >> "preparedStatementsEnabled" which you can enable or disable.
> >> > If enabled Empire-db will use prepared statements for all database
> >> operations on DBRecord.
> >> >
> >> > If you create your own commands using DBCommand you will have to
> >> explicitly declare the parameters using DBCommand.addCmdParam() which
> >> will return a DBCommandParam (I have renamed this inner class from
> >> DBCmdParameter!) except for set() operations.
> >> >
> >> > I have added an example to the empire-db-example-advanced
> >> (SampleAdvApp.java). The commandParamsSample() method shows how to
> use
> >> command params.
> >> > By additionally setting preparedStatementsEnabled to enabled, also
> all
> >> operations performed by Empire-db itself will use prepared
> statements.
> >> > You can easily see it in the log.
> >> > And the good thing is, you can easily switch between prepared and
> non-
> >> prepared (non-prepared statements are still better for debugging).
> >> >
> >> > Let me know, if you have problems or need any more help.
> >> > Best regards,
> >> >
> >> > Rainer
> >> >
> >> >
> >> > Kenji Nakamura wrote:
> >> >> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
> >> >> to: empire-db-user@incubator.apache.org
> >> >> re: Re: Prepared statement support?
> >> >>
> >> >> Hi Rainer,
> >> >>
> >> >> Yes, this is exactly what I was looking for.
> >> >> Regarding statement pooling, it is a part of JDBC 3.0 spec and I
> >> think
> >> >> it is a job of connection pool utility.
> >> >> We use c3p0 and it has statement pooling capability. It is highly
> >> >> configurable and has lots of features.
> >> >>
> >> >>
> >>
> http://www.mchange.com/projects/c3p0/index.html#configuring_statement_po
> >> >> oling
> >> >>
> >> >> I really appreciate if you can include the bug fix of DBReader in
> the
> >> >> next release as this is crucial feature to persuade DBAs and
> security
> >> >> auditors.
> >> >>
> >> >> Thanks a lot!
> >> >>
> >> >> Kenji Nakamura
> >> >>
> >> >> On Dec 2, 2010, at 19:29, Rainer Döbele <do...@esteam.de> wrote:
> >> >>
> >> >> > Hi everyone,
> >> >> >
> >> >> > thanks for your comment Matt.
> >> >> >
> >> >> > To my own surprise I have overlooked that there is already
> >> substantial
> >> >> support for prepared statement generation in Empire-db now, but
> you
> >> have
> >> >> to explicitly declare the parameters.
> >> >> > Here is an example of how to generate a prepared statement
> phrase
> >> and
> >> >> execute it with the corresponding parameters:
> >> >> >
> >> >> > // Define the query
> >> >> > DBCommand cmd = db.createCommand();
> >> >> >
> >> >> > // Create parameters
> >> >> > DBCmdParameter depIdParam = cmd.addCmdParam(1);
> >> >> > DBCmdParameter genderParam = cmd.addCmdParam('F');
> >> >> >
> >> >> > // create statement
> >> >> > cmd.select(EMP.getColumns());
> >> >> > cmd.where(EMP.DEPARTMENT_ID.is(depIdParam));
> >> >> > cmd.where(EMP.GENDER.is(genderParam));
> >> >> >
> >> >> > // First execution
> >> >> > String sql = cmd.getSelect();
> >> >> > ResultSet r = db.executeQuery(sql, cmd.getCmdParams(), false,
> >> >> conn);
> >> >> > // do something
> >> >> > r.close();
> >> >> >
> >> >> > // Modify command parameters
> >> >> > depIdParam.setValue(2);
> >> >> > genderParam.setValue('M');
> >> >> >
> >> >> > // Second execution
> >> >> > r = db.executeQuery(sql, cmd.getCmdParams(), false, conn);
> >> >> > // do something
> >> >> > r.close();
> >> >> >
> >> >> > This will result in the following SQL:
> >> >> >
> >> >> > SELECT t2.EMPLOYEE_ID, t2...
> >> >> > FROM EMPLOYEES t2
> >> >> > WHERE t2.DEPARTMENT_ID=? AND t2.GENDER=?
> >> >> >
> >> >> > And set the parameter to 1 and 'F' for the first query and to 2
> and
> >> >> 'M' for the second.
> >> >> >
> >> >> > Unfortunately there is a bug in DBReader so that cmd params are
> not
> >> >> properly set.
> >> >> > This is the reason why I used db.executeQuery(..) instead of a
> >> >> DBReader in the example above.
> >> >> > I will fix this bug as soon as possible.
> >> >> >
> >> >> > Another thing we should do is to use the prepared statements for
> >> >> DBRecord.read (which in turn uses DBRowSet.readRecord(...)).
> >> >> >
> >> >> > As far as the pooling of prepared statements is concerned, if
> it's
> >> not
> >> >> done by the data source already it can also be done by subclassing
> >> the
> >> >> DBDatabaseDriver and overriding executeQuery() and / or
> executeSQL()
> >> and
> >> >> do it yourself. But it is not necessary for Empire-db to provide
> >> this.
> >> >> >
> >> >> > Kenji will this satisfy your needs?
> >> >> >
> >> >> > Regards,
> >> >> > Rainer
> >> >> >
> >> >> >
> >> >> >
> >> >> > Matthew Bond wrote:
> >> >> >> from: Matthew Bond [mailto:bond@bond-it.de]
> >> >> >> to: empire-db-dev@incubator.apache.org; empire-db-
> >> >> >> re: AW: Prepared statement support?
> >> >> >>
> >> >> >> Hi Rainer, Hi Kenji,
> >> >> >>
> >> >> >> Rainer's comments are true in a Web Application scenario where
> the
> >> >> >> connection if got for a short time and then released again.
> Empire
> >> DB
> >> >> >> can also be used in other scenarios, like a Fat Clients or
> Command
> >> >> Line
> >> >> >> Utility tools, where a connection will probably be held for the
> >> whole
> >> >> >> duration of the application lifetime and PooledStatements
> could
> >> >> bring
> >> >> >> more performance. So it really depends on what you application
> >> type
> >> >> you
> >> >> >> are programming.
> >> >> >>
> >> >> >> FYI: WebSphere too pools prepared statements (see page 2 of
> >> >> http://www-
> >> >> >>
> >> >>
> >>
> 03.ibm.com/systems/resources/systems_i_advantages_perfmgmt_pdf_stmntcach
> >> >> >> e.pdf "WebSphere, however, will do the caching automatically.
> >> When
> >> >> you
> >> >> >> execute a query, WebSphere determines if the SQL text is
> already
> >> in
> >> >> the
> >> >> >> cache and if so, it will use that cached statement instead of
> >> >> preparing
> >> >> >> a new one." ). So if EmpireDB was extended to make more use of
> >> >> Prepared
> >> >> >> Statements it would be advantageous.
> >> >> >>
> >> >> >> However as Rainer describes, the big benefit of using EmpireDB
> is
> >> >> that
> >> >> >> the selects are going to be way better than other ORM's as the
> >> >> developer
> >> >> >> hand crafts the "SQL" statement.
> >> >> >>
> >> >> >> The great thing is that it is Open Source so if you feel
> strongly
> >> >> about
> >> >> >> the use of PreparedStatements, you could submit a Patch adding
> >> this
> >> >> >> functionality.
> >> >> >>
> >> >> >> Cheers
> >> >> >> Matt
> >> >> >>
> >> >> >> -----Ursprüngliche Nachricht-----
> >> >> >> Von: Rainer Döbele [mailto:doebele@esteam.de]
> >> >> >> Gesendet: Donnerstag, 2. Dezember 2010 00:11
> >> >> >> An: empire-db-user@incubator.apache.org; empire-db-
> >> >> >> dev@incubator.apache.org
> >> >> >> Betreff: re: Prepared statement support?
> >> >> >>
> >> >> >> Dear Kenji,
> >> >> >>
> >> >> >> I have reviewed our code and thought about this subject again.
> >> >> >> As you mentioned there is both a performance and a security
> issue
> >> to
> >> >> >> consider.
> >> >> >> For the moment I would like to focus on the performance issue
> as
> >> >> >> security can as well be established by other measures.
> >> >> >>
> >> >> >> It's pretty obvious to understand that creating a prepared
> >> statement
> >> >> and
> >> >> >> executing it multiple times with varying parameters is superior
> >> over
> >> >> >> creating a normal statement each time. But as far as I
> understand
> >> it,
> >> >> >> the advantage of a ps exists only as long as the statement
> lives,
> >> and
> >> >> >> ends when you close it.
> >> >> >>
> >> >> >> The problem is, that a prepared statement is created for a
> >> particular
> >> >> >> connection. In a web-application we usually use a connection
> pool
> >> and
> >> >> >> the connection is fetched for a particular request. It is
> >> extremely
> >> >> >> rare, that the same statement is executed multiple times within
> a
> >> >> single
> >> >> >> request - whereas it is very likely that the same statement
> needs
> >> to
> >> >> be
> >> >> >> executed by other users' requests. As those other users have
> >> >> different
> >> >> >> connections they cannot share the same prepared statement.
> >> >> >>
> >> >> >> Here is a thread discussing this issue:
> >> >> >> http://www.velocityreviews.com/forums/t644638-jdbc-
> >> preparedstatement-
> >> >> in-
> >> >> >> a-multi-threaded-environment.html
> >> >> >>
> >> >> >> As Empire-db does not store or maintain a connection, it is not
> >> >> sensible
> >> >> >> for us to store the actual JDBC prepared statement object. But
> >> this
> >> >> >> might not be necessary as it could be done on another level.
> >> Possibly
> >> >> >> the solution lies just in another Apache Project: Apache
> Commons
> >> >> DBCP.
> >> >> >> http://commons.apache.org/dbcp/index.html
> >> >> >>
> >> >> >> From my understanding it should be possible to use a commons-
> dbcp
> >> >> >> connection pool that will also pool prepared statements. The
> >> >> connections
> >> >> >> returned by the pool can be used with Empire db just like a
> normal
> >> >> JDBC
> >> >> >> connection.
> >> >> >> Of course we still need to enforce and extend the generation of
> >> >> prepared
> >> >> >> statement phrases beyond the CUD operations.
> >> >> >>
> >> >> >> Still we must keep in mind, that probably for most real world
> >> >> >> applications the performance benefit of prepared statements
> over
> >> >> simple
> >> >> >> statements is negligible, and it is our primary goal to
> maintain
> >> >> >> simplicity and transparency.
> >> >> >> It is IMO far more important to be able to create efficient
> >> >> statements -
> >> >> >> and avoid the problem of OR-Mappers that usually work with lots
> of
> >> >> >> simple operations. After all, one clever statement with server
> >> side
> >> >> db
> >> >> >> logic will still execute a lot faster than 10 prepared
> statements
> >> >> with
> >> >> >> trailed Java logic.
> >> >> >> (Still the gloal is to have it all of course)
> >> >> >>
> >> >> >> Any more suggestions or remarks on this topic?
> >> >> >>
> >> >> >> Regards
> >> >> >> Rainer
> >> >> >>
> >> >> >>
> >> >> >> Kenji Nakamura wrote:
> >> >> >>> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
> >> >> >>> to: empire-db-user@incubator.apache.org
> >> >> >>> re Re: Prepared statement support?
> >> >> >>>
> >> >> >>> Rainer,
> >> >> >>>
> >> >> >>> Thank you for your reply. My comment are inline.
> >> >> >>>
> >> >> >>> On Wed, Dec 1, 2010 at 2:14 AM, Rainer Döbele
> <do...@esteam.de>
> >> >> >>> wrote:
> >> >> >>>> Hi Kenji,
> >> >> >>>>
> >> >> >>>> thanks for your interesting links about this subject.
> >> >> >>>>
> >> >> >>>> It is certainly true, that the performance of a prepared
> >> statements
> >> >> >>> is better when you execute it multiple times with varying
> >> parameter
> >> >> >>> values.
> >> >> >>>> This is not always possible when varying statements with
> >> >> conditional
> >> >> >>> joins are created at runtime.
> >> >> >>>> For a one-time statement using a prepared statement does not
> >> >> execute
> >> >> >>> faster than a normal statement.
> >> >> >>>
> >> >> >>> I understand the issue that the use of PreparedStatement seems
> to
> >> >> have
> >> >> >>> overhead and actually it may take longer if we measure it with
> a
> >> >> >>> single execution from application developer's point of view,
> but
> >> the
> >> >> >>> compiled result of the statement is kept added to Oracle's
> cache
> >> and
> >> >> >>> it flushes the compiled results of the PreparedStatement
> invoked
> >> >> from
> >> >> >>> different applications as the cache is managed per SID in
> Oracle.
> >> So
> >> >> >>> it has negative impact from the DBA's point of view. It is
> not
> >> an
> >> >> >>> issue as long as the DB is used as the data storage of a web
> >> >> >>> application server and the performance of the app is only
> >> concern,
> >> >> but
> >> >> >>> the assumption is not true when the DB is also used in data
> >> >> >>> processing.
> >> >> >>>
> >> >> >>>> The inclusion of parameter values in the SQL text when
> >> assembling
> >> >> >>> statements is an advantage when it comes to logging (logging
> of
> >> >> >>> parameterized statements is not sufficient to track errors) or
> >> for
> >> >> the
> >> >> >>> creation of SQL scripts that are saved and executed later.
> >> >> >>>
> >> >> >>> I see your point.
> >> >> >>>
> >> >> >>>>
> >> >> >>>> Currently Empire-db uses prepared statements by default only
> for
> >> >> >>> statements with BLOB and CLOB fields.
> >> >> >>>>
> >> >> >>>> However at least as far as update and insert statements are
> >> >> >>>> concerned
> >> >> >>> you can override the method useCmdParam() in DBCommandOracle,
> but
> >> >> you
> >> >> >>> need to subclass the DBDatabaseDriverOracle and override
> >> >> createCommand
> >> >> >>> first. If you return true in useCmdParam(), then Empire-DB
> will
> >> use
> >> >> a
> >> >> >>> prepared statement and supply this value as a prepared
> statement
> >> >> >>> parameter.
> >> >> >>>
> >> >> >>> From the point of view of Oracle administrator, the primary
> >> interest
> >> >> >>> is how to reduce the # of hard parse and increase the hit rate
> of
> >> >> the
> >> >> >>> cache, and using PreparedStatement only for CUD operation is
> not
> >> >> >>> sufficient if the ratio of Select outweigh CUD operations.
> From
> >> >> >>> security point of view, Select statement with parameters
> >> embedding
> >> >> >>> user's input is as vulnerable as other DMLs, so the option to
> use
> >> >> >>> PreparedStatement for CUD operation doesn't address those
> >> concerns,
> >> >> >>> while it may be useful to improve the performance on iterative
> >> >> >>> operations.
> >> >> >>>
> >> >> >>>>
> >> >> >>>> Personally I have used Empire-DB in many projects and
> >> performance
> >> >> or
> >> >> >>> security have never been a problem. However, if you except to
> >> >> execute
> >> >> >>> 10.000 sql statements a minute then certainly this needs to be
> >> >> >>> thoroughly checked.
> >> >> >>>
> >> >> >>> It is nice to know the framework has been proven in production
> >> >> >>> environments. Our current performance test also doesn't show
> the
> >> >> hard
> >> >> >>> parse is the primary culprit of the performance bottleneck, so
> it
> >> is
> >> >> >>> not an urgent problem, but I'd like prepare to answer the
> >> questions
> >> >> >>> from our DB engineers.
> >> >> >>>
> >> >> >>>>
> >> >> >>>> I have created a new Jira (EMPIREDB-91) issue for us to
> check,
> >> how
> >> >> >>> and where we can increase and optimize the use of prepared
> >> >> statements.
> >> >> >>>
> >> >> >>> Thank you for the reaction. I registered myself to the watch
> >> list.
> >> >> Let
> >> >> >>> me know if I can do something to make this forward.
> >> >> >>>
> >> >> >>> Lastly, I really thank you to share the framework in public. I
> >> have
> >> >> >>> used Toplink, Hibernate, and iBatis, but I favor empire-db a
> lot
> >> >> >>> because of the simplicity and type-safe coding. It is very
> >> >> >>> straightforward to customize to fulfill our specific needs
> such
> >> as
> >> >> the
> >> >> >>> support of TableFunction in Oracle.
> >> >> >>>
> >> >> >>> Regards,
> >> >> >>>
> >> >> >>> Kenji
> >> >> >>>
> >> >> >>>>
> >> >> >>>> Regards
> >> >> >>>> Rainer
> >> >> >>>>
> >> >> >>>>
> >> >> >>>> Kenji Nakamura wrote:
> >> >> >>>>> from: Kenji Nakamura [mailto:kenji_nakamura@diva-
> america.com]
> >> >> >>>>> to: empire-db-user@incubator.apache.org
> >> >> >>>>> re: Prepared statement support?
> >> >> >>>>>
> >> >> >>>>> Hi,
> >> >> >>>>>
> >> >> >>>>> I got a question from one of our DB engineer about the use
> of
> >> >> >>> prepared
> >> >> >>>>> statements.
> >> >> >>>>> According to him, or a thread in AskTom, it is always
> preferred
> >> to
> >> >> >>> use
> >> >> >>>>> PreparedStatement instead of Statement whenever possible.
> >> >> >>>>>
> >> >> >>>
> >> >>
> >>
> http://asktom.oracle.com/pls/asktom/f?p=100:11:7607696421577136::::P11
> >> >> >>> _
> >> >> >>> Q
> >> >> >>>>> UESTION_ID:1993620575194
> >> >> >>>>>
> >> >> >>>>> As far as I looked at the code, PreparedStatement is not
> used
> >> >> other
> >> >> >>>>> than DBDatabaseDriver class and the method is not used from
> >> other
> >> >> >>>>> code.
> >> >> >>>>>
> >> >> >>>>> My understanding is that creation of PreparedStatement has
> >> certain
> >> >> >>>>> overhead, but statement pooling introduced in JDBC 3.0
> >> mitigates
> >> >> >>>>> the impact especially from application server point of view.
> >> >> >>>>> We use Oracle, and the DB engineer explained that the use of
> >> >> >>> statement
> >> >> >>>>> floods the library cache in SGA and reduce the hit rate of
> >> >> >>>>> pre-compiled statements so it has negative impact on entire
> db,
> >> >> and
> >> >> >>>>> using PreparedStatement simply reduces the cost of hard
> parse.
> >> >> >>>>>
> >> >> >>>>> Another aspect is about SQL injection prevention. I noticed
> >> single
> >> >> >>>>> quotes are escaped at DBDatabaseDriver#getValueString()
> method,
> >> >> but
> >> >> >>>>> the preferred way to prevent SQL injection is to use
> >> >> >>> PreparedStatement
> >> >> >>>>> according to OWASP website.
> >> >> >>>>>
> >> >>
> http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
> >> >> >>>>>
> >> >> >>>>> Would you tell me the design philosophy or reasons not to
> use
> >> or
> >> >> >>>>> provide the option to use prepared statement? Is it
> possible,
> >> or
> >> >> >>> have
> >> >> >>>>> a plan to support PreparedStatement?
> >> >> >>>>>
> >> >> >>>>> Thanks,
> >> >> >>>>>
> >> >> >>>>> Kenji Nakamura
> >> >> >>>>
> >> >
> >>
> >>
> >>
> >> --
> >> http://www.somatik.be
> >> Microsoft gives you windows, Linux gives you the whole house.
> >
>
>
>
> --
> http://www.somatik.be
> Microsoft gives you windows, Linux gives you the whole house.
Re: All "prepared" now!
Posted by Francis De Brabandere <fr...@gmail.com>.
I would just continue with the release. That will be the last 2.0.x
Afterwards we can directly cut the 2.1.0
Cheers,
Francis
On Fri, Dec 3, 2010 at 11:25 AM, Rainer Döbele <do...@esteam.de> wrote:
> Hi Francis,
>
> Yes indeed a hard night.
> I had a problem with the parameter order that just took me some time to fix.
>
> I have seen that I missed a few places that sill generate simple statements.
> Will submit another patch today after a few more tests.
>
> Yes it's a shame that this isn't part of the release - I think it is an important major feature.
>
> How about stopping the current rc and going for the next one?
> I would probably safe us some unnecessary double work.
>
> Since I had to change a few things in the interface of DBCommand I would like to call the next release 2.1.0.
>
> What do you think?
>
> Regards
> Rainer
>
>
> Francis De Brabandere wrote:
>> from: Francis De Brabandere [mailto:francisdb@gmail.com]
>> to: empire-db-dev@incubator.apache.org
>> re: Re: All "prepared" now!
>>
>> Busy night Rainer? Great to hear this is implemented :-) Too bad we
>> just cut a release :-s
>>
>> On Fri, Dec 3, 2010 at 9:30 AM, Rainer Döbele <do...@esteam.de> wrote:
>> > Hi Kenji,
>> >
>> > good news for you: it's all "prepared" now :-)
>> > Most of it was already there, but a few bits and pieces were missing.
>> > However you have to take the latest sources directly from our SVN
>> repository.
>> >
>> > In DBDatabase you now have a property called
>> "preparedStatementsEnabled" which you can enable or disable.
>> > If enabled Empire-db will use prepared statements for all database
>> operations on DBRecord.
>> >
>> > If you create your own commands using DBCommand you will have to
>> explicitly declare the parameters using DBCommand.addCmdParam() which
>> will return a DBCommandParam (I have renamed this inner class from
>> DBCmdParameter!) except for set() operations.
>> >
>> > I have added an example to the empire-db-example-advanced
>> (SampleAdvApp.java). The commandParamsSample() method shows how to use
>> command params.
>> > By additionally setting preparedStatementsEnabled to enabled, also all
>> operations performed by Empire-db itself will use prepared statements.
>> > You can easily see it in the log.
>> > And the good thing is, you can easily switch between prepared and non-
>> prepared (non-prepared statements are still better for debugging).
>> >
>> > Let me know, if you have problems or need any more help.
>> > Best regards,
>> >
>> > Rainer
>> >
>> >
>> > Kenji Nakamura wrote:
>> >> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
>> >> to: empire-db-user@incubator.apache.org
>> >> re: Re: Prepared statement support?
>> >>
>> >> Hi Rainer,
>> >>
>> >> Yes, this is exactly what I was looking for.
>> >> Regarding statement pooling, it is a part of JDBC 3.0 spec and I
>> think
>> >> it is a job of connection pool utility.
>> >> We use c3p0 and it has statement pooling capability. It is highly
>> >> configurable and has lots of features.
>> >>
>> >>
>> http://www.mchange.com/projects/c3p0/index.html#configuring_statement_po
>> >> oling
>> >>
>> >> I really appreciate if you can include the bug fix of DBReader in the
>> >> next release as this is crucial feature to persuade DBAs and security
>> >> auditors.
>> >>
>> >> Thanks a lot!
>> >>
>> >> Kenji Nakamura
>> >>
>> >> On Dec 2, 2010, at 19:29, Rainer Döbele <do...@esteam.de> wrote:
>> >>
>> >> > Hi everyone,
>> >> >
>> >> > thanks for your comment Matt.
>> >> >
>> >> > To my own surprise I have overlooked that there is already
>> substantial
>> >> support for prepared statement generation in Empire-db now, but you
>> have
>> >> to explicitly declare the parameters.
>> >> > Here is an example of how to generate a prepared statement phrase
>> and
>> >> execute it with the corresponding parameters:
>> >> >
>> >> > // Define the query
>> >> > DBCommand cmd = db.createCommand();
>> >> >
>> >> > // Create parameters
>> >> > DBCmdParameter depIdParam = cmd.addCmdParam(1);
>> >> > DBCmdParameter genderParam = cmd.addCmdParam('F');
>> >> >
>> >> > // create statement
>> >> > cmd.select(EMP.getColumns());
>> >> > cmd.where(EMP.DEPARTMENT_ID.is(depIdParam));
>> >> > cmd.where(EMP.GENDER.is(genderParam));
>> >> >
>> >> > // First execution
>> >> > String sql = cmd.getSelect();
>> >> > ResultSet r = db.executeQuery(sql, cmd.getCmdParams(), false,
>> >> conn);
>> >> > // do something
>> >> > r.close();
>> >> >
>> >> > // Modify command parameters
>> >> > depIdParam.setValue(2);
>> >> > genderParam.setValue('M');
>> >> >
>> >> > // Second execution
>> >> > r = db.executeQuery(sql, cmd.getCmdParams(), false, conn);
>> >> > // do something
>> >> > r.close();
>> >> >
>> >> > This will result in the following SQL:
>> >> >
>> >> > SELECT t2.EMPLOYEE_ID, t2...
>> >> > FROM EMPLOYEES t2
>> >> > WHERE t2.DEPARTMENT_ID=? AND t2.GENDER=?
>> >> >
>> >> > And set the parameter to 1 and 'F' for the first query and to 2 and
>> >> 'M' for the second.
>> >> >
>> >> > Unfortunately there is a bug in DBReader so that cmd params are not
>> >> properly set.
>> >> > This is the reason why I used db.executeQuery(..) instead of a
>> >> DBReader in the example above.
>> >> > I will fix this bug as soon as possible.
>> >> >
>> >> > Another thing we should do is to use the prepared statements for
>> >> DBRecord.read (which in turn uses DBRowSet.readRecord(...)).
>> >> >
>> >> > As far as the pooling of prepared statements is concerned, if it's
>> not
>> >> done by the data source already it can also be done by subclassing
>> the
>> >> DBDatabaseDriver and overriding executeQuery() and / or executeSQL()
>> and
>> >> do it yourself. But it is not necessary for Empire-db to provide
>> this.
>> >> >
>> >> > Kenji will this satisfy your needs?
>> >> >
>> >> > Regards,
>> >> > Rainer
>> >> >
>> >> >
>> >> >
>> >> > Matthew Bond wrote:
>> >> >> from: Matthew Bond [mailto:bond@bond-it.de]
>> >> >> to: empire-db-dev@incubator.apache.org; empire-db-
>> >> >> re: AW: Prepared statement support?
>> >> >>
>> >> >> Hi Rainer, Hi Kenji,
>> >> >>
>> >> >> Rainer's comments are true in a Web Application scenario where the
>> >> >> connection if got for a short time and then released again. Empire
>> DB
>> >> >> can also be used in other scenarios, like a Fat Clients or Command
>> >> Line
>> >> >> Utility tools, where a connection will probably be held for the
>> whole
>> >> >> duration of the application lifetime and PooledStatements could
>> >> bring
>> >> >> more performance. So it really depends on what you application
>> type
>> >> you
>> >> >> are programming.
>> >> >>
>> >> >> FYI: WebSphere too pools prepared statements (see page 2 of
>> >> http://www-
>> >> >>
>> >>
>> 03.ibm.com/systems/resources/systems_i_advantages_perfmgmt_pdf_stmntcach
>> >> >> e.pdf "WebSphere, however, will do the caching automatically.
>> When
>> >> you
>> >> >> execute a query, WebSphere determines if the SQL text is already
>> in
>> >> the
>> >> >> cache and if so, it will use that cached statement instead of
>> >> preparing
>> >> >> a new one." ). So if EmpireDB was extended to make more use of
>> >> Prepared
>> >> >> Statements it would be advantageous.
>> >> >>
>> >> >> However as Rainer describes, the big benefit of using EmpireDB is
>> >> that
>> >> >> the selects are going to be way better than other ORM's as the
>> >> developer
>> >> >> hand crafts the "SQL" statement.
>> >> >>
>> >> >> The great thing is that it is Open Source so if you feel strongly
>> >> about
>> >> >> the use of PreparedStatements, you could submit a Patch adding
>> this
>> >> >> functionality.
>> >> >>
>> >> >> Cheers
>> >> >> Matt
>> >> >>
>> >> >> -----Ursprüngliche Nachricht-----
>> >> >> Von: Rainer Döbele [mailto:doebele@esteam.de]
>> >> >> Gesendet: Donnerstag, 2. Dezember 2010 00:11
>> >> >> An: empire-db-user@incubator.apache.org; empire-db-
>> >> >> dev@incubator.apache.org
>> >> >> Betreff: re: Prepared statement support?
>> >> >>
>> >> >> Dear Kenji,
>> >> >>
>> >> >> I have reviewed our code and thought about this subject again.
>> >> >> As you mentioned there is both a performance and a security issue
>> to
>> >> >> consider.
>> >> >> For the moment I would like to focus on the performance issue as
>> >> >> security can as well be established by other measures.
>> >> >>
>> >> >> It's pretty obvious to understand that creating a prepared
>> statement
>> >> and
>> >> >> executing it multiple times with varying parameters is superior
>> over
>> >> >> creating a normal statement each time. But as far as I understand
>> it,
>> >> >> the advantage of a ps exists only as long as the statement lives,
>> and
>> >> >> ends when you close it.
>> >> >>
>> >> >> The problem is, that a prepared statement is created for a
>> particular
>> >> >> connection. In a web-application we usually use a connection pool
>> and
>> >> >> the connection is fetched for a particular request. It is
>> extremely
>> >> >> rare, that the same statement is executed multiple times within a
>> >> single
>> >> >> request - whereas it is very likely that the same statement needs
>> to
>> >> be
>> >> >> executed by other users' requests. As those other users have
>> >> different
>> >> >> connections they cannot share the same prepared statement.
>> >> >>
>> >> >> Here is a thread discussing this issue:
>> >> >> http://www.velocityreviews.com/forums/t644638-jdbc-
>> preparedstatement-
>> >> in-
>> >> >> a-multi-threaded-environment.html
>> >> >>
>> >> >> As Empire-db does not store or maintain a connection, it is not
>> >> sensible
>> >> >> for us to store the actual JDBC prepared statement object. But
>> this
>> >> >> might not be necessary as it could be done on another level.
>> Possibly
>> >> >> the solution lies just in another Apache Project: Apache Commons
>> >> DBCP.
>> >> >> http://commons.apache.org/dbcp/index.html
>> >> >>
>> >> >> From my understanding it should be possible to use a commons-dbcp
>> >> >> connection pool that will also pool prepared statements. The
>> >> connections
>> >> >> returned by the pool can be used with Empire db just like a normal
>> >> JDBC
>> >> >> connection.
>> >> >> Of course we still need to enforce and extend the generation of
>> >> prepared
>> >> >> statement phrases beyond the CUD operations.
>> >> >>
>> >> >> Still we must keep in mind, that probably for most real world
>> >> >> applications the performance benefit of prepared statements over
>> >> simple
>> >> >> statements is negligible, and it is our primary goal to maintain
>> >> >> simplicity and transparency.
>> >> >> It is IMO far more important to be able to create efficient
>> >> statements -
>> >> >> and avoid the problem of OR-Mappers that usually work with lots of
>> >> >> simple operations. After all, one clever statement with server
>> side
>> >> db
>> >> >> logic will still execute a lot faster than 10 prepared statements
>> >> with
>> >> >> trailed Java logic.
>> >> >> (Still the gloal is to have it all of course)
>> >> >>
>> >> >> Any more suggestions or remarks on this topic?
>> >> >>
>> >> >> Regards
>> >> >> Rainer
>> >> >>
>> >> >>
>> >> >> Kenji Nakamura wrote:
>> >> >>> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
>> >> >>> to: empire-db-user@incubator.apache.org
>> >> >>> re Re: Prepared statement support?
>> >> >>>
>> >> >>> Rainer,
>> >> >>>
>> >> >>> Thank you for your reply. My comment are inline.
>> >> >>>
>> >> >>> On Wed, Dec 1, 2010 at 2:14 AM, Rainer Döbele <do...@esteam.de>
>> >> >>> wrote:
>> >> >>>> Hi Kenji,
>> >> >>>>
>> >> >>>> thanks for your interesting links about this subject.
>> >> >>>>
>> >> >>>> It is certainly true, that the performance of a prepared
>> statements
>> >> >>> is better when you execute it multiple times with varying
>> parameter
>> >> >>> values.
>> >> >>>> This is not always possible when varying statements with
>> >> conditional
>> >> >>> joins are created at runtime.
>> >> >>>> For a one-time statement using a prepared statement does not
>> >> execute
>> >> >>> faster than a normal statement.
>> >> >>>
>> >> >>> I understand the issue that the use of PreparedStatement seems to
>> >> have
>> >> >>> overhead and actually it may take longer if we measure it with a
>> >> >>> single execution from application developer's point of view, but
>> the
>> >> >>> compiled result of the statement is kept added to Oracle's cache
>> and
>> >> >>> it flushes the compiled results of the PreparedStatement invoked
>> >> from
>> >> >>> different applications as the cache is managed per SID in Oracle.
>> So
>> >> >>> it has negative impact from the DBA's point of view. It is not
>> an
>> >> >>> issue as long as the DB is used as the data storage of a web
>> >> >>> application server and the performance of the app is only
>> concern,
>> >> but
>> >> >>> the assumption is not true when the DB is also used in data
>> >> >>> processing.
>> >> >>>
>> >> >>>> The inclusion of parameter values in the SQL text when
>> assembling
>> >> >>> statements is an advantage when it comes to logging (logging of
>> >> >>> parameterized statements is not sufficient to track errors) or
>> for
>> >> the
>> >> >>> creation of SQL scripts that are saved and executed later.
>> >> >>>
>> >> >>> I see your point.
>> >> >>>
>> >> >>>>
>> >> >>>> Currently Empire-db uses prepared statements by default only for
>> >> >>> statements with BLOB and CLOB fields.
>> >> >>>>
>> >> >>>> However at least as far as update and insert statements are
>> >> >>>> concerned
>> >> >>> you can override the method useCmdParam() in DBCommandOracle, but
>> >> you
>> >> >>> need to subclass the DBDatabaseDriverOracle and override
>> >> createCommand
>> >> >>> first. If you return true in useCmdParam(), then Empire-DB will
>> use
>> >> a
>> >> >>> prepared statement and supply this value as a prepared statement
>> >> >>> parameter.
>> >> >>>
>> >> >>> From the point of view of Oracle administrator, the primary
>> interest
>> >> >>> is how to reduce the # of hard parse and increase the hit rate of
>> >> the
>> >> >>> cache, and using PreparedStatement only for CUD operation is not
>> >> >>> sufficient if the ratio of Select outweigh CUD operations. From
>> >> >>> security point of view, Select statement with parameters
>> embedding
>> >> >>> user's input is as vulnerable as other DMLs, so the option to use
>> >> >>> PreparedStatement for CUD operation doesn't address those
>> concerns,
>> >> >>> while it may be useful to improve the performance on iterative
>> >> >>> operations.
>> >> >>>
>> >> >>>>
>> >> >>>> Personally I have used Empire-DB in many projects and
>> performance
>> >> or
>> >> >>> security have never been a problem. However, if you except to
>> >> execute
>> >> >>> 10.000 sql statements a minute then certainly this needs to be
>> >> >>> thoroughly checked.
>> >> >>>
>> >> >>> It is nice to know the framework has been proven in production
>> >> >>> environments. Our current performance test also doesn't show the
>> >> hard
>> >> >>> parse is the primary culprit of the performance bottleneck, so it
>> is
>> >> >>> not an urgent problem, but I'd like prepare to answer the
>> questions
>> >> >>> from our DB engineers.
>> >> >>>
>> >> >>>>
>> >> >>>> I have created a new Jira (EMPIREDB-91) issue for us to check,
>> how
>> >> >>> and where we can increase and optimize the use of prepared
>> >> statements.
>> >> >>>
>> >> >>> Thank you for the reaction. I registered myself to the watch
>> list.
>> >> Let
>> >> >>> me know if I can do something to make this forward.
>> >> >>>
>> >> >>> Lastly, I really thank you to share the framework in public. I
>> have
>> >> >>> used Toplink, Hibernate, and iBatis, but I favor empire-db a lot
>> >> >>> because of the simplicity and type-safe coding. It is very
>> >> >>> straightforward to customize to fulfill our specific needs such
>> as
>> >> the
>> >> >>> support of TableFunction in Oracle.
>> >> >>>
>> >> >>> Regards,
>> >> >>>
>> >> >>> Kenji
>> >> >>>
>> >> >>>>
>> >> >>>> Regards
>> >> >>>> Rainer
>> >> >>>>
>> >> >>>>
>> >> >>>> Kenji Nakamura wrote:
>> >> >>>>> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
>> >> >>>>> to: empire-db-user@incubator.apache.org
>> >> >>>>> re: Prepared statement support?
>> >> >>>>>
>> >> >>>>> Hi,
>> >> >>>>>
>> >> >>>>> I got a question from one of our DB engineer about the use of
>> >> >>> prepared
>> >> >>>>> statements.
>> >> >>>>> According to him, or a thread in AskTom, it is always preferred
>> to
>> >> >>> use
>> >> >>>>> PreparedStatement instead of Statement whenever possible.
>> >> >>>>>
>> >> >>>
>> >>
>> http://asktom.oracle.com/pls/asktom/f?p=100:11:7607696421577136::::P11
>> >> >>> _
>> >> >>> Q
>> >> >>>>> UESTION_ID:1993620575194
>> >> >>>>>
>> >> >>>>> As far as I looked at the code, PreparedStatement is not used
>> >> other
>> >> >>>>> than DBDatabaseDriver class and the method is not used from
>> other
>> >> >>>>> code.
>> >> >>>>>
>> >> >>>>> My understanding is that creation of PreparedStatement has
>> certain
>> >> >>>>> overhead, but statement pooling introduced in JDBC 3.0
>> mitigates
>> >> >>>>> the impact especially from application server point of view.
>> >> >>>>> We use Oracle, and the DB engineer explained that the use of
>> >> >>> statement
>> >> >>>>> floods the library cache in SGA and reduce the hit rate of
>> >> >>>>> pre-compiled statements so it has negative impact on entire db,
>> >> and
>> >> >>>>> using PreparedStatement simply reduces the cost of hard parse.
>> >> >>>>>
>> >> >>>>> Another aspect is about SQL injection prevention. I noticed
>> single
>> >> >>>>> quotes are escaped at DBDatabaseDriver#getValueString() method,
>> >> but
>> >> >>>>> the preferred way to prevent SQL injection is to use
>> >> >>> PreparedStatement
>> >> >>>>> according to OWASP website.
>> >> >>>>>
>> >> http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
>> >> >>>>>
>> >> >>>>> Would you tell me the design philosophy or reasons not to use
>> or
>> >> >>>>> provide the option to use prepared statement? Is it possible,
>> or
>> >> >>> have
>> >> >>>>> a plan to support PreparedStatement?
>> >> >>>>>
>> >> >>>>> Thanks,
>> >> >>>>>
>> >> >>>>> Kenji Nakamura
>> >> >>>>
>> >
>>
>>
>>
>> --
>> http://www.somatik.be
>> Microsoft gives you windows, Linux gives you the whole house.
>
--
http://www.somatik.be
Microsoft gives you windows, Linux gives you the whole house.
re: All "prepared" now!
Posted by Rainer Döbele <do...@esteam.de>.
Hi Francis,
Yes indeed a hard night.
I had a problem with the parameter order that just took me some time to fix.
I have seen that I missed a few places that sill generate simple statements.
Will submit another patch today after a few more tests.
Yes it's a shame that this isn't part of the release - I think it is an important major feature.
How about stopping the current rc and going for the next one?
I would probably safe us some unnecessary double work.
Since I had to change a few things in the interface of DBCommand I would like to call the next release 2.1.0.
What do you think?
Regards
Rainer
Francis De Brabandere wrote:
> from: Francis De Brabandere [mailto:francisdb@gmail.com]
> to: empire-db-dev@incubator.apache.org
> re: Re: All "prepared" now!
>
> Busy night Rainer? Great to hear this is implemented :-) Too bad we
> just cut a release :-s
>
> On Fri, Dec 3, 2010 at 9:30 AM, Rainer Döbele <do...@esteam.de> wrote:
> > Hi Kenji,
> >
> > good news for you: it's all "prepared" now :-)
> > Most of it was already there, but a few bits and pieces were missing.
> > However you have to take the latest sources directly from our SVN
> repository.
> >
> > In DBDatabase you now have a property called
> "preparedStatementsEnabled" which you can enable or disable.
> > If enabled Empire-db will use prepared statements for all database
> operations on DBRecord.
> >
> > If you create your own commands using DBCommand you will have to
> explicitly declare the parameters using DBCommand.addCmdParam() which
> will return a DBCommandParam (I have renamed this inner class from
> DBCmdParameter!) except for set() operations.
> >
> > I have added an example to the empire-db-example-advanced
> (SampleAdvApp.java). The commandParamsSample() method shows how to use
> command params.
> > By additionally setting preparedStatementsEnabled to enabled, also all
> operations performed by Empire-db itself will use prepared statements.
> > You can easily see it in the log.
> > And the good thing is, you can easily switch between prepared and non-
> prepared (non-prepared statements are still better for debugging).
> >
> > Let me know, if you have problems or need any more help.
> > Best regards,
> >
> > Rainer
> >
> >
> > Kenji Nakamura wrote:
> >> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
> >> to: empire-db-user@incubator.apache.org
> >> re: Re: Prepared statement support?
> >>
> >> Hi Rainer,
> >>
> >> Yes, this is exactly what I was looking for.
> >> Regarding statement pooling, it is a part of JDBC 3.0 spec and I
> think
> >> it is a job of connection pool utility.
> >> We use c3p0 and it has statement pooling capability. It is highly
> >> configurable and has lots of features.
> >>
> >>
> http://www.mchange.com/projects/c3p0/index.html#configuring_statement_po
> >> oling
> >>
> >> I really appreciate if you can include the bug fix of DBReader in the
> >> next release as this is crucial feature to persuade DBAs and security
> >> auditors.
> >>
> >> Thanks a lot!
> >>
> >> Kenji Nakamura
> >>
> >> On Dec 2, 2010, at 19:29, Rainer Döbele <do...@esteam.de> wrote:
> >>
> >> > Hi everyone,
> >> >
> >> > thanks for your comment Matt.
> >> >
> >> > To my own surprise I have overlooked that there is already
> substantial
> >> support for prepared statement generation in Empire-db now, but you
> have
> >> to explicitly declare the parameters.
> >> > Here is an example of how to generate a prepared statement phrase
> and
> >> execute it with the corresponding parameters:
> >> >
> >> > // Define the query
> >> > DBCommand cmd = db.createCommand();
> >> >
> >> > // Create parameters
> >> > DBCmdParameter depIdParam = cmd.addCmdParam(1);
> >> > DBCmdParameter genderParam = cmd.addCmdParam('F');
> >> >
> >> > // create statement
> >> > cmd.select(EMP.getColumns());
> >> > cmd.where(EMP.DEPARTMENT_ID.is(depIdParam));
> >> > cmd.where(EMP.GENDER.is(genderParam));
> >> >
> >> > // First execution
> >> > String sql = cmd.getSelect();
> >> > ResultSet r = db.executeQuery(sql, cmd.getCmdParams(), false,
> >> conn);
> >> > // do something
> >> > r.close();
> >> >
> >> > // Modify command parameters
> >> > depIdParam.setValue(2);
> >> > genderParam.setValue('M');
> >> >
> >> > // Second execution
> >> > r = db.executeQuery(sql, cmd.getCmdParams(), false, conn);
> >> > // do something
> >> > r.close();
> >> >
> >> > This will result in the following SQL:
> >> >
> >> > SELECT t2.EMPLOYEE_ID, t2...
> >> > FROM EMPLOYEES t2
> >> > WHERE t2.DEPARTMENT_ID=? AND t2.GENDER=?
> >> >
> >> > And set the parameter to 1 and 'F' for the first query and to 2 and
> >> 'M' for the second.
> >> >
> >> > Unfortunately there is a bug in DBReader so that cmd params are not
> >> properly set.
> >> > This is the reason why I used db.executeQuery(..) instead of a
> >> DBReader in the example above.
> >> > I will fix this bug as soon as possible.
> >> >
> >> > Another thing we should do is to use the prepared statements for
> >> DBRecord.read (which in turn uses DBRowSet.readRecord(...)).
> >> >
> >> > As far as the pooling of prepared statements is concerned, if it's
> not
> >> done by the data source already it can also be done by subclassing
> the
> >> DBDatabaseDriver and overriding executeQuery() and / or executeSQL()
> and
> >> do it yourself. But it is not necessary for Empire-db to provide
> this.
> >> >
> >> > Kenji will this satisfy your needs?
> >> >
> >> > Regards,
> >> > Rainer
> >> >
> >> >
> >> >
> >> > Matthew Bond wrote:
> >> >> from: Matthew Bond [mailto:bond@bond-it.de]
> >> >> to: empire-db-dev@incubator.apache.org; empire-db-
> >> >> re: AW: Prepared statement support?
> >> >>
> >> >> Hi Rainer, Hi Kenji,
> >> >>
> >> >> Rainer's comments are true in a Web Application scenario where the
> >> >> connection if got for a short time and then released again. Empire
> DB
> >> >> can also be used in other scenarios, like a Fat Clients or Command
> >> Line
> >> >> Utility tools, where a connection will probably be held for the
> whole
> >> >> duration of the application lifetime and PooledStatements could
> >> bring
> >> >> more performance. So it really depends on what you application
> type
> >> you
> >> >> are programming.
> >> >>
> >> >> FYI: WebSphere too pools prepared statements (see page 2 of
> >> http://www-
> >> >>
> >>
> 03.ibm.com/systems/resources/systems_i_advantages_perfmgmt_pdf_stmntcach
> >> >> e.pdf "WebSphere, however, will do the caching automatically.
> When
> >> you
> >> >> execute a query, WebSphere determines if the SQL text is already
> in
> >> the
> >> >> cache and if so, it will use that cached statement instead of
> >> preparing
> >> >> a new one." ). So if EmpireDB was extended to make more use of
> >> Prepared
> >> >> Statements it would be advantageous.
> >> >>
> >> >> However as Rainer describes, the big benefit of using EmpireDB is
> >> that
> >> >> the selects are going to be way better than other ORM's as the
> >> developer
> >> >> hand crafts the "SQL" statement.
> >> >>
> >> >> The great thing is that it is Open Source so if you feel strongly
> >> about
> >> >> the use of PreparedStatements, you could submit a Patch adding
> this
> >> >> functionality.
> >> >>
> >> >> Cheers
> >> >> Matt
> >> >>
> >> >> -----Ursprüngliche Nachricht-----
> >> >> Von: Rainer Döbele [mailto:doebele@esteam.de]
> >> >> Gesendet: Donnerstag, 2. Dezember 2010 00:11
> >> >> An: empire-db-user@incubator.apache.org; empire-db-
> >> >> dev@incubator.apache.org
> >> >> Betreff: re: Prepared statement support?
> >> >>
> >> >> Dear Kenji,
> >> >>
> >> >> I have reviewed our code and thought about this subject again.
> >> >> As you mentioned there is both a performance and a security issue
> to
> >> >> consider.
> >> >> For the moment I would like to focus on the performance issue as
> >> >> security can as well be established by other measures.
> >> >>
> >> >> It's pretty obvious to understand that creating a prepared
> statement
> >> and
> >> >> executing it multiple times with varying parameters is superior
> over
> >> >> creating a normal statement each time. But as far as I understand
> it,
> >> >> the advantage of a ps exists only as long as the statement lives,
> and
> >> >> ends when you close it.
> >> >>
> >> >> The problem is, that a prepared statement is created for a
> particular
> >> >> connection. In a web-application we usually use a connection pool
> and
> >> >> the connection is fetched for a particular request. It is
> extremely
> >> >> rare, that the same statement is executed multiple times within a
> >> single
> >> >> request - whereas it is very likely that the same statement needs
> to
> >> be
> >> >> executed by other users' requests. As those other users have
> >> different
> >> >> connections they cannot share the same prepared statement.
> >> >>
> >> >> Here is a thread discussing this issue:
> >> >> http://www.velocityreviews.com/forums/t644638-jdbc-
> preparedstatement-
> >> in-
> >> >> a-multi-threaded-environment.html
> >> >>
> >> >> As Empire-db does not store or maintain a connection, it is not
> >> sensible
> >> >> for us to store the actual JDBC prepared statement object. But
> this
> >> >> might not be necessary as it could be done on another level.
> Possibly
> >> >> the solution lies just in another Apache Project: Apache Commons
> >> DBCP.
> >> >> http://commons.apache.org/dbcp/index.html
> >> >>
> >> >> From my understanding it should be possible to use a commons-dbcp
> >> >> connection pool that will also pool prepared statements. The
> >> connections
> >> >> returned by the pool can be used with Empire db just like a normal
> >> JDBC
> >> >> connection.
> >> >> Of course we still need to enforce and extend the generation of
> >> prepared
> >> >> statement phrases beyond the CUD operations.
> >> >>
> >> >> Still we must keep in mind, that probably for most real world
> >> >> applications the performance benefit of prepared statements over
> >> simple
> >> >> statements is negligible, and it is our primary goal to maintain
> >> >> simplicity and transparency.
> >> >> It is IMO far more important to be able to create efficient
> >> statements -
> >> >> and avoid the problem of OR-Mappers that usually work with lots of
> >> >> simple operations. After all, one clever statement with server
> side
> >> db
> >> >> logic will still execute a lot faster than 10 prepared statements
> >> with
> >> >> trailed Java logic.
> >> >> (Still the gloal is to have it all of course)
> >> >>
> >> >> Any more suggestions or remarks on this topic?
> >> >>
> >> >> Regards
> >> >> Rainer
> >> >>
> >> >>
> >> >> Kenji Nakamura wrote:
> >> >>> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
> >> >>> to: empire-db-user@incubator.apache.org
> >> >>> re Re: Prepared statement support?
> >> >>>
> >> >>> Rainer,
> >> >>>
> >> >>> Thank you for your reply. My comment are inline.
> >> >>>
> >> >>> On Wed, Dec 1, 2010 at 2:14 AM, Rainer Döbele <do...@esteam.de>
> >> >>> wrote:
> >> >>>> Hi Kenji,
> >> >>>>
> >> >>>> thanks for your interesting links about this subject.
> >> >>>>
> >> >>>> It is certainly true, that the performance of a prepared
> statements
> >> >>> is better when you execute it multiple times with varying
> parameter
> >> >>> values.
> >> >>>> This is not always possible when varying statements with
> >> conditional
> >> >>> joins are created at runtime.
> >> >>>> For a one-time statement using a prepared statement does not
> >> execute
> >> >>> faster than a normal statement.
> >> >>>
> >> >>> I understand the issue that the use of PreparedStatement seems to
> >> have
> >> >>> overhead and actually it may take longer if we measure it with a
> >> >>> single execution from application developer's point of view, but
> the
> >> >>> compiled result of the statement is kept added to Oracle's cache
> and
> >> >>> it flushes the compiled results of the PreparedStatement invoked
> >> from
> >> >>> different applications as the cache is managed per SID in Oracle.
> So
> >> >>> it has negative impact from the DBA's point of view. It is not
> an
> >> >>> issue as long as the DB is used as the data storage of a web
> >> >>> application server and the performance of the app is only
> concern,
> >> but
> >> >>> the assumption is not true when the DB is also used in data
> >> >>> processing.
> >> >>>
> >> >>>> The inclusion of parameter values in the SQL text when
> assembling
> >> >>> statements is an advantage when it comes to logging (logging of
> >> >>> parameterized statements is not sufficient to track errors) or
> for
> >> the
> >> >>> creation of SQL scripts that are saved and executed later.
> >> >>>
> >> >>> I see your point.
> >> >>>
> >> >>>>
> >> >>>> Currently Empire-db uses prepared statements by default only for
> >> >>> statements with BLOB and CLOB fields.
> >> >>>>
> >> >>>> However at least as far as update and insert statements are
> >> >>>> concerned
> >> >>> you can override the method useCmdParam() in DBCommandOracle, but
> >> you
> >> >>> need to subclass the DBDatabaseDriverOracle and override
> >> createCommand
> >> >>> first. If you return true in useCmdParam(), then Empire-DB will
> use
> >> a
> >> >>> prepared statement and supply this value as a prepared statement
> >> >>> parameter.
> >> >>>
> >> >>> From the point of view of Oracle administrator, the primary
> interest
> >> >>> is how to reduce the # of hard parse and increase the hit rate of
> >> the
> >> >>> cache, and using PreparedStatement only for CUD operation is not
> >> >>> sufficient if the ratio of Select outweigh CUD operations. From
> >> >>> security point of view, Select statement with parameters
> embedding
> >> >>> user's input is as vulnerable as other DMLs, so the option to use
> >> >>> PreparedStatement for CUD operation doesn't address those
> concerns,
> >> >>> while it may be useful to improve the performance on iterative
> >> >>> operations.
> >> >>>
> >> >>>>
> >> >>>> Personally I have used Empire-DB in many projects and
> performance
> >> or
> >> >>> security have never been a problem. However, if you except to
> >> execute
> >> >>> 10.000 sql statements a minute then certainly this needs to be
> >> >>> thoroughly checked.
> >> >>>
> >> >>> It is nice to know the framework has been proven in production
> >> >>> environments. Our current performance test also doesn't show the
> >> hard
> >> >>> parse is the primary culprit of the performance bottleneck, so it
> is
> >> >>> not an urgent problem, but I'd like prepare to answer the
> questions
> >> >>> from our DB engineers.
> >> >>>
> >> >>>>
> >> >>>> I have created a new Jira (EMPIREDB-91) issue for us to check,
> how
> >> >>> and where we can increase and optimize the use of prepared
> >> statements.
> >> >>>
> >> >>> Thank you for the reaction. I registered myself to the watch
> list.
> >> Let
> >> >>> me know if I can do something to make this forward.
> >> >>>
> >> >>> Lastly, I really thank you to share the framework in public. I
> have
> >> >>> used Toplink, Hibernate, and iBatis, but I favor empire-db a lot
> >> >>> because of the simplicity and type-safe coding. It is very
> >> >>> straightforward to customize to fulfill our specific needs such
> as
> >> the
> >> >>> support of TableFunction in Oracle.
> >> >>>
> >> >>> Regards,
> >> >>>
> >> >>> Kenji
> >> >>>
> >> >>>>
> >> >>>> Regards
> >> >>>> Rainer
> >> >>>>
> >> >>>>
> >> >>>> Kenji Nakamura wrote:
> >> >>>>> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
> >> >>>>> to: empire-db-user@incubator.apache.org
> >> >>>>> re: Prepared statement support?
> >> >>>>>
> >> >>>>> Hi,
> >> >>>>>
> >> >>>>> I got a question from one of our DB engineer about the use of
> >> >>> prepared
> >> >>>>> statements.
> >> >>>>> According to him, or a thread in AskTom, it is always preferred
> to
> >> >>> use
> >> >>>>> PreparedStatement instead of Statement whenever possible.
> >> >>>>>
> >> >>>
> >>
> http://asktom.oracle.com/pls/asktom/f?p=100:11:7607696421577136::::P11
> >> >>> _
> >> >>> Q
> >> >>>>> UESTION_ID:1993620575194
> >> >>>>>
> >> >>>>> As far as I looked at the code, PreparedStatement is not used
> >> other
> >> >>>>> than DBDatabaseDriver class and the method is not used from
> other
> >> >>>>> code.
> >> >>>>>
> >> >>>>> My understanding is that creation of PreparedStatement has
> certain
> >> >>>>> overhead, but statement pooling introduced in JDBC 3.0
> mitigates
> >> >>>>> the impact especially from application server point of view.
> >> >>>>> We use Oracle, and the DB engineer explained that the use of
> >> >>> statement
> >> >>>>> floods the library cache in SGA and reduce the hit rate of
> >> >>>>> pre-compiled statements so it has negative impact on entire db,
> >> and
> >> >>>>> using PreparedStatement simply reduces the cost of hard parse.
> >> >>>>>
> >> >>>>> Another aspect is about SQL injection prevention. I noticed
> single
> >> >>>>> quotes are escaped at DBDatabaseDriver#getValueString() method,
> >> but
> >> >>>>> the preferred way to prevent SQL injection is to use
> >> >>> PreparedStatement
> >> >>>>> according to OWASP website.
> >> >>>>>
> >> http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
> >> >>>>>
> >> >>>>> Would you tell me the design philosophy or reasons not to use
> or
> >> >>>>> provide the option to use prepared statement? Is it possible,
> or
> >> >>> have
> >> >>>>> a plan to support PreparedStatement?
> >> >>>>>
> >> >>>>> Thanks,
> >> >>>>>
> >> >>>>> Kenji Nakamura
> >> >>>>
> >
>
>
>
> --
> http://www.somatik.be
> Microsoft gives you windows, Linux gives you the whole house.
Re: All "prepared" now!
Posted by Francis De Brabandere <fr...@gmail.com>.
Busy night Rainer? Great to hear this is implemented :-) Too bad we
just cut a release :-s
On Fri, Dec 3, 2010 at 9:30 AM, Rainer Döbele <do...@esteam.de> wrote:
> Hi Kenji,
>
> good news for you: it's all "prepared" now :-)
> Most of it was already there, but a few bits and pieces were missing.
> However you have to take the latest sources directly from our SVN repository.
>
> In DBDatabase you now have a property called "preparedStatementsEnabled" which you can enable or disable.
> If enabled Empire-db will use prepared statements for all database operations on DBRecord.
>
> If you create your own commands using DBCommand you will have to explicitly declare the parameters using DBCommand.addCmdParam() which will return a DBCommandParam (I have renamed this inner class from DBCmdParameter!) except for set() operations.
>
> I have added an example to the empire-db-example-advanced (SampleAdvApp.java). The commandParamsSample() method shows how to use command params.
> By additionally setting preparedStatementsEnabled to enabled, also all operations performed by Empire-db itself will use prepared statements.
> You can easily see it in the log.
> And the good thing is, you can easily switch between prepared and non-prepared (non-prepared statements are still better for debugging).
>
> Let me know, if you have problems or need any more help.
> Best regards,
>
> Rainer
>
>
> Kenji Nakamura wrote:
>> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
>> to: empire-db-user@incubator.apache.org
>> re: Re: Prepared statement support?
>>
>> Hi Rainer,
>>
>> Yes, this is exactly what I was looking for.
>> Regarding statement pooling, it is a part of JDBC 3.0 spec and I think
>> it is a job of connection pool utility.
>> We use c3p0 and it has statement pooling capability. It is highly
>> configurable and has lots of features.
>>
>> http://www.mchange.com/projects/c3p0/index.html#configuring_statement_po
>> oling
>>
>> I really appreciate if you can include the bug fix of DBReader in the
>> next release as this is crucial feature to persuade DBAs and security
>> auditors.
>>
>> Thanks a lot!
>>
>> Kenji Nakamura
>>
>> On Dec 2, 2010, at 19:29, Rainer Döbele <do...@esteam.de> wrote:
>>
>> > Hi everyone,
>> >
>> > thanks for your comment Matt.
>> >
>> > To my own surprise I have overlooked that there is already substantial
>> support for prepared statement generation in Empire-db now, but you have
>> to explicitly declare the parameters.
>> > Here is an example of how to generate a prepared statement phrase and
>> execute it with the corresponding parameters:
>> >
>> > // Define the query
>> > DBCommand cmd = db.createCommand();
>> >
>> > // Create parameters
>> > DBCmdParameter depIdParam = cmd.addCmdParam(1);
>> > DBCmdParameter genderParam = cmd.addCmdParam('F');
>> >
>> > // create statement
>> > cmd.select(EMP.getColumns());
>> > cmd.where(EMP.DEPARTMENT_ID.is(depIdParam));
>> > cmd.where(EMP.GENDER.is(genderParam));
>> >
>> > // First execution
>> > String sql = cmd.getSelect();
>> > ResultSet r = db.executeQuery(sql, cmd.getCmdParams(), false,
>> conn);
>> > // do something
>> > r.close();
>> >
>> > // Modify command parameters
>> > depIdParam.setValue(2);
>> > genderParam.setValue('M');
>> >
>> > // Second execution
>> > r = db.executeQuery(sql, cmd.getCmdParams(), false, conn);
>> > // do something
>> > r.close();
>> >
>> > This will result in the following SQL:
>> >
>> > SELECT t2.EMPLOYEE_ID, t2...
>> > FROM EMPLOYEES t2
>> > WHERE t2.DEPARTMENT_ID=? AND t2.GENDER=?
>> >
>> > And set the parameter to 1 and 'F' for the first query and to 2 and
>> 'M' for the second.
>> >
>> > Unfortunately there is a bug in DBReader so that cmd params are not
>> properly set.
>> > This is the reason why I used db.executeQuery(..) instead of a
>> DBReader in the example above.
>> > I will fix this bug as soon as possible.
>> >
>> > Another thing we should do is to use the prepared statements for
>> DBRecord.read (which in turn uses DBRowSet.readRecord(...)).
>> >
>> > As far as the pooling of prepared statements is concerned, if it's not
>> done by the data source already it can also be done by subclassing the
>> DBDatabaseDriver and overriding executeQuery() and / or executeSQL() and
>> do it yourself. But it is not necessary for Empire-db to provide this.
>> >
>> > Kenji will this satisfy your needs?
>> >
>> > Regards,
>> > Rainer
>> >
>> >
>> >
>> > Matthew Bond wrote:
>> >> from: Matthew Bond [mailto:bond@bond-it.de]
>> >> to: empire-db-dev@incubator.apache.org; empire-db-
>> >> re: AW: Prepared statement support?
>> >>
>> >> Hi Rainer, Hi Kenji,
>> >>
>> >> Rainer's comments are true in a Web Application scenario where the
>> >> connection if got for a short time and then released again. Empire DB
>> >> can also be used in other scenarios, like a Fat Clients or Command
>> Line
>> >> Utility tools, where a connection will probably be held for the whole
>> >> duration of the application lifetime and PooledStatements could
>> bring
>> >> more performance. So it really depends on what you application type
>> you
>> >> are programming.
>> >>
>> >> FYI: WebSphere too pools prepared statements (see page 2 of
>> http://www-
>> >>
>> 03.ibm.com/systems/resources/systems_i_advantages_perfmgmt_pdf_stmntcach
>> >> e.pdf "WebSphere, however, will do the caching automatically. When
>> you
>> >> execute a query, WebSphere determines if the SQL text is already in
>> the
>> >> cache and if so, it will use that cached statement instead of
>> preparing
>> >> a new one." ). So if EmpireDB was extended to make more use of
>> Prepared
>> >> Statements it would be advantageous.
>> >>
>> >> However as Rainer describes, the big benefit of using EmpireDB is
>> that
>> >> the selects are going to be way better than other ORM's as the
>> developer
>> >> hand crafts the "SQL" statement.
>> >>
>> >> The great thing is that it is Open Source so if you feel strongly
>> about
>> >> the use of PreparedStatements, you could submit a Patch adding this
>> >> functionality.
>> >>
>> >> Cheers
>> >> Matt
>> >>
>> >> -----Ursprüngliche Nachricht-----
>> >> Von: Rainer Döbele [mailto:doebele@esteam.de]
>> >> Gesendet: Donnerstag, 2. Dezember 2010 00:11
>> >> An: empire-db-user@incubator.apache.org; empire-db-
>> >> dev@incubator.apache.org
>> >> Betreff: re: Prepared statement support?
>> >>
>> >> Dear Kenji,
>> >>
>> >> I have reviewed our code and thought about this subject again.
>> >> As you mentioned there is both a performance and a security issue to
>> >> consider.
>> >> For the moment I would like to focus on the performance issue as
>> >> security can as well be established by other measures.
>> >>
>> >> It's pretty obvious to understand that creating a prepared statement
>> and
>> >> executing it multiple times with varying parameters is superior over
>> >> creating a normal statement each time. But as far as I understand it,
>> >> the advantage of a ps exists only as long as the statement lives, and
>> >> ends when you close it.
>> >>
>> >> The problem is, that a prepared statement is created for a particular
>> >> connection. In a web-application we usually use a connection pool and
>> >> the connection is fetched for a particular request. It is extremely
>> >> rare, that the same statement is executed multiple times within a
>> single
>> >> request - whereas it is very likely that the same statement needs to
>> be
>> >> executed by other users' requests. As those other users have
>> different
>> >> connections they cannot share the same prepared statement.
>> >>
>> >> Here is a thread discussing this issue:
>> >> http://www.velocityreviews.com/forums/t644638-jdbc-preparedstatement-
>> in-
>> >> a-multi-threaded-environment.html
>> >>
>> >> As Empire-db does not store or maintain a connection, it is not
>> sensible
>> >> for us to store the actual JDBC prepared statement object. But this
>> >> might not be necessary as it could be done on another level. Possibly
>> >> the solution lies just in another Apache Project: Apache Commons
>> DBCP.
>> >> http://commons.apache.org/dbcp/index.html
>> >>
>> >> From my understanding it should be possible to use a commons-dbcp
>> >> connection pool that will also pool prepared statements. The
>> connections
>> >> returned by the pool can be used with Empire db just like a normal
>> JDBC
>> >> connection.
>> >> Of course we still need to enforce and extend the generation of
>> prepared
>> >> statement phrases beyond the CUD operations.
>> >>
>> >> Still we must keep in mind, that probably for most real world
>> >> applications the performance benefit of prepared statements over
>> simple
>> >> statements is negligible, and it is our primary goal to maintain
>> >> simplicity and transparency.
>> >> It is IMO far more important to be able to create efficient
>> statements -
>> >> and avoid the problem of OR-Mappers that usually work with lots of
>> >> simple operations. After all, one clever statement with server side
>> db
>> >> logic will still execute a lot faster than 10 prepared statements
>> with
>> >> trailed Java logic.
>> >> (Still the gloal is to have it all of course)
>> >>
>> >> Any more suggestions or remarks on this topic?
>> >>
>> >> Regards
>> >> Rainer
>> >>
>> >>
>> >> Kenji Nakamura wrote:
>> >>> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
>> >>> to: empire-db-user@incubator.apache.org
>> >>> re Re: Prepared statement support?
>> >>>
>> >>> Rainer,
>> >>>
>> >>> Thank you for your reply. My comment are inline.
>> >>>
>> >>> On Wed, Dec 1, 2010 at 2:14 AM, Rainer Döbele <do...@esteam.de>
>> >>> wrote:
>> >>>> Hi Kenji,
>> >>>>
>> >>>> thanks for your interesting links about this subject.
>> >>>>
>> >>>> It is certainly true, that the performance of a prepared statements
>> >>> is better when you execute it multiple times with varying parameter
>> >>> values.
>> >>>> This is not always possible when varying statements with
>> conditional
>> >>> joins are created at runtime.
>> >>>> For a one-time statement using a prepared statement does not
>> execute
>> >>> faster than a normal statement.
>> >>>
>> >>> I understand the issue that the use of PreparedStatement seems to
>> have
>> >>> overhead and actually it may take longer if we measure it with a
>> >>> single execution from application developer's point of view, but the
>> >>> compiled result of the statement is kept added to Oracle's cache and
>> >>> it flushes the compiled results of the PreparedStatement invoked
>> from
>> >>> different applications as the cache is managed per SID in Oracle. So
>> >>> it has negative impact from the DBA's point of view. It is not an
>> >>> issue as long as the DB is used as the data storage of a web
>> >>> application server and the performance of the app is only concern,
>> but
>> >>> the assumption is not true when the DB is also used in data
>> >>> processing.
>> >>>
>> >>>> The inclusion of parameter values in the SQL text when assembling
>> >>> statements is an advantage when it comes to logging (logging of
>> >>> parameterized statements is not sufficient to track errors) or for
>> the
>> >>> creation of SQL scripts that are saved and executed later.
>> >>>
>> >>> I see your point.
>> >>>
>> >>>>
>> >>>> Currently Empire-db uses prepared statements by default only for
>> >>> statements with BLOB and CLOB fields.
>> >>>>
>> >>>> However at least as far as update and insert statements are
>> >>>> concerned
>> >>> you can override the method useCmdParam() in DBCommandOracle, but
>> you
>> >>> need to subclass the DBDatabaseDriverOracle and override
>> createCommand
>> >>> first. If you return true in useCmdParam(), then Empire-DB will use
>> a
>> >>> prepared statement and supply this value as a prepared statement
>> >>> parameter.
>> >>>
>> >>> From the point of view of Oracle administrator, the primary interest
>> >>> is how to reduce the # of hard parse and increase the hit rate of
>> the
>> >>> cache, and using PreparedStatement only for CUD operation is not
>> >>> sufficient if the ratio of Select outweigh CUD operations. From
>> >>> security point of view, Select statement with parameters embedding
>> >>> user's input is as vulnerable as other DMLs, so the option to use
>> >>> PreparedStatement for CUD operation doesn't address those concerns,
>> >>> while it may be useful to improve the performance on iterative
>> >>> operations.
>> >>>
>> >>>>
>> >>>> Personally I have used Empire-DB in many projects and performance
>> or
>> >>> security have never been a problem. However, if you except to
>> execute
>> >>> 10.000 sql statements a minute then certainly this needs to be
>> >>> thoroughly checked.
>> >>>
>> >>> It is nice to know the framework has been proven in production
>> >>> environments. Our current performance test also doesn't show the
>> hard
>> >>> parse is the primary culprit of the performance bottleneck, so it is
>> >>> not an urgent problem, but I'd like prepare to answer the questions
>> >>> from our DB engineers.
>> >>>
>> >>>>
>> >>>> I have created a new Jira (EMPIREDB-91) issue for us to check, how
>> >>> and where we can increase and optimize the use of prepared
>> statements.
>> >>>
>> >>> Thank you for the reaction. I registered myself to the watch list.
>> Let
>> >>> me know if I can do something to make this forward.
>> >>>
>> >>> Lastly, I really thank you to share the framework in public. I have
>> >>> used Toplink, Hibernate, and iBatis, but I favor empire-db a lot
>> >>> because of the simplicity and type-safe coding. It is very
>> >>> straightforward to customize to fulfill our specific needs such as
>> the
>> >>> support of TableFunction in Oracle.
>> >>>
>> >>> Regards,
>> >>>
>> >>> Kenji
>> >>>
>> >>>>
>> >>>> Regards
>> >>>> Rainer
>> >>>>
>> >>>>
>> >>>> Kenji Nakamura wrote:
>> >>>>> from: Kenji Nakamura [mailto:kenji_nakamura@diva-america.com]
>> >>>>> to: empire-db-user@incubator.apache.org
>> >>>>> re: Prepared statement support?
>> >>>>>
>> >>>>> Hi,
>> >>>>>
>> >>>>> I got a question from one of our DB engineer about the use of
>> >>> prepared
>> >>>>> statements.
>> >>>>> According to him, or a thread in AskTom, it is always preferred to
>> >>> use
>> >>>>> PreparedStatement instead of Statement whenever possible.
>> >>>>>
>> >>>
>> http://asktom.oracle.com/pls/asktom/f?p=100:11:7607696421577136::::P11
>> >>> _
>> >>> Q
>> >>>>> UESTION_ID:1993620575194
>> >>>>>
>> >>>>> As far as I looked at the code, PreparedStatement is not used
>> other
>> >>>>> than DBDatabaseDriver class and the method is not used from other
>> >>>>> code.
>> >>>>>
>> >>>>> My understanding is that creation of PreparedStatement has certain
>> >>>>> overhead, but statement pooling introduced in JDBC 3.0 mitigates
>> >>>>> the impact especially from application server point of view.
>> >>>>> We use Oracle, and the DB engineer explained that the use of
>> >>> statement
>> >>>>> floods the library cache in SGA and reduce the hit rate of
>> >>>>> pre-compiled statements so it has negative impact on entire db,
>> and
>> >>>>> using PreparedStatement simply reduces the cost of hard parse.
>> >>>>>
>> >>>>> Another aspect is about SQL injection prevention. I noticed single
>> >>>>> quotes are escaped at DBDatabaseDriver#getValueString() method,
>> but
>> >>>>> the preferred way to prevent SQL injection is to use
>> >>> PreparedStatement
>> >>>>> according to OWASP website.
>> >>>>>
>> http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
>> >>>>>
>> >>>>> Would you tell me the design philosophy or reasons not to use or
>> >>>>> provide the option to use prepared statement? Is it possible, or
>> >>> have
>> >>>>> a plan to support PreparedStatement?
>> >>>>>
>> >>>>> Thanks,
>> >>>>>
>> >>>>> Kenji Nakamura
>> >>>>
>
--
http://www.somatik.be
Microsoft gives you windows, Linux gives you the whole house.