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) {
}
}
}
public ResultSet getResultSet(CallableStatement ps) throws SQLException {
boolean isResultSet = ps.execute();
while (!isResultSet && ps.getUpdateCount() != -1) {
isResultSet = ps.getMoreResults();
}
return ps.getResultSet();
}
/** @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;
}
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;
}
/** @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;
}
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;
}
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;
}
/**
* 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);
}
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");
}
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;
}
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();
}
}
/**
* 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();
}
/** @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);
}
}
@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();
}
}
}
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;
}
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();
}
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();
}
}
