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

          oRSet.close();
        }
        oDlte.close();
      }
      else {
        CallableStatement oCall = oConn.prepareCall("{ call k_sp_del_mime_msg(?) }");
        for (int m=0; m<iObjs; m++) {
          oCall.setString(1, oObjs.getString(0,m));
          oCall.execute();
        }
        oCall.close();
      }
    } // fi (exists(k_mime_msgs))


    oObjs = null;


    if (DBBind.exists(oConn, DB.k_x_company_prods, "U")) {


      oStmt = oConn.createStatement();


      if (DebugFile.trace)
        DebugFile.writeln("Statement.executeUpdate(DELETE FROM " + DB.k_x_company_prods + " WHERE " + DB.gu_category + "='" + sCategoryGUID + "')");


      oStmt.executeUpdate("DELETE FROM " + DB.k_x_company_prods + " WHERE " + DB.gu_category + "='" + sCategoryGUID + "'");


      oStmt.close();
    } // fi (k_x_company_prods)


    if (DBBind.exists(oConn, DB.k_x_contact_prods, "U")) {


      oStmt = oConn.createStatement();


      if (DebugFile.trace)
        DebugFile.writeln("Statement.executeUpdate(DELETE FROM " + DB.k_x_contact_prods + " WHERE " + DB.gu_category + "='" + sCategoryGUID + "')");


      oStmt.executeUpdate("DELETE FROM " + DB.k_x_contact_prods + " WHERE " + DB.gu_category + "='" + sCategoryGUID + "'");


      oStmt.close();
    } // fi (k_x_contact_prods)


    // Saca la lista de categorías hijas de primer nivel y repite el proceso de borrado
    LinkedList oChilds = oCat.browse(oConn, BROWSE_DOWN, BROWSE_BOTTOMUP);
    ListIterator oIter = oChilds.listIterator();


    while (oIter.hasNext()) {
      oCat = (Category) oIter.next();
      oCat.delete(oConn);
    } // wend


    if (oConn.getDataBaseProduct()==JDCConnection.DBMS_POSTGRESQL) {
      if (DebugFile.trace) DebugFile.writeln("Connection.executeQuery(SELECT k_sp_del_category_r ('" + sCategoryGUID + "'))");
      oStmt = oConn.createStatement();
      oStmt.executeQuery("SELECT k_sp_del_category_r ('" + sCategoryGUID + "')");
      oStmt.close();
      bRetVal = true;
    } else {
      if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({call k_sp_del_category_r('" + sCategoryGUID + "')})");
      CallableStatement oCall = oConn.prepareCall("{call k_sp_del_category_r ('" + sCategoryGUID + "')}");
      bRetVal = oCall.execute();
      oCall.close();
    }


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

View Full Code Here

   * Expansion is stored at k_project_expand table.
   * @param oConn Database Connection
   * @throws SQLException
   */
  public void expand(JDCConnection oConn) throws SQLException {
    CallableStatement oCall;
    Statement oStmt;


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


    if (oConn.getDataBaseProduct()==JDCConnection.DBMS_POSTGRESQL) {
      if (DebugFile.trace) DebugFile.writeln("Connection.executeQuery(SELECT  k_sp_prj_expand ('" + getStringNull(DB.gu_project,"null") + "')");
      oStmt = oConn.createStatement();
      oStmt.executeQuery("SELECT k_sp_prj_expand ('" + getString(DB.gu_project) + "')");
      oStmt.close();
    } else {
      if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({ call k_sp_prj_expand ('" + getStringNull(DB.gu_project,"null") + "')}");
      oCall = oConn.prepareCall("{ call k_sp_prj_expand ('" + getString(DB.gu_project) + "') }");
      oCall.execute();
      oCall.close();
    }


    if (DebugFile.trace) {
      DebugFile.decIdent();
      DebugFile.writeln("End Project.expand()");

View Full Code Here

      oStmt.close();
      bRetVal = true;
    }
    else {
      if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({ call k_sp_del_project ('" + sProjectGUID + "')})");
      CallableStatement oCall = oConn.prepareCall("{ call k_sp_del_project ('" + sProjectGUID + "')}");
      bRetVal = oCall.execute();
      oCall.close();
    }


    if (DebugFile.trace) DebugFile.writeln("sTopParent="+sTopParent);


    if (!sProjectGUID.equals(sTopParent)) new Project(sTopParent).expand(oConn);

View Full Code Here

      oStmt.close();
      bRetVal = true;
    }
    else {
      if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({ call k_sp_del_bug ('" + sBugGUID + "')})");
      CallableStatement oCall = oConn.prepareCall("{call k_sp_del_bug ('" + sBugGUID + "')}");
      bRetVal = oCall.execute();
      oCall.close();
    }


    return bRetVal;
  } // delete()

