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