Пример #1
0
  public static void main(String args[]) throws Exception {

    // Instantiate our DAO and get connection
    Dao dao = new Dao();
    Connection conn = dao.getConnection();

    // Prepare a callable statement
    // NOTE:  Curly brackets aka braces are required for the call builder
    // to work correctly.  Otherwise it'll actually just treat it as a
    // PreparedStatement, which may or may not be what you want!
    String s1 = "{CALL ADD_SERVICE(?,?,?)}";
    CallableStatement cs = conn.prepareCall(s1);

    // Set variables the usual way
    cs.setString(1, "DUB");
    cs.setString(2, "EWR");
    cs.setString(3, "A380");

    // Execute it
    cs.execute();

    // Get the result set (as usual)
    ResultSet rs = cs.getResultSet();

    // Go to the first result (the only one!) which came from the SELECT
    // statement in the procedure.
    rs.next();

    // Print the flight number to the screen
    System.out.println("New flight number is " + rs.getInt("flightNumber"));
  }
  @Override
  public void performAction(Connection connection, OperationObserver observer)
      throws SQLException, Exception {

    ProcedureTranslator transl = createTranslator(connection);
    CallableStatement statement = (CallableStatement) transl.createStatement();

    try {
      // stored procedure may contain a mixture of update counts and result sets,
      // and out parameters. Read out parameters first, then
      // iterate until we exhaust all results
      boolean hasResultSet = statement.execute();

      // local observer to cache results and provide them to the external observer
      // in the order consistent with other adapters.

      Observer localObserver = new Observer(observer);

      // read query, using local observer

      while (true) {
        if (hasResultSet) {
          ResultSet rs = statement.getResultSet();
          try {
            RowDescriptor descriptor = describeResultSet(rs, processedResultSets++);
            readResultSet(rs, descriptor, query, localObserver);
          } finally {
            try {
              rs.close();
            } catch (SQLException ex) {
            }
          }
        } else {
          int updateCount = statement.getUpdateCount();
          if (updateCount == -1) {
            break;
          }
          dataNode.getJdbcEventLogger().logUpdateCount(updateCount);
          localObserver.nextCount(query, updateCount);
        }

        hasResultSet = statement.getMoreResults();
      }

      // read out parameters to the main observer ... AFTER the main result set
      // TODO: I hope SQLServer does not support ResultSets as OUT parameters,
      // otherwise
      // the order of custom result descriptors will be messed up
      readProcedureOutParameters(statement, observer);

      // add results back to main observer
      localObserver.flushResults(query);
    } finally {
      try {
        statement.close();
      } catch (SQLException ex) {

      }
    }
  }
Пример #3
0
 public ResultSet getResultSet(CallableStatement ps) throws SQLException {
   boolean isResultSet = ps.execute();
   while (!isResultSet && ps.getUpdateCount() != -1) {
     isResultSet = ps.getMoreResults();
   }
   return ps.getResultSet();
 }
Пример #4
0
 /** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */
 protected void doPost(HttpServletRequest request, HttpServletResponse response)
     throws ServletException, IOException {
   // / TODO Auto-generated method stub
   PrintWriter out = response.getWriter();
   String mail = request.getParameter("Correo");
   DBConnection ping = new DBConnection();
   Connection con = ping.makeConnection("root", "sharPedo319");
   String resp = null;
   try {
     CallableStatement stmnt = con.prepareCall("{call obtenerPProducto(?)}");
     stmnt.setString(1, mail);
     // resp = stmnt.getString(1);
     stmnt.executeUpdate();
     ResultSet rs = stmnt.getResultSet();
     while (rs.next()) {
       json.put("activo", rs.getString(1));
       json.put("imagen", rs.getString(2));
       json.put("nombre", rs.getString(3));
       json.put("ubicacionInt", rs.getString(4));
       // resp = resp.concat("\nActivo:" + rs.getString(1) + ", imagen:" + rs.getString(2) + ",
       // nombre:" + rs.getString(3) + ", ubicacionInt" + rs.getString(4));
     }
   } catch (SQLException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
     resp = e.getMessage();
   } catch (JSONException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
   } finally {
     ping.closeConnection();
   }
   out.print(resp);
   // doGet(request, response);
 }
 @Override
 public List<MotInteInventario> findByInternamiento(Long codigo)
     throws MotInteInventarioDaoException {
   Connection conn = null;
   CallableStatement stmt = null;
   ResultSet rs = null;
   List<MotInteInventario> list = null;
   try {
     conn = ResourceManager.getConnection();
     stmt = conn.prepareCall("{call SP_MOT_GET_INTEINVENTARIOBYINTERNAMIENTO;1(?)}");
     stmt.setLong(1, codigo);
     boolean results = stmt.execute();
     if (results) {
       list = new ArrayList<MotInteInventario>();
       rs = stmt.getResultSet();
       MotInteInventario inventario = null;
       while (rs.next()) {
         inventario = new MotInteInventario();
         inventario.getInternamiento().setIntcodigoD(rs.getLong("INTCODIGO"));
         inventario.getInventarioTipo().setBitcodigoI(rs.getInt("BITCODIGO"));
         inventario.setBivcantidadI(rs.getInt("CANTIDAD"));
         inventario.setBivestadoC(rs.getString("ESTADO"));
         list.add(inventario);
       }
     }
   } catch (SQLException e) {
     throw new MotInteInventarioDaoException(e.getMessage(), e);
   } finally {
     ResourceManager.close(rs);
     ResourceManager.close(stmt);
     ResourceManager.close(conn);
   }
   return list;
 }
