002-jdbc-sqlserver.ppt

Using Callable Statements Review stored procedure What’s stored procedure? Create a stored procedure for the Oracle : the sp named ‘FindBySupID’: CREATE PROCEDURE FindBySupID @supid int , @name1 varchar(30) output AS begin SELECT @name1=COF_NAME FROM COFFEES WHERE SUP_ID=@supid end Using CallableStatement CallableStatement cs = con.prepareCall(“{ call showNames (?, ?) }”); cs.registerOutParameter(1, java.sql.Types.CHAR); cs.setInt(2, 2222); cs.excute(); String value = cs.getString(1); Example 1 Class.forName(DRIVER); Connection conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD); CallableStatement stmt = conn.prepareCall({ call FindBySupID(?,?) }); stmt.setInt(1, 123); stmt.registerOutParameter(2, java.sql.Types.CHAR); stmt.execute(); String value = stmt.getString(2); System.out.println(value); stmt.close();conn.close(); Example 2 Class.forName(DRIVER); Connection conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD); CallableStatement stmt = conn.prepareCall({ call FindCoffees() }); ResultSet rs = stmt.executeQuery() ; while(rs.next()){ String cofName = rs.getString(COF_NAME); System.out.print(cofName + \t) ; } rs.close() ;stmt.close();conn.close(); Example 2 CREATE PROCEDURE FindCoffees AS begin SELECT * FROM COFFEES end Using Database Transactions Using Transactions There are times when you do not want one statement to take effect unless another one also succeeds. For example: Take $400 out of your Checking Account. Take this $400 and transfer to your Savings Account. If the first statement succeeds, but the second one fails, you are out $400! To do with this possibility, most database support many levels of transactions. Using Transactions A transaction is a set of one or more statements that are executed together as a unit. Hence, either all of the statements are executed, or none of the statements are executed. Disabling Auto-Commit Mode When a connection is created, it is in auto-commit mode. This m