Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Bind variables

Posted on 2004-11-03
14
Medium Priority
?
899 Views
Last Modified: 2008-01-09
How do i change the following to use bind variables? I need sql to change the following to use bind variables.



Here are the two most prominent statements showing in Shared Pool with No Bind Variables:  

--Stmt                                                           COUNT(*)        Mem       Open       EXEC
-------------------------------------------------------------- ---------- ---------- ---------- ----------
--insert into ABC_data ( price, message,        6504   66900419          0       6504
--insert into ABC_iypattern ( type, message, sequ       4911   51056835          0       4911

many thanks.

0
Comment
Question by:crishna1
  • 7
  • 6
14 Comments
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12484714
How are you executing the SQL on the database?

if PL/SQL:

INSERT INTO abc_data( price, message ) VALUES( :price, :message )
USING my_price, my_message;

If Java:

PreparedStatement p = connection.prepareStatement( "INSERT INTO abc_data( price, message ) VALUES( ?, ? )" );
p.setLong( 1, myPrice );
p.setLong( 2, myMessage );

There is a feature of Oracle 9i+ that allows Oracle to automatically create compiled SQL with system generated bind variables (I believe its called "CURSOR SHARING"... This is definitely worth a look...)
0
 

Author Comment

by:crishna1
ID: 12484809

Dumb question.....is itthe same for Java J2SE application?
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12484859
yup, my Java code above is not J2EE specific, standard J2SE java.sql package usage.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 11

Expert Comment

by:cjjclifford
ID: 12484880
have a look at the JavaDoc for java.sql.Connection.prepareStatement and java.sql.PreparedStatement - to execute the statement after the "set" methods use preparedStatement.executeUpdate() since this is updating the database (if you were using a SELECT with bind variables you'd use executeQuery().)

Cheers,
C.
0
 

Author Comment

by:crishna1
ID: 12485143
Hi thank you very much for your immediate responce.

Here is the deal , we are using the following in the code and the values for the insert statement are taken from XML.

The request is to change the following to use bind variables as it is taking too much shared pool right now.

many thanks.

----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------


//sequence++;
            insertstr =
                "insert into aci_importenergypattern ( daytype, message, sequence, ";
            insertstr = insertstr +
                "tarifftype, startdate, enddate, batch ) values ( '";
            insertstr = insertstr + daytype + "', " + message + ", " + sequence +
                ", '" + tarifftype + "', ";
            insertstr = insertstr + "to_date('" + df.format(tempstartdate) +
                "', 'MM/dd/yyyy HH24:MI:SS'), ";
            insertstr = insertstr + "to_date('" + df.format(tempenddate) +
                "', 'MM/dd/yyyy HH24:MI:SS'), " + batch + ")";
            cat.debug("(Job-" + jobId + ") " + insertstr);
            try {
                stmt.execute(insertstr);
            }
            catch (Exception e) {
                setError(e.toString());
                cat.error("(Job-" + jobId + ") " + e.toString());
                throw e;
0
 

Author Comment

by:crishna1
ID: 12485152
what do i need to do to replace the above to use bind variables?
0
 
LVL 11

Accepted Solution

by:
cjjclifford earned 2000 total points
ID: 12485238
change your insertstr to:

insertstr = "insert into aci_importenergypattern( daytype, message, sequence, tarifftype, startdate, enddate, batch ) " +
                "values( ?, ?, ?, ?, ?, ?, ? )";
PreparedStatement pstmt = connection.prepareStatement( insertstr );
// I've made some assumptions on the datatypes of the various values...
pstmt.setLong( 1, daytype );
pstmt.setString( 2, message );
pstmt.setLong( 3, seqence );
pstmt.setLong( 4, tarifftype );
pstmt.setTimestamp( 5, new java.sql.Timestamp( startdate.getTime() ) );
pstmt.setTimestamp( 6, new java.sql.Timestamp( enddate.getTime() ) );
pstmt.setString( 7, batch );

pstmt.executeUpdate( insertstr );


Note, I've removed error checking, etc...
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12485257
again, it might be worth your time having a look at SHARED_CURSOR feature (assuming Oracle 9i+), but since you're in a position to change the application, change the application....
0
 

Author Comment

by:crishna1
ID: 12485289
looks like in the above insert statement i will have to supply the values, if you look at the code supplied there are no values?

sorry i am not very familiar how this stuff works.

thanks.
0
 

Author Comment

by:crishna1
ID: 12485304
also i am looking at the following example, doest it make any sense ty you,

http://forum.java.sun.com/thread.jsp?thread=561026&forum=48&message=2758314
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12485843
" if you look at the code supplied there are no values" - yes there are values, the insertstr is build from the values in question

e.g. the following line:

 insertstr = insertstr + daytype + "', " + message + ", " + sequence +

has variables "daytype", "message" and "sequence", these are what are used in the set..() methods of the Prepared Statement (see my code above).

The code you posted has the following variables (or at least it is using the following variables to build the string, so if the code compiles, these variables exist!)

daytype
message
sequence
tarifftype
tempstartdate
tempenddate
batch

the url you posted is an example of what would happen if the set() methods were not called...

0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12485866
the only thing I couldn't figure out from your code snippet was the types of these variables (some are obviously dates, some are obviously strings (actually, it looks like tarifftype is a String), others are probably numeric...

0
 
LVL 22

Expert Comment

by:earth man2
ID: 12485961
Yes it makes sense to me.
0
 

Author Comment

by:crishna1
ID: 12486229
Thank you very much for your help!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question