Пример #6
0
  public static void main(String[] args) {
    Connection conn = null;
    CallableStatement cStmt = null;
    try {
      // STEP 2: Register JDBC driver
      Class.forName(JDBC_DRIVER);

      // STEP 3: Open a connection
      System.out.println("Connecting to a selected database...");
      conn = DriverManager.getConnection(DB_URL, USER, PASS);
      System.out.println("Connected database successfully...");

      // STEP 4: Execute a query
      System.out.println("Enrollno: 140053131008");
      cStmt = conn.prepareCall("{call studentnew1()}");
      cStmt.execute();

      System.out.println("Selected Data is:");
      System.out.println();

      ResultSet rs = cStmt.getResultSet();
      System.out.print("ID:");
      System.out.print("Name:");
      System.out.print("Branch:");

      while (rs.next()) {
        System.out.println();
        System.out.print(rs.getInt(1) + "\t");
        System.out.print(rs.getString(2) + "\t");
        System.out.print(rs.getString(3) + "\t");
        System.out.println();
      }
      cStmt.close();
    } catch (SQLException se) {
      // Handle errors for JDBC
      se.printStackTrace();
    } catch (Exception e) {
      // Handle errors for Class.forName
      e.printStackTrace();
    } finally {
      // finally block used to close resources

      try {
        if (cStmt != null) conn.close();
        cStmt.close();

      } catch (SQLException se) {
      } // do nothing
      try {
        if (conn != null) conn.close();
      } catch (SQLException se) {
        se.printStackTrace();
      } // end finally try
    } // end try
    System.out.println("Goodbye!");
  } // end main
 public ResultSet getResultSet(CallableStatement ps) throws SQLException {
   boolean isResultSet = ps.execute();
   //		 This assumes you will want to ignore any update counts
   while (!isResultSet && ps.getUpdateCount() != -1) {
     isResultSet = ps.getMoreResults();
   }
   //		 You may still have other ResultSets or update counts left to process here
   //		 but you can't do it now or the ResultSet you just got will be closed
   return ps.getResultSet();
 }
  /** @since 3.0 */
  @Override
  protected long longPkFromDatabase(DataNode node, DbEntity entity) throws Exception {
    // handle CAY-588 - get connection that is separate from the connection in the
    // current transaction.

    // TODO (andrus, 7/6/2006) Note that this will still work in a pool with a single
    // connection, as PK generator is invoked early in the transaction, before the
    // connection is grabbed for commit... So maybe promote this to other adapters in
    // 3.0?

    Transaction transaction = Transaction.getThreadTransaction();
    Transaction.bindThreadTransaction(null);

    try {

      Connection connection = node.getDataSource().getConnection();
      try {
        CallableStatement statement = connection.prepareCall("{call auto_pk_for_table(?, ?)}");
        try {
          statement.setString(1, entity.getName());
          statement.setInt(2, super.getPkCacheSize());

          // can't use "executeQuery"
          // per http://jtds.sourceforge.net/faq.html#expectingResultSet
          statement.execute();
          if (statement.getMoreResults()) {
            ResultSet rs = statement.getResultSet();

            try {
              if (rs.next()) {
                return rs.getLong(1);
              } else {
                throw new CayenneRuntimeException(
                    "Error generating pk for DbEntity " + entity.getName());
              }
            } finally {
              rs.close();
            }
          } else {
            throw new CayenneRuntimeException(
                "Error generating pk for DbEntity "
                    + entity.getName()
                    + ", no result set from stored procedure.");
          }
        } finally {
          statement.close();
        }
      } finally {
        connection.close();
      }
    } finally {
      Transaction.bindThreadTransaction(transaction);
    }
  }
  @Override
  public List<MotEmpConductor> findByCriterio(String criterio, String texto, Long empcodigoD)
      throws MotEmpConductorDaoException {

    Connection conn = null;
    CallableStatement stmt = null;
    ResultSet rs = null;

    List<MotEmpConductor> list = new ArrayList<MotEmpConductor>();
    try {
      conn = ResourceManager.getConnection();
      stmt = conn.prepareCall("{call SP_MOT_GET_LISTADO_CONDUCTORPORRITERIO;1(?,?,?)}");
      stmt.setString(1, criterio);
      stmt.setString(2, texto);
      stmt.setLong(3, empcodigoD);
      boolean results = stmt.execute();
      if (results) {
        rs = stmt.getResultSet();
        MotPersona persona = null;
        MotConductor conductor = null;
        MotEmpConductor empresaconductor = null; // ???

        while (rs.next()) {

          persona = new MotPersona();
          persona.setPernombresV(rs.getString("Nombres"));
          persona.setPerpaternoV(rs.getString("Paterno"));
          persona.setPermaternoV(rs.getString("Materno"));
          persona.setPerdniV(rs.getString("DNI"));

          conductor = new MotConductor();
          conductor.setConcodigoD(rs.getLong("CONDCODIGO"));
          conductor.setPersona(persona);

          empresaconductor = new MotEmpConductor();
          empresaconductor.setMotosasignadasI(rs.getInt("Motos Asignadas"));
          empresaconductor.setEcofechainicioF(rs.getString("Fecha Inicio"));
          empresaconductor.setEcofechaceseF(rs.getString("Fecha Cese"));
          empresaconductor.setConductor(conductor);
          list.add(empresaconductor);
        }
      }

    } catch (SQLException ex) {
      throw new MotEmpConductorDaoException(ex.getMessage());
    } finally {

      ResourceManager.close(rs);
      ResourceManager.close(stmt);
      ResourceManager.close(conn);
    }

    return list;
  }
Пример #10
0
  /** @param args */
  public static void main(String[] args) throws Exception {
    // Chargement du driver
    Class.forName("com.mysql.jdbc.Driver");
    //		Driver driver = new com.mysql.jdbc.Driver();
    //		DriverManager.registerDriver(driver);

    // Connexion au SGBD
    java.sql.Connection connection =
        DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "");
    // Creation de la procedure
    Statement statement = connection.createStatement();
    statement.executeUpdate("DROP PROCEDURE IF EXISTS rechercherNom");
    statement.executeUpdate(
        "CREATE PROCEDURE rechercherNom(IN leNom VARCHAR(50))\n"
            + "BEGIN\n"
            + " SELECT * FROM Annuaire WHERE nom = leNom;\n"
            + " SELECT COUNT(*) FROM Annuaire WHERE nom = leNom;\n"
            + "END\n");
    // Appel a la procedure stockee
    CallableStatement callableStatement = connection.prepareCall("{call rechercherNom(?)}");
    // Passage de kane comme valeur du premier parametre
    callableStatement.setString(1, "kane");
    boolean resultOK = callableStatement.execute();
    if (resultOK) {
      // Recuperation des ResultSet
      ResultSet resultSet1 = callableStatement.getResultSet();
      callableStatement.getMoreResults(Statement.KEEP_CURRENT_RESULT);
      ResultSet resultSet2 = callableStatement.getResultSet();
      // Traitement des informations
      while (resultSet1.next()) {
        for (int i = 0; i < resultSet1.getMetaData().getColumnCount(); i++) {
          System.out.print(resultSet1.getObject(i + 1) + " ,");
        }
        System.out.println("");
      }
      resultSet2.next();
      System.out.println("Nombre de lignes = " + resultSet2.getObject(1));
      resultSet1.close();
      resultSet2.close();
    }
  }
 public ResultSet sp_SubjectByPassedRateBySemester(int semesterId) {
   try {
     ConnectionDB conn = new ConnectionDB();
     Connection con = conn.getConnecttion();
     CallableStatement cStmt = con.prepareCall("{CALL sp_SubjectByPassedRateBySemester(?)}");
     cStmt.setInt(1, semesterId);
     cStmt.execute();
     ResultSet rs1 = cStmt.getResultSet();
     return rs1;
   } catch (SQLException ex) {
     Logger.getLogger(Statistics.class.getName()).log(Level.SEVERE, null, ex);
   }
   return null;
 }
Пример #12
0
  public static synchronized Message execute_query(Connection con, String receiver, String sender) {
    CallableStatement callFunction = null;
    Message newMessage = new Message();
    try {

      // checking if the connection that is returning is not closed
      if (!con.isClosed()) {
        //				System.out.println("conencted!!");

        // prepare callable function
        callFunction = con.prepareCall(callableFunction);

        // setting the parameter for the callable function
        callFunction.setString(1, receiver);
        callFunction.setString(2, sender);

        callFunction.execute();
        ResultSet result = callFunction.getResultSet();
        result.next();
        if (result.getInt(7) == 1) {
          System.out.println("No message found!");
        } else {
          System.out.println(result.getString(1));
          newMessage.message = result.getString(1);
          newMessage.sender = result.getString(2);
          newMessage.reciever = result.getString(3);
          newMessage.messageID = UUID.fromString(result.getString(4));
          newMessage.timestamp = Timestamp.valueOf(result.getString(5));
        }

        return newMessage;
      }
    } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } finally {
      // close the database connection
      if (callFunction != null) {
        try {
          callFunction.close();
          //					System.out.println("Call function closed");
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      }
    }
    return null;
  }
Пример #13
0
 public static void main(String[] args) {
   try {
     Class.forName("com.mysql.jdbc.Driver");
     Connection conn =
         DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "Harsh1408");
     CallableStatement callable = conn.prepareCall("{ call getdata(?)}");
     callable.setString(1, "cse");
     callable.executeUpdate();
     ResultSet rs = callable.getResultSet();
     while (rs.next()) {
       System.out.println(rs.getString(2) + "    " + rs.getInt(1) + "    " + rs.getString(3));
     }
   } catch (Exception e) {
     System.out.println(e);
   }
 }
 public List<PublisherDA> SelectAll() {
   List<PublisherDA> aListPublisherDA = new ArrayList<PublisherDA>();
   try {
     CallableStatement call = conn.prepareCall("{call spd_Publisher_SelectAll()}");
     call.execute();
     ResultSet rs = call.getResultSet();
     while (rs.next()) {
       PublisherDA aPublisherDA =
           new PublisherDA(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4));
       aListPublisherDA.add(aPublisherDA);
     }
   } catch (SQLException ex) {
     Logger.getLogger(PublisherBO.class.getName()).log(Level.SEVERE, null, ex);
   }
   return aListPublisherDA;
 }
