Re: bind variables from java code
From: Jim Kennedy <kennedy-down_with_spammers_at_attbi.com>
Date: Mon, 26 May 2003 12:08:51 GMT
Message-ID: <ntnAa.1024828$S_4.1036097_at_rwcrnsc53>
Date: Mon, 26 May 2003 12:08:51 GMT
Message-ID: <ntnAa.1024828$S_4.1036097_at_rwcrnsc53>
Do you mean this procedure gets called once and never ever again? Sounds unlikely. By once I mean once since the database was built and never again. Rauf is correct if you can have it parsed once and from then on just execute it it will be much more scalable and perform better. Jim
-- Replace part of the email address: kennedy-down_with_spammers_at_attbi.com with family. Remove the negative part, keep the minus sign. You can figure it out. "hayt" <hayt_at_inwind.it> wrote in message news:11f54ca0.0305260322.58a9dfaf_at_posting.google.com...Received on Mon May 26 2003 - 14:08:51 CEST
> rs_arwar_at_hotmail.com (Rauf Sarwar) wrote in message
news:<92eeeff0.0305242134.51d95bca_at_posting.google.com>...
> >
> > Second approach is not only syntactically wrong BUT it must be
> > compiled everytime it is called... because first two variables are not
> > bound.
> >
> > "{ CALL SC_FILL_PRESENCE(v_udptype, v_ip, ?, ?) }";
> > This syntax is incorrect. If the idea is to build a string by
> > embedding variables, then this will not work in any modern programming
> > language. You would have to concatenate the variables e.g.
> > "{ CALL SC_FILL_PRESENCE(" + v_udptype + "," + v_ip + ", ?, ?) }";
> >
> > Regards
> > /Rauf Sarwar
>
> 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 ;-)