SCROLLABLE RESULTSET

So far we ‘ve been navigating JDBC result sets using the next() method, which enables us to move forward only. Another option is to create a Scrollable result set, so the cursor can navigate the result set both backward and forward. A two-argument version of the createStatement() method exists. The first argument specifies the type of scrolling (TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, TYEP_SCROLL_SENSITIVE), and the second enables us to make the result set either read-only or updateable (CONCUR_READ_ONLY or CONCUR_UPDATABLE, respectively), as in the following example:

import java.sql.*;

public class jdbc_type1_scroll {

public static void main(String a[]) throws Exception { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:TEST");

Statement st=con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rs=st.executeQuery("select * from Emp");

rs.next();

System.out.println(rs.getString(3));

rs.last();

System.out.println(rs.getString(3));

rs.previous();

System.out.println(rs.getString(3));

rs.absolute(3);

System.out.println(rs.getString(3));

rs.first();

rs.close();

st.close();

con.close();

} }

Table

ename eage ecity esalary epin

--- --- --- --- --- shimith 23 banglore 9000.0 3222

emp5 30 pune 2345.0 1221 ved 30 allahabad 2345.0 12345 bill 30 texsas 2345.0 4321 Output

3.6 PREPAREDSTATEMENT

This is a subclass of the statement class: It compiles the SQL statement before execution and can also take parameters. Let’s say we need to execute the same query – for example select * from student where id=… - multiple times. The user IDs come from the userid[] array. If we use the Statement class, this SQL query will be compiled in each iteration of the loop, But in Prepared Statement the SQL statement is compiled only once and parameters are provided by the appropriate setXXX() method, depending on the data type of the underlying column. For example:

Table

ename city --- --- Raj bangalo Output

Table, After Execution ename city --- --- Raj bangalo raj chennai

3.7 CALLABLESTAEMENT

A CallableStatement object provides a way to call stored procedures in a standard way for all RDBMS. A stored procedure is stored in a database; the call to the stored procedure is what a CallableStatement object contains. This call is written in an escape syntax that may take one of two forms: one form with a result parameter and the other without one. A result parameter, a kind of OUT parameter, is the return value for the stored procedure. Both forms may have a variable number of parameters used for input (IN parameters), output (OUT parameters), or both (INOUT parameters). A question mark (?) serves as a placeholder for a parameter.

The syntax for invoking a stored procedure using the JDBC API is shown here.

 Calling a stored procedure with no parameters:

 {call procedure_name}

 Calling a stored procedure with one or more IN, OUT, or INOUT parameters:

 {call procedure_name(?, ?, ...)}

 Calling a procedure that returns a result parameter and may or may not take any IN, OUT, or INOUT parameters: (Note that the square brackets indicate that what is between them is optional; they are not themselves part of the syntax.)

 {? = call procedure_name[(?, ?, ...)]}

IN Parameters

Passing in any IN parameter values to a CallableStatement object is done using setter methods.

These methods include both the setter methods inherited from the PreparedStatement interface and those defined in the CallableStatement interface. The type of the value being passed in determines which setter method to use (setFloat to pass in a float value, setBoolean to pass in a boolean, and so on).

The following code fragment uses the setter methods that take the parameter number to indicate which parameter is to be set.

String sql = "{call updateStats(?, ?)}";

CallableStatement cstmt = con.prepareCall(sql);

cstmt.setInt(1, 398);

cstmt.setDoublel(2, 0.04395);

OUT Parameters

If the stored procedure returns OUT parameters, the data type of each OUT parameter must be registered before the CallableStatement object can be executed. This is necessary because some DBMSs require the SQL type (which the JDBC type represents); the JDBC API itself does not require that the SQL type be registered. JDBC types, a set of generic SQL type identifiers that represent the most commonly used SQL types.

Registering the JDBC type is done with the method registerOutParameter. Then after the statement has been executed, the CallableStatement interface's getter methods can be used to retrieve OUT parameter values. The correct getter method to use is the type in the Java programming language that corresponds to the JDBC type registered for that parameter.

CallableStatement cstmt = con.prepareCall(

"{call getTestData(?, ?)}");

cstmt.registerOutParameter(1, java.sql.Types.TINYINT);

cstmt.registerOutParameter(2, java.sql.Types.DECIMAL);

ResultSet rs = cstmt.executeQuery();

INOUT Parameters

A parameter that supplies input as well as accepts output (an INOUT parameter) requires a call to the appropriate setter method in addition to a call to the method registerOutParameter. The setter method sets a parameter's value as an input parameter, and the method

registerOutParameter registers its JDBC type as an output parameter. The setter method provides a Java value that the driver converts to a JDBC value before sending it to the database.

The JDBC type of this IN value and the JDBC type supplied to the method registerOutParameter should be the same. If they are not the same, they should at least be types that are compatible, that is, types that can be mapped to each other. Then, to retrieve the output value, a corresponding getter method is used.

CallableStatement cstmt = con.prepareCall(

"{call reviseTotal(?)}");

cstmt.setByte(1, (byte)25);

cstmt.registerOutParameter(1, java.sql.Types.TINYINT);

cstmt.executeUpdate();

byte x = cstmt.getByte(1);

for example

import java.sql.*;

public class calldemo {

public static void main(String a[]) throws Exception { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:TEST");

CallableStatement ps=con.prepareCall("{ call pdemo(?,?)}");

ps.setInt(1,2);

ps.registerOutParameter(2,java.sql.Types.VARCHAR);

ps.executeUpdate();

String s=ps.getString(2);

System.out.println(s);

} }

Stored procedure

CREATE PROCEDURE pdemo (@empid int,@empname varchar(10) out) AS

set @empname=(select ename from employee where eid=@empid) Table

ename city eid --- --- --- emp1 city1 1

emp2 city2 2 emp3 city3 3 output