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;
 }
示例#3
0
  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) {
      }
    }
  }
示例#6
0
  @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();
  }
示例#7
0
  // 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");
  }
示例#8
0
  public static void main(String args[]) throws Exception {

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

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

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

    // Execute it
    cs.execute();

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

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

    // Print the flight number to the screen
    System.out.println("New flight number is " + rs.getInt("flightNumber"));
  }
  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));
    }
  }
示例#10
0
  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();
    }
  }
示例#11
0
  @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
示例#12
0
  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;
    }
  }
示例#15
0
  @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;
  }
示例#18
0
  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);
    }
  }
示例#19
0
  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);
    }
  }
示例#20
0
  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);
    }
  }
示例#21
0
 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;
 }
示例#22
0
 /**
  * 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;
 }
示例#23
0
  @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;
  }
示例#24
0
  @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) {
     }
   }
 }
示例#27
0
 /**
  * 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);
       }
     }
   }
 }
示例#28
0
  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;
 }