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

  public static int nextVal(JDCConnection oConn, String sSequenceName)
      throws SQLException, UnsupportedOperationException {


    Statement oStmt;
    ResultSet oRSet;
    CallableStatement oCall;
    int iNextVal;


    if (DebugFile.trace)
      {
      DebugFile.writeln("Begin hipergate DBBind.nextVal([JDCConnection], " + sSequenceName + ")" );
      DebugFile.incIdent();
      }


    switch (oConn.getDataBaseProduct()) {


      case JDCConnection.DBMS_MYSQL:
      case JDCConnection.DBMS_MSSQL:


        if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({call k_sp_nextval ('" + sSequenceName + "',?)})" );


        oCall = oConn.prepareCall("{call k_sp_nextval (?,?)}");
        oCall.setString(1, sSequenceName);
        oCall.registerOutParameter(2, java.sql.Types.INTEGER);
        oCall.execute();
        iNextVal = oCall.getInt(2);
        oCall.close();
        oCall = null;
        break;


      case JDCConnection.DBMS_POSTGRESQL:
        oStmt = oConn.createStatement();

View Full Code Here

   * @param oConn Database Connection
   * @param sOportunityGUID GUID of Oportunity to be deleted.
   * @throws SQLException
   */
  public static boolean delete(JDCConnection oConn, String sOportunityGUID) throws SQLException {
    CallableStatement oCall;
    Statement oStmt;
    boolean bRetVal;


    if (DebugFile.trace) {
      DebugFile.writeln("Begin Oportunity.delete([Connection], " + sOportunityGUID + ")");
      DebugFile.incIdent();


    }


    if (oConn.getDataBaseProduct()==JDCConnection.DBMS_POSTGRESQL) {
      oStmt = oConn.createStatement();


      if (DebugFile.trace) DebugFile.writeln("Statement.execute(SELECT k_sp_del_oportunity ('" + sOportunityGUID + "'))");


      bRetVal = oStmt.execute("SELECT k_sp_del_oportunity ('" + sOportunityGUID + "')");
      oStmt.close();
    }
    else {
      if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({ call k_sp_del_oportunity('" + sOportunityGUID + "')})");


      oCall = oConn.prepareCall("{ call k_sp_del_oportunity ('" + sOportunityGUID + "')}");
      bRetVal = oCall.execute();
      oCall.close();
    }
    if (DebugFile.trace) {
      DebugFile.decIdent();
      DebugFile.writeln("End Oportunity.delete() : " + String.valueOf(bRetVal));
    }

View Full Code Here

   * @return <b>true</b> if member is blocked (present at associated black list for this list) <b>false</b> otherwise.
   * @throws SQLException
   */
  public boolean isBlocked (JDCConnection oConn) throws SQLException {
    boolean bBlocked;
    CallableStatement oCall;
    PreparedStatement oStmt;
    ResultSet oRSet;
    String sList;
    String sProc;
    String sParm;


    if (DebugFile.trace) {
      DebugFile.writeln("Begin ListMember.isBlocked([Connection])");
      DebugFile.incIdent();
    }


    sList = oMember.getString(DB.gu_list);


    switch (oMember.getShort(DB.tp_member)) {
      case Company.ClassId:
        sProc = "k_sp_company_blocked";
        sParm = oMember.getString(DB.gu_company);
        if (DebugFile.trace) DebugFile.writeln("gu_company=" + sParm);
        break;
      case Contact.ClassId:
        sProc = "k_sp_contact_blocked";
        sParm = oMember.getString(DB.gu_contact);
        if (DebugFile.trace) DebugFile.writeln("gu_contact=" + sParm);
        break;
      default:
        sProc = "k_sp_email_blocked";
        sParm = oMember.getString(DB.tx_email);
        if (DebugFile.trace) DebugFile.writeln("tx_email=" + sParm);
    }


    switch (oConn.getDataBaseProduct()) {


      case JDCConnection.DBMS_POSTGRESQL:
        if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(SELECT " + sProc + "(?,?))");


        oStmt = oConn.prepareStatement("SELECT " + sProc + "(?,?)", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        oStmt.setString(1, sList);
        oStmt.setString(2, sParm);
        oRSet = oStmt.executeQuery();
        oRSet.next();
        bBlocked = (oRSet.getShort(1)!=(short)0);
        oRSet.close();
        oStmt.close();
        break;


      case JDCConnection.DBMS_ORACLE:
        if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({ call " + sProc + "(?,?,?)})");


        oCall = oConn.prepareCall("{ call " + sProc + "(?,?,?)}");
        oCall.setString(1, sList);
        oCall.setString(2, sParm);
        oCall.registerOutParameter(3, java.sql.Types.DECIMAL);
        oCall.execute();
        bBlocked = (oCall.getBigDecimal(3).intValue()!=0);
        oCall.close();
        break;


      default:
        if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({ call " + sProc + "(?,?,?)})");


        oCall = oConn.prepareCall("{ call " + sProc + "(?,?,?)}");
        oCall.setString(1, sList);
        oCall.setString(2, sParm);
        oCall.registerOutParameter(3, java.sql.Types.SMALLINT);
        oCall.execute();
        bBlocked = (oCall.getShort(3)!=(short)0);
        oCall.close();
    }


    if (DebugFile.trace) {
      DebugFile.decIdent();
      DebugFile.writeln("End ListMember.isBlocked() : " + String.valueOf(bBlocked));

View Full Code Here

      oStmt.setString(1, sGuACourse);
      oStmt.executeQuery();
      oStmt.close();
    }
    else {
      CallableStatement oCall = oConn.prepareCall("{ call k_sp_del_subject(?) }");
      oCall.setString(1, sGuACourse);
      oCall.execute();
      oCall.close();
    }
    return true;
  }

View Full Code Here

      DebugFile.writeln("Begin Fellow.delete([Connection]," + sFellowGUID + ")");
      DebugFile.incIdent();
      DebugFile.writeln("Connection.prepareCall({call k_sp_del_fellow ('" + sFellowGUID + "')})");
    }


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


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

View Full Code Here

   * @param sSalesManGUID String GUID of sales man to be deleted
   * @return boolean
   * @throws SQLException
   */
  public static boolean delete(JDCConnection oConn, String sSalesManGUID) throws SQLException {
    CallableStatement oCall;
    Statement oStmt;
    if (DebugFile.trace) {
      DebugFile.writeln("Begin SalesMan.delete([Connection], " + sSalesManGUID + ")");
      DebugFile.incIdent();
      DebugFile.writeln("Connection.prepareCall({ call k_sp_del_sales_man('" + sSalesManGUID + "')}");
    }
    switch (oConn.getDataBaseProduct()) {
      case JDCConnection.DBMS_POSTGRESQL:
        oStmt = oConn.createStatement();
        oStmt.executeQuery("SELECT k_sp_del_sales_man('" + sSalesManGUID + "')").close();
        oStmt.close();
        oStmt=null;
        break;
      default:
        oCall = oConn.prepareCall("{ call k_sp_del_sales_man('" + sSalesManGUID + "')}");
        oCall.execute();
        oCall.close();
        oCall = null;
    }
    if (DebugFile.trace) {
      DebugFile.decIdent();
      DebugFile.writeln("End SalesMan.delete()");

View Full Code Here

    if (oConn.getDataBaseProduct()==JDCConnection.DBMS_POSTGRESQL) {
      Statement oStmt = oConn.createStatement();
      oStmt.executeQuery("SELECT k_sp_del_activity ('"+getString(DB.gu_activity)+"')");
      oStmt.close();
    } else {
      CallableStatement oCall = oConn.prepareCall("{ call k_sp_del_activity ('"+getString(DB.gu_activity)+"') }");
    oCall.execute();
    oCall.close();
    }
    return true;
  } // delete

View Full Code Here

      oStmt.close();
    }
    else {
      if (DebugFile.trace)
        DebugFile.writeln("Connection.prepareCall({ call k_sp_del_course('"+sGuCourse+"') })");
      CallableStatement oCall = oConn.prepareCall("{ call k_sp_del_course(?) }");
      oCall.setString(1, sGuCourse);
      oCall.execute();
      oCall.close();
    }
    if (DebugFile.trace) {
      DebugFile.decIdent();
      DebugFile.writeln("End Course.delete()");
    }

View Full Code Here

   */


  public static short autenticate (JDCConnection oConn, String sUserId, String sAuthStr, int iFlags)
      throws SQLException, UnsupportedOperationException {
    short iStatus;
    CallableStatement oCall;
    PreparedStatement oStmt;
    ResultSet oRSet;
    String sPassword;


    if (DebugFile.trace) {
      DebugFile.writeln("Begin ACL.autenticate([Connection], " + sUserId + "," + sAuthStr + "," + iFlags + ")" );
      DebugFile.incIdent();
    }


    sPassword = decript(sAuthStr, iFlags);


    switch (oConn.getDataBaseProduct()) {


      case JDCConnection.DBMS_ORACLE:


        if (DebugFile.trace) DebugFile.writeln("  Connection.prepareCall({ call k_sp_autenticate (" + sUserId + "," + sPassword + ",?)})");


        oCall = oConn.prepareCall("{ call k_sp_autenticate (?,?,?)}");


        oCall.setString(1,sUserId);
        oCall.setString(2,sPassword);
        oCall.registerOutParameter(3, java.sql.Types.DECIMAL);


        if (DebugFile.trace) DebugFile.writeln("  java.sql.Connection.execute()");


        oCall.execute();
        iStatus = Short.parseShort(oCall.getBigDecimal(3).toString());
        oCall.close();
        break;


      case JDCConnection.DBMS_MSSQL:
      case JDCConnection.DBMS_MYSQL:


        if (DebugFile.trace) DebugFile.writeln("  Connection.prepareCall({ call k_sp_autenticate (" + sUserId + "," + sPassword + ",?)})");


        oCall = oConn.prepareCall("{ call k_sp_autenticate (?,?,?)}");


        oCall.setString(1,sUserId);
        oCall.setString(2,sPassword);
        oCall.registerOutParameter(3, java.sql.Types.SMALLINT);


        if (DebugFile.trace) DebugFile.writeln("  java.sql.Connection.execute()");


        oCall.execute();
        iStatus = oCall.getShort(3);
        oCall.close();
        break;


        case JDCConnection.DBMS_POSTGRESQL:
          oStmt = oConn.prepareStatement("SELECT k_sp_autenticate(?,?)", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

View Full Code Here

    }
    else {
      if (DebugFile.trace) {
        DebugFile.writeln("Connection.prepareCall({call k_sp_del_acourse('"+sGuACourse+"')})");
      }
      CallableStatement oCall = oConn.prepareCall("{ call k_sp_del_acourse(?) }");
      oCall.setString(1, sGuACourse);
      oCall.execute();
      oCall.close();
    }
    if (DebugFile.trace) {
      DebugFile.decIdent();
      DebugFile.writeln("End AcademicCourse.delete()");
    }

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.