Example usage for java.sql CallableStatement registerOutParameter

Introduction

In this page you can find the example usage for java.sql CallableStatement registerOutParameter.

Prototype

default void registerOutParameter(String parameterName, SQLType sqlType) throws SQLException 

Source Link

Document

Registers the OUT parameter named parameterName to the JDBC type sqlType .

Usage

From source file:Main.java

public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);/*  ww w . ja v  a  2s  .  com*/

    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";

    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement cs = connection.prepareCall("{? = call myfuncout(?)}");

    // Register the types of the return value and OUT parameter
    cs.registerOutParameter(1, Types.VARCHAR);
    cs.registerOutParameter(2, Types.VARCHAR);

    // Execute and retrieve the returned values
    cs.execute();
    String retValue = cs.getString(1); // return value
    String outParam = cs.getString(2); // OUT parameter
}

---

From source file:Main.java

public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);/*from  w w w.j  av  a  2  s  .  c  om*/

    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";

    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement cs = connection.prepareCall("{? = call myfunc}");

    // Register the type of the return value
    int intValue = 0;
    cs.registerOutParameter(1, intValue);

    // Execute and retrieve the returned value
    cs.execute();
    String retValue = cs.getString(1);

}

---

From source file:Main.java

public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);//from  w ww  .j  a  v a 2s  . co  m

    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";

    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement cs = connection.prepareCall("{? = call myfuncinout(?)}");

    // Register the types of the return value and OUT parameter
    cs.registerOutParameter(1, Types.VARCHAR);
    cs.registerOutParameter(2, Types.VARCHAR);

    // Set the value for the IN/OUT parameter
    cs.setString(2, "a string");

    // Execute and retrieve the returned values
    cs.execute();
    String retValue = cs.getString(1); // return value
    String outParam = cs.getString(2); // IN/OUT parameter

}

---

From source file:Main.java

public static void main(String[] argv) throws Exception {
    Class.forName(DB_DRIVER);/*from  w ww.  java  2 s .c o m*/
    Connection dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);

    CallableStatement callableStatement = null;
    String getPERSONByUserIdSql = "{call getPERSONByUserId(?,?,?,?)}";
    callableStatement = dbConnection.prepareCall(getPERSONByUserIdSql);

    callableStatement.setInt(1, 10);
    callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
    callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
    callableStatement.registerOutParameter(4, java.sql.Types.DATE);

    callableStatement.executeUpdate();

    String userName = callableStatement.getString(2);
    String createdBy = callableStatement.getString(3);
    Date createdDate = callableStatement.getDate(4);

    System.out.println("UserName : " + userName);
    System.out.println("CreatedBy : " + createdBy);
    System.out.println("CreatedDate : " + createdDate);
    callableStatement.close();
    dbConnection.close();
}

---

From source file:Main.java

public static void main(String[] args) throws Exception {
    String WRITE_OBJECT_SQL = "BEGIN " + "  INSERT INTO java_objects(object_id, object_name, object_value) "
            + "  VALUES (?, ?, empty_blob()) " + "  RETURN object_value INTO ?; " + "END;";
    String READ_OBJECT_SQL = "SELECT object_value FROM java_objects WHERE object_id = ?";

    Connection conn = getOracleConnection();
    conn.setAutoCommit(false);/*from w  w  w . j  av  a  2  s . c o  m*/
    List<Object> list = new ArrayList<Object>();
    list.add("This is a short string.");
    list.add(new Integer(1234));
    list.add(new java.util.Date());

    // write object to Oracle
    long id = 0001;
    String className = list.getClass().getName();
    CallableStatement cstmt = conn.prepareCall(WRITE_OBJECT_SQL);

    cstmt.setLong(1, id);
    cstmt.setString(2, className);

    cstmt.registerOutParameter(3, java.sql.Types.BLOB);

    cstmt.executeUpdate();
    BLOB blob = (BLOB) cstmt.getBlob(3);
    OutputStream os = blob.getBinaryOutputStream();
    ObjectOutputStream oop = new ObjectOutputStream(os);
    oop.writeObject(list);
    oop.flush();
    oop.close();
    os.close();

    // Read object from oracle
    PreparedStatement pstmt = conn.prepareStatement(READ_OBJECT_SQL);
    pstmt.setLong(1, id);
    ResultSet rs = pstmt.executeQuery();
    rs.next();
    InputStream is = rs.getBlob(1).getBinaryStream();
    ObjectInputStream oip = new ObjectInputStream(is);
    Object object = oip.readObject();
    className = object.getClass().getName();
    oip.close();
    is.close();
    rs.close();
    pstmt.close();
    conn.commit();

    // de-serialize list a java object from a given objectID
    List listFromDatabase = (List) object;
    System.out.println("[After De-Serialization] list=" + listFromDatabase);
    conn.close();
}