View Full Code Here

   * does not have a mail root category and a new one could not be created
   * @throws SQLException
   */
  public String getMailRoot (JDCConnection oConn) throws SQLException {
    PreparedStatement oStmt;
    CallableStatement oCall;
    ResultSet oRSet;
    String sRetVal;


    if (DebugFile.trace) {
       DebugFile.writeln("Begin ACLUser.getMailRoot(Connection])");
       DebugFile.incIdent();
    }


    switch (oConn.getDataBaseProduct()) {


      case JDCConnection.DBMS_POSTGRESQL:
        if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(SELECT k_sp_get_user_mailroot('"+getStringNull(DB.gu_user,"null")+"'))");
        oStmt = oConn.prepareStatement("SELECT k_sp_get_user_mailroot(?)");
        oStmt.setString(1, getString(DB.gu_user));
        oRSet = oStmt.executeQuery();
        if (oRSet.next())
          sRetVal = oRSet.getString(1);
        else
          sRetVal = null;
        oRSet.close();
        oStmt.close();
        break;
      default:
        if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({ call k_sp_get_user_mailroot('"+getStringNull(DB.gu_user,"null")+"',?) }");
        oCall = oConn.prepareCall("{ call k_sp_get_user_mailroot(?,?) }");
        oCall.setString(1, getString(DB.gu_user));
        oCall.registerOutParameter(2, java.sql.Types.CHAR);
        oCall.execute();
        sRetVal = oCall.getString(2);
        oCall.close();
        break;
    }


    if (sRetVal==null) {
      if (DebugFile.trace) DebugFile.writeln("user mail root not found creating new one...");

View Full Code Here

   */


  public String getMailFolder (JDCConnection oConn, String sFolderName)
    throws SQLException {
    PreparedStatement oStmt;
    CallableStatement oCall;
    ResultSet oRSet;
    String sRetVal;


    if (DebugFile.trace) {
       DebugFile.writeln("Begin ACLUser.getMailFolder([JDCConnection],"+sFolderName+")");
       DebugFile.incIdent();
    }


    if (sFolderName==null) {
      if (DebugFile.trace) DebugFile.decIdent();
      throw new NullPointerException("ACLUser.getMailFolder() folder name cannot be null");
    }
    if (sFolderName.length()==0) {
      if (DebugFile.trace) DebugFile.decIdent();
      throw new NullPointerException("ACLUser.getMailFolder() folder name cannot be an empty string");
    }


    String sIcon1 = sFolderName.equalsIgnoreCase("inbox") ? "mailbox_16x16.gif" : "folderclosed_16x16.gif";
    String sIcon2 = sFolderName.equalsIgnoreCase("inbox") ? "mailbox_16x16.gif" : "folderopen_16x16.gif";


    switch (oConn.getDataBaseProduct()) {


      case JDCConnection.DBMS_POSTGRESQL:
        if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(SELECT k_sp_get_user_mailfolder('"+getStringNull(DB.gu_user,"null")+"','"+sFolderName+"')");


        oStmt = oConn.prepareStatement("SELECT k_sp_get_user_mailfolder(?,?)");
        oStmt.setString(1, getString(DB.gu_user));
        oStmt.setString(2, sFolderName);
        oRSet = oStmt.executeQuery();
        if (oRSet.next())
          sRetVal = oRSet.getString(1);
        else
          sRetVal = null;
        oRSet.close();
        oStmt.close();
        break;
      default:
        if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({ call k_sp_get_user_mailfolder('"+getStringNull(DB.gu_user,"null")+"','"+sFolderName+"',?)})");
        oCall = oConn.prepareCall("{ call k_sp_get_user_mailfolder(?,?,?) }");
        oCall.setString(1, getString(DB.gu_user));
        oCall.setString(2, sFolderName);
        oCall.registerOutParameter(3, java.sql.Types.CHAR);
        oCall.execute();
        sRetVal = oCall.getString(3);
        oCall.close();
        break;
    }


    if (sRetVal==null) {
      if (DebugFile.trace) DebugFile.writeln("Creating new mail folder " + sFolderName + " for user " + getStringNull(DB.gu_user,"null"));

View Full Code Here

    int iCats, iConts, iMsgs;
    DBSubset oCats, oProds, oConts, oMsgs;
    Statement oStmt;
    PreparedStatement oPtmt;
    ResultSet oRSet;
    CallableStatement oCall;


    if (DebugFile.trace) {
      DebugFile.writeln("Begin ACLUser.delete([Connection], " + sUserGUID + ")");
      DebugFile.incIdent();
    }


    // -----------------------------------------------------------------------------------
    // Verify that user exists before proceeding and, also, avoid deleting more registers
    // than should by a malicious inyection of SQL code at sUserGUID


    oPtmt = oConn.prepareStatement("SELECT "+DB.gu_user+" FROM "+DB.k_users+" WHERE "+DB.gu_user+"=?",
                                   ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    oPtmt.setString(1, sUserGUID);
    oRSet = oPtmt.executeQuery();
    boolean bExists = oRSet.next();
    oRSet.close();
    oPtmt.close();


    if (!bExists) {
      if (DebugFile.trace) {
        DebugFile.writeln("user " + sUserGUID + " not found");
        DebugFile.decIdent();
        DebugFile.writeln("End ACLUser.delete() : false");
      }
      return false;
    }


    // ************
    // New for v5.0


    if (DBBind.exists(oConn, DB.k_activities, "U")) {
      oStmt = oConn.createStatement();
      oStmt.executeUpdate("UPDATE "+DB.k_x_activity_audience+" SET "+DB.gu_writer+"=NULL WHERE "+DB.gu_writer+"='"+sUserGUID+"'");
      oStmt.executeUpdate("UPDATE "+DB.k_activities+" SET "+DB.gu_writer+"=NULL WHERE "+DB.gu_writer+"='"+sUserGUID+"'");
    oStmt.close();
    }


    if (DBBind.exists(oConn, DB.k_sms_audit, "U")) {
      oStmt = oConn.createStatement();
      oStmt.executeUpdate("UPDATE "+DB.k_sms_audit+" SET "+DB.gu_writer+"=NULL WHERE "+DB.gu_writer+"='"+sUserGUID+"'");
    oStmt.close();
    }


    // End new for v5.0
    // ****************


    // ************
    // New for v4.0


    if (DBBind.exists(oConn, DB.k_working_calendar, "U")) {
      oStmt = oConn.createStatement();
      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(DELETE FROM " + DB.k_working_time + " WHERE " + DB.gu_calendar + " IN (SELECT " + DB.gu_calendar + " FROM " + DB.k_working_calendar + " WHERE " + DB.gu_user + "='" + sUserGUID + "'))");
      oStmt.executeUpdate("DELETE FROM " + DB.k_working_time + " WHERE " + DB.gu_calendar + " IN (SELECT " + DB.gu_calendar + " FROM " + DB.k_working_calendar + " WHERE " + DB.gu_user + "='" + sUserGUID + "')");
      oStmt.close();
      oStmt = oConn.createStatement();
      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(DELETE FROM " + DB.k_working_calendar + " WHERE " + DB.gu_user + "='" + sUserGUID + "')");
      oStmt.executeUpdate("DELETE FROM " + DB.k_working_calendar + " WHERE " + DB.gu_user + "='" + sUserGUID + "'");
      oStmt.close();
    }


    // ************
    // New for v3.0


    if (DBBind.exists(oConn, DB.k_user_pwd, "U")) {
      oStmt = oConn.createStatement();
      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(DELETE FROM " + DB.k_x_cat_objs + " WHERE " + DB.gu_object + " IN (SELECT "+DB.gu_pwd+" FROM "+DB.k_user_pwd+" WHERE " + DB.gu_user + "='" + sUserGUID + "'))");
      oStmt.executeUpdate("DELETE FROM " + DB.k_x_cat_objs + " WHERE " + DB.gu_object + " IN (SELECT "+DB.gu_pwd+" FROM "+DB.k_user_pwd+" WHERE " + DB.gu_user + "='" + sUserGUID + "')");
      oStmt.close();


      oStmt = oConn.createStatement();
      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(DELETE FROM " + DB.k_user_pwd + " WHERE " + DB.gu_user + "='" + sUserGUID + "')");
      oStmt.executeUpdate("DELETE FROM " + DB.k_user_pwd + " WHERE " + DB.gu_user + "='" + sUserGUID + "'");
      oStmt.close();
    } // fi


    if (DBBind.exists(oConn, DB.k_user_mail, "U")) {
      oStmt = oConn.createStatement();
      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(DELETE FROM " + DB.k_user_mail + " WHERE " + DB.gu_user + "='" + sUserGUID + "')");
      oStmt.executeUpdate("DELETE FROM " + DB.k_user_mail + " WHERE " + DB.gu_user + "='" + sUserGUID + "'");
      oStmt.close();
    }


    /* Actualizar los estados de negocio creados por el usuario */
    if (DBBind.exists(oConn, DB.k_business_states, "U")) {
      oStmt = oConn.createStatement();
      if (DebugFile.trace)
        DebugFile.writeln("UPDATE " + DB.k_business_states + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "'");
      oStmt.executeUpdate("UPDATE " + DB.k_business_states + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "'");
      oStmt.close();
    }


    /* Borrar la referencia este usuario desde los cuestionarios que haya rellenado */
    if (DBBind.exists(oConn, DB.k_pageset_answers, "U")) {
      oStmt = oConn.createStatement();
      if (DebugFile.trace)
        DebugFile.writeln("UPDATE " + DB.k_pageset_answers + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "'");
      oStmt.executeUpdate("UPDATE " + DB.k_pageset_answers + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "'");
      oStmt.close();
    }


    /* Desasociar las evaluaciones */
    if (DBBind.exists(oConn, DB.k_evaluations, "U")) {
      oStmt = oConn.createStatement();
      if (DebugFile.trace)
        DebugFile.writeln("UPDATE " + DB.k_evaluations + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "'");
      oStmt.executeUpdate("UPDATE " + DB.k_evaluations + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "'");
      oStmt.close();
    }


    /* Desasociar los partes de absentismo */
    if (DBBind.exists(oConn, DB.k_absentisms, "U")) {
      oStmt = oConn.createStatement();
      if (DebugFile.trace)
        DebugFile.writeln("UPDATE " + DB.k_absentisms + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "'");
      oStmt.executeUpdate("UPDATE " + DB.k_absentisms + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "'");
      oStmt.close();
    }


    // End new for v3.0
    // ****************


    // ************
    // New for v6.0


    /* Eliminar los permisos sobre cursos */
    if (DBBind.exists(oConn, DB.k_x_user_acourse, "U")) {
      oStmt = oConn.createStatement();
      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(DELETE FROM " + DB.k_x_user_acourse + " WHERE " + DB.gu_user + "='" + sUserGUID + "')");
      oStmt.executeUpdate("DELETE FROM " + DB.k_x_user_acourse + " WHERE " + DB.gu_user + "='" + sUserGUID + "'");
      oStmt.close();
    }


    // End new for v6.0
    // ****************


    // ************
    // New for v2.1


    /* Desasociar los e-mails */
    if (DBBind.exists(oConn, DB.k_inet_addrs, "U")) {
      oStmt = oConn.createStatement();


      if (DebugFile.trace)
        DebugFile.writeln("UPDATE " + DB.k_inet_addrs + " SET " + DB.gu_user + "=NULL WHERE " + DB.gu_user + "='" + sUserGUID + "'");


      oStmt.executeUpdate("UPDATE " + DB.k_inet_addrs + " SET " + DB.gu_user + "=NULL WHERE " + DB.gu_user + "='" + sUserGUID + "'");


      oStmt.close();
    }


    if (DBBind.exists(oConn, DB.k_x_portlet_user, "U")) {
      oStmt = oConn.createStatement();


      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(DELETE FROM " + DB.k_x_portlet_user + " WHERE " + DB.gu_user + "='" + sUserGUID + "')");


      oStmt.executeUpdate("DELETE FROM " + DB.k_x_portlet_user + " WHERE " + DB.gu_user + "='" + sUserGUID + "'");


      oStmt.close();
    }


    if (DBBind.exists(oConn, DB.k_images, "U")) {


      oStmt = oConn.createStatement();


      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(UPDATE " + DB.k_images + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "')");


      oStmt.executeUpdate("UPDATE " + DB.k_images + " SET "  + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "'");


      oStmt.close();
    }


    if (DBBind.exists(oConn, DB.k_bugs_lookup, "U")) {


      oStmt = oConn.createStatement();


      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(UPDATE " + DB.k_bugs + " SET "  + DB.nm_assigned + "=NULL WHERE " + DB.nm_assigned + "='" + sUserGUID + "')");


      oStmt.executeUpdate("UPDATE " + DB.k_bugs + " SET "  + DB.nm_assigned + "=NULL WHERE " + DB.nm_assigned + "='" + sUserGUID + "'");


      oStmt.close();


      oStmt = oConn.createStatement();


      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(DELETE FROM " + DB.k_bugs_lookup + " WHERE " + DB.vl_lookup + "='" + sUserGUID + "')");


      oStmt.executeUpdate("DELETE FROM " + DB.k_bugs_lookup + " WHERE " + DB.vl_lookup + "='" + sUserGUID + "'");


      oStmt.close();
    }


    if (DBBind.exists(oConn, DB.k_x_duty_resource, "U")) {
      oStmt = oConn.createStatement();


      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(DELETE FROM " + DB.k_x_duty_resource + " WHERE " + DB.nm_resource + "='" + sUserGUID + "')");


      oStmt.executeUpdate("DELETE FROM " + DB.k_x_duty_resource + " WHERE " + DB.nm_resource + "='" + sUserGUID + "'");


      oStmt.close();


      oStmt = oConn.createStatement();


      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(DELETE FROM " + DB.k_duties_lookup + " WHERE " + DB.vl_lookup + "='" + sUserGUID + "')");


      oStmt.executeUpdate("DELETE FROM " + DB.k_duties_lookup + " WHERE " + DB.vl_lookup + "='" + sUserGUID + "'");


      oStmt.close();
    }


    // End new for v2.1
    // ****************


    // ************
    // New for v4.0


    oStmt = oConn.createStatement();
    if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(UPDATE " + DB.k_duties + " SET " + DB.gu_writer + "=NULL WHERE "+ DB.gu_writer + "='" + sUserGUID + "')");
    oStmt.executeUpdate("UPDATE " + DB.k_duties + " SET " + DB.gu_writer + "=NULL WHERE "+ DB.gu_writer + "='" + sUserGUID + "'");
    oStmt.close();


    // End new for v4.0
    // ****************


    // ************
    // New for v2.0


    if (DBBind.exists(oConn, DB.k_newsgroup_subscriptions, "U")) {
      oStmt = oConn.createStatement();


      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(DELETE FROM " + DB.k_newsgroup_subscriptions + " WHERE " + DB.gu_user + "='" + sUserGUID + "')");


      oStmt.executeUpdate("DELETE FROM " + DB.k_newsgroup_subscriptions + " WHERE " + DB.gu_user + "='" + sUserGUID + "'");


      oStmt.close();
    }


    // End new for v2.0
    // ****************


    if (DBBind.exists(oConn, DB.k_newsmsgs, "U")) {
      oStmt = oConn.createStatement();


      // Remove user GUID from messages he validated
      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(UPDATE " + DB.k_newsmsgs + " SET " + DB.gu_validator + "=NULL WHERE " + DB.gu_validator + "='" + sUserGUID + "'");


      oStmt.executeUpdate("UPDATE " + DB.k_newsmsgs + " SET " + DB.gu_validator + "=NULL WHERE " + DB.gu_validator + "='" + sUserGUID + "'");


      // Delete forum messages written by this user without file attachments
      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(DELETE FROM " + DB.k_newsmsgs + " WHERE " + DB.gu_writer + "='" + sUserGUID + "' AND " + DB.gu_product + " IS NULL");


      oStmt.executeUpdate("DELETE FROM " + DB.k_newsmsgs + " WHERE " + DB.gu_writer + "='" + sUserGUID + "' AND " + DB.gu_product + " IS NULL");


      oStmt.close();


      // Delete forum messages written by this user with file attachments
      oMsgs = new DBSubset(DB.k_newsmsgs, DB.gu_msg, DB.gu_writer + "='" + sUserGUID + "'", 100);


      iMsgs = oMsgs.load(oConn);


      for (int m=0; m<iMsgs; m++)
        com.knowgate.forums.NewsMessage.delete(oConn, oMsgs.getString(0,m));


      oMsgs = null;
    } // fi (exists(k_newsmsgs,))


    if (DBBind.exists(oConn, DB.k_member_address, "U")) {
      oStmt = oConn.createStatement();


      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(UPDATE " + DB.k_member_address + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "')");


      oStmt.executeUpdate("UPDATE " + DB.k_member_address + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "'");


      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(UPDATE " + DB.k_member_address + " SET " + DB.gu_sales_man + "=NULL WHERE " + DB.gu_sales_man + "='" + sUserGUID + "')");


      oStmt.executeUpdate("UPDATE " + DB.k_member_address + " SET " + DB.gu_sales_man + "=NULL WHERE " + DB.gu_sales_man + "='" + sUserGUID + "'");


      oStmt.close();
    }


    if (DBBind.exists(oConn, DB.k_companies_recent, "U")) {
      oStmt = oConn.createStatement();


      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(DELETE FROM " + DB.k_companies_recent + " WHERE " + DB.gu_user + "='" + sUserGUID + "')");


      oStmt.executeUpdate("DELETE FROM " + DB.k_companies_recent + " WHERE " + DB.gu_user + "='" + sUserGUID + "'");


      oStmt.close();
    }


    if (DBBind.exists(oConn, DB.k_companies, "U")) {
      oStmt = oConn.createStatement();


      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(UPDATE " + DB.k_companies + " SET " + DB.gu_sales_man + "=NULL WHERE " + DB.gu_sales_man + "='" + sUserGUID + "')");


      oStmt.executeUpdate("UPDATE " + DB.k_companies + " SET " + DB.gu_sales_man + "=NULL WHERE " + DB.gu_sales_man + "='" + sUserGUID + "'");


      oStmt.close();
    }


    if (DBBind.exists(oConn, DB.k_contacts_recent, "U")) {
      oStmt = oConn.createStatement();


      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(DELETE FROM " + DB.k_contacts_recent + " WHERE " + DB.gu_user + "='" + sUserGUID + "')");


      oStmt.executeUpdate("DELETE FROM " + DB.k_contacts_recent + " WHERE " + DB.gu_user + "='" + sUserGUID + "'");


      oStmt.close();
    }


    if (DBBind.exists(oConn, DB.k_contacts, "U")) {
      oConts = new DBSubset(DB.k_contacts, DB.gu_contact, DB.gu_writer + "='" + sUserGUID + "' AND " + DB.bo_private + "<>0", 100);
      iConts = oConts.load(oConn);


      for (int t=0; t<iConts; t++)
         com.knowgate.crm.Contact.delete(oConn, oConts.getString(0,t));


      oConts = null;


      oStmt = oConn.createStatement();


      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(UPDATE " + DB.k_contacts + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "' AND " + DB.bo_private + "=0)");


      oStmt.executeUpdate("UPDATE " + DB.k_contacts + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "' AND " + DB.bo_private + "=0");


      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(UPDATE " + DB.k_contact_notes + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "')");


      oStmt.executeUpdate("UPDATE " + DB.k_contact_notes + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "'");


      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(UPDATE " + DB.k_contact_attachs + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "')");


      oStmt.executeUpdate("UPDATE " + DB.k_contact_attachs + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "'");


      oStmt.close();
    }


    if (DBBind.exists(oConn, DB.k_oportunities, "U")) {
      oStmt = oConn.createStatement();


      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(DELETE FROM " + DB.k_oportunities + " WHERE " + DB.gu_writer + "='" + sUserGUID + "' AND " + DB.bo_private + "<>0)");


      oStmt.executeUpdate("DELETE FROM " + DB.k_oportunities + " WHERE " + DB.gu_writer + "='" + sUserGUID + "' AND " + DB.bo_private + "<>0");


      if (DebugFile.trace) DebugFile.writeln("Statement.executeUpdate(UPDATE " + DB.k_oportunities + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "' AND " + DB.bo_private + "=0)");


      oStmt.executeUpdate("UPDATE " + DB.k_oportunities + " SET " + DB.gu_writer + "=NULL WHERE " + DB.gu_writer + "='" + sUserGUID + "' AND " + DB.bo_private + "=0");


      oStmt.close();
    }


    if (DBBind.exists(oConn, DB.k_sales_men, "U")) {
       if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({ call k_sp_del_sales_man ('" + sUserGUID + "') })");


       oCall = oConn.prepareCall("{call k_sp_del_sales_man ('" + sUserGUID + "')}");
       bRetVal = oCall.execute();
       oCall.close();
     }


    if (DBBind.exists(oConn, DB.k_products, "U")) {
      oProds = new DBSubset(DB.k_products, DB.gu_product, DB.gu_owner + "='" + sUserGUID + "'", 100);
      int iProds = oProds.load(oConn);


      for (int p=0; p<iProds; p++)
        new com.knowgate.hipergate.Product(oConn, oProds.getString(0, p)).delete(oConn);
      oProds = null;
    } // fi (exists(DB.k_products))


    // Delete categories associated with user
    if (DBBind.exists(oConn, DB.k_categories, "U")) {
      String sGuRootCat = null;
      oStmt = oConn.createStatement();
      ResultSet oRCat = oStmt.executeQuery("SELECT " + DB.gu_category + " FROM " + DB.k_users + " WHERE " + DB.gu_user + "='" + sUserGUID + "'");
      if (oRCat.next())
        sGuRootCat = oRCat.getString(1);
      oRCat.close();
      oStmt.close();
      if (sGuRootCat!=null) {
        oStmt = oConn.createStatement();
        oStmt.executeUpdate("UPDATE " + DB.k_users + " SET " + DB.gu_category + "=NULL WHERE " + DB.gu_user + "='" + sUserGUID + "'");
        oStmt.close();
        Category.delete(oConn, sGuRootCat);
      }


      oCats = new DBSubset(DB.k_categories, DB.gu_category, DB.gu_owner + "=?", 10);
      iCats = oCats.load(oConn, new Object[]{sUserGUID});


      for (int r=0; r<iCats; r++)
         Category.delete(oConn, oCats.getString(0,r));


      oCats = null;
    } // fi (exists(oConn, DB.k_categories))


    if (DBBind.exists(oConn, DB.k_phone_calls, "U")) {
      oStmt = oConn.createStatement();
      if (DebugFile.trace) DebugFile.writeln("Statement.execute(DELETE FROM " + DB.k_phone_calls + " WHERE " + DB.gu_user + "='" + sUserGUID + "' OR " + DB.gu_writer + "='" + sUserGUID + "')");
      oStmt.executeUpdate("DELETE FROM " + DB.k_phone_calls + " WHERE " + DB.gu_user + "='" + sUserGUID + "' OR " + DB.gu_writer + "='" + sUserGUID + "'");
      oStmt.close();
    } // fi (exists(oConn, DB.k_phone_calls))


    if (DBBind.exists(oConn, DB.k_to_do, "U")) {
      oStmt = oConn.createStatement();
      if (DebugFile.trace) DebugFile.writeln("Statement.execute(DELETE FROM " + DB.k_to_do + " WHERE " + DB.gu_user + "='" + sUserGUID + "')");
      oStmt.executeUpdate("DELETE FROM " + DB.k_to_do + " WHERE " + DB.gu_user + "='" + sUserGUID + "'");
      oStmt.close();
    } // fi (exists(oConn, DB.k_to_do))


    if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({ call k_sp_del_user ('" + sUserGUID + "') })");


    oCall = oConn.prepareCall("{call k_sp_del_user ('" + sUserGUID + "')}");
    bRetVal = oCall.execute();
    oCall.close();


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

View Full Code Here

   * @throws SQLException
   */
  public static String getUserAccountType(JDCConnection oConn, String sUserId) throws SQLException {
    Statement oStmt;
    ResultSet oRSet;
    CallableStatement oCall;
    String sTp;


    if (oConn.getDataBaseProduct()==JDCConnection.DBMS_POSTGRESQL) {
      oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
      oRSet = oStmt.executeQuery("SELECT k_get_account_tp ('" + sUserId + "')");
      oRSet.next();
      sTp = oRSet.getString(1);
      oRSet.close();
      oStmt.close();
    }
    else {
      oCall = oConn.prepareCall("{ call k_get_account_tp (?,?) }");


      oCall.setString(1, sUserId);
      oCall.registerOutParameter(2, Types.CHAR);


      oCall.execute();


      if (JDCConnection.DBMS_ORACLE==oConn.getDataBaseProduct())
        sTp = oCall.getString(2);
      else
        sTp = oCall.getString(2);


      if (sTp!=null) sTp = sTp.trim();


      oCall.close();
    }
    return sTp;
  } // getUserAccountType()

View Full Code Here

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


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


    return ((short)0!=iActive);


  } // checkStatus

View Full Code Here

   * @throws SQLException
   */
  public static int daysLeft (JDCConnection oConn, String sAccId) throws SQLException {
    Statement oStmt;
    ResultSet oRSet;
    CallableStatement oCall;
    int iDaysLeft;


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


    return iDaysLeft;
  } // daysLeft

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.