Reading large data with stored procedures sample

Download JDBC driver

This Microsoft JDBC Driver for SQL Server 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>\samples\adaptive

Requirements

To run this sample application, you'll need access to the AdventureWorks2022 sample database. Set the classpath to include the mssql-jdbc jar file. For more information about how to set the classpath, see Using the JDBC Driver.

Note

The Microsoft JDBC Driver for SQL Server provides mssql-jdbc class library files to be used depending on your preferred Java Runtime Environment (JRE) settings. For more information about which JAR file to choose, see System Requirements for the JDBC Driver.

The sample would create the required stored procedure in the AdventureWorks2022 sample database:

Example

This sample code:

1. Makes a connection to the AdventureWorks2022 database.
2. Creates sample data and updates the Production.Document table by using a parameterized query. Finally, the sample code gets the adaptive buffering mode by using the getResponseBuffering method of the SQLServerStatement class and executes the GetLargeDataValue stored procedure. 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.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import com.microsoft.sqlserver.jdbc.SQLServerCallableStatement;


public class ExecuteStoredProcedures {

    public static void main(String[] args) {

        String connectionUrl = "jdbc:sqlserver://<server>:<port>;databaseName=AdventureWorks;user=<user>;password=<password>";

        try (Connection con = DriverManager.getConnection(connectionUrl); Statement stmt = con.createStatement()) {

            createTable(stmt);
            createStoredProcedure(stmt);

            // Create test data as an example.
            StringBuffer buffer = new StringBuffer(4000);
            for (int i = 0; i < 4000; i++)
                buffer.append((char) ('A'));

            try (PreparedStatement pstmt = con.prepareStatement(
                    "UPDATE Document_JDBC_Sample " + "SET DocumentSummary = ? WHERE (DocumentID = 1)")) {

                pstmt.setString(1, buffer.toString());
                pstmt.executeUpdate();
            }

            // Query test data by using a stored procedure.
            try (SQLServerCallableStatement cstmt = (SQLServerCallableStatement) con
                    .prepareCall("{call 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.
                System.out.println("Response buffering mode is: " + cstmt.getResponseBuffering());

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

                try (Reader reader = cstmt.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);

                    System.out.println("Document_Summary in half: " + stringOutput1);
                    System.out.println("Document_Summary: " + stringOutput2);
                }
            }
        }
        // Handle any errors that may have occurred.
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void createStoredProcedure(Statement stmt) throws SQLException {
        String outputProcedure = "GetLargeDataValue";

        String sql = " IF EXISTS (select * from sysobjects where id = object_id(N'" + outputProcedure
                + "') and OBJECTPROPERTY(id, N'IsProcedure') = 1)" + " DROP PROCEDURE " + outputProcedure;
        stmt.execute(sql);

        sql = "CREATE PROCEDURE " + outputProcedure + " @p0 int, @p1 int OUTPUT, @p2 char(50) OUTPUT, "
                + "@p3 varchar(max) OUTPUT " + " AS" + " SELECT top 1 @p1=DocumentID, @p2=Title,"
                + " @p3=DocumentSummary FROM Document_JDBC_Sample where DocumentID = @p0";

        stmt.execute(sql);
    }

    private static void createTable(Statement stmt) throws SQLException {
        stmt.execute("if exists (select * from sys.objects where name = 'Document_JDBC_Sample')"
                + "drop table Document_JDBC_Sample");

        String sql = "CREATE TABLE Document_JDBC_Sample(" + "[DocumentID] [int] NOT NULL identity,"
                + "[Title] [char](50) NOT NULL," + "[DocumentSummary] [varchar](max) NULL)";

        stmt.execute(sql);

        sql = "INSERT Document_JDBC_Sample VALUES ('title1','summary1') ";
        stmt.execute(sql);

        sql = "INSERT Document_JDBC_Sample VALUES ('title2','summary2') ";
        stmt.execute(sql);

        sql = "INSERT Document_JDBC_Sample VALUES ('title3','summary3') ";
        stmt.execute(sql);
    }
}

See also

Working with large data