---

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getOracleConnection();
    // Step-2: identify the stored procedure
    String proc3StoredProcedure = "{ call proc3(?, ?, ?) }";
    // Step-3: prepare the callable statement
    CallableStatement cs = conn.prepareCall(proc3StoredProcedure);
    // Step-4: set input parameters ...
    // first input argument
    cs.setString(1, "abcd");
    // third input argument
    cs.setInt(3, 10);//from   w  ww.j  a va 2s .  c o  m
    // Step-5: register output parameters ...
    cs.registerOutParameter(2, java.sql.Types.VARCHAR);
    cs.registerOutParameter(3, java.sql.Types.INTEGER);
    // Step-6: execute the stored procedures: proc3
    cs.execute();
    // Step-7: extract the output parameters
    // get parameter 2 as output
    String param2 = cs.getString(2);
    // get parameter 3 as output
    int param3 = cs.getInt(3);
    System.out.println("param2=" + param2);
    System.out.println("param3=" + param3);
    conn.close();
}

---

From source file:Main.java

public static int storedProcWithResultSet() throws Exception {
    Connection conn = null;//from  www  . j  av  a  2  s. c o m
    CallableStatement cs = conn.prepareCall("{? = call proc (?,?,?,?,?,?,?)}");

    // register input parameters
    cs.setString(2, "");
    cs.setString(3, "");
    cs.setString(4, "123");
    // regsiter ouput parameters
    cs.registerOutParameter(5, java.sql.Types.CHAR);
    cs.registerOutParameter(6, java.sql.Types.CHAR);
    cs.registerOutParameter(7, java.sql.Types.CHAR);

    // Procedure execution
    ResultSet rs = cs.executeQuery();

    ResultSetMetaData rsmd = rs.getMetaData();
    int nbCol = rsmd.getColumnCount();
    while (rs.next()) {
        for (int i = 1; i <= nbCol; i++) {
            System.out.println(rs.getString(i));
            System.out.println(rs.getString(i));
        }
    }
    // OUTPUT parameters
    System.out.println("return code of Stored procedure = : " + cs.getInt(1));
    for (int i = 5; i <= 7; i++)
        System.out.println("parameter " + i + " : " + cs.getString(i));
    return cs.getInt(1);
}

---

From source file:com.ibm.research.rdf.store.runtime.service.sql.UpdateHelper.java

