XPost: comp.lang.java.databases   
   From: kennedy-downwithspammersfamily@attbi.net   
      
   "Dave"  wrote in message   
   news:5e092a4e.0404010857.272068f5@posting.google.com...   
   > ilee@bigpond.net.au (Ivan) wrote in message   
   news:<90137e1b.0403301523.55aed707@posting.google.com>...   
   > > Hi all,   
   > >   
   > > 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   
   >   
   > Ivan -   
   >   
   > 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   
      
   Yes, it is.  If you can leave it open and just change the bind variables and   
   reexecute you will scale even more and eliminate a soft parse.  But the way   
   Dave has it is correct.   
   Jim   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)