Пример #15
0
  /**
   * Execute the desired Callable statement and then call dispResultSet to display the rows and
   * columns
   *
   * @param stmt CallableStatement object to be processed
   * @exception SQLException .
   */
  public void displayRows(CallableStatement stmt) throws SQLException {

    boolean results = stmt.execute();
    int rsnum = 0; // Number of Result Sets processed
    int rowsAffected = 0;
    do {
      if (results) {
        ResultSet rs = stmt.getResultSet();
        output("\n\nDisplaying ResultSet: " + rsnum + "\n");
        dispResultSet(rs);
        rsnum++;
        rs.close();
      } else {
        rowsAffected = stmt.getUpdateCount();
        if (rowsAffected >= 0) output(rowsAffected + " rows Affected.\n");
      }
      results = stmt.getMoreResults();
    } while (results || rowsAffected != -1);
  }
Пример #16
0
  public void testProc1() throws Exception {
    Statement stmt = con.createStatement();
    dropProcedure(stmt, "#spTestExec");
    dropProcedure(stmt, "#spTestExec2");

    stmt.executeUpdate(" create procedure #spTestExec2 as " + "select 'Did it work?' as Result");
    stmt.executeUpdate(
        "create procedure #spTestExec as "
            + "set nocount off "
            + "create table #tmp ( Result varchar(50) ) "
            + "insert #tmp execute #spTestExec2 "
            + "select * from #tmp");
    stmt.close();

    CallableStatement cstmt = con.prepareCall("#spTestExec");
    assertFalse(cstmt.execute());
    assertEquals(1, cstmt.getUpdateCount());

    // The JDBC-ODBC driver does not return update counts from stored
    // procedures so we won't, either.
    //
    // SAfe Yes, we will. It seems like that's how it should work. The idea
    //      however is to only return valid update counts (e.g. not from
    //      SET, EXEC or such).
    assertTrue(cstmt.getMoreResults());

    boolean passed = false;
    ResultSet rs = cstmt.getResultSet();
    while (rs.next()) {
      passed = true;
    }
    assertTrue("Expecting at least one result row", passed);
    assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == -1);
    cstmt.close();
    // stmt.executeQuery("execute spTestExec");
  }
Пример #17
0
 public List<Customer> findCustomers(String id, String match, int limit) {
   Connection connection = null;
   CallableStatement callableStatement = null;
   ResultSet resultSet = null;
   boolean hasResults;
   List<Customer> customers = new ArrayList<Customer>();
   try {
     connection = dataSource.getConnection(); // get connection from dataSource
     connection.setTransactionIsolation(
         Connection.TRANSACTION_READ_COMMITTED); // prevent dirty reads
     callableStatement = connection.prepareCall(listCustomersSql); // prepare callable statement
     if (id == null) {
       callableStatement.setNull(1, Types.INTEGER);
     } else {
       callableStatement.setInt(1, Integer.parseInt(id));
     }
     if (match == null) {
       callableStatement.setNull(2, Types.VARCHAR);
     } else {
       callableStatement.setString(2, match);
     }
     callableStatement.setInt(3, limit);
     callableStatement.setNull(4, Types.INTEGER);
     hasResults = callableStatement.execute();
     if (hasResults) {
       resultSet = callableStatement.getResultSet();
       if (resultSet.isBeforeFirst()) { // customers have been returned
         while (resultSet.next()) {
           customers.add(
               new Customer(
                   resultSet.getString("id"),
                   resultSet.getString("first_name"),
                   resultSet.getString("last_name"),
                   resultSet.getString("street_address"),
                   resultSet.getString("apt_address"),
                   resultSet.getString("city"),
                   resultSet.getString("state"),
                   resultSet.getString("zip"),
                   resultSet.getString("phone"),
                   resultSet.getString("email"),
                   resultSet.getString("notes")));
         }
       } else {
         log.debug("No customers returned.");
       }
     } else {
       log.debug("No customers returned.");
     }
   } catch (SQLException se) {
     log.error("SQL error: ", se);
     return null;
   } finally {
     try {
       resultSet.close();
     } catch (Exception se) {
       log.error("Unable to close resultSet: ", se);
     }
     try {
       callableStatement.close();
     } catch (Exception se) {
       log.error("Unable to close callableStatement: ", se);
     }
     try {
       connection.close();
     } catch (Exception se) {
       log.error("Unable to close connection: ", se);
     }
   }
   return customers;
 }
