does OracleDriver really precompile a PreparedStatement?

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

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

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 ]

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

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

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

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

"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