Set - 6

Question 1 :

How do I execute stored procedures?

Answer :

Here is an example on how to execute a stored procedure with JDBC (to use this in a servlet is the same the only thing is that you create the connection and callable statement in the init() of the servlet):
package DBTest;
import java.sql.*;
public class JdbcTest {

private String msDbUrl = "jdbc:odbc:ms";
private String msJdbcClass = "sun.jdbc.odbc.JdbcOdbcDriver";
private Connection mcDbAccess;
private CallableStatement msProcedure;

public JdbcTest() {
try {
Class.forName( msDbUrl ).newInstance();
mcDbAccess = DriverManager.getConnection( msJdbcClass, "milestone", "milestone" );
msProcedure = mcDbAccess.prepareCall(
"{? = call sp_sav_Bom_Header( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }"
msProcedure.registerOutParameter( 1, java.sql.Types.VARCHAR );
msProcedure.setInt( 2, -1 );
msProcedure.setInt( 3, 39 );
msProcedure.setString( 4, "format" );
long ltTest = new java.util.Date().getTime();
System.out.println( "Today: " + ltTest );
msProcedure.setTimestamp( 5, new Timestamp( ltTest ) );
msProcedure.setString( 6, "type" );
msProcedure.setString( 7, "submitter" );
msProcedure.setString( 8, "email" );
msProcedure.setString( 9, "phone" );
msProcedure.setString( 10, "comments" );
msProcedure.setString( 11, "label" );
msProcedure.setInt( 12, 52 );
msProcedure.setBoolean( 13, true );
msProcedure.setBoolean( 14, false );
msProcedure.setInt( 15, 53 );
msProcedure.setString( 16, "runtime" );
msProcedure.setString( 17, "configuration" );
msProcedure.setBoolean( 18, true );
msProcedure.setBoolean( 19, false );
msProcedure.setString( 20, "special instructions" );
msProcedure.setInt( 21, 54 );

ResultSet lrsReturn = null;
System.out.println( "Execute: " + (lrsReturn = msProcedure.executeQuery() ) );
while( ) {
System.out.println( "Got from result set: " + lrsReturn.getInt( 1 ) );
System.out.println( "Got from stored procedure: " + msProcedure.getString( 1 ) );
} catch( Throwable e ) {

public static void main(String[] args) {
new JdbcTest();
I also tried it by using a native JDBC driver (i-net) and it also works fine. The only problem we encounter with JDBC-ODBC bridge is that a stored procedure pads spaces to the full length of a VARCHAR but the native JDBC behaves right. Therefore I suggest to use JDBC native drivers.
The above example uses the MS SQL Server.

Question 2 :

How can I get data from multiple ResultSets?

Answer :

With certain database systems, a stored procedure can return multiple result sets, multiple update counts, or some combination of both. Also, if you are providing a user with the ability to enter any SQL statement, you don't know if you are going to get a ResultSet or an update count back from each statement, without analyzing the contents. The Statement.execute() method helps in these cases.

Method Statement.execute() returns a boolean to tell you the type of response:

* true indicates next result is a ResultSet
Use Statement.getResultSet to get the ResultSet
* false indicates next result is an update count
Use Statement.getUpdateCount to get the update count
* false also indicates no more results
Update count is -1 when no more results (usually 0 or positive)

After processing each response, you use Statement.getMoreResults to check for more results, again returning a boolean. The following demonstrates the processing of multiple result sets:

boolean result = stmt.execute(" ... ");
int updateCount = stmt.getUpdateCount();

while (result || (updateCount != -1)) {
if(result) {
ResultSet r = stmt.getResultSet();
// process result set
} else if(updateCount != -1) {
// process update count
result = stmt.getMoreResults();
updateCount = stmt.getUpdateCount();

Question 3 :

How can resultset records be restricted to certain rows?

Answer :

The easy answer is "Use a JDBC 2.0 compliant driver".
With a 2.0 driver, you can use the setFetchSize() method within a Statement or a ResultSet object.
For example,
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from customers");

will change the default fetch size to 400.
You can also control the direction in which the rows are processed. For instance:
will process the rows from bottom up.
The driver manager usually defaults to the most efficient fetch you may try experimenting with different value for optimal performance.

Question 4 :

How do I insert an image file (or other raw data) into a database?

Answer :

All raw data types (including binary documents or images) should be read and uploaded to the database as an array of bytes, byte[]. Originating from a binary file,
1. Read all data from the file using a FileInputStream.
2. Create a byte array from the read data.
3. Use method setBytes(int index, byte[] data); of java.sql.PreparedStatement to upload the data.

Question 5 :

How can I connect from an applet to a database on the server?

Answer :

There are two ways of connecting to a database on the server side.
1. The hard way. Untrusted applets cannot touch the hard disk of a computer. Thus, your applet cannot use native or other local files (such as JDBC database drivers) on your hard drive. The first alternative solution is to create a digitally signed applet which may use locally installed JDBC drivers, able to connect directly to the database on the server side.
2. The easy way. Untrusted applets may only open a network connection to the server from which they were downloaded. Thus, you must place a database listener (either the database itself, or a middleware server) on the server node from which the applet was downloaded. The applet would open a socket connection to the middleware server, located on the same computer node as the webserver from which the applet was downloaded. The middleware server is used as a mediator, connecting to and extract data from the database.