public ArrayList<DetalleFactura> buscar(Factura entidad) throws Exception { ArrayList<DetalleFactura> lista = new ArrayList<DetalleFactura>(); try { cnn = Conexion.getConexion(); CallableStatement cs = null; cs = cnn.prepareCall("call uspListDetalleFactrua(?)"); cs.setInt(1, entidad.getIdFactura()); rs = cs.executeQuery(); while (rs.next()) { DetalleFactura objeto = new DetalleFactura(); objeto.setIdDetalleFactura(rs.getInt("Iddetallefactura")); objeto.setIdProducto(rs.getInt("Idproducto")); objeto.setCantidad(rs.getInt("Cantidad")); objeto.setPrecio(rs.getDouble("Precio")); objeto.setSubTotal(rs.getDouble("Subtotal")); objeto.setIdFactura(rs.getInt("Idfactura")); objeto.setDProducto(rs.getString("DProducto")); lista.add(objeto); } cnn.close(); cs.close(); } catch (SQLException ex) { throw ex; } return lista; }
public String getKnownShipperDetails(String locationCode, String shipperId) { Connection connection = null; CallableStatement cStmt = null; ResultSet rs = null; String Code = null; try { connection = getConnection(); cStmt = connection.prepareCall("{? = call ETRANS_UTIL.GETKNOWNSHIPPERSTATUS(?,?,?) }"); cStmt.setString(2, locationCode); cStmt.setString(3, shipperId); cStmt.setString(4, "OPR"); cStmt.registerOutParameter(1, java.sql.Types.VARCHAR); cStmt.execute(); Code = cStmt.getString(1); } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { try { ConnectionUtil.closeConnection(connection, cStmt, rs); } catch (Exception ex) { ex.printStackTrace(); } } return Code; }
public boolean insertarAlquiler( int idtrabajador, int idCliente, ArrayList<Detalle_Alquiler> lista_detalle, int identificador) { boolean resultado = false; Connection cnn = null; CallableStatement cstmt = null; CallableStatement cstm1 = null; CallableStatement cstm2 = null; int id_alquiler = 0; try { cnn = BD.getConnection(); cnn.setAutoCommit(false); String sql = "call spI_Alquiler (?,?,?,?);"; cstmt = cnn.prepareCall(sql); cstmt.setInt(1, 2); cstmt.setInt(2, idCliente); cstmt.setInt(3, idtrabajador); cstmt.setInt(4, identificador); ResultSet rs = cstmt.executeQuery(); if (rs.next()) { id_alquiler = rs.getInt("int_id"); } for (int i = 0; i < lista_detalle.size(); i++) { String sql1 = "call spI_DetalleAlquiler(?,?,?,?,?,?,?);"; cstm1 = cnn.prepareCall(sql1); cstm1.setInt(1, id_alquiler); cstm1.setInt(2, lista_detalle.get(i).getMaterial_id()); cstm1.setInt(3, lista_detalle.get(i).getInt_cantidad()); cstm1.setDouble(4, lista_detalle.get(i).getDec_monto()); cstm1.setTimestamp(5, lista_detalle.get(i).getDat_fechfin()); cstm1.setTimestamp(6, lista_detalle.get(i).getDat_fechinicio()); cstm1.setInt(7, lista_detalle.get(i).getInt_horas()); cstm1.execute(); } // Registrar Pagos String sql2 = "call spI_Pagos_ByAlquiler(?,?);"; cstm2 = cnn.prepareCall(sql2); cstm2.setInt(1, 1); // es el codigo del usuario cambiar despues cstm2.setInt(2, id_alquiler); cstm2.execute(); cnn.commit(); resultado = true; } catch (SQLException s) { try { cnn.rollback(); } catch (SQLException b) { } System.out.println("aquí es :/ " + s); } finally { try { cstmt.close(); cnn.close(); } catch (SQLException ex) { } } return resultado; }
public void testBackPointers() throws Exception { // normal statement Connection conn = newConnection(); assertBackPointers(conn, conn.createStatement()); conn = newConnection(); assertBackPointers(conn, conn.createStatement(0, 0)); conn = newConnection(); assertBackPointers(conn, conn.createStatement(0, 0, 0)); // prepared statement conn = newConnection(); assertBackPointers(conn, conn.prepareStatement("select * from dual")); conn = newConnection(); assertBackPointers(conn, conn.prepareStatement("select * from dual", 0)); conn = newConnection(); assertBackPointers(conn, conn.prepareStatement("select * from dual", 0, 0)); conn = newConnection(); assertBackPointers(conn, conn.prepareStatement("select * from dual", 0, 0, 0)); conn = newConnection(); assertBackPointers(conn, conn.prepareStatement("select * from dual", new int[0])); conn = newConnection(); assertBackPointers(conn, conn.prepareStatement("select * from dual", new String[0])); // callable statement conn = newConnection(); assertBackPointers(conn, conn.prepareCall("select * from dual")); conn = newConnection(); assertBackPointers(conn, conn.prepareCall("select * from dual", 0, 0)); conn = newConnection(); assertBackPointers(conn, conn.prepareCall("select * from dual", 0, 0, 0)); }
public void testGetObjectLongVarchar() throws Throwable { try { Statement stmt = con.createStatement(); stmt.execute("create temp table longvarchar_tab ( t text, null_val text )"); stmt.execute("insert into longvarchar_tab values ('testdata',null)"); boolean ret = stmt.execute( "create or replace function " + "longvarchar_proc( OUT pcn text, OUT nval text) as " + "'begin " + "select t into pcn from longvarchar_tab;" + "select null_val into nval from longvarchar_tab;" + " end;' " + "language plpgsql;"); ret = stmt.execute( "create or replace function " + "lvarchar_in_name( IN pcn text) returns int as " + "'begin " + "update longvarchar_tab set t=pcn;" + "return 0;" + " end;' " + "language plpgsql;"); } catch (Exception ex) { fail(ex.getMessage()); throw ex; } try { CallableStatement cstmt = con.prepareCall("{ call longvarchar_proc(?,?) }"); cstmt.registerOutParameter(1, Types.LONGVARCHAR); cstmt.registerOutParameter(2, Types.LONGVARCHAR); cstmt.executeUpdate(); String val = (String) cstmt.getObject(1); assertTrue(val.equals("testdata")); val = (String) cstmt.getObject(2); assertTrue(val == null); cstmt.close(); cstmt = con.prepareCall("{ call lvarchar_in_name(?) }"); String maxFloat = "3.4E38"; cstmt.setObject(1, new Float(maxFloat), Types.LONGVARCHAR); cstmt.executeUpdate(); cstmt.close(); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("select * from longvarchar_tab"); assertTrue(rs.next()); String rval = (String) rs.getObject(1); assertEquals(rval.trim(), maxFloat.trim()); } catch (Exception ex) { fail(ex.getMessage()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute("drop function longvarchar_proc()"); dstmt.execute("drop function lvarchar_in_name(text)"); } catch (Exception ex) { } } }
@BeforeClass public static void initDriver() throws Exception { Class.forName("org.postgresql.Driver"); PoolingDataSource ds = new PoolingDataSource(); ds.setServerName(System.getProperty("pgsql.server", "localhost")); ds.setPortNumber(Integer.parseInt(System.getProperty("pgsql.port", "5432"))); ds.setDatabaseName(System.getProperty("pgsql.db", "test")); ds.setUser(System.getProperty("pgsql.user", "test")); ds.setPassword(System.getProperty("pgsql.password", "test")); ds.setMaxConnections(10); ds.setInitialConnections(1); try { ds.initialize(); } catch (Exception e) { skipTests = true; System.out.println("Failed to initialize datasource. Tests will be skipped ..."); e.printStackTrace(); return; } datasource = ds; schema = "xlo_test_" + UUID.randomUUID().toString().substring(0, 8); schema_two = "xlo_test_" + UUID.randomUUID().toString().substring(0, 8); if (schema.compareTo(schema_two) > 0) { String tmp = schema; schema = schema_two; schema_two = tmp; } // first try { cleanup(); } catch (Exception e) { // ignore } // create schema for the test try (Connection c = datasource.getConnection()) { try (CallableStatement s = c.prepareCall("create schema " + schema)) { s.execute(); } try (CallableStatement s = c.prepareCall("create schema " + schema_two)) { s.execute(); } } createTables(); insertData(); }
// 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"); }
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")); }
public static Long dbGenerarNumero(Connection conn, String pkModulo) { String sql = "{ call ? := PK_NUMERACION_UTIL.FN_MODULO_NEXT_NUM ( ? ) }"; try { // String sql = "{ call ? := PF_SUMINISTROS_UTIL.FN_OBTENER_NUMERACION( CO_NUMERACION, // usua_modi, nuip_modi, usso_modi, nopc_modi ) }"; // ApplicationUser appUser = SPApplication.getAppUser(); // Connection conn = sessionImplementor.connection(); CallableStatement stmt = conn.prepareCall(sql); stmt.registerOutParameter(1, OracleTypes.VARCHAR); stmt.setObject(2, pkModulo); // stmt.setObject(3, padSize);//padSize // stmt.setObject(4, appUser.getUsername());//"USUMODI" // stmt.setObject(5, appUser.getIpAddress()); //"IPMODI" // stmt.setObject(6, appUser.getOS()); //"SOMODI" // stmt.setObject(7, appUser.getHostName()); //"PCMODI" logger.info(" PK_NUMERACION_UTIL.FN_MODULO_CURR_NUM:" + pkModulo); stmt.execute(); String returnValue = (String) stmt.getObject(1); logger.debug("Numero generado:" + returnValue); stmt.close(); return new Long(returnValue); } catch (SQLException e) { logger.error("SQL:" + sql); throw new AWDeveloperException(AWBusinessException.wrapUnhandledException(logger, e)); } }
public static void main(String[] args) { try { // 1.加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); // 2.获取连接 Connection connection = DriverManager.getConnection( "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "SCOTT", "wcy675600920"); // 3.创建CallableStatement CallableStatement cs = connection.prepareCall("{call wwss_pro3(?,?)}"); // 4.给?赋值 cs.setString(1, "9527"); cs.setInt(2, 3000); // 5.执行 cs.execute(); // 6.关闭 cs.close(); connection.close(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } }
@Override public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String param = ""; java.util.Enumeration<String> headerNames = request.getHeaderNames(); if (headerNames.hasMoreElements()) { param = headerNames.nextElement(); // just grab first element } String bar = new Test().doSomething(param); String sql = "{call verifyUserPassword('foo','" + bar + "')}"; try { java.sql.Connection connection = org.owasp.benchmark.helpers.DatabaseHelper.getSqlConnection(); java.sql.CallableStatement statement = connection.prepareCall( sql, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY, java.sql.ResultSet.CLOSE_CURSORS_AT_COMMIT); statement.execute(); } catch (java.sql.SQLException e) { throw new ServletException(e); } } // end doPost
public static List<Sucursal> buscarSucursales() { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); CallableStatement cs = null; ResultSet rs = null; try { List<Sucursal> sucursales = new ArrayList(); cs = connection.prepareCall("{ call listaSucursal() }"); rs = cs.executeQuery(); while (rs.next()) { Sucursal suc = new Sucursal(rs.getInt("idSucursal"), rs.getString("nombreSucursal")); sucursales.add(suc); } return sucursales; } catch (Exception ex) { ex.printStackTrace(); return null; } finally { DBUtil.closeResultSet(rs); DBUtil.closeStatement(cs); pool.freeConnection(connection); } }
public boolean outStu(String stuId, String rmark) throws DataBaseException { Statement stmt; stmt = DB.CreateStatement(); String sql = "select StuId from StuDormRoom where StuId='" + stuId + "'"; try { ResultSet rs = stmt.executeQuery(sql); if (rs != null && !rs.next()) { return false; } } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } try { CallableStatement cst = connection.prepareCall("{call proc_StuInOut_SDM_out(?,?)}"); cst.setString(1, stuId); cst.setString(2, rmark); int r = cst.executeUpdate(); cst.close(); if (r > 0) { return true; } else { return false; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } }
public boolean insertLCRecord(String stuId, String stuName, String data, String lC, String mark) throws DataBaseException, QueryResultIsNullException { // System.out.println("***********---"); try { CallableStatement cst = connection.prepareCall("{call proc_LeaveComeStu(?,?,?,?,?)}"); cst.setString(1, stuId); cst.setString(2, stuName); cst.setString(3, data); cst.setString(4, lC); cst.setString(5, mark); // System.out.println("***********---"); int r = cst.executeUpdate(); cst.close(); if (r > 0) { return true; } else { return false; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } }
@Override public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { org.owasp.benchmark.helpers.SeparateClassRequest scr = new org.owasp.benchmark.helpers.SeparateClassRequest(request); String param = scr.getTheParameter("foo"); String bar = new Test().doSomething(param); String sql = "{call verifyUserPassword('foo','" + bar + "')}"; try { java.sql.Connection connection = org.owasp.benchmark.helpers.DatabaseHelper.getSqlConnection(); java.sql.CallableStatement statement = connection.prepareCall( sql, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY, java.sql.ResultSet.CLOSE_CURSORS_AT_COMMIT); statement.execute(); } catch (java.sql.SQLException e) { throw new ServletException(e); } } // end doPost
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; } } }
@Override public int save(java.sql.Connection connection) { int i = 0; int nParam = 1; CallableStatement callableStatement = null; mnLastDbActionResult = SLibConstants.UNDEFINED; try { callableStatement = connection.prepareCall("{ CALL mfgu_line_save(" + "?, ?, ?, ?, ?, " + "?, ?, ?) }"); callableStatement.setInt(nParam++, mnPkMfgLineId); callableStatement.setString(nParam++, msMfgLine); callableStatement.setBoolean(nParam++, mbIsDeleted); callableStatement.setInt(nParam++, mnFkCostCenterId); callableStatement.setInt(nParam++, mbIsRegistryNew ? mnFkUserNewId : mnFkUserEditId); callableStatement.registerOutParameter(nParam++, java.sql.Types.INTEGER); callableStatement.registerOutParameter(nParam++, java.sql.Types.SMALLINT); callableStatement.registerOutParameter(nParam++, java.sql.Types.VARCHAR); callableStatement.execute(); mnPkMfgLineId = callableStatement.getInt(nParam - 3); mnDbmsErrorId = callableStatement.getInt(nParam - 2); msDbmsError = callableStatement.getString(nParam - 1); mbIsRegistryNew = false; mnLastDbActionResult = SLibConstants.DB_ACTION_SAVE_OK; } catch (java.lang.Exception e) { mnLastDbActionResult = SLibConstants.DB_ACTION_SAVE_ERROR; SLibUtilities.printOutException(this, e); } return mnLastDbActionResult; }
public static int insertarVenta(Venta v) { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); CallableStatement cs = null; ResultSet rs = null; Venta ven = null; try { cs = connection.prepareCall("{ call insertVenta(?, ?, ?, ?) }"); cs.setInt(1, v.getUsuarioVenta().getId()); cs.setDouble(2, v.getSubtotal()); cs.setInt(3, v.getPagoVenta().getIdPago()); cs.setInt(4, v.getUsuarioVenta().getSucursal().getIdSucursal()); rs = cs.executeQuery(); while (rs.next()) { ven = new Venta(rs.getInt("idVenta")); } return ven.getIdVenta(); } catch (Exception ex) { ex.printStackTrace(); return 0; } finally { DBUtil.closeStatement(cs); pool.freeConnection(connection); } }
public static List<Usuario> buscarUsuarios() { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); CallableStatement cs = null; ResultSet rs = null; try { List<Usuario> usuas = new ArrayList(); cs = connection.prepareCall("{ call cajeroReporte() }"); rs = cs.executeQuery(); while (rs.next()) { Usuario usa = new Usuario( rs.getString("nombreUsuario"), rs.getString("apellidoPaterno"), rs.getInt("idUsuario")); usuas.add(usa); } return usuas; } catch (Exception ex) { ex.printStackTrace(); return null; } finally { DBUtil.closeResultSet(rs); DBUtil.closeStatement(cs); pool.freeConnection(connection); } }
public static List<Pago> buscarPagos() { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); CallableStatement cs = null; ResultSet rs = null; try { List<Pago> pagos = new ArrayList(); cs = connection.prepareCall("{ call listaPago() }"); rs = cs.executeQuery(); while (rs.next()) { Pago pag = new Pago(rs.getInt("idMetodoPago"), rs.getString("nombreMetodoPago")); pagos.add(pag); } return pagos; } catch (Exception ex) { ex.printStackTrace(); return null; } finally { DBUtil.closeResultSet(rs); DBUtil.closeStatement(cs); pool.freeConnection(connection); } }
public Tour insertTour(Tour rec) { Tour flag = null; try { DBCON ob = new DBCON(); Connection con = null; CallableStatement cs = null; con = ob.createConnection(); cs = con.prepareCall("{call trs_srilanka.insertTour(?,?,?,?,?,?,?)}"); cs.setLong(1, rec.getTRID()); cs.setString(2, rec.getTitle()); cs.setString(3, rec.getItinary()); cs.setString(4, rec.getNoOfDays()); cs.setString(5, rec.getAccomadationType()); cs.setString(6, rec.getBasis()); cs.setLong(7, rec.getGEOID()); int res = cs.executeUpdate(); if (res > 0) { flag = rec; } else { flag = null; } } catch (Exception ex) { ex.printStackTrace(); } return flag; }
/** * Funcion con la cual obtengo todos los municipios teniendo en cuenta el id de la ciudad * * @param idCiudad * @return */ public ArrayList<Par_MunicipioTO> obtieneMunicipiosXId(String idCiudad) { String sql = ""; ArrayList<Par_MunicipioTO> rta = null; Connection con = null; try { con = super.getConnection(this.objDataSession); sql = "select mpio_id, mpio_nombre "; sql += "from spar_municipio "; sql += "where ciu_id = ? "; sql += "and mpio_estado = 'A' "; PreparedStatement ps = con.prepareCall(sql); ps.setString(1, idCiudad); ResultSet rs = ps.executeQuery(); while (rs.next()) { if (rta == null) { rta = new ArrayList<Par_MunicipioTO>(); } Par_MunicipioTO aux = new Par_MunicipioTO(); aux.setMpio_Id(rs.getLong("mpio_id")); aux.setMpio_Nombre(rs.getString("mpio_nombre")); rta.add(aux); } } catch (Exception e) { e.printStackTrace(); } return rta; }
@Override public int call(java.sql.Connection connection) { int nParam = 1; java.sql.CallableStatement callableStatement = null; mnLastDbActionResult = SLibConstants.UNDEFINED; try { callableStatement = connection.prepareCall("{ CALL mfg_ltime_cob_val(?, ?, ?) }"); callableStatement.setInt(nParam, (Integer) mvParamsIn.get(nParam - 1)); nParam++; callableStatement.setInt(nParam, (Integer) mvParamsIn.get(nParam - 1)); nParam++; callableStatement.registerOutParameter(nParam, java.sql.Types.INTEGER); callableStatement.execute(); mvParamsOut.clear(); mvParamsOut.add(callableStatement.getInt(nParam)); mnLastDbActionResult = SLibConstants.DB_PROCEDURE_OK; } catch (java.sql.SQLException e) { mnLastDbActionResult = SLibConstants.DB_PROCEDURE_ERROR; SLibUtilities.printOutException(this, e); } catch (java.lang.Exception e) { mnLastDbActionResult = SLibConstants.DB_PROCEDURE_ERROR; SLibUtilities.printOutException(this, e); } return mnLastDbActionResult; }
@Override public ResultSet ExecuteSproc(String sprocName, String[] parameters, int[] returnColumnTypes) throws SQLException { if (_conn.isClosed()) { throw new SQLException("Connection was already closed"); } try { String paramsList = ""; int listSize = parameters.length; for (int i = 0; i < listSize; i++) { paramsList += parameters[i]; if (i != listSize - 1) { paramsList += ", "; } } CallableStatement statement = _conn.prepareCall("{call " + sprocName + "(" + paramsList + ")}"); for (int i = 0; i < returnColumnTypes.length; i++) { statement.registerOutParameter(i + 1, returnColumnTypes[i]); } return statement.executeQuery(); } catch (SQLException ex) { throw ex; } }
public boolean insertCurfew( String stuId, String stuName, String curfew, String rmark, String dMid) throws DataBaseException, QueryResultIsNullException { try { CallableStatement cst = connection.prepareCall("{call proc_Curfew(?,?,?,?,?)}"); cst.setString(1, stuId); cst.setString(2, stuName); cst.setString(3, curfew); cst.setString(4, rmark); cst.setString(5, dMid); int r = cst.executeUpdate(); cst.close(); if (r > 0) { return true; } else { return false; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } }
/** * 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) { } } }
/** * Function to create temp visit dimension table using stored proc. * * @param tempTableName * @throws Exception */ public void createTempTable(String tempEncounterMappingTableName) throws I2B2Exception { Connection conn = null; try { // smuniraju: Postgres requires only the IN arguments to be supplied in the call to proc. // CallableStatement callStmt = conn.prepareCall("{call "+ getDbSchemaName() + // "CREATE_TEMP_EID_TABLE(?,?)}"); String prepareCallString = ""; if (dataSourceLookup.getServerType().equalsIgnoreCase(DataSourceLookupDAOFactory.POSTGRES)) { prepareCallString = "{call " + getDbSchemaName() + "CREATE_TEMP_EID_TABLE(?)}"; } else { prepareCallString = "{call " + getDbSchemaName() + "CREATE_TEMP_EID_TABLE(?,?)}"; } conn = getDataSource().getConnection(); CallableStatement callStmt = conn.prepareCall(prepareCallString); callStmt.setString(1, tempEncounterMappingTableName); callStmt.registerOutParameter(2, java.sql.Types.VARCHAR); callStmt.execute(); this.getSQLServerProcedureError(dataSourceLookup.getServerType(), callStmt, 2); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); throw new I2B2Exception("SQLException occured" + sqlEx.getMessage(), sqlEx); } catch (Exception ex) { ex.printStackTrace(); throw new I2B2Exception("Exception occured" + ex.getMessage(), ex); } finally { if (conn != null) { try { conn.close(); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); log.error("Error while closing connection", sqlEx); } } } }
public static List<Departamento> buscarDepartamentos() { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); CallableStatement cs = null; ResultSet rs = null; try { List<Departamento> departamentos = new ArrayList(); cs = connection.prepareCall("{ call listaDepartamento() }"); rs = cs.executeQuery(); while (rs.next()) { Departamento dpo = new Departamento(rs.getInt("idDepartamento"), rs.getString("nombreDepartamento")); departamentos.add(dpo); } return departamentos; } catch (Exception ex) { ex.printStackTrace(); return null; } finally { DBUtil.closeResultSet(rs); DBUtil.closeStatement(cs); pool.freeConnection(connection); } }
public boolean guardarSubDependencia( orgen_ta_subdependencia obj, String nomUsuario, int idUsuario) { boolean estado = false; try { cn = ds.getConnection(); cn.setAutoCommit(false); String sql = "{ CALL SP_ORGEN_TA_SUBDEPENDENCIA_INS(?,?,?,?,?) }"; CallableStatement cstm = cn.prepareCall(sql); cstm.setString("P_VC_NOMBRE", obj.getVc_nombre().toUpperCase()); cstm.setString("P_VC_DESCRIPCION", obj.getVc_descripcion().toUpperCase()); cstm.setString("P_VC_USUARIO_CREA", nomUsuario); cstm.setInt("P_IN_CODIGO_USUARIO", idUsuario); cstm.setInt("P_IN_CODIGO_DEP", obj.getIn_codigo_dependecia()); cstm.executeUpdate(); cn.commit(); estado = true; cstm.close(); } catch (Exception e) { System.out.println("Failed to execute a JDBC task: " + e); estado = false; } finally { try { cn.close(); } catch (Exception ex) { System.out.println("finally - Failed to finalize JDBC task: " + ex); } return estado; } }
public boolean cambiarEstado(orgen_ta_subdependencia obj, String idUsu) { boolean exito = false; try { cn = ds.getConnection(); cn.setAutoCommit(false); String sql = "{ CALL SP_ORGEN_TA_SUBDEPENDENCIA_ELI(?,?,?) }"; CallableStatement cstm = cn.prepareCall(sql); cstm.setInt("P_IN_CODIGO_SUBDEPENDENCIA", obj.getIn_codigo_subdependencia()); cstm.setString("P_CH_ESTADO", obj.getCh_estado()); cstm.setInt("P_IN_CODIGO_USUARIO", Integer.parseInt(idUsu)); cstm.executeUpdate(); cn.commit(); cstm.close(); exito = true; } catch (Exception e) { exito = false; System.out.println("Failed to execute a JDBC task: " + e); } finally { try { cn.close(); } catch (Exception ex) { System.out.println("finally - Failed to finalize JDBC task: " + ex); } } return exito; }