Пример #18
0
  public static void main(String[] args) {
    Connection conn = null;
    ResultSet rs = null;
    CallableStatement cStmt = null;
    String dbURL = "jdbc:mysql://localhost/data_mining_assignment";

    try {
      // Load our driver into memory
      Class.forName("com.mysql.jdbc.Driver");

      // load credentials for SQL connection
      Properties config = new Properties();
      FileInputStream in = new FileInputStream("C:\\data-minning-config.properties");
      config.load(in);

      // establish connection to DB
      conn = DriverManager.getConnection(dbURL, config);

      // Create our procedure call and execute
      cStmt = conn.prepareCall("{CALL getBinnedPoints()}");
      boolean hadResults = cStmt.execute();

      // while we have results to process
      while (hadResults) {
        // load the results into a result set
        rs = cStmt.getResultSet();
        // while data still in the result set
        while (rs.next()) {
          // read in each column
          String team = rs.getString("Team");
          String league = rs.getString("League");
          String season = rs.getString("Season");
          int spend = rs.getInt("Spend");
          int income = rs.getInt("Income");
          int nett = rs.getInt("Nett");
          String points = rs.getString("Points");
          int position = rs.getInt("Position");

          // just print the data for now
          System.out.print("Team: " + team);
          System.out.print(" League: " + league);
          System.out.print(" Season: " + season);
          System.out.print(" Spend: " + spend);
          System.out.print(" Income: " + income);
          System.out.print(" Nett: " + nett);
          System.out.print(" Points: " + points);
          System.out.println(" Position: " + position);
        }
        // check if the procedure returns any more results... it never will :p
        hadResults = cStmt.getMoreResults();
      }

    }
    // catch any SQL exceptions
    catch (SQLException ex) {
      System.out.println("SQLException: " + ex.getMessage());
      System.out.println("SQLState: " + ex.getSQLState());
      System.out.println("VendorError: " + ex.getErrorCode());
    }
    // catch any exception with loading driver
    catch (Exception e) {
      e.printStackTrace();
    }
    // clean up
    finally {
      // close the result set
      if (rs != null) {
        try {
          rs.close();
          System.out.println("Closed Result Set");
        } catch (SQLException sqlEx) {
        }
      }
      rs = null;
      // close our procedure
      if (cStmt != null) {
        try {
          cStmt.close();
          System.out.println("Closed Statment");
        } catch (SQLException sqlEx) {
        }
        cStmt = null;
      }
      // close our connection
      if (conn != null) {
        try {
          conn.close();
          System.out.println("Closed Connection");
        } catch (SQLException sqlEx) {
        }
        conn = null;
      }
    }
  }
  private void updateCustomerInfo(ResultSet rs, String mode) throws OptInCustomerException {

    String insertSQL =
        "INSERT INTO DAOptInCustomer ("
            + " CAPNumber, BusinessName1, Address1, Address2, Address3, "
            + " Address4, City, State, PostalCode, Country, "
            + " Contact, EMailAddress, "
            + " OptInStatus, LastStatusUpdateDt, SiteAdminCount, "
            + " AbeType, LastUpdateUserId, LastUpdateDt, BusinessName2, Optinattempt )"
            + " VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'N', getDate(), 0, 'A', 'javaint', getDate(), ?, 0 )";
    String updateSQL =
        "UPDATE DAOptInCustomer "
            + "SET BusinessName1 = ?, Address1=?, Address2=?, Address3=?, Address4=?, "
            + " City=?, State=?, PostalCode=?, Country=?, Contact=?, EmailAddress=?, "
            + " LastUpdateUserId='javaint', LastUpdateDt=getDate(), BusinessName2=? "
            + "  WHERE CAPNumber=? ";
    String addressID = null;
    String auAddressSQL = null;
    String addr1 = null;
    String addr2 = null;
    String addr3 = null;
    String addr4 = null;
    String city = null;
    String state = null;
    String zip = null;
    String country = null;
    String name = null;

    int totalCount = 0;

    PreparedStatement pstmt = null;
    PreparedStatement pstmtAddr = null;

    /* Get Address Details using SP lpp_get_address */
    PreparedStatement psLAPAUAddressDetails = null;

    ResultSet rsAddress = null;
    ResultSet rsSP = null;

    CallableStatement cstmt = null;

    try {
      _appLog.writeAuditLog(
          "Retrieving Address details for each AU and Updating DAOptInCustomer table ..");
      cstmt = _ifxConn.prepareCall("{call lpp_get_address(?,?,?)}");
      if (mode.equalsIgnoreCase("new")) {
        pstmt = _webAppConn.prepareStatement(insertSQL);
      } else {
        pstmt = _webAppConn.prepareStatement(updateSQL);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }

    try {
      while (rs.next()) {
        try {
          totalCount++;

          // Set the au and CAPID
          cstmt.setInt(1, rs.getInt(1));
          cstmt.setTimestamp(2, new Timestamp(new java.util.Date().getTime()));
          cstmt.setString(3, "M");

          cstmt.execute();
          rsSP = cstmt.getResultSet();

          if (totalCount % 2000 == 0) {
            System.out.println("Processed " + totalCount + " Rows..");
          }

          if (rsSP.next()) {
            addressID = rsSP.getString(3);

            /* Using addressID get address information from ptt_Address table */
            auAddressSQL =
                "SELECT a.addr_1_t, a.addr_2_t, a.addr_3_t, "
                    + " a.addr_4_t, a.city_t, a.state_c, a.zip_t, p.value_t "
                    + " FROM ptt_address a, outer ptt_standard_codes p "
                    + " WHERE a.country_c = p.key_u and p.table_u = 10 and "
                    + " current between p.effective_dt and p.termination_dt and "
                    + " a.address_u = "
                    + addressID;
            pstmtAddr = _ifxConn.prepareStatement(auAddressSQL);
            rsAddress = pstmtAddr.executeQuery();
            if (rsAddress.next()) {
              if (rsAddress.getString(1) != null) {
                addr1 = rsAddress.getString(1).trim();
              } else {
                System.out.println("ADDRESS1 VALUE WAS NULL for address ID: " + addressID);
                addr1 = "";
              }

              if (rsAddress.getString(2) != null) {
                addr2 = rsAddress.getString(2).trim();
              } else {
                System.out.println("ADDRESS2 VALUE WAS NULL for address ID: " + addressID);
                addr2 = "";
              }

              if (rsAddress.getString(3) != null) {
                addr3 = rsAddress.getString(3).trim();
              } else {
                System.out.println("ADDRESS3 VALUE WAS NULL for address ID: " + addressID);
                addr3 = "";
              }

              if (rsAddress.getString(4) != null) {
                addr4 = rsAddress.getString(4).trim();
              } else {
                System.out.println("ADDRESS4 VALUE WAS NULL for address ID: " + addressID);
                addr4 = "";
              }

              if (rsAddress.getString(5) != null) {
                city = rsAddress.getString(5).trim();
              } else {
                System.out.println("CITY VALUE WAS NULL for address ID: " + addressID);
                city = "";
              }

              if (rsAddress.getString(6) != null) {
                state = rsAddress.getString(6).trim();
              } else {
                System.out.println("STATE VALUE WAS NULL for address ID: " + addressID);
                state = "";
              }

              if (rsAddress.getString(7) != null) {
                zip = rsAddress.getString(7).trim();
              } else {
                System.out.println("ZIP VALUE WAS NULL for address ID: " + addressID);
                zip = "";
              }

              if (rsAddress.getString(8) != null) {
                country = rsAddress.getString(8).trim();
              } else {
                country = "United States";
              }

              // If Country is United States, insert an empty space
              if (country.equalsIgnoreCase("united states") || country.equalsIgnoreCase("usa"))
                country = "";
              // Name Formatting
              if (rs.getString(6) != null && rs.getString(5) != null && rs.getString(7) != null) {
                if (rs.getString(6).trim().length() > 0)
                  name =
                      rs.getString(5).trim()
                          + " "
                          + rs.getString(6).trim()
                          + ". "
                          + rs.getString(7).trim();
                else name = rs.getString(5).trim() + " " + rs.getString(7).trim();
              } else name = rs.getString(5).trim() + " " + rs.getString(7).trim();
              // Adding Credentials
              if (rs.getString(10) != null) {
                if (rs.getString(10).trim().length() > 0)
                  name = name + ", " + rs.getString(10).trim();
              }

              updateData.setCAPID(rs.getString(2));
              updateData.setAddress1(addr1);
              updateData.setAddress2(addr2);
              updateData.setAddress3(addr3);
              updateData.setAddress4(addr4);
              updateData.setCity(city);
              updateData.setState(state);
              updateData.setZip(zip);
              updateData.setCountry(country);
              updateData.setCompanyName(rs.getString(3));
              updateData.setContactName(name);
              updateData.setEmail(rs.getString(8));
              updateData.setBusinessName2(rs.getString(9));
              updateData.setAbeType("A"); // Not used here

              // Insert mode (add new AUs)
              if (mode.equalsIgnoreCase("new")) {
                System.out.println("Inserting the row for : " + updateData.getCAPID());
                pstmt.setString(1, updateData.getCAPID()); // -- CAPID
                pstmt.setString(2, updateData.getCompanyName()); // -- CompanyName
                pstmt.setString(3, updateData.getAddress1()); // -- Address1
                pstmt.setString(4, updateData.getAddress2()); // -- Address2
                pstmt.setString(5, updateData.getAddress3()); // -- Address3
                pstmt.setString(6, updateData.getAddress4()); // -- Address4
                pstmt.setString(7, updateData.getCity()); // -- City
                pstmt.setString(8, updateData.getState()); // -- State
                pstmt.setString(9, updateData.getZip()); // -- Zip
                pstmt.setString(10, updateData.getCountry()); // -- Country
                pstmt.setString(11, updateData.getContactName()); // -- Contact Name
                pstmt.setString(12, updateData.getEmail()); // -- Email
                pstmt.setString(13, updateData.getBusinessName2()); // -- BusinessName2
                pstmt.executeUpdate();
              }
              // Update mode (refresh data for existing AUs)
              else {
                pstmt.setString(1, updateData.getCompanyName()); // -- Company name
                pstmt.setString(2, updateData.getAddress1()); // -- Address1
                pstmt.setString(3, updateData.getAddress2()); // -- Address2
                pstmt.setString(4, updateData.getAddress3()); // -- Address3
                pstmt.setString(5, updateData.getAddress4()); // -- Address4
                pstmt.setString(6, updateData.getCity()); // -- City
                pstmt.setString(7, updateData.getState()); // -- State
                pstmt.setString(8, updateData.getZip()); // -- Zip
                pstmt.setString(9, updateData.getCountry()); // -- Country
                pstmt.setString(10, updateData.getContactName()); // -- Contact Name
                pstmt.setString(11, updateData.getEmail()); // -- Email
                pstmt.setString(12, updateData.getBusinessName2()); // -- BusinessName2
                pstmt.setString(13, updateData.getCAPID()); // -- CAPID
                pstmt.executeUpdate();
              }
            }
          }
        } catch (SQLException e) {
          if (e.getErrorCode() == 2627) {
            // user Exists in the table. Update the record
            try {
              _appLog.writeAuditLog(
                  "LAP Customer:Updated the status:" + "CAPID=" + updateData.getCAPID());
              pstmt = _webAppConn.prepareStatement(updateSQL);
              pstmt.setString(1, updateData.getCompanyName()); // -- Company name
              pstmt.setString(2, updateData.getAddress1()); // -- Address1
              pstmt.setString(3, updateData.getAddress2()); // -- Address2
              pstmt.setString(4, updateData.getAddress3()); // -- Address3
              pstmt.setString(5, updateData.getAddress4()); // -- Address4
              pstmt.setString(6, updateData.getCity()); // -- City
              pstmt.setString(7, updateData.getState()); // -- State
              pstmt.setString(8, updateData.getZip()); // -- Zip
              pstmt.setString(9, updateData.getCountry()); // -- Country
              pstmt.setString(10, updateData.getContactName()); // -- Contact Name
              pstmt.setString(11, updateData.getEmail()); // -- Email
              pstmt.setString(12, updateData.getBusinessName2()); // -- BusinessName2
              pstmt.setString(13, updateData.getCAPID()); // -- CAPID
              pstmt.executeUpdate();
              // Set the prepared statement back to the new mode
              pstmt = _webAppConn.prepareStatement(insertSQL);
              // Now Update the AbeType
              String sqlString =
                  "Update dbo.DAOptInCustomer set AbeType='A' where CAPNumber = '"
                      + updateData.getCAPID()
                      + "'";
              PreparedStatement p = _webAppConn.prepareStatement(sqlString);
              p.executeUpdate();
              p.close();
            } catch (SQLException f) {
              System.out.println("Error: Updating DAOptInCustomer table....");
              f.printStackTrace();
              throw new OptInCustomerException(f);
            }

          } else {
            e.printStackTrace();
            throw new OptInCustomerException(e);
          }
        }
      }
      if (mode.equalsIgnoreCase("refresh"))
        _appLog.writeAuditLog(
            "Part -- I Complete : Updated address and contact information for "
                + totalCount
                + " AUs in DAOptInCustomer table..");
      else
        _appLog.writeAuditLog(
            "Part -- II Complete : Inserted " + totalCount + " new AUs in DAOptInCustomer table..");

    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
Пример #20
0
  /**
   * WL#6406 - Stacked diagnostic areas
   *
   * <p>"STACKED" in "GET [CURRENT | STACKED] DIAGNOSTICS" syntax was added in 5.7.0. Final behavior
   * was implemented in version 5.7.2, by WL#5928 - Most statements should clear the diagnostic
   * area.
   *
   * @throws SQLException
   */
  public void testGetStackedDiagnostics() throws Exception {

    if (!versionMeetsMinimum(5, 7, 2)) {
      return;
    }

    // test calling GET STACKED DIAGNOSTICS outside an handler
    final Statement locallyScopedStmt = this.stmt;
    assertThrows(
        SQLException.class,
        "GET STACKED DIAGNOSTICS when handler not active",
        new Callable<Void>() {
          public Void call() throws Exception {
            locallyScopedStmt.executeQuery("GET STACKED DIAGNOSTICS @num = NUMBER");
            return null;
          }
        });

    // test calling GET STACKED DIAGNOSTICS inside an handler
    // (stored procedure is based on documentation example)
    createTable("testGetStackedDiagnosticsTbl", "(c VARCHAR(8) NOT NULL)");
    createProcedure(
        "testGetStackedDiagnosticsSP",
        "() BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN "
            + "GET CURRENT DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; "
            + "SELECT 'current DA before insert in handler' AS op, @errno AS errno, @msg AS msg; " // 1st result
            + "GET STACKED DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; "
            + "SELECT 'stacked DA before insert in handler' AS op, @errno AS errno, @msg AS msg; " // 2nd result
            + "INSERT INTO testGetStackedDiagnosticsTbl (c) VALUES('gnitset'); "
            + "GET CURRENT DIAGNOSTICS @num = NUMBER; "
            + "IF @num = 0 THEN SELECT 'INSERT succeeded, current DA is empty' AS op; " // 3rd
                                                                                        // result
            + "ELSE GET CURRENT DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; "
            + "SELECT 'current DA after insert in handler' AS op, @errno AS errno, @msg AS msg; END IF; "
            + "GET STACKED DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; "
            + "SELECT 'stacked DA after insert in handler' AS op, @errno AS errno, @msg AS msg; END; " // 4th result
            + "INSERT INTO testGetStackedDiagnosticsTbl (c) VALUES ('testing');INSERT INTO testGetStackedDiagnosticsTbl (c) VALUES (NULL); END");

    CallableStatement cStmt = this.conn.prepareCall("CALL testGetStackedDiagnosticsSP()");
    assertTrue(cStmt.execute());

    // test 1st ResultSet
    this.rs = cStmt.getResultSet();
    assertTrue(this.rs.next());
    assertEquals("current DA before insert in handler", this.rs.getString(1));
    assertEquals(1048, this.rs.getInt(2));
    assertEquals("Column 'c' cannot be null", this.rs.getString(3));
    assertFalse(this.rs.next());
    this.rs.close();

    // test 2nd ResultSet
    assertTrue(cStmt.getMoreResults());
    this.rs = cStmt.getResultSet();
    assertTrue(this.rs.next());
    assertEquals("stacked DA before insert in handler", this.rs.getString(1));
    assertEquals(1048, this.rs.getInt(2));
    assertEquals("Column 'c' cannot be null", this.rs.getString(3));
    assertFalse(this.rs.next());
    this.rs.close();

    // test 3rd ResultSet
    assertTrue(cStmt.getMoreResults());
    this.rs = cStmt.getResultSet();
    assertTrue(this.rs.next());
    assertEquals("INSERT succeeded, current DA is empty", this.rs.getString(1));
    assertFalse(this.rs.next());
    this.rs.close();

    // test 4th ResultSet
    assertTrue(cStmt.getMoreResults());
    this.rs = cStmt.getResultSet();
    assertTrue(this.rs.next());
    assertEquals("stacked DA after insert in handler", this.rs.getString(1));
    assertEquals(1048, this.rs.getInt(2));
    assertEquals("Column 'c' cannot be null", this.rs.getString(3));
    assertFalse(this.rs.next());
    this.rs.close();

    // no more ResultSets
    assertFalse(cStmt.getMoreResults());
    cStmt.close();

    // test table contents
    this.rs = this.stmt.executeQuery("SELECT * FROM testGetStackedDiagnosticsTbl");
    assertTrue(this.rs.next());
    assertEquals("testing", this.rs.getString(1));
    assertTrue(this.rs.next());
    assertEquals("gnitset", this.rs.getString(1));
    assertFalse(this.rs.next());
    this.rs.close();
  }
Пример #21
0
 /** @see java.sql.Statement#getResultSet() */
 public ResultSet getResultSet() throws SQLException {
   return original.getResultSet();
 }
  public void _jspService(
      final javax.servlet.http.HttpServletRequest request,
      final javax.servlet.http.HttpServletResponse response)
      throws java.io.IOException, javax.servlet.ServletException {

    final java.lang.String _jspx_method = request.getMethod();
    if (!"GET".equals(_jspx_method)
        && !"POST".equals(_jspx_method)
        && !"HEAD".equals(_jspx_method)
        && !javax.servlet.DispatcherType.ERROR.equals(request.getDispatcherType())) {
      response.sendError(
          HttpServletResponse.SC_METHOD_NOT_ALLOWED, "JSPs only permit GET POST or HEAD");
      return;
    }

    final javax.servlet.jsp.PageContext pageContext;
    javax.servlet.http.HttpSession session = null;
    final javax.servlet.ServletContext application;
    final javax.servlet.ServletConfig config;
    javax.servlet.jsp.JspWriter out = null;
    final java.lang.Object page = this;
    javax.servlet.jsp.JspWriter _jspx_out = null;
    javax.servlet.jsp.PageContext _jspx_page_context = null;

    try {
      response.setContentType("text/html");
      pageContext = _jspxFactory.getPageContext(this, request, response, null, true, 8192, true);
      _jspx_page_context = pageContext;
      application = pageContext.getServletContext();
      config = pageContext.getServletConfig();
      session = pageContext.getSession();
      out = pageContext.getOut();
      _jspx_out = out;

      out.write('\r');
      out.write('\n');

      String username = "";
      if (session.getAttribute("login") != null) {
        username = session.getAttribute("login").toString();
      } else {
        out.println("Invalid session! You must log back into the system.");
        return;
      }

      String outputMsg = "TODO";

      ResultSet employeeRevRes = null;

      String JDBC_DRIVER = "com.mysql.jdbc.Driver";
      String DB_URL = "jdbc:mysql://localhost:3306/SilkRoad 5.0";
      String USER = "******";
      String PASS = "******";

      Statement stmt = null;
      String sql = null;
      Connection conn = null;
      CallableStatement cs = null;
      try {
        // Register JDBC driver
        Class.forName(JDBC_DRIVER).newInstance();

        // Open a connection
        conn = java.sql.DriverManager.getConnection(DB_URL, USER, PASS);

        System.out.println("HERE");
        cs = conn.prepareCall("call GetEmployeeRevenues()");
        cs.execute();
        employeeRevRes = cs.getResultSet();

        // TODO. Error handling

        out.write("\r\n");
        out.write(" <html lang=\"en\">\r\n");
        out.write("  <head>\r\n");
        out.write("   <meta charset=\"utf-8\">\r\n");
        out.write("    <meta http-equiv=\"X-UA-Compatible\" content=\"IE=edge\">\r\n");
        out.write(
            "     <meta name=\"viewport\" content=\"width=device-width, initial-scale=1\">\r\n");
        out.write(
            "      <!-- The above 3 meta tags *must* come first in the head; any other head content must come *after* these tags -->\r\n");
        out.write("      <title>Silk Road 5.0</title>\r\n");
        out.write("      <!-- Bootstrap -->\r\n");
        out.write("      <link href=\"css/bootstrap.min.css\" rel=\"stylesheet\">\r\n");
        out.write("       ");
        out.write("\r\n");
        out.write(
            "       <link href=\"css/responsive.bootstrap.min.css\" rel=\"stylesheet\" type=\"text/css\">\r\n");
        out.write(
            "        <link href=\"https://cdnjs.cloudflare.com/ajax/libs/bootstrap3-dialog/1.34.5/css/bootstrap-dialog.min.css\" rel=\"stylesheet\" type=\"text/css\" />\r\n");
        out.write("        <!-- Our own custom css -->\r\n");
        out.write(
            "        <link href=\"css/stylesheet.css\" rel=\"stylesheet\" type=\"text/css\">\r\n");
        out.write(
            "         <!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->\r\n");
        out.write(
            "         <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->\r\n");
        out.write("         <!--[if lt IE 9]>\r\n");
        out.write(
            "         <script src=\"https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js\"></script>\r\n");
        out.write(
            "         <script src=\"https://oss.maxcdn.com/respond/1.4.2/respond.min.js\"></script>\r\n");
        out.write("         <![endif]-->\r\n");
        out.write("         <!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->\r\n");
        out.write("         <script src=\"js/jquery-1.11.3.min.js\"></script>\r\n");
        out.write("         <script src=\"js/jquery.validate.js\"></script>\r\n");
        out.write("         ");
        out.write("\r\n");
        out.write(
            "         <!-- Include all compiled plugins (below), or include individual files as needed -->\r\n");
        out.write("         <script src=\"js/bootstrap.min.js\"></script>\r\n");
        out.write(
            "         <script src=\"https://cdnjs.cloudflare.com/ajax/libs/bootstrap3-dialog/1.34.5/js/bootstrap-dialog.min.js\"></script>\r\n");
        out.write("         <script src=\"js/pattern.js\"></script>\r\n");
        out.write("         <script src=\"js/script.js\"></script>\r\n");
        out.write("         <script src=\"js/editCustomer.js\"></script>\r\n");
        out.write("        </head>\r\n");
        out.write("        <nav class=\"navbar\">\r\n");
        out.write("         <div class=\"container-fluid\">\r\n");
        out.write("          <!-- Brand and toggle get grouped for better mobile display -->\r\n");
        out.write("          <div class=\"navbar-header\">\r\n");
        out.write(
            "           <button type=\"button\" class=\"navbar-toggle collapsed\" data-toggle=\"collapse\" data-target=\"#bs-example-navbar-collapse-1\" aria-expanded=\"false\">\r\n");
        out.write("            <span class=\"sr-only\">Toggle navigation</span>\r\n");
        out.write("            <span class=\"icon-bar\"></span>\r\n");
        out.write("            <span class=\"icon-bar\"></span>\r\n");
        out.write("            <span class=\"icon-bar\"></span>\r\n");
        out.write("           </button>\r\n");
        out.write("          </div>\r\n");
        out.write("          <!-- navbar-header -->\r\n");
        out.write(
            "          <!-- Collect the nav links, forms, and other content for toggling -->\r\n");
        out.write("          <div class=\"myNavbar\">\r\n");
        out.write("           <ul class=\"nav\">\r\n");
        out.write(
            "            <li class=\"floatLeft\"><a href=\"ManagerInformation.jsp\">Home</a></li>\r\n");
        out.write(
            "            <li class=\"dropdown navbar-right\" style=\"padding-left:125px;\">\r\n");
        out.write(
            "             <a data-target=\"#collapseHelp\" data-toggle=\"collapse\">Help<span class=\"caret\"></span></a>\r\n");
        out.write("             <ul>\r\n");
        out.write("              <div id=\"collapseHelp\" class=\"dropdown-menu\">\r\n");
        out.write(
            "               <li><a href=\"javascript:showEmployeeScreenHelp()\">Screens</a></li>\r\n");
        out.write("               <br>\r\n");
        out.write(
            "                <li><a href=\"javascript:showAuctionHelp()\">Auctions</a></li>\r\n");
        out.write("                <br>\r\n");
        out.write("                </div>\r\n");
        out.write("               </ul>\r\n");
        out.write("              </li>\r\n");
        out.write(
            "              <li class=\"dropdown navbar-right\" style=\"padding-left:200px;\">\r\n");
        out.write(
            "               <a data-target=\"#collapseMenu\" data-toggle=\"collapse\" >Menu<span class=\"caret\"></span></a>\r\n");
        out.write("               <ul>\r\n");
        out.write("                <div id=\"collapseMenu\" class=\"dropdown-menu\">\r\n");
        out.write("                  </div>\r\n");
        out.write("                 </ul>\r\n");
        out.write("                </li>\r\n");
        out.write("               </ul>\r\n");
        out.write("               <!-- .nav -->\r\n");
        out.write("              </div>\r\n");
        out.write("              <!-- .myNavbar -->\r\n");
        out.write("             </div>\r\n");
        out.write("             <!-- .container-fluid -->\r\n");
        out.write("            </nav>\r\n");
        out.write("            <body class=\"auctionHouseBody\">\r\n");
        out.write(
            "             <h4 class=\"auctionTableHeader\">Highest Grossing Employee</h4>\r\n");
        out.write(
            "             <table id =\"bestSellersTable\" class=\"table table-striped table-bordered dt-responsive nowrap auctionHouseTable\">\r\n");
        out.write("              <thead>\r\n");
        out.write("               <tr>\r\n");
        out.write("                <th>Employee ID</th>\r\n");
        out.write("                <th>Username</th>\r\n");
        out.write("                <th>Revenue</th>\r\n");
        out.write("               </tr>\r\n");
        out.write("              </thead>\r\n");
        out.write("              <tbody>\r\n");
        out.write("               ");
        while (employeeRevRes.next()) {
          out.write("\r\n");
          out.write("               <tr>\r\n");
          out.write("                <td>\r\n");
          out.write("                 ");
          out.print(employeeRevRes.getString("EmployeeID"));
          out.write("\r\n");
          out.write("                </td>\r\n");
          out.write("                <td>\r\n");
          out.write("                 ");
          out.print(employeeRevRes.getString("Username"));
          out.write("\r\n");
          out.write("                </td>\r\n");
          out.write("                <td>\r\n");
          out.write("                 ");
          out.print(employeeRevRes.getDouble("Revenue"));
          out.write("\r\n");
          out.write("                </td>\r\n");
          out.write("               </tr>\r\n");
          out.write("               ");
        }
        out.write("\r\n");
        out.write("              </tbody>\r\n");
        out.write("             </table>\r\n");
        out.write("            </body>\r\n");
        out.write("           </html>\r\n");
        out.write("\r\n");
        out.write("           ");

      } catch (Exception e) {
        e.printStackTrace();
      } finally {
        try {
          conn.close();
        } catch (Exception ee) {
        }
        ;
      }

      out.write('\r');
      out.write('\n');
    } catch (java.lang.Throwable t) {
      if (!(t instanceof javax.servlet.jsp.SkipPageException)) {
        out = _jspx_out;
        if (out != null && out.getBufferSize() != 0)
          try {
            if (response.isCommitted()) {
              out.flush();
            } else {
              out.clearBuffer();
            }
          } catch (java.io.IOException e) {
          }
        if (_jspx_page_context != null) _jspx_page_context.handlePageException(t);
        else throw new ServletException(t);
      }
    } finally {
      _jspxFactory.releasePageContext(_jspx_page_context);
    }
  }
Пример #23
0
  @Override
  public void actionPerformed(ActionEvent e) {
    // TODO Auto-generated method stub

    String url = "jdbc:oracle:thin:skui/[email protected]:1521:etudom";
    Connection co = OutilsJDBC.openConnection(url);
    // Cas du bouton quitter
    if (e.getActionCommand() == "Quitter") {
      vueDepart vue;
      vue = new vueDepart();
      // Fermeture de la connexion de la base de données
      try {
        co.close();
      } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
      }
      this.dispose();
    }

    // _________________PROBLEME A CE NIVEAU___________________
    // Cas de la requete 1
    if (e.getActionCommand() == "Requete 1") {
      try {
        this.labelhaut.setText(titreRequete[0]);
        String sql = "{? = call nbEtudiantAvecStage}";
        System.out.println(sql);
        CallableStatement call = co.prepareCall(sql);
        call.registerOutParameter(1, java.sql.Types.INTEGER);
        call.execute();
        // récupération des ResultSet
        ResultSet resultat1 = call.getResultSet();
        System.out.println(resultat1.getInt(1));
      } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
      }
    }

    // Cas de la requete 2
    if (e.getActionCommand() == "Requete 2") {
      this.labelhaut.setText(titreRequete[1]);

      String sql = "{?=call nbEtudiantSansStage()}";
      try {
        CallableStatement call = co.prepareCall(sql);
        call.registerOutParameter(1, java.sql.Types.INTEGER);
        if (call.execute()) {
          // récupération des ResultSet
          ResultSet resultat1 = call.getResultSet();
          while (resultat1.next()) {
            for (int i = 0; i < resultat1.getMetaData().getColumnCount(); i++) {
              System.out.print(resultat1.getObject(i + 1) + ", ");
              System.out.println(resultat1.getInt(2));
            }
          }
        }
      } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
      }
    }

    // Cas de la requete 3
    if (e.getActionCommand() == "Requete 3") {
      this.labelhaut.setText(titreRequete[2]);

      String sql = "{call nbEtudiantSansStageAnnee(?)}";
      try {
        CallableStatement call = co.prepareCall(sql);
        call.setObject(1, 5);
        if (call.execute()) {
          // récupération des ResultSet
          ResultSet resultat1 = call.getResultSet();
          while (resultat1.next()) {
            for (int i = 0; i < resultat1.getMetaData().getColumnCount(); i++) {
              System.out.print(resultat1.getObject(i + 1) + ", ");
            }
          }
        }
      } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
      }
    }

    // Cas de la requete 4
    if (e.getActionCommand() == "Requete 4") {
      this.labelhaut.setText(titreRequete[3]);

      String sql = "{call nbStagiaireParEntreprise(?,?)}";

      try {
        CallableStatement call = co.prepareCall(sql);
        call.setObject(1, 5);
        if (call.execute()) {
          // récupération des ResultSet
          ResultSet resultat1 = call.getResultSet();
          while (resultat1.next()) {
            for (int i = 0; i < resultat1.getMetaData().getColumnCount(); i++) {
              System.out.print(resultat1.getObject(i + 1) + ", ");
            }
          }
        }
      } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
      }
    }

    // Cas de la requete 5
    if (e.getActionCommand() == "Requete 5") {
      this.labelhaut.setText(titreRequete[4]);

      String sql = "{call nbMoyenEtudiantsDansEntreprise(?)}";
      try {
        CallableStatement call = co.prepareCall(sql);
        call.setObject(1, 5);
        if (call.execute()) {
          // récupération des ResultSet
          ResultSet resultat1 = call.getResultSet();
          while (resultat1.next()) {
            for (int i = 0; i < resultat1.getMetaData().getColumnCount(); i++) {
              System.out.print(resultat1.getObject(i + 1) + ", ");
            }
          }
        }
      } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
      }
    }

    // Cas de la requete 6
    if (e.getActionCommand() == "Requete 6") {
      this.labelhaut.setText(titreRequete[5]);

      String sql = "{call nbStagesParZone(?,?)}";
      try {
        CallableStatement call = co.prepareCall(sql);
        call.setObject(1, "Orsay");
        call.setObject(2, "91200");
        if (call.execute()) {
          // récupération des ResultSet
          ResultSet resultat1 = call.getResultSet();
          while (resultat1.next()) {
            for (int i = 0; i < resultat1.getMetaData().getColumnCount(); i++) {
              System.out.print(resultat1.getObject(i + 1) + ", ");
            }
          }
        }
      } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
      }
    }

    // Cas de la requete 7
    if (e.getActionCommand() == "Requete 7") {
      this.labelhaut.setText(titreRequete[6]);

      String sql = "{call nbStagiaireZone()}";
      try {
        CallableStatement call = co.prepareCall(sql);
        call.registerOutParameter("nb", java.sql.Types.INTEGER);
        if (call.execute()) {
          // récupération des ResultSet
          ResultSet resultat1 = call.getResultSet();
          while (resultat1.next()) {
            for (int i = 0; i < resultat1.getMetaData().getColumnCount(); i++) {
              System.out.print(resultat1.getObject(i + 1) + ", ");
            }
          }
        }
      } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
      }
    }

    // Cas de la requete 8
    if (e.getActionCommand() == "Requete 8") {
      this.labelhaut.setText(titreRequete[7]);

      String sql = "{call contactEntreprise(?)}";
      try {
        CallableStatement call = co.prepareCall(sql);
        call.setObject(1, 5);
        if (call.execute()) {
          // récupération des ResultSet
          ResultSet resultat1 = call.getResultSet();
          while (resultat1.next()) {
            for (int i = 0; i < resultat1.getMetaData().getColumnCount(); i++) {
              System.out.print(resultat1.getObject(i + 1) + ", ");
            }
          }
        }
      } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
      }
    }
  }
