Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EGL Development Tools » How does sql prepare statement really work?
How does sql prepare statement really work? [message #882972] Thu, 07 June 2012 11:02 Go to next message
Gianluca Donati is currently offline Gianluca Donati
Messages: 10
Registered: May 2012
Junior Member
Hi,

the library created by wizard "Library from a database" in EDT 0.8.0 has several functions sharing the same datasource definition. If aTable is the table chosen during the wizard, the function getaTableListWithStatus receives listSpec, a parameter of ListSpecification type, and prepares a select statement.
If I call repeatedly this function changing the content of parameter listspec.fromTablesWhere, the function still prepares always the same statement!
I think this happens because the java code generated from EGL code:

selectStatement SQLStatement?{@SQLResultSetControl{scrollablity =
TYPE_SCROLL_INSENSITIVE}};
prepare selectStatement from dataSource with "SELECT * " + listSpec.fromTablesWhere;

looks like:

selectStatement =
(java.sql.PreparedStatement)dataSource.getStatement("my.package.library", 0);
if (selectStatement== null) {
String stmtStr = (("SELECT * ") + listSpec.fromTablesWhere);
selectStatement = dataSource.getConnection().prepareStatement(stmtStr,
java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
dataSource.registerStatement("my.package.library", 0, selectStatement);
}

I understand that, for some reason, this code tries to get the statement from previous registered statements, and use my parameter only if there is nothing to get. But the EGL code doesn't suggest anything about this behaviour, nor have I found a way in EGL to force the use of my parameter, or clear the list of registered statements. And I bet that if I create another function in the same library with a complete different prepare statement from the same datasource, I will get always the same select...

Any idea? (except defining the datasource in each function: it works, because the datasource is created at any function call, but I wouldn't like it as a stable workaround, for code mantaining issues)

Gianluca
Re: How does sql prepare statement really work? [message #885014 is a reply to message #882972] Tue, 12 June 2012 05:16 Go to previous messageGo to next message
Gianluca Donati is currently offline Gianluca Donati
Messages: 10
Registered: May 2012
Junior Member
Hi,
could someone from EDT Team explain the choices in the java created code I pointed out and how to deal with them, or have I got to open a bug report?

Gianluca
Re: How does sql prepare statement really work? [message #885831 is a reply to message #885014] Wed, 13 June 2012 13:20 Go to previous messageGo to next message
Joseph Vincens is currently offline Joseph Vincens
Messages: 31
Registered: December 2011
Location: Prospect CT
Member
Hi Gianluca,

Sorry for the delay, but I needed to research the cause.
The key used in caching the statement is not correct. The key to the cache should be based on the SQL string and the options on the Prepared declaration. Please open a bugzilla defect for this and please reference this forum post in the defect.


regards,
Joe
Re: How does sql prepare statement really work? [message #886079 is a reply to message #885831] Thu, 14 June 2012 04:00 Go to previous messageGo to next message
Gianluca Donati is currently offline Gianluca Donati
Messages: 10
Registered: May 2012
Junior Member
Hi Joe,

thanks for your reply and helpfulness, now I see what's the matter, I'm going to open a defect.
One question, if you have time, to get me into the difficulties of developing a real tool used by many people: is the act of preparing a sql statement so resource consuming to be worth the effort of registering it and then reclaiming it instead of doing it straight?

Regards,
Gianluca
Re: How does sql prepare statement really work? [message #886229 is a reply to message #886079] Thu, 14 June 2012 09:35 Go to previous messageGo to next message
Matt Heitz is currently offline Matt Heitz
Messages: 36
Registered: July 2009
Member
Hi Gianluca,

Yes, reusing a prepared statement can be much better than making a new statement. Your results will vary depending on the database and the SQL statement, but it's a good practice.

The database must do a lot of work when you make a new statement. First, the SQL string is parsed. Then references to tables, columns, etc. must be verified. The types of the columns and literals must be checked for compatibility. The database must verify that the user has permission to access and/or modify everything in the statement. And the database must compute the access path to the data, and determine how to return it.

When you make a prepared statement, most of that work is done only once. When you reuse the statement, the work doesn't need to be done again. In addition, some of the work that must be done every time can be done by the JDBC driver, which may eliminate extra communication with the database and reduce the load on the database server. Reusing the prepared statement might also mean that fewer objects are created, so there's less work for the JVM's garbage collector.

-Matt
Re: How does sql prepare statement really work? [message #886262 is a reply to message #886229] Thu, 14 June 2012 11:15 Go to previous message
Gianluca Donati is currently offline Gianluca Donati
Messages: 10
Registered: May 2012
Junior Member
Thank you Matt, I'm a business developer but I'm sometimes intrigued by details of middleware development, just to learn writing better (careful) code.

Gianluca
Previous Topic:Enyone can help me
Next Topic:How to deploy batch program
Goto Forum:
  


Current Time: Thu Aug 28 11:26:48 EDT 2014

Powered by FUDForum. Page generated in 0.05037 seconds