Re: bind variables from java code
Date: 26 May 2003 14:29:08 -0700
Message-ID: <92eeeff0.0305261329.48a6cc59_at_posting.google.com>
hayt_at_inwind.it (hayt) wrote in message news:<11f54ca0.0305260322.58a9dfaf_at_posting.google.com>...
> rs_arwar_at_hotmail.com (Rauf Sarwar) wrote in message news:<92eeeff0.0305242134.51d95bca_at_posting.google.com>...
> >
> Yes, you are right. I've done a mistake with the syntax:
> "{ CALL SC_FILL_PRESENCE(" + v_udptype + "," + v_ip + ", ?, ?) }";
> is the right version, of course. Only my fault typing the message.
> Then you say that concatenating variables inside the "callable string"
> is bad? I don't have to call the stored procedure multiple times (only
> once), and with this behaviour I've seen that the second approach is
> faster.
> I include a simple client to verify this (I've tried with Oracle OCI
> drivers):
>
> import java.sql.*;
> public class DBCheckConnection {
> private static Connection dbconn;
> private static final int LOOPS = 400;
> public static void main (String[] args){
> /*
> args = new String[4];
> args[0]="oracle.jdbc.driver.OracleDriver";
> args[1]="jdbc:oracle:thin:_at_217.169.119.169:1521:ORIOTEST";
> args[2]="R3_PR";
> args[3]="R3_PR";
> */
> try{
> //args[0] = driver
> Class.forName(args[0]);
> //args[1] = url, args[2] = username, args[3] = password
> dbconn = DriverManager.getConnection(args[1], args[2],
> args[3]);
> System.out.println("DB available");
>
> dbconn.setAutoCommit(true);
>
> long startSPTime = System.currentTimeMillis();
> for (int i = 0; i<LOOPS; i++){
> callWithParameters();
> }
> System.out.println("sc_fill_presence with params and
> autoCommit execution time: " + (System.currentTimeMillis() -
> startSPTime) + " ms.");
>
> startSPTime = System.currentTimeMillis();
> for (int i = 0; i<LOOPS; i++){
> callWithoutParameters();
> }
> System.out.println("sc_fill_presence without params and
> autoCommit execution time: " + (System.currentTimeMillis() -
> startSPTime) + " ms.");
>
> dbconn.setAutoCommit(false);
>
> startSPTime = System.currentTimeMillis();
> for (int i = 0; i<LOOPS; i++){
> callWithParameters();
> }
> System.out.println("sc_fill_presence with params execution
> time: " + (System.currentTimeMillis() - startSPTime) + " ms.");
>
> startSPTime = System.currentTimeMillis();
> for (int i = 0; i<LOOPS; i++){
> callWithoutParameters();
> }
> System.out.println("sc_fill_presence without params
> execution time: " + (System.currentTimeMillis() - startSPTime) + "
> ms.");
> dbconn.close();
> }catch (SQLException sqle) {
> System.out.println("DB connection not available");
> sqle.printStackTrace();
> }catch (Exception e) {
> System.out.println("DB connection not available");
> e.printStackTrace();
> }
> }
>
> private static void callWithoutParameters() throws SQLException{
> String spCall = "{ CALL SC_FILL_PRESENCE(?, ?, ?, ?, ?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }";
> // Preparing the stored procedure
> CallableStatement cstmt = dbconn.prepareCall(spCall);
> // Binding input varialbes
> cstmt.setString(1, "0002");
> cstmt.setString(2, "62.211.11.121");
> cstmt.setString(3, "151.99.13.24");
> cstmt.setString(4, "3/0/0");
> cstmt.setString(5, "52");
> cstmt.setString(6, "59");
> cstmt.setLong (7, 1053705650420l);
> cstmt.setString(8, "test");
> cstmt.setString(9, "test");
> //Registering SP out parameters
> cstmt.registerOutParameter(10, java.sql.Types.INTEGER);
> cstmt.registerOutParameter(11, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(12, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(13, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(14, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(15, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(16, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(17, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(18, java.sql.Types.VARCHAR);
> //Executing stored procedure
> cstmt.execute();
> cstmt.close();
> }
>
> private static void callWithParameters() throws SQLException{
> String spCall = "{ CALL SC_FILL_PRESENCE('0002',
> '62.211.11.121', '151.99.13.24', '3/0/0', '52', '59', '1053705650420',
> 'test', 'test', ?, ?, ?, ?, ?, ?, ?, ?, ?) }";
> // Preparing the stored procedure
> CallableStatement cstmt = dbconn.prepareCall(spCall);
> //Registering SP out parameters
> cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
> cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(3, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(6, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(7, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(8, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(9, java.sql.Types.VARCHAR);
> //Executing stored procedure
> cstmt.execute();
> cstmt.close();
> }
> }
>
> Try by yourself ;-)
Change your code as following and then try again,
Take CallableStatement cstmt = dbconn.prepareCall(spCall); and cstmt.close(); out of callWithoutParameters() and put it before and after you call callWithoutParameters() in a loop to execute cstmt. Change callWithoutParameters() to accept CallableStatement as parameter.
startSPTime = System.currentTimeMillis(); String spCall = "{ CALL SC_FILL_PRESENCE(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }"; CallableStatement cstmt = dbconn.prepareCall(spCall); for (int i = 0; i<LOOPS; i++){ callWithoutParameters(cstmt); } cstmt.close();
This way, you will have the driver precompile the statement before you enter the loop to execute it... (compile once, execute 400 times). Try it and note the execution time.
Regards
/Rauf Sarwar
Received on Mon May 26 2003 - 23:29:08 CEST