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

Example 31 with SQLServerCallableStatement

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

the class CallableStatementTest method testcoerctionsOutputProcedureNumericInorder.

private void testcoerctionsOutputProcedureNumericInorder(String sql) throws SQLException {
    try (SQLServerCallableStatement callableStatement = (SQLServerCallableStatement) Util.getCallableStmt(con, sql, stmtColEncSetting)) {
        callableStatement.registerOutParameter(1, java.sql.Types.BIT);
        callableStatement.registerOutParameter(2, java.sql.Types.TINYINT);
        callableStatement.registerOutParameter(3, java.sql.Types.SMALLINT);
        callableStatement.registerOutParameter(4, java.sql.Types.INTEGER);
        callableStatement.registerOutParameter(5, java.sql.Types.BIGINT);
        callableStatement.registerOutParameter(6, java.sql.Types.DOUBLE);
        callableStatement.registerOutParameter(7, java.sql.Types.DOUBLE, 30, 0);
        callableStatement.registerOutParameter(8, java.sql.Types.REAL);
        callableStatement.registerOutParameter(9, java.sql.Types.DECIMAL, 18, 0);
        callableStatement.registerOutParameter(10, java.sql.Types.DECIMAL, 10, 5);
        callableStatement.registerOutParameter(11, java.sql.Types.NUMERIC, 18, 0);
        callableStatement.registerOutParameter(12, java.sql.Types.NUMERIC, 8, 2);
        callableStatement.registerOutParameter(13, microsoft.sql.Types.SMALLMONEY);
        callableStatement.registerOutParameter(14, microsoft.sql.Types.MONEY);
        callableStatement.registerOutParameter(15, java.sql.Types.DECIMAL, 28, 4);
        callableStatement.registerOutParameter(16, java.sql.Types.NUMERIC, 28, 4);
        callableStatement.execute();
        Class[] boolean_coercions = { Object.class, Short.class, Integer.class, Long.class, Float.class, Double.class, BigDecimal.class, String.class };
        for (int i = 0; i < boolean_coercions.length; i++) {
            Object value = getxxx(1, boolean_coercions[i], callableStatement);
            Object boolVal = null;
            if (value.toString().equals("1") || value.equals(true) || value.toString().equals("1.0"))
                boolVal = true;
            else if (value.toString().equals("0") || value.equals(false) || value.toString().equals("0.0"))
                boolVal = false;
            assertEquals("" + boolVal, numericValues[0], "Test for output parameter fails.\n");
        }
        Class[] tinyint_coercions = { Object.class, Short.class, Integer.class, Long.class, Float.class, Double.class, BigDecimal.class, String.class };
        for (int i = 0; i < tinyint_coercions.length; i++) {
            Object tinyIntValue = getxxx(2, tinyint_coercions[i], callableStatement);
            Object x = createValue(tinyint_coercions[i], 1);
            if (x instanceof String)
                assertEquals("" + tinyIntValue, x, "Test for output parameter fails.\n");
            else
                assertEquals(tinyIntValue, x, "Test for output parameter fails.\n");
        }
        Class[] smallint_coercions = { Object.class, Short.class, Integer.class, Long.class, Float.class, Double.class, BigDecimal.class, String.class };
        for (int i = 0; i < smallint_coercions.length; i++) {
            Object smallIntValue = getxxx(3, smallint_coercions[i], callableStatement);
            Object x = createValue(smallint_coercions[i], 2);
            if (x instanceof String)
                assertEquals("" + smallIntValue, x, "Test for output parameter fails.\n");
            else
                assertEquals(smallIntValue, x, "Test for output parameter fails.\n");
        }
        Class[] int_coercions = { Object.class, Short.class, Integer.class, Long.class, Float.class, Double.class, BigDecimal.class, String.class };
        for (int i = 0; i < int_coercions.length; i++) {
            Object IntValue = getxxx(4, int_coercions[i], callableStatement);
            Object x = createValue(int_coercions[i], 3);
            if (x != null) {
                if (x instanceof String)
                    assertEquals("" + IntValue, x, "Test for output parameter fails.\n");
                else
                    assertEquals(IntValue, x, "Test for output parameter fails.\n");
            }
        }
        Class[] bigint_coercions = { Object.class, Short.class, Integer.class, Long.class, Float.class, Double.class, BigDecimal.class, String.class };
        for (int i = 0; i < int_coercions.length; i++) {
            Object bigIntValue = getxxx(5, bigint_coercions[i], callableStatement);
            Object x = createValue(bigint_coercions[i], 4);
            if (x != null) {
                if (x instanceof String)
                    assertEquals("" + bigIntValue, x, "Test for output parameter fails.\n");
                else
                    assertEquals(bigIntValue, x, "Test for output parameter fails.\n");
            }
        }
        Class[] float_coercions = { Object.class, Short.class, Integer.class, Long.class, Float.class, Double.class, BigDecimal.class, String.class };
        for (int i = 0; i < float_coercions.length; i++) {
            Object floatDefaultValue = getxxx(6, float_coercions[i], callableStatement);
            Object x = createValue(float_coercions[i], 5);
            if (x != null) {
                if (x instanceof String)
                    assertEquals("" + floatDefaultValue, x, "Test for output parameter fails.\n");
                else
                    assertEquals(floatDefaultValue, x, "Test for output parameter fails.\n");
            }
        }
        for (int i = 0; i < float_coercions.length; i++) {
            Object floatValue = getxxx(7, float_coercions[i], callableStatement);
            Object x = createValue(float_coercions[i], 6);
            if (x != null) {
                if (x instanceof String)
                    assertEquals("" + floatValue, x, "Test for output parameter fails.\n");
                else
                    assertEquals(floatValue, x, "Test for output parameter fails.\n");
            }
        }
        Class[] real_coercions = { Object.class, Short.class, Integer.class, Long.class, Float.class, BigDecimal.class, String.class };
        for (int i = 0; i < real_coercions.length; i++) {
            Object realValue = getxxx(8, real_coercions[i], callableStatement);
            Object x = createValue(real_coercions[i], 7);
            if (x != null) {
                if (x instanceof String)
                    assertEquals("" + realValue, x, "Test for output parameter fails for Coercion: " + real_coercions[i] + " for real value.\n");
                else
                    assertEquals(realValue, x, "Test for output parameter fails for Coercion: " + real_coercions[i] + " for real value.\n");
            }
        }
        Class[] decimalDefault_coercions = { Object.class, Short.class, Integer.class, Long.class, Float.class, Double.class, BigDecimal.class, String.class };
        for (int i = 0; i < decimalDefault_coercions.length; i++) {
            Object decimalDefaultValue = getxxx(9, decimalDefault_coercions[i], callableStatement);
            Object x = createValue(decimalDefault_coercions[i], 8);
            if (x != null) {
                if (x instanceof String)
                    assertEquals("" + decimalDefaultValue, x, "Test for output parameter fails.\n");
                else
                    assertEquals(decimalDefaultValue, x, "Test for output parameter fails.\n");
            }
        }
        for (int i = 0; i < decimalDefault_coercions.length; i++) {
            Object decimalValue = getxxx(10, decimalDefault_coercions[i], callableStatement);
            Object x = createValue(decimalDefault_coercions[i], 9);
            if (x != null) {
                if (x instanceof String)
                    assertEquals("" + decimalValue, x, "Test for output parameter fails.\n");
                else
                    assertEquals(decimalValue, x, "Test for output parameter fails.\n");
            }
        }
        for (int i = 0; i < decimalDefault_coercions.length; i++) {
            Object numericDefaultValue = getxxx(11, decimalDefault_coercions[i], callableStatement);
            Object x = createValue(decimalDefault_coercions[i], 10);
            if (x != null) {
                if (x instanceof String)
                    assertEquals("" + numericDefaultValue, x, "Test for output parameter fails.\n");
                else
                    assertEquals(numericDefaultValue, x, "Test for output parameter fails.\n");
            }
        }
        for (int i = 0; i < decimalDefault_coercions.length; i++) {
            Object numericValue = getxxx(12, decimalDefault_coercions[i], callableStatement);
            Object x = createValue(decimalDefault_coercions[i], 11);
            if (x != null) {
                if (x instanceof String)
                    assertEquals("" + numericValue, x, "Test for output parameter fails.\n");
                else
                    assertEquals(numericValue, x, "Test for output parameter fails.\n");
            }
        }
        for (int i = 0; i < decimalDefault_coercions.length; i++) {
            Object smallMoneyValue = getxxx(13, decimalDefault_coercions[i], callableStatement);
            Object x = createValue(decimalDefault_coercions[i], 12);
            if (x != null) {
                if (x instanceof String)
                    assertEquals("" + smallMoneyValue, x, "Test for output parameter fails.\n");
                else
                    assertEquals(smallMoneyValue, x, "Test for output parameter fails.\n");
            }
        }
        for (int i = 0; i < decimalDefault_coercions.length; i++) {
            Object moneyValue = getxxx(14, decimalDefault_coercions[i], callableStatement);
            Object x = createValue(decimalDefault_coercions[i], 13);
            if (x != null) {
                if (x instanceof String)
                    assertEquals("" + moneyValue, x, "Test for output parameter fails.\n");
                else
                    assertEquals(moneyValue, x, "Test for output parameter fails.\n");
            }
        }
        for (int i = 0; i < decimalDefault_coercions.length; i++) {
            Object decimalValue2 = getxxx(15, decimalDefault_coercions[i], callableStatement);
            Object x = createValue(decimalDefault_coercions[i], 14);
            if (x != null) {
                if (x instanceof String)
                    assertEquals("" + decimalValue2, x, "Test for output parameter fails.\n");
                else
                    assertEquals(decimalValue2, x, "Test for output parameter fails.\n");
            }
        }
        for (int i = 0; i < decimalDefault_coercions.length; i++) {
            Object numericValue1 = getxxx(16, decimalDefault_coercions[i], callableStatement);
            Object x = createValue(decimalDefault_coercions[i], 15);
            if (x != null) {
                if (x instanceof String)
                    assertEquals("" + numericValue1, x, "Test for output parameter fails.\n");
                else
                    assertEquals(numericValue1, x, "Test for output parameter fails.\n");
            }
        }
    } catch (Exception e) {
        fail(e.toString());
    }
}

