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:[email protected]] > to: [email protected] > 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 <[email protected]> > 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:[email protected]] > >> to: [email protected] > >> 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 <[email protected]> > 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:[email protected]] > >> >> to: [email protected] > >> >> 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 <[email protected]> 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:[email protected]] > >> >> >> to: [email protected]; 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:[email protected]] > >> >> >> Gesendet: Donnerstag, 2. Dezember 2010 00:11 > >> >> >> An: [email protected]; empire-db- > >> >> >> [email protected] > >> >> >> 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:[email protected]] > >> >> >>> to: [email protected] > >> >> >>> 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 > <[email protected]> > >> >> >>> 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_nakam...@diva- > america.com] > >> >> >>>>> to: [email protected] > >> >> >>>>> 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.