Пример #24
0
 public Customer updateCustomer(Customer customer) {
   Connection connection = null;
   CallableStatement callableStatement = null;
   ResultSet resultSet = null;
   boolean hasResults;
   String id;
   try {
     connection = dataSource.getConnection(); // get connection from dataSource
     callableStatement = connection.prepareCall(updateCustomerSql); // prepare callable statement
     id = customer.getId();
     if (id == null) {
       callableStatement.setNull(1, Types.INTEGER);
     } else {
       callableStatement.setInt(1, Integer.parseInt(id));
     }
     if (customer.isInactive()) {
       callableStatement.setNull(2, Types.VARCHAR);
       callableStatement.setNull(3, Types.VARCHAR);
       callableStatement.setNull(4, Types.VARCHAR);
       callableStatement.setNull(5, Types.VARCHAR);
       callableStatement.setNull(6, Types.VARCHAR);
       callableStatement.setNull(7, Types.VARCHAR);
       callableStatement.setNull(8, Types.VARCHAR);
       callableStatement.setNull(9, Types.VARCHAR);
       callableStatement.setNull(10, Types.VARCHAR);
       callableStatement.setNull(11, Types.VARCHAR);
       callableStatement.setByte(12, (byte) 1);
     } else {
       callableStatement.setString(2, customer.getFirstName());
       callableStatement.setString(3, customer.getLastName());
       callableStatement.setString(4, customer.getStreetAddress());
       callableStatement.setString(5, customer.getAptAddress());
       callableStatement.setString(6, customer.getCity());
       callableStatement.setString(7, customer.getState());
       callableStatement.setString(8, customer.getZip());
       callableStatement.setString(9, customer.getPhone());
       callableStatement.setString(10, customer.getEmail());
       callableStatement.setString(11, customer.getNotes());
       callableStatement.setByte(12, (byte) 0);
     }
     hasResults = callableStatement.execute();
     if (hasResults) {
       resultSet = callableStatement.getResultSet();
       if (resultSet.next()) {
         customer.setId(resultSet.getString(1));
       } else {
         throw new SQLException("Unable to update customer.");
       }
     } else {
       throw new SQLException("Unable to update customer.");
     }
   } catch (SQLException se) {
     log.error("SQL error: ", se);
     return null;
   } finally {
     try {
       resultSet.close();
     } catch (Exception se) {
       log.error("Unable to close resultSet: ", se);
     }
     try {
       callableStatement.close();
     } catch (Exception se) {
       log.error("Unable to close callableStatement: ", se);
     }
     try {
       connection.close();
     } catch (Exception se) {
       log.error("Unable to close connection: ", se);
     }
   }
   return customer;
 }
