Ejemplo n.º 1
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
Ejemplo n.º 2
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);
    }
  }
  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();
    }
  }