/**
* Called when a stored procedure or function is executed and OUT parameters need to be registered
* with the statement.
*
* @param CallableStatement statement
* @param int index the JDBC offset column number
* @param int colType the column as from DatabaseMetaData (eg, procedureColumnOut)
* @param int dataType the JDBC datatype from Types
* @param String dataTypeName the JDBC datatype name
* @throws SQLException
*/
public void registerOut(
CallableStatement statement, int index, int colType, int dataType, String dataTypeName)
throws SQLException {
if (dataType == Types.OTHER) {
if ("REF CURSOR".equals(dataTypeName)) {
statement.registerOutParameter(index, OracleTypes.CURSOR);
return;
} else if ("PL/SQL RECORD".equals(dataTypeName)) {
statement.registerOutParameter(index, OracleTypes.CURSOR);
return;
}
}
super.registerOut(statement, index, colType, dataType, dataTypeName);
}
/**
* ************************************************************************ 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
/**
* Execute the desired Callable statement and then call dispResultSet to display the rows and
* columns
*
* @param stmt CallableStatement object to be processed
* @exception SQLException .
*/
public void displayRows(CallableStatement stmt) throws SQLException {
boolean results = stmt.execute();
int rsnum = 0; // Number of Result Sets processed
int rowsAffected = 0;
do {
if (results) {
ResultSet rs = stmt.getResultSet();
output("\n\nDisplaying ResultSet: " + rsnum + "\n");
dispResultSet(rs);
rsnum++;
rs.close();
} else {
rowsAffected = stmt.getUpdateCount();
if (rowsAffected >= 0) output(rowsAffected + " rows Affected.\n");
}
results = stmt.getMoreResults();
} while (results || rowsAffected != -1);
}
//
// Find all the methods for java.sql objects in the Connection which raise
// SQLFeatureNotSupportedException.
//
private void connectionWorkhorse(
Connection conn, HashSet<String> unsupportedList, HashSet<String> notUnderstoodList)
throws Exception {
vetSavepoint(conn, unsupportedList, notUnderstoodList);
vetLargeObjects(conn, unsupportedList, notUnderstoodList);
DatabaseMetaData dbmd = conn.getMetaData();
PreparedStatement ps = conn.prepareStatement("select * from sys.systables where tablename = ?");
ps.setString(1, "foo");
ParameterMetaData parameterMetaData = ps.getParameterMetaData();
ResultSet rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
Statement stmt = conn.createStatement();
CallableStatement cs = conn.prepareCall("CALL SYSCS_UTIL.SET_RUNTIMESTATISTICS(0)");
ParameterMetaData csmd = cs.getParameterMetaData();
//
// The vetObject() method calls all of the methods in these objects
// in a deterministic order, calling the close() method last.
// Inspect these objects in an order which respects the fact that
// the objects are closed as a result of calling vetObject().
//
vetObject(dbmd, unsupportedList, notUnderstoodList);
vetObject(stmt, unsupportedList, notUnderstoodList);
vetObject(csmd, unsupportedList, notUnderstoodList);
vetObject(cs, unsupportedList, notUnderstoodList);
vetObject(rsmd, unsupportedList, notUnderstoodList);
vetObject(rs, unsupportedList, notUnderstoodList);
vetObject(parameterMetaData, unsupportedList, notUnderstoodList);
vetObject(ps, unsupportedList, notUnderstoodList);
vetObject(conn, unsupportedList, notUnderstoodList);
// No need to close the objects. They were closed by vetObject().
}
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);
}
}
static void task4()
throws FileNotFoundException, IOException, InterruptedException, SQLException {
System.out.println("Task4 Started...");
BufferedReader br = new BufferedReader(new FileReader(inputFile));
br.readLine();
br.readLine();
br.readLine();
br.readLine();
String task4Input = br.readLine();
br.close();
double supportPercent =
Double.parseDouble(
task4Input.split(":")[1].split(",")[0].split("=")[1].split("%")[0].trim());
double confidence =
Double.parseDouble(
task4Input.split(":")[1].split(",")[1].split("=")[1].split("%")[0].trim());
int size = Integer.parseInt(task4Input.split(":")[1].split(",")[2].split("=")[1].trim());
if (supportPercent >= 0 && size >= 2 && confidence >= 0) {
System.out.println("Task4 Confidence Percent: " + confidence);
System.out.println("Task4 Support Percent: " + supportPercent);
System.out.println("Task4 Size : " + size);
BufferedWriter bw = new BufferedWriter(new FileWriter("system.out.4"));
for (int i = 2; i <= size; i++) {
CallableStatement cstmt = con.prepareCall("{CALL GenerateAR(?,?,?)}");
cstmt.setDouble(1, confidence / 100);
cstmt.setInt(2, i);
cstmt.setDouble(3, supportPercent / 100);
cstmt.executeQuery();
String sqlTask4 = "select * from artable order by ruleid,confidence";
PreparedStatement selTask4 = con.prepareStatement(sqlTask4);
ResultSet rsTask4 = selTask4.executeQuery(sqlTask4);
String leftSet = "";
String rightSet = "";
Boolean resultSetExhausted = false;
String itemName;
String isLeft;
Double support;
Double conf;
if (rsTask4.next()) {
while (true && !resultSetExhausted) {
leftSet = "{";
rightSet = "{";
for (int j = 0; j < i; j++) {
itemName = rsTask4.getString(3);
isLeft = rsTask4.getString(5);
// support = rsTask4.getBigDecimal(6);
// conf = rsTask4.getBigDecimal(4);
if (j == 0) {
if (isLeft.equals("Y")) leftSet = leftSet + itemName;
else rightSet = rightSet + itemName;
if (!rsTask4.next()) {
resultSetExhausted = true;
break;
}
}
if (j == i - 1) {
if (isLeft.equals("Y")) {
if (leftSet.equals("{")) leftSet = leftSet + itemName;
else leftSet = leftSet + "," + itemName;
} else {
if (rightSet.equals("{")) rightSet = rightSet + itemName;
else rightSet = rightSet + ", " + itemName;
}
leftSet = leftSet + "}";
rightSet = rightSet + "}";
String resultString =
"{"
+ leftSet
+ " - > "
+ rightSet
+ "} s="
+ rsTask4.getDouble(6)
+ "%, c="
+ rsTask4.getDouble(4)
+ "%";
bw.write(resultString);
bw.newLine();
leftSet = "{";
rightSet = "{";
if (!rsTask4.next()) {
resultSetExhausted = true;
break;
}
}
if (j > 0 && j < i - 1) {
if (isLeft.equals("Y")) {
if (leftSet.equals("{")) leftSet = leftSet + itemName;
else leftSet = leftSet + ", " + itemName;
} else {
if (rightSet.equals("{")) rightSet = rightSet + itemName;
else rightSet = rightSet + ", " + itemName;
}
if (!rsTask4.next()) {
resultSetExhausted = true;
break;
}
}
}
}
}
}
bw.close();
System.out.println("Task4 Completed...\n");
} else
System.out.println(
"For Task4, supportPercent and confidence should be a positive numbers and size should be greater than or equal to 2");
}
static void task3()
throws FileNotFoundException, IOException, InterruptedException, SQLException {
System.out.println("Task3 Started..");
BufferedReader br = new BufferedReader(new FileReader(inputFile));
br.readLine();
br.readLine();
br.readLine();
String task3Input = br.readLine();
br.close();
double supportPercent =
Double.parseDouble(
task3Input.split(":")[1].split(",")[0].split("=")[1].split("%")[0].trim());
int size = Integer.parseInt(task3Input.split(":")[1].split(",")[1].split("=")[1].trim());
if (supportPercent >= 0 && size > 0) {
System.out.println("Task3 Size : " + size);
System.out.println("Task3 Support Percent: " + supportPercent);
BufferedWriter bw = new BufferedWriter(new FileWriter("system.out.3"));
for (int i = 1; i <= size; i++) {
CallableStatement cstmt = con.prepareCall("{CALL GenerateFI(?,?)}");
cstmt.setInt(1, i);
cstmt.setDouble(2, supportPercent / 100);
cstmt.executeQuery();
String sqlTask3 =
"select i.itemname,f.percent from FISet f ,items i where f.itemid = i.itemid order by f.percent,f.isetid";
// String sqlTask3 = "select i.itemname,f.percent from FISet f ,items i where f.itemid =
// i.itemid order by f.isetid";
PreparedStatement selTask3 = con.prepareStatement(sqlTask3);
ResultSet rsTask3 = selTask3.executeQuery(sqlTask3);
int j = 1;
String res = "";
j = 1;
while (rsTask3.next()) {
if (j == 1) {
res += "{";
}
res = res + rsTask3.getString(1);
if (j == i) {
res += "}, s=" + rsTask3.getDouble(2) + "%";
bw.write(res);
bw.newLine();
j = 1;
res = "";
} else {
res += ", ";
j++;
}
}
rsTask3.close();
selTask3.close();
}
bw.close();
System.out.println("Task3 Completed...\n");
} else {
System.out.println(
"Support percent should a postive number and size should be a positive integer.");
}
}
public static void main(String[] argv) throws Exception {
String[] sArray = new String[8];
long time = System.currentTimeMillis();
try {
FileInputStream fstream = new FileInputStream("system.in");
DataInputStream in = new DataInputStream(fstream);
BufferedReader br = new BufferedReader(new InputStreamReader(in));
int i = 0;
while ((sArray[i] = br.readLine()) != null) {
sArray[i] = sArray[i].split("=")[1].trim();
System.out.println(sArray[i]);
i++;
}
in.close();
} catch (Exception e) {
}
System.out.println("-------- Oracle JDBC Connection Testing ------");
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("Where is your Oracle JDBC Driver?");
e.printStackTrace();
return;
}
System.out.println("Oracle JDBC Driver Registered!");
Connection connection = null;
try {
connection =
DriverManager.getConnection(
"jdbc:oracle:thin:@//oracle1.cise.ufl.edu:1521/orcl", sArray[0], sArray[1]);
} catch (SQLException e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return;
}
ResultSet rset = null;
if (connection != null) {
System.out.println(" -|||- ");
Process p =
Runtime.getRuntime()
.exec("sqlplus " + sArray[0] + "@orcl/" + sArray[1] + " @adwords.sql");
p.waitFor();
// CallableStatement cstmt;
System.out.println(" ||| ");
System.out.println(
"Time taken in Milliseconds (establishing connection): "
+ (System.currentTimeMillis() - time));
time = System.currentTimeMillis();
Process proc3 =
Runtime.getRuntime()
.exec(
"sqlldr "
+ sArray[0]
+ "/"
+ sArray[1]
+ "@orcl DATA=Keywords.dat CONTROL=Keywords.ctl LOG=Keywords.log");
proc3.waitFor();
Process proc1 =
Runtime.getRuntime()
.exec(
"sqlldr "
+ sArray[0]
+ "/"
+ sArray[1]
+ "@orcl DATA=Advertisers.dat CONTROL=Advertisers.ctl LOG=Advertiser.log");
proc1.waitFor();
Process proc2 =
Runtime.getRuntime()
.exec(
"sqlldr "
+ sArray[0]
+ "/"
+ sArray[1]
+ "@orcl DATA=Queries.dat CONTROL=Queries.ctl LOG=Queries.log");
proc2.waitFor();
System.out.println(" ||| ");
System.out.println(
"Time taken in Milliseconds (loading dat): " + (System.currentTimeMillis() - time));
time = System.currentTimeMillis();
CallableStatement callableStatement = null;
String storeProc = "{call sq(?,?,?,?,?,?)}";
try {
callableStatement = connection.prepareCall(storeProc);
callableStatement.setInt(1, Integer.parseInt(sArray[2]));
callableStatement.setInt(2, Integer.parseInt(sArray[3]));
callableStatement.setInt(3, Integer.parseInt(sArray[4]));
callableStatement.setInt(4, Integer.parseInt(sArray[5]));
callableStatement.setInt(5, Integer.parseInt(sArray[6]));
callableStatement.setInt(6, Integer.parseInt(sArray[7]));
callableStatement.executeUpdate();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
/*callableStatement = connection.prepareCall("{call sq( " +
Integer.parseInt(sArray[2]) + ", " +
Integer.parseInt(sArray[3]) + ", " +
Integer.parseInt(sArray[4]) + ", " +
Integer.parseInt(sArray[5]) + ", " +
Integer.parseInt(sArray[6]) + ", " +
Integer.parseInt(sArray[7]) + " ) }");*/
System.out.println(" ||| ");
System.out.println(
"Time taken in Milliseconds (sql procedure): " + (System.currentTimeMillis() - time));
time = System.currentTimeMillis();
FileWriter fw = null;
File file = null;
PrintWriter pw = null;
Statement resStmt = null;
ResultSet resRs = null;
int qid = 0;
int rank;
for (int i = 0; i < 6; i++) {
file = new File("system.out." + (i + 1));
try {
if (!file.exists()) file.createNewFile();
else file.delete();
fw = new FileWriter(file.getPath(), true);
} catch (Exception e) {
System.out.println(e.getMessage());
}
pw = new PrintWriter(fw);
resStmt = connection.createStatement();
resRs =
resStmt.executeQuery(
"SELECT * FROM OUTPUT" + (i + 1) + " order by qid asc, adrank asc");
while (resRs.next()) {
qid = resRs.getInt("QID");
rank = (int) resRs.getFloat("ADRANK");
int advertiserId = resRs.getInt("ADVERTISERID");
float balance = resRs.getFloat("BALANCE");
float budget = resRs.getFloat("BUDGET");
StringBuffer resStr = new StringBuffer();
resStr.append(qid + "," + rank + "," + advertiserId + "," + balance + "," + budget);
// System.out.println(resStr.toString());
pw.println(resStr.toString());
}
pw.close();
}
} else {
System.out.println("Failed to make connection!");
}
System.out.println(
"Time taken in Milliseconds (file write): " + (System.currentTimeMillis() - time));
}
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();
}
}