Пример #25
0
  public void testProc2() throws Exception {
    Statement stmt = con.createStatement();
    String sqlwithcount =
        "create procedure #multi1withcount as "
            + "  set nocount off "
            + "  select 'a' "
            + "  select 'b' "
            + "  create table #multi1withcountt (A VARCHAR(20)) "
            + "  insert into #multi1withcountt VALUES ('a') "
            + "  insert into #multi1withcountt VALUES ('a') "
            + "  insert into #multi1withcountt VALUES ('a') "
            + "  select 'a' "
            + "  select 'b' ";
    String sqlnocount =
        "create procedure #multi1nocount as "
            + "  set nocount on "
            + "  select 'a' "
            + "  select 'b' "
            + "  create table #multi1nocountt (A VARCHAR(20)) "
            + "  insert into #multi1nocountt VALUES ('a') "
            + "  insert into #multi1nocountt VALUES ('a') "
            + "  insert into #multi1nocountt VALUES ('a') "
            + "  select 'a' "
            + "  select 'b' ";
    dropProcedure(stmt, "#multi1withcount");
    dropProcedure(stmt, "#multi1nocount");
    stmt.executeUpdate(sqlwithcount);
    stmt.executeUpdate(sqlnocount);
    stmt.close();

    CallableStatement cstmt = con.prepareCall("#multi1nocount");
    assertTrue(cstmt.execute());
    ResultSet rs = cstmt.getResultSet();
    assertTrue(rs.next());
    assertTrue(rs.getString(1).equals("a"));
    assertTrue(!rs.next());
    assertTrue(cstmt.getMoreResults());
    rs = cstmt.getResultSet();
    assertTrue(rs.next());
    assertTrue(rs.getString(1).equals("b"));
    assertTrue(!rs.next());
    assertTrue(cstmt.getMoreResults());
    rs = cstmt.getResultSet();
    assertTrue(rs.next());
    assertTrue(!rs.next());
    assertTrue(cstmt.getMoreResults());
    rs = cstmt.getResultSet();
    assertTrue(rs.next());
    assertTrue(!rs.next());
    assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == -1);
    cstmt.close();

    cstmt = con.prepareCall("#multi1withcount");

    // The JDBC-ODBC driver does not return update counts from stored
    // procedures so we won't, either.
    //
    // SAfe Yes, we will. It seems like that's how it should work. The idea
    //      however is to only return valid update counts (e.g. not from
    //      SET, EXEC or such).
    assertTrue(cstmt.execute());
    rs = cstmt.getResultSet();
    assertTrue(rs.next());
    assertTrue(rs.getString(1).equals("a"));
    assertTrue(!rs.next());
    assertTrue(cstmt.getMoreResults());
    rs = cstmt.getResultSet();
    assertTrue(rs.next());
    assertTrue(rs.getString(1).equals("b"));
    assertTrue(!rs.next());
    assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == 1); // insert
    assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == 1); // insert
    assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == 1); // insert
    assertTrue(cstmt.getMoreResults()); // select
    rs = cstmt.getResultSet();
    assertTrue(rs.next());
    assertTrue(!rs.next());
    assertTrue(cstmt.getMoreResults());
    rs = cstmt.getResultSet();
    assertTrue(rs.next());
    assertTrue(!rs.next());
    assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == -1);
    cstmt.close();
  }