Example 32 with SQLServerCallableStatement

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

the class CallableStatementTest method testOutputProcedure2Inorder.

private void testOutputProcedure2Inorder(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();
        int intValue = callableStatement.getInt(1);
        assertEquals("" + intValue, values[3], "Test for output parameter fails.\n");
        int encryptedInt = callableStatement.getInt(2);
        assertEquals("" + encryptedInt, values[3], "Test for output parameter fails.\n");
        short smallintValue = callableStatement.getShort(3);
        assertEquals("" + smallintValue, values[2], "Test for output parameter fails.\n");
        short encryptedSmallint = callableStatement.getShort(4);
        assertEquals("" + encryptedSmallint, values[2], "Test for output parameter fails.\n");
        short tinyintValue = callableStatement.getShort(5);
        assertEquals("" + tinyintValue, values[1], "Test for output parameter fails.\n");
        short encryptedTinyint = callableStatement.getShort(6);
        assertEquals("" + encryptedTinyint, values[1], "Test for output parameter fails.\n");
        BigDecimal encryptedSmallMoney = callableStatement.getSmallMoney(7);
        assertEquals("" + encryptedSmallMoney, values[12], "Test for output parameter fails.\n");
        BigDecimal SmallMoneyValue = callableStatement.getSmallMoney(8);
        assertEquals("" + SmallMoneyValue, values[12], "Test for output parameter fails.\n");
        BigDecimal MoneyValue = callableStatement.getMoney(9);
        assertEquals("" + MoneyValue, values[13], "Test for output parameter fails.\n");
        BigDecimal encryptedMoney = callableStatement.getMoney(10);
        assertEquals("" + encryptedMoney, values[13], "Test for output parameter fails.\n");
    } catch (Exception e) {
        fail(e.toString());
    }
}

