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" <[email protected]> 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:[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.
