473,224 Members | 1,379 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,224 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 5232

"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...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...

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.