Example 33 with SQLServerCallableStatement

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

the class CallableStatementTest method testOutputProcedureCharInorder.

private void testOutputProcedureCharInorder(String sql) throws SQLException {
    try (SQLServerCallableStatement callableStatement = (SQLServerCallableStatement) Util.getCallableStmt(con, sql, stmtColEncSetting)) {
        callableStatement.registerOutParameter(1, java.sql.Types.CHAR, 20, 0);
        callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR, 50, 0);
        callableStatement.registerOutParameter(3, java.sql.Types.NCHAR, 30, 0);
        callableStatement.registerOutParameter(4, java.sql.Types.NVARCHAR, 60, 0);
        callableStatement.registerOutParameter(5, microsoft.sql.Types.GUID);
        callableStatement.registerOutParameter(6, java.sql.Types.LONGVARCHAR);
        callableStatement.registerOutParameter(7, java.sql.Types.LONGNVARCHAR);
        callableStatement.registerOutParameter(8, java.sql.Types.VARCHAR, 8000, 0);
        callableStatement.registerOutParameter(9, java.sql.Types.NVARCHAR, 4000, 0);
        callableStatement.execute();
        String charValue = callableStatement.getString(1).trim();
        assertEquals(charValue, charValues[0], "Test for output parameter fails.\n");
        String varcharValue = callableStatement.getString(2).trim();
        assertEquals(varcharValue, charValues[1], "Test for output parameter fails.\n");
        String ncharValue = callableStatement.getString(3).trim();
        assertEquals(ncharValue, charValues[3], "Test for output parameter fails.\n");
        String nvarcharValue = callableStatement.getString(4).trim();
        assertEquals(nvarcharValue, charValues[4], "Test for output parameter fails.\n");
        String uniqueIdentifierValue = callableStatement.getString(5).trim();
        assertEquals(uniqueIdentifierValue.toLowerCase(), charValues[6], "Test for output parameter fails.\n");
        String varcharValuemax = callableStatement.getString(6).trim();
        assertEquals(varcharValuemax, charValues[2], "Test for output parameter fails.\n");
        String nvarcharValuemax = callableStatement.getString(7).trim();
        assertEquals(nvarcharValuemax, charValues[5], "Test for output parameter fails.\n");
        String varcharValue8000 = callableStatement.getString(8).trim();
        assertEquals(varcharValue8000, charValues[7], "Test for output parameter fails.\n");
        String nvarcharValue4000 = callableStatement.getNString(9).trim();
        assertEquals(nvarcharValue4000, charValues[8], "Test for output parameter fails.\n");
    } catch (Exception e) {
        fail(e.toString());
    }
}