Пример #26
0
 protected Object doGetResultSet(
     SPDataSetValueBean spBean, Object typeObj, StringBuffer systemParamsBuf) {
   log.debug(systemParamsBuf.toString());
   String procedure = spBean.getProcedure();
   if (rbean.getInterceptor() != null) {
     Object obj = rbean.getInterceptor().beforeLoadData(rrequest, rbean, typeObj, procedure);
     if (!(obj instanceof String)) {
       return obj;
     }
     procedure = (String) obj;
   }
   if (Config.show_sql) log.info("Execute sql: " + procedure);
   CallableStatement cstmt = null;
   try {
     cstmt =
         rrequest
             .getConnection(spBean.getOwnerSpProvider().getDatasource())
             .prepareCall(procedure);
     AbsDatabaseType dbtype = rrequest.getDbType(spBean.getOwnerSpProvider().getDatasource());
     VarcharType varcharObj =
         (VarcharType) Config.getInstance().getDataTypeByClass(VarcharType.class);
     int idx = 1;
     if (spBean.getLstStoreProcedureParams() != null
         && spBean.getLstStoreProcedureParams().size() > 0) {
       for (String paramTmp : spBean.getLstStoreProcedureParams()) {
         if (WabacusAssistant.getInstance()
             .isGetRequestContextValue(paramTmp)) { // 从request/session中取值
           varcharObj.setPreparedStatementValue(
               idx,
               WabacusAssistant.getInstance().getRequestContextStringValue(rrequest, paramTmp, ""),
               cstmt,
               dbtype);
         } else if (Tools.isDefineKey("condition", paramTmp)) {
           setConditionValue(
               rrequest,
               spBean,
               cstmt,
               dbtype,
               idx,
               Tools.getRealKeyByDefine("condition", paramTmp),
               varcharObj);
         } else {
           varcharObj.setPreparedStatementValue(idx, paramTmp, cstmt, dbtype);
         }
         idx++;
       }
     }
     if (spBean.getOwnerSpProvider().isUseSystemParams()) {
       if (systemParamsBuf == null) systemParamsBuf = new StringBuffer();
       cstmt.setString(idx++, systemParamsBuf.toString()); // 如果是查询报表数据,将系统参数传入
     }
     if (dbtype instanceof Oracle) {
       cstmt.registerOutParameter(idx, OracleTypes.CURSOR);
     }
     rrequest.addUsedStatement(cstmt);
     cstmt.executeQuery();
     ResultSet rs = null;
     if (dbtype instanceof Oracle) {
       rs = (ResultSet) cstmt.getObject(idx);
     } else {
       rs = cstmt.getResultSet();
     }
     return rs;
   } catch (SQLException e) {
     throw new WabacusRuntimeException(
         "从数据库取报表" + rbean.getPath() + "数据时执行SQL:" + procedure + "失败", e);
   }
 }
  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();
    }
  }