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

Example 26 with SQLServerCallableStatement

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

the class TVPTypesTest method testTVPXMLStoredProcedure.

/**
 * XML with StoredProcedure
 *
 * @throws SQLException
 */
@Test
public void testTVPXMLStoredProcedure() throws SQLException {
    createTables("xml");
    createTVPS("xml");
    createPreocedure();
    value = "<vx53_e>Variable E</vx53_e>" + "<vx53_f>Variable F</vx53_f>" + "<doc>API<!-- comments --></doc>" + "<doc>The following are Japanese chars.</doc>" + "<doc>    Some UTF-8 encoded characters: �������</doc>";
    tvp = new SQLServerDataTable();
    tvp.addColumnMetadata("c1", java.sql.Types.SQLXML);
    tvp.addRow(value);
    final String sql = "{call " + procedureName + "(?)}";
    SQLServerCallableStatement P_C_statement = (SQLServerCallableStatement) connection.prepareCall(sql);
    P_C_statement.setStructured(1, tvpName, tvp);
    P_C_statement.execute();
    rs = stmt.executeQuery("select * from " + table);
    while (rs.next()) assertEquals(rs.getString(1), value);
    if (null != P_C_statement) {
        P_C_statement.close();
    }
}

Example 27 with SQLServerCallableStatement

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

the class TVPTypesTest method testTVPTextStoredProcedure.

/**
 * Text with StoredProcedure
 *
 * @throws SQLException
 */
@Test
public void testTVPTextStoredProcedure() throws SQLException {
    createTables("text");
    createTVPS("text");
    createPreocedure();
    StringBuffer buffer = new StringBuffer();
    for (int i = 0; i < 9000; i++) buffer.append("a");
    value = buffer.toString();
    tvp = new SQLServerDataTable();
    tvp.addColumnMetadata("c1", java.sql.Types.LONGVARCHAR);
    tvp.addRow(value);
    final String sql = "{call " + procedureName + "(?)}";
    SQLServerCallableStatement P_C_statement = (SQLServerCallableStatement) connection.prepareCall(sql);
    P_C_statement.setStructured(1, tvpName, tvp);
    P_C_statement.execute();
    rs = stmt.executeQuery("select * from " + table);
    while (rs.next()) assertEquals(rs.getString(1), value);
    if (null != P_C_statement) {
        P_C_statement.close();
    }
}

Example 28 with SQLServerCallableStatement

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

the class TVPTypesTest method testTVPImageStoredProcedure.

/**
 * Image with StoredProcedure acts the same as varbinary(max)
 *
 * @throws SQLException
 */
@Test
public void testTVPImageStoredProcedure() throws SQLException {
    createTables("image");
    createTVPS("image");
    createPreocedure();
    StringBuffer buffer = new StringBuffer();
    for (int i = 0; i < 9000; i++) buffer.append("a");
    value = buffer.toString();
    tvp = new SQLServerDataTable();
    tvp.addColumnMetadata("c1", java.sql.Types.LONGVARBINARY);
    tvp.addRow(value.getBytes());
    final String sql = "{call " + procedureName + "(?)}";
    SQLServerCallableStatement P_C_statement = (SQLServerCallableStatement) connection.prepareCall(sql);
    P_C_statement.setStructured(1, tvpName, tvp);
    P_C_statement.execute();
    rs = stmt.executeQuery("select * from " + table);
    while (rs.next()) assertTrue(parseByte(rs.getBytes(1), value.getBytes()));
    if (null != P_C_statement) {
        P_C_statement.close();
    }
}

Example 29 with SQLServerCallableStatement

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

the class CallableStatementTest method testMixedProcedureDateScaleWithParameterName.

private void testMixedProcedureDateScaleWithParameterName(String sql) throws SQLException {
    try (SQLServerCallableStatement callableStatement = (SQLServerCallableStatement) Util.getCallableStmt(con, sql, stmtColEncSetting)) {
        callableStatement.registerOutParameter("p1", java.sql.Types.TIMESTAMP, 2);
        callableStatement.registerOutParameter("p2", java.sql.Types.TIMESTAMP, 2);
        callableStatement.registerOutParameter("p3", java.sql.Types.TIME, 2);
        callableStatement.registerOutParameter("p4", java.sql.Types.TIME, 2);
        callableStatement.registerOutParameter("p5", microsoft.sql.Types.DATETIMEOFFSET, 2);
        callableStatement.registerOutParameter("p6", microsoft.sql.Types.DATETIMEOFFSET, 2);
        callableStatement.setTimestamp("p1", (Timestamp) dateValues.get(4), 2);
        callableStatement.setTime("p3", (Time) dateValues.get(5), 2);
        callableStatement.setDateTimeOffset("p5", (DateTimeOffset) dateValues.get(6), 2);
        callableStatement.execute();
        assertEquals(callableStatement.getTimestamp(1), callableStatement.getTimestamp(2), "Test for output parameter fails.\n");
        assertEquals(callableStatement.getTime(3), callableStatement.getTime(4), "Test for output parameter fails.\n");
        assertEquals(callableStatement.getDateTimeOffset(5), callableStatement.getDateTimeOffset(6), "Test for output parameter fails.\n");
    } catch (Exception e) {
        fail(e.toString());
    }
}

Example 30 with SQLServerCallableStatement

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

the class CallableStatementTest method testOutputProcedureBinaryInorderString.

private void testOutputProcedureBinaryInorderString(String sql) throws SQLException {
    try (SQLServerCallableStatement callableStatement = (SQLServerCallableStatement) Util.getCallableStmt(con, sql, stmtColEncSetting)) {
        callableStatement.registerOutParameter(1, java.sql.Types.BINARY, 20, 0);
        callableStatement.registerOutParameter(2, java.sql.Types.VARBINARY, 50, 0);
        callableStatement.registerOutParameter(3, java.sql.Types.LONGVARBINARY);
        callableStatement.registerOutParameter(4, java.sql.Types.BINARY, 512, 0);
        callableStatement.registerOutParameter(5, java.sql.Types.VARBINARY, 8000, 0);
        callableStatement.execute();
        int index = 1;
        for (int i = 0; i < byteValues.size(); i++) {
            String stringValue1 = ("" + callableStatement.getString(index)).trim();
            StringBuffer expected = new StringBuffer();
            String expectedStr = null;
            if (null != byteValues.get(i)) {
                for (byte b : byteValues.get(i)) {
                    expected.append(String.format("%02X", b));
                }
                expectedStr = "" + expected.toString();
            } else {
                expectedStr = "null";
            }
            try {
                assertEquals(stringValue1.startsWith(expectedStr), true, "\nDecryption failed with getString(): " + stringValue1 + ".\nExpected Value: " + expectedStr);
            } catch (Exception e) {
                fail(e.toString());
            } finally {
                index++;
            }
        }
    }
}