private static String executeCall(Connection conn, String sql, int retPid, Object... params) {

    CallableStatement stmt = null;
    String ret = null;/*from www. j a  v  a 2 s . c o m*/

    try {
        conn.setAutoCommit(false);
    } catch (SQLException ex) {
        log.error(ex);
        ex.printStackTrace();
        System.out.println(ex.getLocalizedMessage());
        return ret;
    }

    try {

        stmt = conn.prepareCall(sql);
        int i = 1;
        for (Object o : params) {
            stmt.setObject(i, o);
            i++;
        }

        stmt.registerOutParameter(retPid, Types.VARCHAR);

        stmt.execute();
        ret = stmt.getString(retPid);

        conn.commit();

    } catch (SQLException e) {
        //         log.error(e);
        //         e.printStackTrace();
        //         System.out.println(e.getLocalizedMessage());
        ret = null;

        try {
            conn.rollback();
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            log.error(e1);
            e1.printStackTrace();
            System.out.println(e1.getLocalizedMessage());
            ret = null;
        }

    } finally {
        closeSQLObjects(stmt, null);
    }

    try {
        conn.setAutoCommit(true);
    } catch (SQLException ex) {
        log.error(ex);
        ex.printStackTrace();
        System.out.println(ex.getLocalizedMessage());
        ret = null;
    }

    return ret;
}

---

From source file:jongo.jdbc.JDBCExecutor.java

/**
 * Utility method which registers in a CallableStatement object the different {@link jongo.jdbc.StoredProcedureParam}
 * instances in the given list. Returns a List of {@link jongo.jdbc.StoredProcedureParam} with all the OUT parameters
 * registered in the CallableStatement/*from w  ww  .jav  a  2s.com*/
 * @param cs the CallableStatement object where the parameters are registered.
 * @param params a list of {@link jongo.jdbc.StoredProcedureParam}
 * @return a list of OUT {@link jongo.jdbc.StoredProcedureParam} 
 * @throws SQLException if we fail to register any of the parameters in the CallableStatement
 */
private static List<StoredProcedureParam> addParameters(final CallableStatement cs,
        final List<StoredProcedureParam> params) throws SQLException {
    List<StoredProcedureParam> outParams = new ArrayList<StoredProcedureParam>();
    int i = 1;
    for (StoredProcedureParam p : params) {
        final Integer sqlType = p.getType();
        if (p.isOutParameter()) {
            l.debug("Adding OUT parameter " + p.toString());
            cs.registerOutParameter(i++, sqlType);
            outParams.add(p);
        } else {
            l.debug("Adding IN parameter " + p.toString());
            switch (sqlType) {
            case Types.BIGINT:
            case Types.INTEGER:
            case Types.TINYINT:
                //                    case Types.NUMERIC:
                cs.setInt(i++, Integer.valueOf(p.getValue()));
                break;
            case Types.DATE:
                cs.setDate(i++, (Date) JongoUtils.parseValue(p.getValue()));
                break;
            case Types.TIME:
                cs.setTime(i++, (Time) JongoUtils.parseValue(p.getValue()));
                break;
            case Types.TIMESTAMP:
                cs.setTimestamp(i++, (Timestamp) JongoUtils.parseValue(p.getValue()));
                break;
            case Types.DECIMAL:
                cs.setBigDecimal(i++, (BigDecimal) JongoUtils.parseValue(p.getValue()));
                break;
            case Types.DOUBLE:
                cs.setDouble(i++, Double.valueOf(p.getValue()));
                break;
            case Types.FLOAT:
                cs.setLong(i++, Long.valueOf(p.getValue()));
                break;
            default:
                cs.setString(i++, p.getValue());
                break;
            }
        }
    }
    return outParams;
}

---

From source file:cn.gov.scciq.timer.acceptOrder.FRMDao.java

/**
 * ???CIQ???????CIQ?//from  w  w w. j  av a2s  .co m
 */
public static int saveDeclInfo(String declNo) {
    int retCode = -1;
    Connection conn = null;
    CallableStatement proc = null;
    String call = "{call Pro_SaveDeclInfo(?,?)}";
    try {
        conn = DBPool.ds.getConnection();
        proc = conn.prepareCall(call);
        proc.setString(1, declNo);
        proc.registerOutParameter(2, Types.INTEGER);
        proc.execute();
        retCode = proc.getInt(2);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        log.error("N48", e);
    } catch (Exception e) {
        log.error("N49", e);
    } finally {
        try {
            if (proc != null) {
                proc.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            log.error("N50", e);
        }
    }
    return retCode;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »