Examples with SQLServerCallableStatement com.microsoft.sqlserver.jdbc.SQLServerCallableStatement used on opensource projects

Example 1 with SQLServerCallableStatement

use of com.microsoft.sqlserver.jdbc.SQLServerCallableStatement in project mssql-jdbc by Microsoft.

the class executeStoredProcedure method main.

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

Example 2 with SQLServerCallableStatement

use of com.microsoft.sqlserver.jdbc.SQLServerCallableStatement in project mssql-jdbc by Microsoft.

the class CallableStatementTest method testMixedProcedureNumericPrcisionScaleParameterName.

private void testMixedProcedureNumericPrcisionScaleParameterName(String sql) throws SQLException {
    try (SQLServerCallableStatement callableStatement = (SQLServerCallableStatement) Util.getCallableStmt(con, sql, stmtColEncSetting)) {
        callableStatement.registerOutParameter("p1", java.sql.Types.DECIMAL, 18, 0);
        callableStatement.registerOutParameter("p2", java.sql.Types.DECIMAL, 10, 5);
        callableStatement.registerOutParameter("p3", java.sql.Types.NUMERIC, 18, 0);
        callableStatement.registerOutParameter("p4", java.sql.Types.NUMERIC, 8, 2);
        callableStatement.setBigDecimal("p2", new BigDecimal(numericValues[9]), 10, 5);
        callableStatement.setBigDecimal("p4", new BigDecimal(numericValues[11]), 8, 2);
        callableStatement.execute();
        BigDecimal value1 = callableStatement.getBigDecimal(1);
        assertEquals(value1, new BigDecimal(numericValues[8]), "Test for input output parameter fails.\n");
        BigDecimal value2 = callableStatement.getBigDecimal(2);
        assertEquals(value2, new BigDecimal(numericValues[9]), "Test for input output parameter fails.\n");
        BigDecimal value3 = callableStatement.getBigDecimal(3);
        assertEquals(value3, new BigDecimal(numericValues[10]), "Test for input output parameter fails.\n");
        BigDecimal value4 = callableStatement.getBigDecimal(4);
        assertEquals(value4, new BigDecimal(numericValues[11]), "Test for input output parameter fails.\n");
    } catch (Exception e) {
        fail(e.toString());
    }
}

Example 3 with SQLServerCallableStatement

use of com.microsoft.sqlserver.jdbc.SQLServerCallableStatement in project mssql-jdbc by Microsoft.

the class CallableStatementTest method testMixedProcedure3RandomOrder.

private void testMixedProcedure3RandomOrder(String sql) throws SQLException {
    try (SQLServerCallableStatement callableStatement = (SQLServerCallableStatement) Util.getCallableStmt(con, sql, stmtColEncSetting)) {
        callableStatement.registerOutParameter(1, java.sql.Types.BIGINT);
        callableStatement.registerOutParameter(2, java.sql.Types.FLOAT);
        callableStatement.setInt(3, Integer.parseInt(numericValues[3]));
        callableStatement.setShort(4, Short.parseShort(numericValues[2]));
        callableStatement.execute();
        double floatValue = callableStatement.getDouble(2);
        assertEquals("" + floatValue, numericValues[5], "Test for output parameter fails.\n");
        long bigintValue = callableStatement.getLong(1);
        assertEquals("" + bigintValue, numericValues[4], "Test for output parameter fails.\n");
        long bigintValue1 = callableStatement.getLong(1);
        assertEquals("" + bigintValue1, numericValues[4], "Test for output parameter fails.\n");
        double floatValue2 = callableStatement.getDouble(2);
        assertEquals("" + floatValue2, numericValues[5], "Test for output parameter fails.\n");
        double floatValue3 = callableStatement.getDouble(2);
        assertEquals("" + floatValue3, numericValues[5], "Test for output parameter fails.\n");
        long bigintValue3 = callableStatement.getLong(1);
        assertEquals("" + bigintValue3, numericValues[4], "Test for output parameter fails.\n");
    } catch (Exception e) {
        fail(e.toString());
    }
}

Example 4 with SQLServerCallableStatement

use of com.microsoft.sqlserver.jdbc.SQLServerCallableStatement in project mssql-jdbc by Microsoft.

the class CallableStatementTest method testOutputProcedureInorder.

