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 isGuest(JDCConnection oConn, String guWorkArea, String sUserId) throws SQLException {
    int iIsGuest;
    CallableStatement oCall;
    PreparedStatement oStmt;
    ResultSet oRSet;


    if (DebugFile.trace) {
      DebugFile.writeln("Begin WorkArea.isGuest([Connection], " + guWorkArea + "," + sUserId + ")");
      DebugFile.incIdent();
    }


    switch (oConn.getDataBaseProduct()) {


      case JDCConnection.DBMS_ORACLE:


        if (DebugFile.trace)
          DebugFile.writeln("Connection.prepareCall({ call k_is_workarea_guest ('" + guWorkArea + "','" + sUserId + "',?)})");


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


        oCall.setString(1, guWorkArea);
        oCall.setString(2, sUserId);
        oCall.registerOutParameter(3, Types.DECIMAL);
        oCall.execute();
        iIsGuest = oCall.getBigDecimal(3).intValue();
        oCall.close();
        break;


      case JDCConnection.DBMS_MSSQL:
      case JDCConnection.DBMS_MYSQL:


        if (DebugFile.trace)
          DebugFile.writeln("Connection.prepareCall({ call k_is_workarea_guest ('" + guWorkArea + "','" + sUserId + "',?)})");


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


        oCall.setString(1, guWorkArea);
        oCall.setString(2, sUserId);
        oCall.registerOutParameter(3, Types.INTEGER);
        oCall.execute();
        iIsGuest = oCall.getInt(3);
        oCall.close();
        break;


      default:


        if (DebugFile.trace)

View Full Code Here

   * @since 4.0
   */


  public static boolean isAnyRole(JDCConnection oConn, String guWorkArea, String sUserId) throws SQLException {
    int iIsAnyRole;
    CallableStatement oCall;
    PreparedStatement oStmt;
    ResultSet oRSet;


    if (DebugFile.trace) {
      DebugFile.writeln("Begin WorkArea.iIsAnyRole([Connection], " + guWorkArea + "," + sUserId + ")");
      DebugFile.incIdent();
    }


    switch (oConn.getDataBaseProduct()) {


      case JDCConnection.DBMS_ORACLE:


        if (DebugFile.trace)
          DebugFile.writeln("Connection.prepareCall({ call k_is_workarea_anyrole ('" + guWorkArea + "','" + sUserId + "',?)})");


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


        oCall.setString(1, guWorkArea);
        oCall.setString(2, sUserId);
        oCall.registerOutParameter(3, Types.DECIMAL);
        oCall.execute();
        iIsAnyRole = oCall.getBigDecimal(3).intValue();
        oCall.close();
        break;


      case JDCConnection.DBMS_MSSQL:
      case JDCConnection.DBMS_MYSQL:


        if (DebugFile.trace)
          DebugFile.writeln("Connection.prepareCall({ call k_is_workarea_anyrole ('" + guWorkArea + "','" + sUserId + "',?)})");


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


        oCall.setString(1, guWorkArea);
        oCall.setString(2, sUserId);
        oCall.registerOutParameter(3, Types.INTEGER);
        oCall.execute();
        iIsAnyRole = oCall.getInt(3);
        oCall.close();
        break;


      default:


        if (DebugFile.trace)

View Full Code Here

   */


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


    CallableStatement oCall = oConn.prepareCall("{call k_sp_del_room ('" + sRoomNm + "','" + sWrkAId + "')}");
    bRetVal = oCall.execute();
    oCall.close();


    return bRetVal;
  } // delete

View Full Code Here

  public int deleteDuplicates(JDCConnection oConn) throws SQLException,IllegalStateException,UnsupportedOperationException {
  
    int iDeleted = 0;
    ResultSet oRSet;
    PreparedStatement oStmt;
    CallableStatement oCall;


    if (isNull(DB.tp_list))
    throw new IllegalStateException("DistributionList.deleteDuplicates() List must be loaded before attempting to delete duplicates");
    
    if (getShort(DB.tp_list)!=TYPE_STATIC && getShort(DB.tp_list)!=TYPE_DIRECT)
    throw new SQLException("DistributionList.deleteDuplicates() is only allowed for static or direct lists");
  
  switch (oConn.getDataBaseProduct()) {
    case JDCConnection.DBMS_POSTGRESQL:
      oStmt = oConn.prepareStatement("SELECT k_sp_del_duplicates(?)");
      oStmt.setString(1, getString(DB.gu_list));
      oRSet = oStmt.executeQuery();
    oRSet.next();
    iDeleted = oRSet.getInt(1);
    oStmt.close();
    break;
    case JDCConnection.DBMS_MSSQL:
    case JDCConnection.DBMS_MYSQL:
      oCall = oConn.prepareCall("{ call k_sp_del_duplicates(?,?) }");
        oCall.setString(1, getString(DB.gu_list));
        oCall.registerOutParameter(2, java.sql.Types.INTEGER);
        oCall.execute();
        iDeleted = oCall.getInt(2);
        oCall.close();
        break;
      case JDCConnection.DBMS_ORACLE:
      oStmt = oConn.prepareStatement("DELETE FROM "+DB.k_x_list_members+" WHERE "+DB.gu_list+"=? AND ROWID NOT IN (SELECT MAX(ROWID) FROM "+DB.k_x_list_members+" WHERE "+DB.gu_list+"=? GROUP BY "+DB.tx_email+")");
      oStmt.setString(1, getString(DB.gu_list));
      oStmt.setString(2, getString(DB.gu_list));

View Full Code Here

      Statement oStmt = oConn.createStatement();
      oStmt.executeQuery("SELECT k_sp_del_list ('" + sListGUID + "')");
      oStmt.close();
      bRetVal = true;
    } else {
      CallableStatement oCall = oConn.prepareCall("{ call k_sp_del_list ('" + sListGUID + "') }");
      bRetVal = oCall.execute();
      oCall.close();
    }


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

View Full Code Here

    }


    protected void setDatabaseProperty(
            String propertyName, String value, Connection conn) 
    throws SQLException {
        CallableStatement setDBP =  conn.prepareCall(
        "CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?, ?)");
        setDBP.setString(1, propertyName);
        setDBP.setString(2, value);
        setDBP.execute();
        setDBP.close();
    }

View Full Code Here

   * @see com.gnizr.db.dao.foruser.ForUserDao#createForUser(com.gnizr.db.dao.ForUser)
   */
  public int createForUser(ForUser forUser) {
    logger.debug("input: forUser="+forUser);
    Connection conn = null;
    CallableStatement cStmt = null;
    int id = -1;
    try {
      conn = dataSource.getConnection();
      cStmt = conn.prepareCall("{call createForUser(?,?,?,?,?)}");
      cStmt.setInt(1,forUser.getForUser().getId());
      cStmt.setInt(2,forUser.getBookmark().getId());
      cStmt.setString(3,forUser.getMessage());
      cStmt.setTimestamp(4,new Timestamp(forUser.getCreatedOn().getTime()));
      cStmt.registerOutParameter(5,Types.INTEGER);
      cStmt.execute();
      id = cStmt.getInt(5);
    } catch (Exception e) {
      logger.fatal(e);
    } finally{
      try {
        DBUtil.cleanup(conn, cStmt);

View Full Code Here

  }


  public int addImportFolders(FeedSubscription subscription, List<Folder> folders) {
    logger.debug("addImportFolders: subscription="+subscription+",folders="+folders);
    Connection conn = null;
    CallableStatement cStmt = null;
    int numUpdated = 0;
    try {
      conn = dataSource.getConnection();
      cStmt = conn.prepareCall("{call addImportFolder(?,?)};");      
      for(Folder folder : folders){
        cStmt.setInt(1,subscription.getId());
        cStmt.setInt(2,folder.getId());        
        cStmt.addBatch();        
      }      
      int result[] = cStmt.executeBatch();
      for(int i = 0; i < result.length; i++){
        if(result[i] >= 0 ){
          numUpdated++;
        }
      }

View Full Code Here

  }


  public int createSubscription(FeedSubscription subscription) {
    logger.debug("createSubscription: subscription=" + subscription);
    Connection conn = null;
    CallableStatement cStmt = null;
    int id = -1;
    try {
      conn = dataSource.getConnection();
      cStmt = conn.prepareCall("{call createSubscription(?,?,?,?,?,?)}");
      cStmt.setInt(1,subscription.getBookmark().getId());
      Date lastSync = subscription.getLastSync();
      if(lastSync != null){
        cStmt.setTimestamp(2,new Timestamp(lastSync.getTime()));
      }else{
        cStmt.setTimestamp(2,null);
      }
      cStmt.setString(3,subscription.getMatchText());
      cStmt.setBoolean(4,subscription.isAutoImport());      
      Date pubDate = subscription.getPubDate();
      if(pubDate != null){
        cStmt.setTimestamp(5, new Timestamp(pubDate.getTime()));
      }else{
        cStmt.setTimestamp(5, null);
      }
      cStmt.registerOutParameter(6,Types.INTEGER);
      cStmt.execute();
      id = cStmt.getInt(6);
    } catch (Exception e) {
      logger.fatal(e);
    } finally{
      try {
        DBUtil.cleanup(conn, cStmt);

View Full Code Here


  public DaoResult<FeedSubscription> pageSubscription(User user, int offset, int count) {
    logger.debug("pageSubscription: user="+user + ",offset="+offset+",count="+count);    
    List<FeedSubscription> feeds = new ArrayList<FeedSubscription>();
    DaoResult<FeedSubscription> result = null;
    CallableStatement cStmt = null;
    Connection conn = null;
    try{            
      conn = dataSource.getConnection();
      cStmt = conn.prepareCall("call pageSubscriptionByOwnerId(?,?,?,?);");
      cStmt.setInt(1,user.getId());
      cStmt.setInt(2,offset);
      cStmt.setInt(3,count);
      cStmt.registerOutParameter(4,Types.INTEGER);
      ResultSet rs = cStmt.executeQuery();
      while(rs.next()){
        FeedSubscription feed = createFeedSubscriptionObject(rs);        
        feeds.add(feed);
      }
      int size = cStmt.getInt(4);
      if(size < 0){
        size = 0;
      }      
      result = new DaoResult<FeedSubscription>(feeds,size);
      logger.debug("DaoResult: feeds="+feeds+",size="+size);

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.