Reading Large Data Sample

This Microsoft SQL Server JDBC Driver sample application demonstrates how to retrieve a large single-column value from a SQL Server database by using the getCharacterStream method.

The code file for this sample is named readLargeData.java, and it 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.

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.

In addition, the sample code demonstrates how to get the adaptive buffering mode by using the getResponseBuffering method of the SQLServerStatement class. Note that starting with the JDBC driver version 2.0 release, the responseBuffering connection property is set to "adaptive" by default.

Then, using an SQL statement with the SQLServerStatement object, the sample code runs the SQL statement and places the data that it returns into a SQLServerResultSet object.

Finally, the sample code iterates through the rows of data that are contained in the result set, and uses the getCharacterStream method to access some of the data that it contains.

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

public class readLargeData {
    
   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();

          // In adaptive mode, the application does not have to use a server cursor 
          // to avoid OutOfMemoryError when the SELECT statement produces very large
          // results. 

          // Create and execute an SQL statement that returns some data.
          String SQL = "SELECT Title, DocumentSummary " +
                       "FROM Production.Document";
          stmt = con.createStatement();

          // Display the response buffering mode.
          SQLServerStatement SQLstmt = (SQLServerStatement) stmt;          
          System.out.println("Response buffering mode is: " +
             SQLstmt.getResponseBuffering());              
          
          // Get the updated data from the database and display it.
          rs = stmt.executeQuery(SQL);
                      
          while (rs.next()) {
               Reader reader = rs.getCharacterStream(2);
               if (reader != null)
               {
                  char output[] = new char[40];
                  while (reader.read(output) != -1)
                  {
                      // Do something with the chunk of the data that was                       
                      // read.
                  }              
 
                  System.out.println(rs.getString(1) + 
                      " has been accessed for the summary column.");
                  // 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