Examples of java.sql.CallableStatement

java.sql.CallableStatement
The interface used to execute SQL stored procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs. This escape syntax has one form that includes a result parameter and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters can be used for input, output or both. Parameters are referred to sequentially, by number, with the first parameter being 1.
```
 {?= call <procedure-name>[(<arg1>,<arg2>, ...)]} {call <procedure-name>[(<arg1>,<arg2>, ...)]} 
```
IN parameter values are set using the set methods inherited from {@link PreparedStatement}. The type of all OUT parameters must be registered prior to executing the stored procedure; their values are retrieved after execution via the get methods provided here.
A CallableStatement can return one {@link ResultSet} object ormultiple ResultSet objects. Multiple ResultSet objects are handled using operations inherited from {@link Statement}.
For maximum portability, a call's ResultSet objects and update counts should be processed prior to getting the values of output parameters. @see Connection#prepareCall @see ResultSet

   * @throws SQLException
   */
  public static boolean isTrial (JDCConnection oConn, String sAccId) throws SQLException {
    Statement oStmt;
    ResultSet oRSet;
    CallableStatement oCall;
    short iTrial;


    if (oConn.getDataBaseProduct()==JDCConnection.DBMS_POSTGRESQL) {
      oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
      oRSet = oStmt.executeQuery("SELECT k_get_account_trial ('" + sAccId + "')");
      oRSet.next();
      iTrial = oRSet.getShort(1);
      oRSet.close();
      oStmt.close();
    }
    else {
      oCall = oConn.prepareCall("{ call k_get_account_trial (?,?) }");
      oCall.setString(1, sAccId);
      oCall.registerOutParameter(2, Types.SMALLINT);
      oCall.execute();
      iTrial = oCall.getShort(2);
      oCall.close();
    }


    return ((short)0!=iTrial);
  } // isTrial

View Full Code Here

    else {


      if (DebugFile.trace)
        DebugFile.writeln ("Conenction.prepareCall({call k_sp_del_company ('" + sCompanyGUID + "')}");


      CallableStatement oCall = oConn.prepareCall("{call k_sp_del_company ('" + sCompanyGUID + "')}");
      bRetVal = oCall.execute();
      oCall.close();
    }


    if (DebugFile.trace) {
      DebugFile.decIdent();
      DebugFile.writeln("End Company.delete() : " + String.valueOf(bRetVal));

View Full Code Here

    oRSet.close();      
      oStmt.close();
    }
    else if (oConn.getDataBaseProduct()==JDCConnection.DBMS_ORACLE) {
      if (DebugFile.trace) DebugFile.writeln("{call K_SP_WRITE_INET_ADDR (?,?,?,?,?,?,?)}");
      CallableStatement oCall = oConn.prepareCall("{call K_SP_WRITE_INET_ADDR (?,?,?,?,?,?,?)}");
      oCall.setBigDecimal(1 , new java.math.BigDecimal(iDomainId));
      oCall.setString(2, sWorkAreaId);
      oCall.setString(3, sGuMimeMsg);
      oCall.setString(4, sIdMimeMsg);
      if (sTpRecipient!=null)
        oCall.setString(5, sTpRecipient);
      else
        oCall.setNull(5, Types.VARCHAR);
      oCall.setString(6, sTxEMail);
      if (sTxPersonal!=null)
        oCall.setString(7, sTxPersonal);
      else
        oCall.setNull(7, Types.VARCHAR);
      if (DebugFile.trace) DebugFile.writeln("CallableStatement.execute()");
      oCall.execute();
      oCall.close();
    }
    else {
      if (DebugFile.trace) DebugFile.writeln("{call k_sp_write_inet_addr (?,?,?,?,?,?,?)}");
      CallableStatement oCall = oConn.prepareCall("{call k_sp_write_inet_addr (?,?,?,?,?,?,?)}");
      oCall.setInt   (1, iDomainId);
      oCall.setString(2, sWorkAreaId);
      oCall.setString(3, sGuMimeMsg);
      oCall.setString(4, sIdMimeMsg);
      oCall.setString(5, sTpRecipient);
      oCall.setString(6, sTxEMail);
      if (sTxPersonal!=null)
        oCall.setString(7, sTxPersonal);
      else
        oCall.setNull(7, Types.VARCHAR);
      if (DebugFile.trace) DebugFile.writeln("CallableStatement.execute()");
      oCall.execute();
      oCall.close();
    }
    if (DebugFile.trace) {
      DebugFile.decIdent();
      DebugFile.writeln("End DBInetAddr.write()");
    }