private void testOutputProcedureInorder(String sql, String[] values) throws SQLException {
    try (SQLServerCallableStatement callableStatement = (SQLServerCallableStatement) Util.getCallableStmt(con, sql, stmtColEncSetting)) {
        callableStatement.registerOutParameter(1, java.sql.Types.INTEGER);
        callableStatement.registerOutParameter(2, java.sql.Types.DOUBLE);
        callableStatement.registerOutParameter(3, java.sql.Types.SMALLINT);
        callableStatement.registerOutParameter(4, java.sql.Types.BIGINT);
        callableStatement.registerOutParameter(5, java.sql.Types.TINYINT);
        callableStatement.registerOutParameter(6, microsoft.sql.Types.SMALLMONEY);
        callableStatement.registerOutParameter(7, microsoft.sql.Types.MONEY);
        callableStatement.execute();
        int intValue2 = callableStatement.getInt(1);
        assertEquals("" + intValue2, values[3], "Test for output parameter fails.\n");
        double floatValue0 = callableStatement.getDouble(2);
        assertEquals("" + floatValue0, values[5], "Test for output parameter fails.\n");
        short shortValue3 = callableStatement.getShort(3);
        assertEquals("" + shortValue3, values[2], "Test for output parameter fails.\n");
        long bigintValue = callableStatement.getLong(4);
        assertEquals("" + bigintValue, values[4], "Test for output parameter fails.\n");
        short tinyintValue = callableStatement.getShort(5);
        assertEquals("" + tinyintValue, values[1], "Test for output parameter fails.\n");
        BigDecimal smallMoney1 = callableStatement.getSmallMoney(6);
        assertEquals("" + smallMoney1, values[12], "Test for output parameter fails.\n");
        BigDecimal money1 = callableStatement.getMoney(7);
        assertEquals("" + money1, values[13], "Test for output parameter fails.\n");
    } catch (Exception e) {
        fail(e.toString());
    }
}

Example 5 with SQLServerCallableStatement

use of com.microsoft.sqlserver.jdbc.SQLServerCallableStatement in project mssql-jdbc by Microsoft.

the class CallableStatementTest method testOutputProcedure2RandomOrder.

private void testOutputProcedure2RandomOrder(String sql, String[] values) throws SQLException {
    try (SQLServerCallableStatement callableStatement = (SQLServerCallableStatement) Util.getCallableStmt(con, sql, stmtColEncSetting)) {
        callableStatement.registerOutParameter(1, java.sql.Types.INTEGER);
        callableStatement.registerOutParameter(2, java.sql.Types.INTEGER);
        callableStatement.registerOutParameter(3, java.sql.Types.SMALLINT);
        callableStatement.registerOutParameter(4, java.sql.Types.SMALLINT);
        callableStatement.registerOutParameter(5, java.sql.Types.TINYINT);
        callableStatement.registerOutParameter(6, java.sql.Types.TINYINT);
        callableStatement.registerOutParameter(7, microsoft.sql.Types.SMALLMONEY);
        callableStatement.registerOutParameter(8, microsoft.sql.Types.SMALLMONEY);
        callableStatement.registerOutParameter(9, microsoft.sql.Types.MONEY);
        callableStatement.registerOutParameter(10, microsoft.sql.Types.MONEY);
        callableStatement.execute();
        BigDecimal ecnryptedSmallMoney = callableStatement.getSmallMoney(7);
        assertEquals("" + ecnryptedSmallMoney, values[12], "Test for output parameter fails.\n");
        short encryptedSmallint = callableStatement.getShort(4);
        assertEquals("" + encryptedSmallint, values[2], "Test for output parameter fails.\n");
        BigDecimal SmallMoneyValue = callableStatement.getSmallMoney(8);
        assertEquals("" + SmallMoneyValue, values[12], "Test for output parameter fails.\n");
        short encryptedTinyint = callableStatement.getShort(6);
        assertEquals("" + encryptedTinyint, values[1], "Test for output parameter fails.\n");
        short tinyintValue = callableStatement.getShort(5);
        assertEquals("" + tinyintValue, values[1], "Test for output parameter fails.\n");
        BigDecimal encryptedMoneyValue = callableStatement.getMoney(9);
        assertEquals("" + encryptedMoneyValue, values[13], "Test for output parameter fails.\n");
        short smallintValue = callableStatement.getShort(3);
        assertEquals("" + smallintValue, values[2], "Test for output parameter fails.\n");
        int intValue = callableStatement.getInt(1);
        assertEquals("" + intValue, values[3], "Test for output parameter fails.\n");
        BigDecimal encryptedSmallMoney = callableStatement.getMoney(10);
        assertEquals("" + encryptedSmallMoney, values[13], "Test for output parameter fails.\n");
        int encryptedInt = callableStatement.getInt(2);
        assertEquals("" + encryptedInt, values[3], "Test for output parameter fails.\n");
    } catch (Exception e) {
        fail(e.toString());
    }
}