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

Example 36 with SQLServerPreparedStatement

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

the class TVPTypesTest method testImage.

/**
 * Test text support
 *
 * @throws SQLException
 */
@Test
public void testImage() throws SQLException {
    createTables("varbinary(max)");
    createTVPS("varbinary(max)");
    StringBuffer buffer = new StringBuffer();
    for (int i = 0; i < 10000; i++) buffer.append("a");
    value = buffer.toString();
    tvp = new SQLServerDataTable();
    tvp.addColumnMetadata("c1", java.sql.Types.LONGVARBINARY);
    tvp.addRow(value.getBytes());
    SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) connection.prepareStatement("INSERT INTO " + table + " select * from ? ;");
    pstmt.setStructured(1, tvpName, tvp);
    pstmt.execute();
    Connection con = DriverManager.getConnection(connectionString);
    ResultSet rs = con.createStatement().executeQuery("select * from " + table);
    while (rs.next()) assertTrue(parseByte(rs.getBytes(1), value.getBytes()));
    if (null != pstmt) {
        pstmt.close();
    }
}

Example 37 with SQLServerPreparedStatement

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

the class CallableStatementTest method populateDateNormalCase.

private static void populateDateNormalCase() throws SQLException {
    String sql = "insert into " + dateTable + " values( " + "?,?,?," + "?,?,?," + "?,?,?," + "?,?,?," + "?,?,?," + "?,?,?," + "?,?,?," + "?,?,?," + "?,?,?" + ")";
    SQLServerPreparedStatement sqlPstmt = (SQLServerPreparedStatement) Util.getPreparedStmt(con, sql, stmtColEncSetting);
    // date
    for (int i = 1; i <= 3; i++) {
        sqlPstmt.setDate(i, (Date) dateValues.get(0));
    }
    // datetime2 default
    for (int i = 4; i <= 6; i++) {
        sqlPstmt.setTimestamp(i, (Timestamp) dateValues.get(1));
    }
    // datetimeoffset default
    for (int i = 7; i <= 9; i++) {
        sqlPstmt.setDateTimeOffset(i, (DateTimeOffset) dateValues.get(2));
    }
    // time default
    for (int i = 10; i <= 12; i++) {
        sqlPstmt.setTime(i, (Time) dateValues.get(3));
    }
    // datetime2(2)
    for (int i = 13; i <= 15; i++) {
        sqlPstmt.setTimestamp(i, (Timestamp) dateValues.get(4), 2);
    }
    // time(2)
    for (int i = 16; i <= 18; i++) {
        sqlPstmt.setTime(i, (Time) dateValues.get(5), 2);
    }
    // datetimeoffset(2)
    for (int i = 19; i <= 21; i++) {
        sqlPstmt.setDateTimeOffset(i, (DateTimeOffset) dateValues.get(6), 2);
    }
    // datetime()
    for (int i = 22; i <= 24; i++) {
        sqlPstmt.setDateTime(i, (Timestamp) dateValues.get(7));
    }
    // smalldatetime()
    for (int i = 25; i <= 27; i++) {
        sqlPstmt.setSmallDateTime(i, (Timestamp) dateValues.get(8));
    }
    sqlPstmt.execute();
}

Example 38 with SQLServerPreparedStatement

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

the class DateAndTimeTypeTest method testQueryDateTVP.

/**
 * Test query with date TVP
 */
@Test
public void testQueryDateTVP() throws SQLException {
    SQLServerDataTable tvp = new SQLServerDataTable();
    tvp.addColumnMetadata("c1", java.sql.Types.DATE);
    tvp.addRow(DATE_TO_TEST);
    String sPrepStmt = "select * from dateandtime where my_date IN (select * from ?)";
    pstmt = connection.prepareStatement(sPrepStmt);
    ((SQLServerPreparedStatement) pstmt).setStructured(1, "dateTVP", tvp);
    rs = pstmt.executeQuery();
    rs.next();
    assertTrue(rs.getInt(1) == 42, "did not find correct timestamp");
    rs.close();
    pstmt.close();
}

Example 39 with SQLServerPreparedStatement

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

the class DateAndTimeTypeTest method testQueryTimeTVP.

/**
 * Test query with date TVP
 */
@Test
public void testQueryTimeTVP() throws SQLException {
    SQLServerDataTable tvp = new SQLServerDataTable();
    tvp.addColumnMetadata("c1", java.sql.Types.TIME);
    tvp.addRow(TIME_TO_TEST);
    String sPrepStmt = "select * from dateandtime where my_time IN (select * from ?)";
    pstmt = connection.prepareStatement(sPrepStmt);
    ((SQLServerPreparedStatement) pstmt).setStructured(1, "timeTVP", tvp);
    rs = pstmt.executeQuery();
    rs.next();
    assertTrue(rs.getInt(1) == 42, "did not find correct timestamp");
    rs.close();
    pstmt.close();
}

Example 40 with SQLServerPreparedStatement

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

the class SQLVariantResultSetTest method insertVarChar8001.

/**
 * Testing that inserting value more than 8000 on varchar(8000) should throw failure operand type clash
 *
 * @throws SQLException
 */
@Test
public void insertVarChar8001() throws SQLException {
    StringBuffer buffer = new StringBuffer();
    for (int i = 0; i < 8001; i++) {
        buffer.append("a");
    }
    Utils.dropTableIfExists(tableName, stmt);
    stmt.executeUpdate("create table " + tableName + " (col1 sql_variant)");
    SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareStatement("insert into " + tableName + " values (?)");
    pstmt.setObject(1, buffer.toString());
    try {
        pstmt.execute();
    } catch (SQLServerException e) {
        assertTrue(e.toString().contains("com.microsoft.sqlserver.jdbc.SQLServerException: Operand type clash"));
    }
}