• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

does OracleDriver really precompile a PreparedStatement?

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("URL","USER","PASS");
PreparedStatement statement = conn.prepareStatement(" Selec * from temp ");
SQLWarning warning = statement.getWarnings();
System.out.println("warning : "+warning);
ResultSet set = statement.executeQuery();

I am using the above code to use a PreparedStatement. Here we can see that the Query has the syntax error. But the code throws the exception after i execute the statement. I have idea about the PreparedStatement that it precompiles the SQL query string passed, if it does then it should throw the exception at the time when we call prepareStatement method from connection. Or this precompilation depends upon the driver?

Abhishek
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The driver does not compile any sql. The database does.
Your query will get compiled at first use.

The advantage of PreparedStatement is, that your database knows you're using the same query the second time, even when you use different values for your parameters.
If you use a Statement, Oracle will compile the query again if you change the where clause values.

Example:

preparedstatement
query select cust_name from cust where cust_id = ?
bind parameter cust_id with value 6
execute query -> Oracle compiles the statement
bind parameter cust_id with value 8
execute query -> Oracle can reuse the statement because it knows it's the same as the previous one.


non-prepared statement
query select cust_name from cust where cust_id = 6
execute query -> Oracle compiles the statement
query select cust_name from cust where cust_id = 8
execute query -> Oracle compiles the statement

Regards, Jan
 
Abhishek Bharti
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks a lot Jan,

i still have a confusion why no exception was thrown at this line

PreparedStatement statement = conn.prepareStatement(" Selec * from temp ");

as query string has error it should throw some exception, or the query string is compiled at once only a PreparedStatement is executed for the first time....

Thanks,

Abhishek
[ April 27, 2007: Message edited by: Abhishek ]
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Your last remark is correct.
prepareStatement() does not validate your sql. It's not passed to the database at that time. (note: the driver does not validate your sql. The database does)

The database will receive, validate, compile and execute your query in your line ResultSet set = statement.executeQuery();.

Regards, Jan
 
Abhishek Bharti
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank Jan,

I really got the idea that even in case of prepared statements everything happens at runtime , only the compilation takes place for one time , but in simple statements validation, compilation and execution takes place each time we execute the statement.


How can we deal with the dynamic queries in case of prepared statements as the query string will be compiled only once.


Thanks,

Abhishek
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

How can we deal with the dynamic queries in case of prepared statements as the query string will be compiled only once.

If pass a new sql statement to your PreparedStatement (example: statement = conn.prepareStatement(" Select * from temp where temp_name is not null ");), Oracle will not find a match in it's cached sql statements, and will compile the new one.

(small asside: Oracle pools a number of sql statements. If the query you pass to Oracle exactly matches one of the pooled statement, Oracle can use the pooled statement. See ALL ABOUT SHARED POOL by Tapas Kumar).

Regards, Jan
 
Abhishek Bharti
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks a lot Jan,

I will study about the connection caching and pooling as i do not have much idea about it.


Thanks again,

Abhishek
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"Abhishek",
Welcome to the JavaRanch.

We're a friendly group, but we do require members to have valid display names.

Display names must be two words: your first name, a space, then your last name. Fictitious names are not allowed.

Please edit your profile and correct your display name since accounts with invalid display names get deleted, often without warning

thanks,
Dave
 
reply
    Bookmark Topic Watch Topic
  • New Topic