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();
