executeStoredProcedure.java

/*
 * Microsoft JDBC Driver for SQL Server
 * 
 * Copyright(c) Microsoft Corporation All rights reserved.
 * 
 * This program is made available under the terms of the MIT License. See the LICENSE file in the project root for more information.
 */

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.Reader;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.microsoft.sqlserver.jdbc.SQLServerCallableStatement;
import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

public class executeStoredProcedure {

    public static void main(String[] args) {

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

        String serverName = null;
        String portNumber = null;
        String databaseName = null;
        String username = null;
        String password = null;

        try (BufferedReader br = new BufferedReader(new InputStreamReader(System.in))) {

            System.out.print("Enter server name: ");
            serverName = br.readLine();
            System.out.print("Enter port number: ");
            portNumber = br.readLine();
            System.out.print("Enter database name: ");
            databaseName = br.readLine();
            System.out.print("Enter username: ");
            username = br.readLine();
            System.out.print("Enter password: ");
            password = br.readLine();

            // Establish the connection.
            SQLServerDataSource ds = new SQLServerDataSource();
            ds.setServerName(serverName);
            ds.setPortNumber(Integer.parseInt(portNumber));
            ds.setDatabaseName(databaseName);
            ds.setUser(username);
            ds.setPassword(password);

            con = ds.getConnection();

            createTable(con);
            createStoredProcedure(con);

            // 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 Document_JDBC_Sample " + "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 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);

            System.out.println("Document_Summary in half: " + stringOutput1);
            System.out.println("Document_Summary: " + stringOutput2);

            // 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) {
                }
        }
    }

    private static void createStoredProcedure(Connection con) throws SQLException {
        Statement stmt = con.createStatement();

        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(Connection con) throws SQLException {
        Statement stmt = con.createStatement();

        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);
    }
}