/**
   * 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);
  }
Example #2
0
 /**
  * ************************************************************************ 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
Example #3
0
  /**
   * 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().
  }
Example #5
0
  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.");
    }
  }
Example #8
0
  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();
    }
  }