List of usage examples for java.sql CallableStatement setString
void setString(String parameterName, String x) throws SQLException;
String
value. From source file:Main.java
public static void main(String[] argv) throws Exception { Connection conn = null;/*from w w w .j a v a2 s .c om*/ String query = "begin proc(?,?,?); end;"; CallableStatement cs = conn.prepareCall(query); cs.setString(1, "string parameter"); cs.setInt(2, 1); cs.registerOutParameter(2, Types.INTEGER); cs.registerOutParameter(3, Types.INTEGER); cs.execute(); int parm2 = cs.getInt(2); // get the result from OUTPUT #2 int parm3 = cs.getInt(3); // get the result from OUTPUT #3 }
From source file:Main.java
public static void main(String[] args) throws Exception { // DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); final Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "manager"); String plsql = " declare " + " p_id varchar2(20) := null; " + " l_rc sys_refcursor;" + " begin " + " p_id := ?; " + " ? := 'input parameter was = ' || p_id;" + " open l_rc for " + " select 1 id, 'abc' name from dual; " + " ? := l_rc;" + " end;"; CallableStatement cs = c.prepareCall(plsql); cs.setString(1, "12345"); cs.registerOutParameter(2, Types.VARCHAR); // cs.registerOutParameter(3, OracleTypes.CURSOR); cs.execute();/*from w w w. j av a 2 s . c o m*/ System.out.println("Result = " + cs.getObject(2)); ResultSet cursorResultSet = (ResultSet) cs.getObject(3); while (cursorResultSet.next()) { System.out.println(cursorResultSet.getInt(1) + " " + cursorResultSet.getString(2)); } cs.close(); c.close(); }
From source file:Main.java
public static void main(String[] argv) throws Exception { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); Connection con = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE", "USERID", "PASSWORD"); CallableStatement proc_stmt = con.prepareCall("{ call generateID(?) }"); proc_stmt.setString(1, "employee"); ResultSet rs = proc_stmt.executeQuery(); if (rs.next()) { int employeeId = rs.getInt(1); System.out.println("Generated employeeId: " + employeeId); } else {/* ww w . j a v a2 s . co m*/ System.out.println("Stored procedure couldn't generate new Id"); } }
From source file:CallableStmt.java
public static void main(String args[]) throws Exception { String storedProc = "create procedure SHOW_ORDERS_BY_STATE @State CHAR (2) as " + "select c.Last_Name+', '+c.First_Name AS Name,o.Order_Number " + "from CUSTOMERS c, ORDERS o where c.Customer_Number = o.Customer_Number " + "AND c.State = @State order by c.Last_Name;"; Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con = DriverManager.getConnection("jdbc:odbc:Customers"); Statement stmt = con.createStatement(); stmt.executeUpdate(storedProc);// w ww . j a v a 2 s. c om CallableStatement cs = con.prepareCall("{call SHOW_ORDERS_BY_STATE(?)}"); cs.setString(1, "NJ"); ResultSet rs = cs.executeQuery(); while (rs.next()) { String name = rs.getString("Name"); int orderNo = rs.getInt("Order_Number"); System.out.println(name + ": " + orderNo); } }
From source file:Main.java
public static void main(String[] args) throws Exception { Class.forName("oracle.jdbc.driver.OracleDriver"); Connection conn = DriverManager.getConnection(url, username, password); CallableStatement stmt = conn.prepareCall("{call CREATE_USERS (?, ?, ?, ?, ?, ?)}"); stmt.setString(1, "a"); stmt.setString(2, "b"); stmt.setString(3, "c"); stmt.setString(4, "d"); stmt.setString(5, "e"); stmt.setString(6, "f"); stmt.execute();/*from ww w.j a va 2s .com*/ conn.close(); }
From source file:Main.java
public static void main(String[] argv) throws Exception { String driverName = "com.jnetdirect.jsql.JSQLDriver"; Class.forName(driverName);//ww w . jav a 2 s. 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); // Call a procedure with no parameters CallableStatement cs = connection.prepareCall("{call myprocin(?)}"); // Set the value for the IN parameter cs.setString(1, "a string"); cs.execute(); }
From source file:Main.java
public static void main(String args[]) throws Exception { String URL = "jdbc:microsoft:sqlserver://yourServer:1433;databasename=pubs"; String userName = "yourUser"; String password = "yourPassword"; Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance(); Connection con = DriverManager.getConnection(URL, userName, password); CallableStatement callstmt = con .prepareCall("INSERT INTO myIdentTable (col2) VALUES (?);SELECT @@IDENTITY"); callstmt.setString(1, "testInputBatch"); callstmt.execute();/*w w w. jav a 2 s .c o m*/ int iUpdCount = callstmt.getUpdateCount(); boolean bMoreResults = true; ResultSet rs = null; int myIdentVal = -1; // to store the @@IDENTITY while (bMoreResults || iUpdCount != -1) { rs = callstmt.getResultSet(); if (rs != null) { rs.next(); myIdentVal = rs.getInt(1); } bMoreResults = callstmt.getMoreResults(); iUpdCount = callstmt.getUpdateCount(); } callstmt.close(); con.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 void main(String[] argv) throws Exception { String driverName = "com.jnetdirect.jsql.JSQLDriver"; Class.forName(driverName);// w w w.j a v a 2 s .c o 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 myfuncin(?)}"); cs.registerOutParameter(1, Types.VARCHAR); // Set the value for the IN parameter cs.setString(2, "a string"); // Execute and retrieve the returned value cs.execute(); String retValue = cs.getString(1); }
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);/*ww w . j a v a2s. co 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(); }