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

Example 31 with SQLServerDataTable

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

the class TVPWithSqlVariantTest method testBigInt.

/**
 * Test with bigint value
 *
 * @throws SQLException
 * @throws SQLTimeoutException
 */
@Test
public void testBigInt() throws SQLException {
    Random r = new Random();
    tvp = new SQLServerDataTable();
    tvp.addColumnMetadata("c1", microsoft.sql.Types.SQL_VARIANT);
    String[] numeric = createNumericValues();
    tvp.addRow(Long.parseLong(numeric[4]));
    pstmt = (SQLServerPreparedStatement) connection.prepareStatement("INSERT INTO " + destTable + " select * from ? ;");
    pstmt.setStructured(1, tvpName, tvp);
    pstmt.execute();
    if (null != pstmt) {
        pstmt.close();
    }
    rs = (SQLServerResultSet) stmt.executeQuery("SELECT * FROM " + destTable);
    while (rs.next()) {
        assertEquals(rs.getLong(1), Long.parseLong(numeric[4]));
    }
}

Example 32 with SQLServerDataTable

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

the class TVPWithSqlVariantTest method testDateTime.

/**
 * Test ith datetime
 *
 * @throws SQLException
 * @throws SQLTimeoutException
 */
@Test
public void testDateTime() throws SQLException {
    java.sql.Timestamp timestamp = java.sql.Timestamp.valueOf("2007-09-23 10:10:10.0");
    tvp = new SQLServerDataTable();
    tvp.addColumnMetadata("c1", microsoft.sql.Types.SQL_VARIANT);
    tvp.addRow(timestamp);
    pstmt = (SQLServerPreparedStatement) connection.prepareStatement("INSERT INTO " + destTable + " select * from ? ;");
    pstmt.setStructured(1, tvpName, tvp);
    pstmt.execute();
    if (null != pstmt) {
        pstmt.close();
    }
    rs = (SQLServerResultSet) stmt.executeQuery("SELECT * FROM " + destTable);
    while (rs.next()) {
        assertEquals(rs.getString(1), "" + timestamp);
    // System.out.println(rs.getDateTime(1));// TODO does not work
    }
}

Example 33 with SQLServerDataTable

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

the class TVPWithSqlVariantTest method testIntStoredProcedure.

/**
 * Test with stored procedure
 *
 * @throws SQLException
 * @throws SQLTimeoutException
 */
@Test
public void testIntStoredProcedure() throws SQLException {
    java.sql.Timestamp timestamp = java.sql.Timestamp.valueOf("2007-09-23 10:10:10.0");
    final String sql = "{call " + procedureName + "(?)}";
    tvp = new SQLServerDataTable();
    tvp.addColumnMetadata("c1", microsoft.sql.Types.SQL_VARIANT);
    tvp.addRow(timestamp);
    SQLServerCallableStatement Cstatement = (SQLServerCallableStatement) connection.prepareCall(sql);
    Cstatement.setStructured(1, tvpName, tvp);
    Cstatement.execute();
    rs = (SQLServerResultSet) stmt.executeQuery("select * from " + destTable);
    while (rs.next()) {
        System.out.println(rs.getString(1));
    }
    if (null != Cstatement) {
        Cstatement.close();
    }
}

Example 34 with SQLServerDataTable

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

the class TVPWithSqlVariantTest method testNull.

/**
 * Test with null value
 *
 * @throws SQLException
 * @throws SQLTimeoutException
 */
// TODO We need to check this later. Right now sending null with TVP is not supported
@Test
public void testNull() throws SQLException {
    tvp = new SQLServerDataTable();
    tvp.addColumnMetadata("c1", microsoft.sql.Types.SQL_VARIANT);
    try {
        tvp.addRow((Date) null);
    } catch (Exception e) {
        assertTrue(e.getMessage().startsWith("Use of TVPs containing null sql_variant columns is not supported."));
    }
    pstmt = (SQLServerPreparedStatement) connection.prepareStatement("INSERT INTO " + destTable + " select * from ? ;");
    pstmt.setStructured(1, tvpName, tvp);
    pstmt.execute();
    if (null != pstmt) {
        pstmt.close();
    }
    rs = (SQLServerResultSet) stmt.executeQuery("SELECT * FROM " + destTable);
    while (rs.next()) {
        System.out.println(rs.getString(1));
    }
}