Hi all,Ivan -
I have an application that is written in servlets that connects to an
oracle database 8.1.7 via jdbc. My DBA has just notify me that I am
not using bind variables in my code and was wondering what i'll need
to change to utilise this.
At the moment i am just opening a connection, making a statement (sql
query) and executing it to return a resultset. Would i need to change
all my queries to stored procedures so that bind variables are used or
is there another way.
thanks all
I little more specific to the java syntax. Right now you are probably
using an implementation of the Statement interface...for instance,
Connection c = new Connection(....
Statement st = c.createStatement();
ResultSet rs = st.executeQuery('select xyz from table1 where a = 2 and
b = 3');
you'll want something more like....
PreparedStatement pt = c.prepareStatement('select xyz from table1
where a = ? and b = ?');
pt.setInt(1, 2);
pt.setInt(2, 3);
ResultSet rs = pt.executeQuery();
Depending on your application, you would either close the prepared
statement after use or leave it open. Probably close it to free up db
resources. But the point is, since you are using bind variables (?),
next time you prepare the same statement in java, the parsed sql
statement will likely still exist in Oracle memory which will allow
you to reuse it. This is much better for scalability.
Dave