// these come from the performance test JDBC.Parameters that was failing
private static void testManyOut(Connection conn) throws SQLException {
System.out.println("start testManyOut");
Statement scp = conn.createStatement();
scp.execute(
"CREATE PROCEDURE OP_OUT "
+ "(OUT I1 INT, OUT I2 INT, OUT I3 INT, OUT I4 INT, OUT I5 INT, "
+ "OUT V1 VARCHAR(40), OUT V2 VARCHAR(40), OUT V3 VARCHAR(40), OUT V4 VARCHAR(40), OUT V5 VARCHAR(40)) "
+ "EXTERNAL NAME '"
+ CLASS_NAME
+ "output' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
scp.execute(
"CREATE PROCEDURE OP_INOUT "
+ "(INOUT I1 INT, INOUT I2 INT, INOUT I3 INT, INOUT I4 INT, INOUT I5 INT, "
+ "INOUT V1 VARCHAR(40), INOUT V2 VARCHAR(40), INOUT V3 VARCHAR(40), INOUT V4 VARCHAR(40), INOUT V5 VARCHAR(40)) "
+ "EXTERNAL NAME '"
+ CLASS_NAME
+ "output' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
CallableStatement csOut_cs = conn.prepareCall("CALL OP_OUT(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
CallableStatement csInOut_cs = conn.prepareCall("CALL OP_INOUT(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
System.out.println("Ten OUT parameters");
executeOutput(csOut_cs);
executeOutput(csOut_cs);
csOut_cs.close();
System.out.println("Ten INOUT parameters");
setupInput(csInOut_cs);
executeOutput(csInOut_cs);
setupInput(csInOut_cs);
executeOutput(csInOut_cs);
csInOut_cs.close();
scp.execute("DROP PROCEDURE OP_OUT");
scp.execute("DROP PROCEDURE OP_INOUT");
scp.close();
System.out.println("end testManyOut");
}
/**
* Control de transacción en el procedimiento almacenado
*
* @param prod
*/
@Override
public void create2(Producto prod) {
Connection cn = null;
try {
cn = AccesoDB.getConnection();
cn.setAutoCommit(true);
String query = "{call usp_crea_producto(?,?,?,?,?)}";
CallableStatement cstm = cn.prepareCall(query);
cstm.registerOutParameter(1, Types.INTEGER);
cstm.setInt(2, prod.getIdcat());
cstm.setString(3, prod.getNombre());
cstm.setDouble(4, prod.getPrecio());
cstm.setInt(5, prod.getStock());
cstm.executeUpdate();
prod.setIdprod(cstm.getInt(1));
cstm.close();
} catch (SQLException e) {
throw new RuntimeException(e.getMessage());
} catch (Exception e) {
throw new RuntimeException("No se puede crear el producto.");
} finally {
try {
cn.close();
} catch (Exception e) {
}
}
}
public Query(String unparsed_query, Connection db) throws SQLException {
params = new ArrayList<String>();
String query =
StringUtil.format(
unparsed_query,
new StringUtil.ParamResolver() {
public String resolveParam(String param) {
params.add(param);
return "?";
}
});
try {
sql = db.prepareCall(query);
pmd = sql.getParameterMetaData();
} catch (SQLException ex) {
throw new SQLException(
"JDBC failed to prepare ESXX-parsed SQL statement: " + query + ": " + ex.getMessage());
}
if (pmd.getParameterCount() != params.size()) {
throw new SQLException(
"JDBC and ESXX report different " + "number of arguments in SQL query");
}
}
@Override
protected String getParam(String name) {
/* function get_setting (
p_key in dxdy_spider_setting.key%type) return varchar2;
*/
String proc = "{? = call dxdy_executable.get_setting (?)}";
CallableStatement st = null;
String result = null;
try {
st = connection.prepareCall(proc);
st.registerOutParameter(1, java.sql.Types.VARCHAR);
st.setString(2, name);
st.execute();
result = st.getString(1);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return result;
}
@Override
protected void saveModer(ModeratorMessage mm, int mID) {
/* function add_moderation (
p_message_id in dxdy_moderation.message_id%type,
p_move_from in dxdy_forum.forum_name%type,
p_move_to in dxdy_forum.forum_name%type,
p_user in dxdy_user.username%type,
p_message_type in dxdy_notice_type.notice_name%type) return varchar2; */
String proc = "{? = call dxdy_executable.add_moderation(?, ?, ?, ?, ?)}";
CallableStatement st = null;
try {
st = connection.prepareCall(proc);
st.registerOutParameter(1, java.sql.Types.VARCHAR);
st.setInt(2, mID);
st.setString(3, mm.moveFrom);
st.setString(4, mm.moveTo);
st.setString(5, mm.moderator);
st.setString(6, mm.messageType);
st.execute();
// System.out.println("Link ID = " + st.getString(1));
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
@Override
protected void saveUser(User u) {
/*procedure add_user(
p_id in dxdy_user.id%type,
p_username in dxdy_user.username%type,
p_status in dxdy_status.status_name%type,
p_reg_date in varchar2);
*/
super.saveUser(u);
String procedure = "{call dxdy_executable.add_user (?, ?, ?, ?)}";
CallableStatement st = null;
try {
st = connection.prepareCall(procedure);
st.setInt(1, u.ID);
st.setString(2, u.name);
st.setString(3, u.status);
st.setString(4, u.regDate);
st.execute();
// System.out.println("User ID = " + u.ID + ": OK");
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public String getStatuses() {
ResultSet rs = null;
try (CallableStatement cs = conn.prepareCall("{call ti_getStatuses}")) {
rs = cs.executeQuery();
if (rs != null) {
ArrayList<Status> statuses = new ArrayList<>();
while (rs.next()) {
// (int status_id, String displayName, boolean isSystem, boolean isNotify, boolean
// isClosed, String notifyMsg)
statuses.add(
new Status(
rs.getInt("status_id"),
rs.getString("display_name"),
rs.getBoolean("isSystem"),
rs.getBoolean("isNotify"),
rs.getBoolean("isClosed"),
rs.getString("notify_msg")));
}
StatusList statusList = new StatusList(statuses);
return generateGson(statusList);
}
} catch (SQLException e) {
e.printStackTrace();
}
return "";
}
public static CallableStatement callPro2(
String sql, String[] inparameters, Integer[] outparameters) {
try {
ct = getConnection();
cs = ct.prepareCall(sql);
if (inparameters != null) {
for (int i = 0; i < inparameters.length; i++) {
cs.setObject(i + 1, inparameters[i]);
}
}
if (outparameters != null) {
for (int i = 0; i < outparameters.length; i++) {
cs.registerOutParameter(inparameters.length + 1 + i, outparameters[i]);
}
}
cs.execute();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
} finally {
}
return cs;
}
private void cargar() {
DefaultTableModel tabla = new DefaultTableModel();
try {
tabla.addColumn("CODIGO");
tabla.addColumn("NOMBRES");
tabla.addColumn("APELLIDOS");
tabla.addColumn("SEXO");
tabla.addColumn("DNI");
tabla.addColumn("TELEFONO");
tabla.addColumn("RUC");
tabla.addColumn("E_MAIL");
tabla.addColumn("DIRECCION");
cts = cn.prepareCall("{call mostrarclientes}");
r = cts.executeQuery();
while (r.next()) {
Object dato[] = new Object[9];
for (int i = 0; i < 9; i++) {
dato[i] = r.getString(i + 1);
}
tabla.addRow(dato);
}
this.jTable1.setModel(tabla);
jLabel2.setText("" + jTable1.getRowCount());
} catch (Exception e) {
}
}
private static void testNull(Connection conn) throws Throwable {
System.out.println("==============================================");
System.out.println("TESTING NULLS");
System.out.println("==============================================\n");
System.out.println("Test for bug 4317, passing null value for a parameter");
Statement scp = conn.createStatement();
scp.execute(
"CREATE PROCEDURE testNullBug4317(IN P1 VARCHAR(10)) "
+ "EXTERNAL NAME '"
+ CLASS_NAME
+ "testNullBug4317'"
+ " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
CallableStatement cs0 = conn.prepareCall("call testNullBug4317(?)");
try {
cs0.setString(1, null); // passing in null
cs0.execute();
} catch (SQLException se) {
System.out.println("cs0.execute() got unexpected exception: " + se);
}
try {
// BUG 5928 - setNull throws an exception - fixed.
cs0.setNull(1, java.sql.Types.VARCHAR); // passing in null
cs0.execute();
} catch (SQLException se) {
System.out.println("cs0.execute() got unexpected exception: " + se);
}
cs0.close();
scp.execute("DROP PROCEDURE testNullBug4317");
}
@Override
public boolean getForumFullScan(String forum) {
/* function get_forum_scan (p_forum in varchar2) return number; */
boolean result = true;
String proc = "{? = call dxdy_executable.get_forum_scan (?)}";
CallableStatement st = null;
try {
st = connection.prepareCall(proc);
st.registerOutParameter(1, Types.INTEGER);
st.setString(2, forum);
st.execute();
result = (st.getInt(1) == 1);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return result;
}
@Override
public String getLastTopicDate(int tID) {
/* function get_topic_last_update (p_topic_id in dxdy_topic.id%type) return varchar2; */
String proc = "{? = call dxdy_executable.get_topic_last_update(?)}";
CallableStatement st = null;
String res = "";
try {
// System.out.println("Request date for topic ID = " + tID);
st = connection.prepareCall(proc);
st.registerOutParameter(1, Types.VARCHAR);
st.setInt(2, tID);
st.execute();
res = st.getString(1);
// System.out.println("Requested date = " + res);
} catch (SQLException e) {
System.out.println("Error while querying last message date of topic ID = " + tID);
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return res;
}
public long nextTSecGroupMemberIdGen(
CFSecurityAuthorization Authorization, CFSecurityTenantPKey PKey) {
final String S_ProcName = "nextTSecGroupMemberIdGen";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Not in a transaction");
}
Connection cnx = schema.getCnx();
long Id = PKey.getRequiredId();
CallableStatement stmtSelectNextTSecGroupMemberIdGen = null;
try {
String sql = "{ call sp_next_tsecgroupmemberidgen( ?" + ", " + "?" + " ) }";
stmtSelectNextTSecGroupMemberIdGen = cnx.prepareCall(sql);
int argIdx = 1;
stmtSelectNextTSecGroupMemberIdGen.registerOutParameter(argIdx++, java.sql.Types.BIGINT);
stmtSelectNextTSecGroupMemberIdGen.setLong(argIdx++, Id);
stmtSelectNextTSecGroupMemberIdGen.execute();
long nextId = stmtSelectNextTSecGroupMemberIdGen.getLong(1);
return (nextId);
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (stmtSelectNextTSecGroupMemberIdGen != null) {
try {
stmtSelectNextTSecGroupMemberIdGen.close();
} catch (SQLException e) {
}
stmtSelectNextTSecGroupMemberIdGen = null;
}
}
}
/**
* Executes a stored Procedure that will update Fields in DB (Ask DBM for SP names)
*
* @param spName Stores Procedure's name
* @param param integer parameter needed by the SP
* @return return 1 if update worked
* @throws SQLException mostly if SP does not exist in DB
*/
public boolean updateStoredProcedure(String spName, int param) throws SQLException {
// preparing callable statement
cs = conn.prepareCall("{call " + spName + "(?)}");
cs.setInt(1, param);
return (cs.executeUpdate() == 1);
}
@Override
public int getLastTopicPage(int tID) {
String proc = "{? = call dxdy_executable.get_topic_last_page(?)}";
CallableStatement st = null;
int res = 0;
try {
st = connection.prepareCall(proc);
st.registerOutParameter(1, java.sql.Types.INTEGER);
st.setInt(2, tID);
st.execute();
res = st.getInt(1);
} catch (SQLException e) {
System.out.println("Error while querying last page of topic ID = " + tID);
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return res;
}
// test: do we get an appropriate update count when using ?=call?
private static void testUpdate(Connection conn) throws Throwable {
System.out.println("==============================================");
System.out.println("TESTING UPDATE COUNT");
System.out.println("==============================================\n");
Statement scp = conn.createStatement();
scp.execute(
"CREATE FUNCTION returnsIntegerP(P1 INT) RETURNS INTEGER "
+ "EXTERNAL NAME '"
+ CLASS_NAME
+ "returnsIntegerP'"
+ " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
CallableStatement cs = conn.prepareCall("? = call returnsIntegerP(0)");
cs.registerOutParameter(1, Types.INTEGER);
try {
int updCount = cs.executeUpdate();
System.out.println("executeUpdate on ? = call returnsIntegerP returned " + updCount);
System.out.println("getString(1) returned " + cs.getString(1));
} catch (SQLException se) {
System.out.println("cs.execute() got unexpected exception: " + se);
}
cs.close();
scp.execute("DROP FUNCTION returnsIntegerP");
scp.close();
}
public void nonQueryStoredProcedure(String SPName, int param1, int param2) throws SQLException {
// preparing callable statement
cs = conn.prepareCall("{call " + dbName + "." + SPName + "(?,?)}");
cs.setInt(1, param1);
cs.setInt(2, param2);
cs.executeUpdate();
}
public CallableStatement prepareCall(
String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) {
try {
return con.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
} catch (Exception ex) {
ex.printStackTrace();
}
return null;
}
/**
* Gets the result of query Stored Procedure into object's result set object.
*
* @param spName Stored Procedure's name
* @param param1 first String parameter
* @param param2 second String parameter
* @throws SQLException if the SQL call is incorect (mostly if SPName is incorect)
*/
public void queryStoredProcedure(String spName, String param1, String param2)
throws SQLException {
// preparing callable statement
cs = conn.prepareCall("{call " + dbName + "." + spName + "(?,?)}");
cs.setString(1, param1);
cs.setString(2, param2);
rs = cs.executeQuery();
}
private static void test5116(Connection conn) throws Throwable {
System.out.println("==============================================");
System.out.println("TESTING FIX OF 5116 -- VAR BIT VARYING INPUT");
System.out.println("==============================================\n");
Statement stmt = conn.createStatement();
stmt.executeUpdate(
"CREATE TABLE ACTIVITY_INSTANCE_T ("
+ "AIID char(16) for bit data NOT NULL ,"
+ "KIND INTEGER NOT NULL ,"
+ "PIID char(16) for bit data NOT NULL ,"
+ "PTID char(16) for bit data NOT NULL ,"
+ "ATID char(16) for bit data NOT NULL ,"
+ "RUN_MODE INTEGER NOT NULL ,"
+ "FINISHED TIMESTAMP ,"
+ "ACTIVATED TIMESTAMP ,"
+ "STARTED TIMESTAMP ,"
+ "LAST_MODIFIED TIMESTAMP ,"
+ "LAST_STATE_CHANGE TIMESTAMP ,"
+ "STATE INTEGER NOT NULL ,"
+ "TRANS_COND_VALUES VARCHAR(66) FOR BIT DATA NOT NULL ,"
+ "NUM_CONN_ACT_EVA INTEGER NOT NULL ,"
+ "NUMBER_OF_ITERATIONS INTEGER NOT NULL ,"
+ "NUMBER_OF_RETRIES INTEGER NOT NULL ,"
+ "HAS_CUSTOM_ATTRIBUTES SMALLINT NOT NULL ,"
+ "NON_BLOCK_PTID char(16) for bit data NOT NULL ,"
+ "NON_BLOCK_PIID char(16) for bit data NOT NULL ,"
+ "EXPIRES TIMESTAMP ,"
+ "TASK_ID VARCHAR(254) ,"
+ "UNHANDLED_EXCEPTION BLOB(3993600) ,"
+ "SUB_PROCESS_PIID char(16) for bit data ,"
+ "OWNER VARCHAR(32) ,"
+ "USER_INPUT VARCHAR(130) FOR BIT DATA ,"
+ "DESCRIPTION VARCHAR(254) ,"
+ "VERSION_ID SMALLINT NOT NULL ,"
+ "PRIMARY KEY ( AIID ) )");
stmt.execute(
"CREATE PROCEDURE doInsertion(IN P1 VARCHAR(2) FOR BIT DATA) "
+ "EXTERNAL NAME '"
+ CLASS_NAME
+ "doInsertion'"
+ " MODIFIES SQL DATA LANGUAGE JAVA PARAMETER STYLE JAVA");
CallableStatement cs = conn.prepareCall("call doInsertion (?)");
cs.setNull(1, java.sql.Types.VARBINARY);
cs.execute();
byte[] b = new byte[2];
b[0] = 1;
b[1] = 2;
cs.setBytes(1, b);
cs.execute();
cs.close();
stmt.executeUpdate("DROP PROCEDURE doInsertion");
stmt.close();
}
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 static void main(String[] args) {
// Declare the JDBC objects.
Connection con = null;
CallableStatement cstmt = null;
ResultSet rs = null;
try {
// Establish the connection.
SQLServerDataSource ds = new SQLServerDataSource();
ds.setIntegratedSecurity(true);
ds.setServerName("localhost");
ds.setPortNumber(1433);
ds.setDatabaseName("AdventureWorks");
con = ds.getConnection();
// Execute a stored procedure that returns some data.
cstmt = con.prepareCall("{call dbo.uspGetEmployeeManagers(?)}");
cstmt.setInt(1, 50);
rs = cstmt.executeQuery();
// Iterate through the data in the result set and display it.
while (rs.next()) {
System.out.println(
"EMPLOYEE: " + rs.getString("LastName") + ", " + rs.getString("FirstName"));
System.out.println(
"MANAGER: "
+ rs.getString("ManagerLastName")
+ ", "
+ rs.getString("ManagerFirstName"));
System.out.println();
}
}
// Handle any errors that may have occurred.
catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null)
try {
rs.close();
} catch (Exception e) {
}
if (cstmt != null)
try {
cstmt.close();
} catch (Exception e) {
}
if (con != null)
try {
con.close();
} catch (Exception e) {
}
}
}
@Override
public void callSelectP(String question) throws SQLException {
CallableStatement statement =
sqlConn.prepareCall(
"COPY ( select p_"
+ question
+ ","
+ "sum(case when code like '%K%' then 1 else 0 end) as k,'',"
+ "sum(case when code like '%M%' then 1 else 0 end) as m,'',"
+ "sum(case when null is null then 1 else 0 end) as all,'',"
+ "sum(case when m_mz = 'a' then 1 else 0 end) as m_mza,'',"
+ "sum(case when m_mz = 'b' then 1 else 0 end) as m_mzb,'',"
+ "sum(case when m_mz = 'c' then 1 else 0 end) as m_mzc,'',"
+ "sum(case when m_mz = 'd' then 1 else 0 end) as m_mzd,'',"
+ "sum(case when m_mz = 'e' then 1 else 0 end) as m_mze,'',"
+ "sum(case when m_lr = '0' then 1 else 0 end) as lr_0, '',"
+ "sum(case when m_lr = '1' then 1 else 0 end) as lr_1, '',"
+ "sum(case when m_lr = '2' then 1 else 0 end) as lr_2, '',"
+ "sum(case when m_lr = '3' then 1 else 0 end) as lr_3, '',"
+ "sum(case when m_lr in ('4', '5', '6', '7') then 1 else 0 end) as lr_4_wiecej, '',"
+ "sum(case when m_wr LIKE '%a%' then 1 else 0 end) as wr_a, '',"
+ "sum(case when m_wr LIKE '%b%' then 1 else 0 end) as wr_b, '',"
+ "sum(case when m_wr LIKE '%c%' then 1 else 0 end) as wr_c, '',"
+ "sum(case when m_wm = 'c' then 1 else 0 end) as wmc,'',"
+ "sum(case when m_wm = 'd' then 1 else 0 end) as wmd,'',"
+ "sum(case when m_wm = 'e' then 1 else 0 end) as wme,'',"
+ "sum(case when m_wm = 'f' then 1 else 0 end) as wmf,'',"
+ "sum(case when m_wm = 'g' then 1 else 0 end) as wmg,'',"
+ "sum(case when m_wm not in ('a','b','c','d','e', 'f', 'g') then 1 else 0 end) as wmnic,'',"
+ "sum(case when m_wo = 'c' then 1 else 0 end) as woc,'',"
+ "sum(case when m_wo = 'd' then 1 else 0 end) as wod,'',"
+ "sum(case when m_wo = 'e' then 1 else 0 end) as woe,'',"
+ "sum(case when m_wo = 'f' then 1 else 0 end) as wof,'',"
+ "sum(case when m_wo = 'g' then 1 else 0 end) as wog,'',"
+ "sum(case when m_wykm = 'a' then 1 else 0 end) as wykma,'',"
+ "sum(case when m_wykm = 'b' then 1 else 0 end) as wykmb,'',"
+ "sum(case when m_wykm = 'c' then 1 else 0 end) as wykmc,'',"
+ "sum(case when m_wykm = 'd' then 1 else 0 end) as wykmd,'',"
+ "sum(case when m_wykm not in ('a','b','c','d') then 1 else 0 end) as wykmnic,'',"
+ "sum(case when m_wyko = 'a' then 1 else 0 end) as wykoa,'',"
+ "sum(case when m_wyko = 'b' then 1 else 0 end) as wykob,'',"
+ "sum(case when m_wyko = 'c' then 1 else 0 end) as wykoc,'',"
+ "sum(case when m_wyko = 'd' then 1 else 0 end) as wykod, ''"
+ " from ankieta_pat group by p_"
+ question
+ " order by p_"
+ question
+ " "
+ ") TO '/tmp/p_a"
+ question
+ ".csv' WITH CSV;");
statement.execute();
sqlConn.commit();
}
public void ramasserObjet() {
boolean vide = true;
try {
ResultSet rset =
stmt.executeQuery(
"select idObjet from objet where positionX="
+ positionX
+ " and positionY="
+ positionY);
Hashtable<String, String> tabObj = new Hashtable<String, String>();
while (rset.next()) {
vide = false;
String idObj = rset.getString("idObjet");
System.out.println(idObj);
tabObj.put(idObj, idObj);
}
rset.close();
if (!vide) {
System.out.println("Quel objet voulez vous ramasser ? : ");
String obj = IO.lireChaine();
if (tabObj.containsKey(obj)) {
proc = conn.prepareCall("{call rammasser(?,?)}");
proc.setInt(1, idTroll);
proc.setString(2, obj);
proc.executeUpdate();
proc.close();
rset = stmt.executeQuery("select typeObjet from objet where idObjet='" + obj + "'");
rset.first();
String type = rset.getString("typeObjet");
if (type.equals("potion")) {
menu.supprimerPopo(obj);
} else {
menu.supprimerObjet(obj);
}
rset = stmt.executeQuery("select paRestants from troll where idTroll=" + idTroll);
int ancVal = 0;
while (rset.next()) {
ancVal = rset.getInt("paRestants");
}
stmt.executeUpdate(
"update troll SET paRestants = " + (ancVal - 1) + " where idTroll=" + idTroll);
paRestants = paRestants - 1;
} else {
System.out.println("Cet objet ne se trouve pas sur votre case !");
}
} else {
System.out.println("Il n'y a aucun objet sur votre case");
}
} catch (SQLException E) {
System.err.println("SQLException: " + E.getMessage());
System.err.println("SQLState: " + E.getSQLState());
}
}
private void init2() {
try {
for (int count = 0; stmt == null && count++ <= 5; count++) {
wait(100L);
dbConn = DBCHPool.getConnection();
stmt = dbConn.createStatement(1004, 1007);
}
if (isProcedure) cstmt = dbConn.prepareCall(querySQL, 1004, 1007);
} catch (Exception exception) {
}
}
@Override
protected void saveMessage(Message m, int tID) {
/*function add_message(
p_id in dxdy_message.id%type,
p_username in dxdy_user.username%type,
p_created_on in dxdy_message.created_on%type,
p_msg_size in dxdy_message.msg_size%type,
p_topic_id in dxdy_message.topic_id%type,
p_size_wo_quote in dxdy_message.msg_size_wo_quote%type,
p_latex_size in dxdy_message.latex_size%type,
p_offtop in dxdy_message.offtop%type,
p_edit_count in dxdy_message.edit_count%type,
p_code_size in dxdy_message.code_size%type,
p_code_language in dxdy_message.code_language%type,
p_header in dxdy_message.header%type) return varchar2; */
String proc = "{? = call dxdy_executable.add_message (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}";
CallableStatement st = null;
try {
st = connection.prepareCall(proc);
st.registerOutParameter(1, java.sql.Types.VARCHAR);
st.setInt(2, m.ID);
st.setString(3, m.user);
st.setString(4, m.createdOn);
st.setInt(5, m.size);
st.setInt(6, tID);
st.setInt(7, m.sizeWithoutQuote);
st.setInt(8, m.latexSize);
st.setInt(9, m.offtop ? 1 : 0);
st.setInt(10, m.editCount);
st.setInt(11, m.sizeCode);
st.setString(12, m.codeLang);
st.setString(13, m.header);
st.execute();
for (Link l : m.links) saveLink(l, m.ID);
for (ModeratorMessage mm : m.mms) saveModer(mm, m.ID);
} catch (SQLException e) {
e.printStackTrace();
System.out.println("m.createdOn = " + m.createdOn);
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public boolean saveInformeReferencia(
int code,
String fecElab,
String diag,
String mot,
String pulso,
String presion,
String tem,
String freqresp,
String resumen,
String instrc,
String fecenvio,
String acomp,
String horaenvia,
String seEnviaA,
String medico) {
boolean resultado = false;
try {
conx = Conexion.getSQLConnection();
if (conx != null) {
ps = conx.prepareCall("{call saveInformeReferencia(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
ps.setInt(1, code);
ps.setString(2, fecElab);
ps.setString(3, diag);
ps.setString(4, mot);
ps.setString(5, pulso);
ps.setString(6, presion);
ps.setString(7, tem);
ps.setString(8, freqresp);
ps.setString(9, resumen);
ps.setString(10, instrc);
ps.setString(11, fecenvio);
ps.setString(12, acomp);
ps.setString(13, horaenvia);
ps.setString(14, seEnviaA);
ps.setString(15, medico);
ps.setString(16, lib.getFechaActual());
ps.setString(17, "usuario");
ps.executeUpdate();
conx.close();
resultado = true;
} else {
System.out.println("Ha ocurrido un error al conectarse a la BD PostgreSQL");
resultado = false;
}
} catch (SQLException e) {
JOptionPane.showMessageDialog(null, e.getMessage());
}
return resultado;
}
/**
* Gets the result of query Stored Procedure into object's result set object
*
* @param spName Stored Procedure's Name without the dataBase name
* @param param integer parameter needed by the SP
* @throws SQLException raised if the SQL call is incorect (mostly if SPName is incorect
*/
public void queryStoredProcedure(String spName, int param) throws SQLException {
// preparing callable statement
String call = "{call " + dbName + "." + spName + "(?)}";
// statement.concat(SPName);
// statement.concat("(");
// statemendt.concat(paramStr);
// statements.concat(")}");
cs = conn.prepareCall(call);
cs.setInt(1, param);
rs = cs.executeQuery();
}
@Override
public boolean createAccount(String username, String password, String ingame, String rank) {
try {
CallableStatement cs;
cs = conn.prepareCall("call ti_createAccount(?,?,?,?)");
cs.setString(1, username);
cs.setString(2, password);
cs.setString(3, ingame);
cs.setString(4, rank);
cs.executeUpdate();
return true;
} catch (SQLException ex) {
return false;
}
}
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);
}
}