473,327 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Re: bind variables with jdbc

il**@bigpond.net.au (Ivan) wrote in message news:<90**************************@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
Jun 27 '08 #1
2 5238

"Dave" <da**********@yahoo.comwrote in message
news:5e**************************@posting.google.c om...
il**@bigpond.net.au (Ivan) wrote in message
news:<90**************************@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
Jun 27 '08 #2
thanks everybody for all the help, the syntax was really helpful. I
didn't really mind changing all the code but i just wanted to get it
right this time.
Jun 27 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Jack | last post by:
Hi All, What is the PHP equivilent of Oracle bind variables in a SQL statement, e.g. select x from y where z=:parameter Which in asp/jsp would be followed by some statements to bind a value...
1
by: Achille Carette | last post by:
Hello all, I noticed a difference in the explain plans between JDBC using bind variables (PreparedStatement) and SQLPlus for the same query. The query made through JDBC using bind variables...
1
by: Daniel Roy | last post by:
Hi gurus, I just started to look at a very slow-running SQL statement generated by an application (Siebel). I spooled the SQL from the application, replaced the bind variables by their values, and...
0
by: ClimberBear | last post by:
Hi, I've got a very strange problem with a Websphere 5.1 cluster attached to DB2 database in Mainframe z/OS. I have a J2EE deployed application running normally fine agains the DB2 host. But,...
0
by: lnd | last post by:
A few question regarding PostgreSQL handling of queries: - Is each query submitted parsed and planned even if it is identical to a query submitted before? For example, 10 queries "select * from...
1
by: femski | last post by:
Folks ! How do you pass an argument to "date" or for that mater any function in JDBC for DB2 ? I am migrating an application from Oracle to DB2 (type IV driver) and I can't seem to be able to...
2
by: lunas | last post by:
hi i am trying to update a table selecting a value from another table and ve written the following codes for it.. import java.sql.*; import java.io.*; import java.util.*; public class...
3
by: skaushik | last post by:
Hi all, I ran an explain plan for a sql query which has some bind variables used in it. The plan shows that index is being used but the execution takes a very long time. Also, in another query the...
1
by: rshome | last post by:
A compromise that works well is to get the DBA to change the CURSOR_SHARING instance parameter from EXACT to FORCE. This pretty much simulates the effects of using bind variables without having to...
2
by: uwcssa | last post by:
I have a third-party Java application running queries with parameter markers (through PreaparedStatement). I wish the server optimize all queries from scratch as there is oppotunity to match...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.