/**
* ************************************************************************ Start Database Process
*
* @param ProcedureName PL/SQL procedure name
* @return true if success
*/
private boolean startDBProcess(String ProcedureName) {
// execute on this thread/connection
log.fine(ProcedureName + "(" + m_pi.getAD_PInstance_ID() + ")");
String sql = "{call " + ProcedureName + "(?)}";
try {
CallableStatement cstmt = DB.prepareCall(sql, ResultSet.CONCUR_UPDATABLE, null); // ro??
cstmt.setInt(1, m_pi.getAD_PInstance_ID());
cstmt.executeUpdate();
cstmt.close();
} catch (Exception e) {
log.log(Level.SEVERE, sql, e);
m_pi.setSummary(Msg.getMsg(m_wscctx, "ProcessRunError") + " " + e.getLocalizedMessage());
m_pi.setError(true);
return false;
}
// log.fine(Log.l4_Data, "ProcessCtl.startProcess - done");
return true;
} // startDBProcess
public void sampleCode() {
String sproc = "{?=call sp_who}";
String sproc2 = "{? = call sp_callableSample(?,?)}";
/* *CREATE PROCEDURE*
//Query to create the procedure
String createSproc2 =
"create procedure sp_callableSample"+
" (@p1 int, @p2 varchar(255) out)"+
" as"+
" begin"+
" select @p1, @p2"+
" select @p2 = 'The Answer to Life, the Universe, and Everything.'"+
" return 42"+
" end";
String dropSproc2 = "drop proc sp_callableSample";
*/
try {
// Excecute a sproc which has a return status only -sp_who
CallableStatement cstmt = _con.prepareCall(sproc);
cstmt.registerOutParameter(1, Types.INTEGER);
output("Executing: " + sproc);
displayRows(cstmt);
// Display our Return Parameters
output("\n\nReturn Parameters\n");
output("OUT Param 1= " + cstmt.getString(1) + "\n");
cstmt.close();
// Now Excecute a sproc which has IN and INOUT params as
// well as a return stuat
// Note: SQL Server does not have an OUT only param. jConnect
// will pass a NULL silently as an IN param if one is not passed.
/* *CREATE PROCEDURE*
//execute the creation of the stored procedure
execDDL(createSproc2);
*/
cstmt = _con.prepareCall(sproc2);
output("Executing: " + sproc2 + "\n");
// Declare the IN Params. Note, you must skip the Return Status
cstmt.setInt(2, 1961);
cstmt.setString(3, "Hello");
// Now declare our OUT Params
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.registerOutParameter(3, Types.VARCHAR);
displayRows(cstmt);
// Display our Return Parameters
output("\n\nReturn Parameters\n");
output("OUT Param 1= " + cstmt.getString(1) + "\n");
output("OUT Param 3= " + cstmt.getString(3) + "\n");
/* *CREATE PROCEDURE*
// Now delete the sproc
execDDL(dropSproc2);
*/
// close our resources
cstmt.close();
} catch (SQLException ex) {
displaySQLEx(ex);
}
}
public void sampleCode() {
// Variables for simple test case
String procname = "sp_timestampSample";
/* *CREATE PROCEDURE*
String createQuery1 =
"create table spt_timestampSample(f1 int, f2 char(5), f3 timestamp )";
String insertQuery1 =
"insert spt_timestampSample(f1,f2) values(1, 'Hello')";
// Sample Stored Procedure
String dropProc = "drop proc " + procname;
String createProc =
"create proc " + procname +
"(@p1 int, @p2 timestamp out)" +
" as " +
"select 'p1=' + convert(varchar(10),@p1) " +
"select @p2 = f3 from spt_timestampSample where f1=1" +
"select * from spt_timestampSample " +
"return 21";
*/
String sproc = "{? = call " + procname + "(?,?)}";
try {
/* *CREATE PROCEDURE*
// We will create a temp table which contains a timestamp column
// and we will insert a row. We will then execute a stored
// procedure which will returnt the timestamp column as an OUTPUT
// parameter
// Create our table
execDDL( createQuery1);
// Insert our row
execDDL( insertQuery1);
// Now create the Proc
execDDL( createProc);
*/
// Now execute our Sproc
CallableStatement cstmt = _con.prepareCall(sproc);
output("Executing: " + sproc + "\n");
// Declare the IN Params. Note, you must skip the Return Status
cstmt.setInt(2, 1961);
// Now declare our OUT Params
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.registerOutParameter(3, Types.VARBINARY);
boolean results = cstmt.execute();
int rsnum = 0; // Number of Result Sets processed
int rowsAffected = 0;
do {
if (results) {
ResultSet rs = cstmt.getResultSet();
output("\n\nDisplaying ResultSet: " + rsnum + "\n");
dispResultSet(rs);
rsnum++;
rs.close();
} else {
rowsAffected = cstmt.getUpdateCount();
if (rowsAffected >= 0) output(rowsAffected + " rows Affected.\n");
}
results = cstmt.getMoreResults();
} while (results || rowsAffected != -1);
String s = cstmt.getString(1);
String s2 = cstmt.getString(3);
// Now grab the same output parameter as VARBINARY
byte[] ts = cstmt.getBytes(3);
// Display the Output Parameters
output("OUT Param1=" + s + "\n");
output("OUT Param2 as String=" + s2 + "\n");
output("OUT Param2 as byte[]=" + toHexString(ts) + "\n");
cstmt.close();
/* *CREATE PROCEDURE*
// Drop our sproc
execDDL( dropProc);
*/
} catch (SQLException ex) {
displaySQLEx(ex);
} catch (java.lang.Exception ex) {
// Got some other type of exception. Dump it.
ex.printStackTrace();
}
}
