473,320 Members | 1,993 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,320 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 5237

"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: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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...
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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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...
0
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
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.