View Full Code Here

   */


  public int call (JDCConnection oConn, int iSkip)
      throws SQLException, IllegalArgumentException, ArrayIndexOutOfBoundsException
  {
    CallableStatement oStmt;
    ResultSet oRSet;
    ResultSetMetaData oMDat;
    int iRows = 0;
    int iType = (iSkip==0 ? ResultSet.TYPE_FORWARD_ONLY : ResultSet.TYPE_SCROLL_INSENSITIVE);


    if (DebugFile.trace)
      {
      DebugFile.writeln("Begin DBSubset.call([Connection]," + iSkip + ")");
      DebugFile.incIdent();
      }


    // Begin SQLException


      if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({call " + sTable + "()}");
      oStmt = oConn.prepareCall("{call " + sTable + "()}", iType, ResultSet.CONCUR_READ_ONLY);


      if (DebugFile.trace) DebugFile.writeln("Connection.executeQuery(" + sTable + ")");


      oRSet = oStmt.executeQuery();


      oMDat = oRSet.getMetaData();
      iColCount = oMDat.getColumnCount();
      ColNames = new String[iColCount];


      for (int c=1; c<=iColCount; c++) {
        ColNames[c-1] = oMDat.getColumnName(c).toLowerCase();
      }
      oMDat = null;


      setFetchSize(oConn, oRSet);


      iRows = fetchResultSet(oRSet,iSkip);


      oRSet.close();
      oRSet = null;


      oStmt.close();
      oStmt = null;


    // End SQLException


    if (DebugFile.trace)

View Full Code Here

   */


  public int call (JDCConnection oConn, Object[] aFilterValues, int iSkip)
    throws SQLException, IllegalArgumentException, ArrayIndexOutOfBoundsException
    {
    CallableStatement oStmt;
    ResultSet oRSet;
    ResultSetMetaData oMDat;


    int iRows = 0;
    int iType = (iSkip==0 ? ResultSet.TYPE_FORWARD_ONLY : ResultSet.TYPE_SCROLL_INSENSITIVE);


    if (DebugFile.trace)
      {
      DebugFile.writeln("Begin DBSubset.call([Connection:"+oConn.pid()+"], Object[]," + iSkip + ")");
      DebugFile.incIdent();
      }


    // Begin SQLException
      if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({call " + sTable + "()}");
      oStmt = oConn.prepareCall("{call " + sTable + "()}", iType, ResultSet.CONCUR_READ_ONLY);


      for (int p=0; p<aFilterValues.length; p++)
        oStmt.setObject(p+1, aFilterValues[p]);


      if (DebugFile.trace) DebugFile.writeln("Connection.executeQuery()");


      oRSet = oStmt.executeQuery();


      oMDat = oRSet.getMetaData();
      iColCount = oMDat.getColumnCount();
      ColNames = new String[iColCount];


      for (int c=1; c<=iColCount; c++) {
        ColNames[c-1] = oMDat.getColumnName(c).toLowerCase();
      }
      oMDat = null;


      setFetchSize(oConn, oRSet);


      iRows = fetchResultSet(oRSet, iSkip);


      oRSet.close();
      oRSet = null;


      oStmt.close();
      oStmt = null;


    // End SQLException


    if (DebugFile.trace)

View Full Code Here

      PreparedStatement oStmt = oConn.prepareStatement("SELECT k_sp_del_supplier(?)");
      oStmt.setString(1, sGuSupplier);
      oStmt.executeQuery();
      oStmt.close();
    } else {
      CallableStatement oCall = oConn.prepareCall("{ call k_sp_del_supplier(?) }");
      oCall.setString(1, sGuSupplier);
      oCall.execute();
      oCall.close();
    }
    return true;
  } // delete

View Full Code Here

   */


  public static boolean delete(JDCConnection oConn, String sMeetingGUID) throws SQLException {
    boolean bRetVal;


    CallableStatement oCall = oConn.prepareCall("{call k_sp_del_meeting ('" + sMeetingGUID + "')}");
    bRetVal = oCall.execute();
    oCall.close();


    return bRetVal;
  } // delete

View Full Code Here

                        String.valueOf(iBatchType) + ")");
      DebugFile.incIdent();
    }


    int iStatements;
    CallableStatement oCall = null;
    Statement oStmt = null;
    String sSQL = null;
    String aStatements[];


    iErrors = 0;


    if (sResourcePath.endsWith(".ddl") || sResourcePath.endsWith(".DDL")) {
      aStatements = split(oBuffer, ';', "GO");
    }
    else {
      aStatements = split(oBuffer, ';', null);
    }


    iStatements = aStatements.length;


      switch (iBatchType) {
        case BULK_PROCEDURES:
          for (int s = 0; s < iStatements; s++) {
            sSQL = aStatements[s];
            if (sSQL.length() > 0) {
              if (null!=oStrLog) oStrLog.append(sSQL + "\n");
              try {
                oCall = oConn.prepareCall(sSQL);
                oCall.execute();
                oCall.close();
                oCall = null;
              }
              catch (SQLException sqle) {
                iErrors++;
                if (null!=oStrLog) oStrLog.append("SQLException: " + sqle.getMessage() + "\n");
                try { if (null!=oCall) oCall.close(); } catch (SQLException ignore) { }
                if (bStopOnError) throw new java.lang.InterruptedException();
              }
            } // fi (sSQL)
          } // next
          break;

View Full Code Here

  */
  public void recompileOrcl () throws SQLException {


    String sqlgencmd;
    Statement oStmt;
    CallableStatement oCall;
    ResultSet oRSet;
    String sAlterSql = "";


    if (DebugFile.trace) {
      DebugFile.writeln("Begin ModelManager.recompileOrcl()");
      DebugFile.incIdent();
    }


    // This SQL query produces the alter statements for recompile the objects which status is 'INVALID'
    sqlgencmd = " SELECT 'ALTER ' || DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' || ";
    sqlgencmd += "object_name || ' COMPILE' || DECODE(object_type, 'PACKAGE BODY', ' BODY', '') ";
    sqlgencmd += " cmd ";
    sqlgencmd += "FROM USER_OBJECTS ";
    sqlgencmd += "WHERE status = 'INVALID' AND ";
    sqlgencmd += "object_type IN ('TRIGGER','PACKAGE','PACKAGEBODY','VIEW','PROCEDURE','FUNCTION') AND ";
    sqlgencmd += "(object_type <> 'PACKAGE BODY' OR ";
    sqlgencmd += " (object_name) NOT IN ";
    sqlgencmd += "               (SELECT object_name ";
    sqlgencmd += "                FROM USER_OBJECTS ";
    sqlgencmd += "                WHERE object_type = 'PACKAGE' AND status = 'INVALID'))";


    if (null!=oStrLog) oStrLog.append(sqlgencmd+"\n");


    oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    oRSet = oStmt.executeQuery(sqlgencmd);


    while (oRSet.next()) {


      try {
           sAlterSql = oRSet.getString(1);
           oCall = oConn.prepareCall(sAlterSql);
           oCall.execute();
           oCall.close();


           if (null!=oStrLog) oStrLog.append(sAlterSql+"\n");
      }
      catch (SQLException sqle) {

View Full Code Here

   * @return <b>null</b>
   * @throws MessagingException
   */
  public Message[] expunge() throws MessagingException {
    Statement oStmt = null;
    CallableStatement oCall = null;
    PreparedStatement oUpdt = null;
    PreparedStatement oPart = null;
    PreparedStatement oAddr = null;
    ResultSet oRSet;
    String sSQL;


    if (DebugFile.trace) {
      DebugFile.writeln("Begin DBFolder.expunge()");
      DebugFile.incIdent();
    }


    // *************************************************************************
    // If Folder is not opened is read-write mode then raise an exception
    if (0==(iOpenMode&READ_WRITE)) {
      if (DebugFile.trace) DebugFile.decIdent();
      throw new javax.mail.FolderClosedException(this, "Folder is not open is READ_WRITE mode");
    }


    if ((0==(iOpenMode&MODE_MBOX)) && (0==(iOpenMode&MODE_BLOB))) {
      if (DebugFile.trace) DebugFile.decIdent();
      throw new javax.mail.FolderClosedException(this, "Folder is not open in MBOX nor BLOB mode");
    }


    // ***********************************************
    // First delete the empty messages
    // (used for clearing drafts that have no contents)


  DBSubset oEmptyDrafts = new DBSubset (DB.k_mime_msgs+" m",
                      "m."+DB.gu_mimemsg,
                      "m."+DB.gu_category+"=? AND m."+DB.gu_workarea+"=? AND " +
                                          "m." + DB.bo_deleted + "<>1 AND m." + DB.gu_parent_msg + " IS NULL AND " +
                                          DBBind.Functions.LENGTH+"("+DBBind.Functions.ISNULL+"(m."+DB.tx_subject+",''))=0 AND " +
                                          "m."+DB.len_mimemsg + "=0 AND NOT EXISTS (SELECT p." + DB.gu_mimemsg + " FROM " +
                                          DB.k_mime_parts + " p WHERE m." + DB.gu_mimemsg + "=p." + DB.gu_mimemsg + ")" , 10);
    int iEmptyDrafts = 0;
    
    JDCConnection oConn = null;
    try {
      oConn = getConnection();
      iEmptyDrafts = oEmptyDrafts.load(oConn, new Object[]{getCategoryGuid(),((DBStore)getStore()).getUser().getString(DB.gu_workarea)});
    } catch (SQLException sqle) {
    throw new MessagingException(sqle.getMessage(), sqle);
    }
    
    if (iEmptyDrafts>0) {
    sSQL = "UPDATE " + DB.k_mime_msgs + " SET " + DB.bo_deleted + "=1 WHERE " + DB.gu_mimemsg + "=?";
    
      if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement("+sSQL+")");
    
      try {
        oUpdt = oConn.prepareStatement(sSQL);
        for (int d=0; d<iEmptyDrafts; d++) {
          oUpdt.setString(1,oEmptyDrafts.getString(0,d));
        oUpdt.executeUpdate();
        } // next
      oUpdt.close();
      } catch (SQLException sqle) {
      throw new MessagingException(sqle.getMessage(), sqle);
      }
    } // fi


    // ***********************************************
    // Get the list of deleted and not purged messages


    MboxFile oMBox = null;
    DBSubset oDeleted = new DBSubset(DB.k_mime_msgs,
                                     DB.gu_mimemsg+","+DB.pg_message,
                                     DB.bo_deleted+"=1 AND "+DB.gu_category+"='"+oCatg.getString(DB.gu_category)+"'", 100);


    try {
      int iDeleted = oDeleted.load(getConnection());


      File oFile = getFile();


      // *************************************
      // Purge deleted messages from MBOX file


      if (oFile.exists() && iDeleted>0) {
        oMBox = new MboxFile(oFile, MboxFile.READ_WRITE);
        int[] msgnums = new int[iDeleted];
        for (int m=0; m<iDeleted; m++)
          msgnums[m] = oDeleted.getInt(1, m);
        oMBox.purge(msgnums);
        oMBox.close();
      }


      // *********************************************************
      // Remove from disk the files referenced by deleted messages


      oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
      
      if (DebugFile.trace) DebugFile.writeln("Connection.executeQuery(SELECT p." + DB.file_name + " FROM " + DB.k_mime_parts + " p," + DB.k_mime_msgs + " m WHERE p." + DB.gu_mimemsg + "=m."+ DB.gu_mimemsg + " AND m." + DB.id_disposition + "='reference' AND m." + DB.bo_deleted + "=1 AND m." + DB.gu_category +"='"+oCatg.getString(DB.gu_category)+"')");


      oRSet = oStmt.executeQuery("SELECT p." + DB.id_part + ",p." + DB.id_type + ",p." + DB.file_name + " FROM " + DB.k_mime_parts + " p," + DB.k_mime_msgs + " m WHERE p." +
                                 DB.gu_mimemsg + "=m."+ DB.gu_mimemsg + " AND m." + DB.id_disposition + "='reference' AND m." +
                                 DB.bo_deleted + "=1 AND m." + DB.gu_category +"='"+oCatg.getString(DB.gu_category)+"'");


      while (oRSet.next()) {
        if (DebugFile.trace) DebugFile.writeln("processing part "+String.valueOf(oRSet.getInt(1))+" "+oRSet.getString(2));
        String sFileName = oRSet.getString(3);
        if (!oRSet.wasNull()) {
          if (DebugFile.trace) DebugFile.writeln("trying to delete file "+sFileName);
          try {
            File oRef = new File(sFileName);
            if (oRef.exists())
              oRef.delete();
            else if (DebugFile.trace)
              DebugFile.writeln("file "+sFileName+" not found");            
          }
          catch (SecurityException se) {
            if (DebugFile.trace) DebugFile.writeln("SecurityException deleting file " + sFileName + " " + se.getMessage());
          }
        }
      } // wend


      oRSet.close();
      oRSet = null;
      oStmt.close();
      oStmt = null;


      // ****************************************************
      // Set Category size to length of MBOX file after purge


      oFile = getFile();
      oStmt = oConn.createStatement();
      oStmt.executeUpdate("UPDATE "+DB.k_categories+" SET "+DB.len_size+"="+String.valueOf(oFile.length())+" WHERE "+DB.gu_category+"='"+getCategory().getString(DB.gu_category)+"'");
      oStmt.close();
      oStmt=null;


      // *********************************************
      // Actually delete messages from database tables


      if (oConn.getDataBaseProduct()==JDCConnection.DBMS_POSTGRESQL) {
        oStmt = oConn.createStatement();
        for (int d=0; d<iDeleted; d++)
          oStmt.executeQuery("SELECT k_sp_del_mime_msg('" + oDeleted.getString(0,d) + "')");
        oStmt.close();
        oStmt=null;
      }
      else {
        oCall = oConn.prepareCall("{ call k_sp_del_mime_msg(?) }");


        for (int d=0; d<iDeleted; d++) {
          oCall.setString(1, oDeleted.getString(0,d));
          oCall.execute();
        } // next
        oCall.close();
        oCall=null;
      }


      if (oFile.exists() && iDeleted>0) {


        // ***********************************************************************
        // Temporary move all messages at k_mime_msgs, k_mime_parts & k_inet_addrs
        // beyond its maximum so they do not clash when progressive identifiers
        // are re-assigned


        BigDecimal oUnit = new BigDecimal(1);
        oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        oRSet = oStmt.executeQuery("SELECT MAX("+DB.pg_message+") FROM "+DB.k_mime_msgs+" WHERE "+DB.gu_category+"='"+getCategory().getString(DB.gu_category)+"'");
        oRSet.next();
        BigDecimal oMaxPg = oRSet.getBigDecimal(1);
        if (oRSet.wasNull()) oMaxPg = new BigDecimal(0);
        oRSet.close();
        oRSet = null;
        oStmt.close();
        oStmt = null;
        oMaxPg = oMaxPg.add(oUnit);


        String sCat = getCategory().getString(DB.gu_category);
        oStmt = oConn.createStatement();
        oStmt.executeUpdate("UPDATE "+DB.k_mime_msgs+" SET "+DB.pg_message+"="+DB.pg_message+"+"+oMaxPg.toString()+" WHERE "+DB.gu_category+"='"+sCat+"'");
        oStmt.close();
        oStmt = null;


        // *********************************************************************************
        // Re-assign ordinal position and byte offset for all messages remaining after purge


        DBSubset oMsgSet = new DBSubset(DB.k_mime_msgs, DB.gu_mimemsg+","+DB.pg_message, DB.gu_category+"='"+getCategory().getString(DB.gu_category)+"' ORDER BY "+DB.pg_message, 1000);
        int iMsgCount = oMsgSet.load(oConn);


        oMBox = new MboxFile(oFile, MboxFile.READ_ONLY);
        long[] aPositions = oMBox.getMessagePositions();
        oMBox.close();


        oMaxPg = new BigDecimal(0);
        oUpdt = oConn.prepareStatement("UPDATE "+DB.k_mime_msgs+" SET "+DB.pg_message+"=?,"+DB.nu_position+"=? WHERE "+DB.gu_mimemsg+"=?");
        oPart = oConn.prepareStatement("UPDATE "+DB.k_mime_parts+" SET "+DB.pg_message+"=? WHERE "+DB.gu_mimemsg+"=?");
        oAddr = oConn.prepareStatement("UPDATE "+DB.k_inet_addrs+" SET "+DB.pg_message+"=? WHERE "+DB.gu_mimemsg+"=?");
        for (int m=0; m<iMsgCount; m++) {
          String sGuMsg = oMsgSet.getString(0,m);
          oUpdt.setBigDecimal(1, oMaxPg);
          oUpdt.setBigDecimal(2, new BigDecimal(aPositions[m]));
          oUpdt.setString(3, sGuMsg);
          oUpdt.executeUpdate();
          oPart.setBigDecimal(1, oMaxPg);
          oPart.setString(2, sGuMsg);
          oPart.executeUpdate();
          oAddr.setBigDecimal(1, oMaxPg);
          oAddr.setString(2, sGuMsg);
          oAddr.executeUpdate();
          oMaxPg = oMaxPg.add(oUnit);
        }
        oUpdt.close();
        oPart.close();
        oAddr.close();
      }
      oConn.commit();
    } catch (SQLException sqle) {
      try { if (oMBox!=null) oMBox.close(); } catch (Exception e) {}
      try { if (oStmt!=null) oStmt.close(); } catch (Exception e) {}
      try { if (oCall!=null) oCall.close(); } catch (Exception e) {}
      try { if (oConn!=null) oConn.rollback(); } catch (Exception e) {}
      throw new MessagingException (sqle.getMessage(), sqle);
    }
    catch (IOException sqle) {
      try { if (oMBox!=null) oMBox.close(); } catch (Exception e) {}
      try { if (oStmt!=null) oStmt.close(); } catch (Exception e) {}
      try { if (oCall!=null) oCall.close(); } catch (Exception e) {}
      try { if (oConn!=null) oConn.rollback(); } catch (Exception e) {}
      throw new MessagingException (sqle.getMessage(), sqle);
    }


    if (DebugFile.trace) {

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of java.sql.CallableStatement

bitronix.tm.resource.jdbc.proxy.ConnectionJavaProxy

br.net.woodstock.rockframework.core.jdbc.impl.CommonCallableClient

br.net.woodstock.rockframework.jdbc.impl.ClientHelper

br.net.woodstock.rockframework.jdbc.impl.CommonCallableClient

ch.inftec.ju.db.OracleTest

com.alibaba.druid.bvt.filter.FilterChainImplTest2

com.alibaba.druid.bvt.pool.DruidPooledCallableStatementTest

com.alibaba.druid.filter.FilterChainImpl

com.alibaba.druid.pool.ha.MultiDataSourceConnection

com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.