Example 34 with SQLServerCallableStatement

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

the class CallableStatementTest method testOutputProcedureDateInorderObject.

private void testOutputProcedureDateInorderObject(String sql) throws SQLException {
    try (SQLServerCallableStatement callableStatement = (SQLServerCallableStatement) Util.getCallableStmt(con, sql, stmtColEncSetting)) {
        callableStatement.registerOutParameter(1, java.sql.Types.DATE);
        callableStatement.registerOutParameter(2, java.sql.Types.DATE);
        callableStatement.registerOutParameter(3, java.sql.Types.TIMESTAMP);
        callableStatement.registerOutParameter(4, java.sql.Types.TIMESTAMP);
        callableStatement.registerOutParameter(5, microsoft.sql.Types.DATETIMEOFFSET);
        callableStatement.registerOutParameter(6, microsoft.sql.Types.DATETIMEOFFSET);
        callableStatement.registerOutParameter(7, java.sql.Types.TIME);
        callableStatement.registerOutParameter(8, java.sql.Types.TIME);
        // datetime
        callableStatement.registerOutParameter(9, microsoft.sql.Types.DATETIME);
        // datetime
        callableStatement.registerOutParameter(10, microsoft.sql.Types.DATETIME);
        // smalldatetime
        callableStatement.registerOutParameter(11, microsoft.sql.Types.SMALLDATETIME);
        // smalldatetime
        callableStatement.registerOutParameter(12, microsoft.sql.Types.SMALLDATETIME);
        callableStatement.registerOutParameter(13, java.sql.Types.TIMESTAMP, 2);
        callableStatement.registerOutParameter(14, java.sql.Types.TIMESTAMP, 2);
        callableStatement.registerOutParameter(15, java.sql.Types.TIME, 2);
        callableStatement.registerOutParameter(16, java.sql.Types.TIME, 2);
        callableStatement.registerOutParameter(17, microsoft.sql.Types.DATETIMEOFFSET, 2);
        callableStatement.registerOutParameter(18, microsoft.sql.Types.DATETIMEOFFSET, 2);
        callableStatement.execute();
        assertEquals(callableStatement.getObject(1), callableStatement.getObject(2), "Test for output parameter fails.\n");
        assertEquals(callableStatement.getObject(3), callableStatement.getObject(4), "Test for output parameter fails.\n");
        assertEquals(callableStatement.getObject(5), callableStatement.getObject(6), "Test for output parameter fails.\n");
        assertEquals(callableStatement.getObject(7), callableStatement.getObject(8), "Test for output parameter fails.\n");
        assertEquals(// actual plain
        callableStatement.getObject(9), // received expected enc
        callableStatement.getObject(10), "Test for output parameter fails.\n");
        assertEquals(callableStatement.getObject(11), callableStatement.getObject(12), "Test for output parameter fails.\n");
        assertEquals(callableStatement.getObject(13), callableStatement.getObject(14), "Test for output parameter fails.\n");
        assertEquals(callableStatement.getObject(15), callableStatement.getObject(16), "Test for output parameter fails.\n");
        assertEquals(callableStatement.getObject(17), callableStatement.getObject(18), "Test for output parameter fails.\n");
    } catch (Exception e) {
        fail(e.toString());
    }
}

Example 35 with SQLServerCallableStatement

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

the class CallableStatementTest method testMixedProcedure3ReverseOrder.

private void testMixedProcedure3ReverseOrder(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");
    } catch (Exception e) {
        fail(e.toString());
    }
}