Reading Large Data with Stored Procedures Sample

This Microsoft SQL Server JDBC Driver sample application demonstrates how to retrieve a large OUT parameter from a stored procedure.

The code file for this sample is named executeStoredProcedure.java, and can be found in the following location:

<installation directory>\sqljdbc_<version>\<language>\help\samples\adaptive

Requirements

To run this sample application, you will need access to the SQL Server 2005 AdventureWorks sample database. You must also set the classpath to include the sqljdbc.jar file or sqljdbc4.jar file. If the classpath is missing an entry for sqljdbc.jar or sqljdbc4.jar, the sample application will throw the common "Class not found" exception. For more information about how to set the classpath, see Using the JDBC Driver.

You must also create the following stored procedure in the SQL Server 2005 AdventureWorks sample database:

CREATE PROCEDURE GetLargeDataValue 
  (@Document_ID int, 
   @Document_ID_out int OUTPUT, 
   @Document_Title varchar(50) OUTPUT,
   @Document_Summary nvarchar(max) OUTPUT)

AS 
BEGIN  
   SELECT @Document_ID_out = DocumentID, 
          @Document_Title = Title,
          @Document_Summary = DocumentSummary 
    FROM  Production.Document
    WHERE DocumentID = @Document_ID
END

Example

In the following example, the sample code makes a connection to the SQL Server 2005 AdventureWorks database. Next, the sample code creates sample data and updates the Production.Document table by using a parameterized query. Then, the sample code gets the adaptive buffering mode by using the getResponseBuffering method of the SQLServerStatement class and executes the GetLargeDataValue stored procedure. Note that starting with the JDBC driver version 2.0 release, the responseBuffering connection property is set to "adaptive" by default.

Finally, the sample code displays the data returned with the OUT parameters and also demonstrates how to use the mark and reset methods on the stream to re-read any portion of the data.

import java.sql.*;
import java.io.*;
import com.microsoft.sqlserver.jdbc.SQLServerCallableStatement;

public class executeStoredProcedure {

    public static void main(String[] args) {
        // Create a variable for the connection string.
        String connectionUrl = 
           "jdbc:sqlserver://localhost:1433;" +
           "databaseName=AdventureWorks;integratedSecurity=true;";

        // Declare the JDBC objects.
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;  

        try {
          // Establish the connection.
          Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
          con = DriverManager.getConnection(connectionUrl);
 
          // Create test data as an example.
          StringBuffer buffer = new StringBuffer(4000);
          for (int i = 0; i < 4000; i++) 
             buffer.append( (char) ('A'));

          PreparedStatement pstmt = con.prepareStatement(
               "UPDATE Production.Document " +
               "SET DocumentSummary = ? WHERE (DocumentID = 1)");
 
          pstmt.setString(1, buffer.toString());
          pstmt.executeUpdate();
          pstmt.close();

          // Query test data by using a stored procedure.
          CallableStatement cstmt = 
             con.prepareCall("{call dbo.GetLargeDataValue(?, ?, ?, ?)}");

          cstmt.setInt(1, 1);
          cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
          cstmt.registerOutParameter(3, java.sql.Types.CHAR);
          cstmt.registerOutParameter(4, java.sql.Types.LONGVARCHAR);

          // Display the response buffering mode.
          SQLServerCallableStatement SQLcstmt = (SQLServerCallableStatement) cstmt;
          System.out.println("Response buffering mode is: " +
               SQLcstmt.getResponseBuffering());

          SQLcstmt.execute();
          System.out.println("DocumentID: " + cstmt.getInt(2));
          System.out.println("Document_Title: " + cstmt.getString(3));

          Reader reader = SQLcstmt.getCharacterStream(4);

          // If your application needs to re-read any portion of the value, 
          // it must call the mark method on the InputStream or Reader to 
          // start buffering data that is to be re-read after a subsequent
          // call to the reset method.            
          reader.mark(4000);

          // Read the first half of data.
          char output1[] = new char[2000];
          reader.read(output1);
          String stringOutput1 = new String(output1);

          // Reset the stream.
          reader.reset();

          // Read all the data.
          char output2[] = new char[4000];
          reader.read(output2);
          String stringOutput2 = new String(output2);

          // Close the stream.
          reader.close();
      }
      // Handle any errors that may have occurred.
      catch (Exception e) {
          e.printStackTrace();
      }
      finally {
          if (rs != null) try { rs.close(); } catch(Exception e) {}
          if (stmt != null) try { stmt.close(); } catch(Exception e) {}
          if (con != null) try { con.close(); } catch(Exception e) {}
      }
   }
}

See Also

Other Resources

Working with Large Data