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

Example 26 with SQLServerPreparedStatement

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

the class TVPResultSetCursorTest method testMultiplePreparedStatementAndResultSet.

/**
 * test with multiple prepared statements and result sets
 *
 * @throws SQLException
 */
@Test
public void testMultiplePreparedStatementAndResultSet() throws SQLException {
    conn = DriverManager.getConnection(connectionString);
    stmt = conn.createStatement();
    dropTVPS();
    dropTables();
    createTVPS();
    createTables();
    populateSourceTable();
    ResultSet rs = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE).executeQuery("select * from " + srcTable);
    SQLServerPreparedStatement pstmt1 = (SQLServerPreparedStatement) conn.prepareStatement("INSERT INTO " + desTable + " select * from ? ;");
    pstmt1.setStructured(1, tvpName, rs);
    pstmt1.execute();
    verifyDestinationTableData(expectedBigDecimals.length);
    rs.beforeFirst();
    pstmt1 = (SQLServerPreparedStatement) conn.prepareStatement("INSERT INTO " + desTable + " select * from ? ;");
    pstmt1.setStructured(1, tvpName, rs);
    pstmt1.execute();
    verifyDestinationTableData(expectedBigDecimals.length * 2);
    rs.beforeFirst();
    SQLServerPreparedStatement pstmt2 = (SQLServerPreparedStatement) conn.prepareStatement("INSERT INTO " + desTable + " select * from ? ;");
    pstmt2.setStructured(1, tvpName, rs);
    pstmt2.execute();
    verifyDestinationTableData(expectedBigDecimals.length * 3);
    String sql = "insert into " + desTable + " values (?,?,?,?)";
    Calendar calGMT = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
    pstmt1 = (SQLServerPreparedStatement) conn.prepareStatement(sql);
    for (int i = 0; i < expectedBigDecimals.length; i++) {
        pstmt1.setBigDecimal(1, expectedBigDecimals[i]);
        pstmt1.setString(2, expectedStrings[i]);
        pstmt1.setTimestamp(3, expectedTimestamps[i], calGMT);
        pstmt1.setString(4, expectedStrings[i]);
        pstmt1.execute();
    }
    verifyDestinationTableData(expectedBigDecimals.length * 4);
    ResultSet rs2 = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE).executeQuery("select * from " + srcTable);
    pstmt1 = (SQLServerPreparedStatement) conn.prepareStatement("INSERT INTO " + desTable + " select * from ? ;");
    pstmt1.setStructured(1, tvpName, rs2);
    pstmt1.execute();
    verifyDestinationTableData(expectedBigDecimals.length * 5);
    if (null != pstmt1) {
        pstmt1.close();
    }
    if (null != pstmt2) {
        pstmt2.close();
    }
    if (null != rs) {
        rs.close();
    }
    if (null != rs2) {
        rs2.close();
    }
}

Example 27 with SQLServerPreparedStatement

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

the class TVPResultSetCursorTest method testInvalidTVPName.

/**
 * Test exception when giving invalid TVP name
 *
 * @throws SQLException
 */
@Test
public void testInvalidTVPName() throws SQLException {
    Properties info = new Properties();
    info.setProperty("SelectMethod", "cursor");
    conn = DriverManager.getConnection(connectionString, info);
    stmt = conn.createStatement();
    dropTVPS();
    dropTables();
    createTVPS();
    createTables();
    populateSourceTable();
    ResultSet rs = conn.createStatement().executeQuery("select * from " + srcTable);
    SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) conn.prepareStatement("INSERT INTO " + desTable + " select * from ? ;");
    pstmt.setStructured(1, "invalid" + tvpName, rs);
    try {
        pstmt.execute();
    } catch (SQLException e) {
        if (!e.getMessage().contains("Cannot find data type")) {
            throw e;
        }
    } finally {
        if (null != pstmt) {
            pstmt.close();
        }
        if (null != rs) {
            rs.close();
        }
    }
}

Example 28 with SQLServerPreparedStatement

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

the class TVPResultSetCursorTest method serverCursorsTest.

private void serverCursorsTest(int resultSetType, int resultSetConcurrency) throws SQLException {
    conn = DriverManager.getConnection(connectionString);
    stmt = conn.createStatement();
    dropTVPS();
    dropTables();
    createTVPS();
    createTables();
    populateSourceTable();
    ResultSet rs = conn.createStatement(resultSetType, resultSetConcurrency).executeQuery("select * from " + srcTable);
    SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) conn.prepareStatement("INSERT INTO " + desTable + " select * from ? ;");
    pstmt.setStructured(1, tvpName, rs);
    pstmt.execute();
    verifyDestinationTableData(expectedBigDecimals.length);
    if (null != pstmt) {
        pstmt.close();
    }
    if (null != rs) {
        rs.close();
    }
}

Example 29 with SQLServerPreparedStatement

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

the class TVPResultSetCursorTest method populateSourceTable.

private static void populateSourceTable() throws SQLException {
    String sql = "insert into " + srcTable + " values (?,?,?,?)";
    Calendar calGMT = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
    SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) conn.prepareStatement(sql);
    for (int i = 0; i < expectedBigDecimals.length; i++) {
        pstmt.setBigDecimal(1, expectedBigDecimals[i]);
        pstmt.setString(2, expectedStrings[i]);
        pstmt.setTimestamp(3, expectedTimestamps[i], calGMT);
        pstmt.setString(4, expectedStrings[i]);
        pstmt.execute();
    }
}

Example 30 with SQLServerPreparedStatement

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

the class TVPTypesTest method testDateTime.

/**
 * Test a datetime support
 *
 * @throws SQLException
 */
@Test
public void testDateTime() throws SQLException {
    createTables("datetime");
    createTVPS("datetime");
    java.sql.Timestamp value = java.sql.Timestamp.valueOf("2007-09-23 10:10:10.123");
    tvp = new SQLServerDataTable();
    tvp.addColumnMetadata("c1", microsoft.sql.Types.DATETIME);
    tvp.addRow(value);
    SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) connection.prepareStatement("INSERT INTO " + table + " select * from ? ;");
    pstmt.setStructured(1, tvpName, tvp);
    pstmt.execute();
    rs = conn.createStatement().executeQuery("select * from " + table);
    while (rs.next()) {
        assertEquals(((SQLServerResultSet) rs).getDateTime(1), value);
    }
    if (null != pstmt) {
        pstmt.close();
    }
}