Search in sources :

Example 1 with SQLServerPreparedStatement

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

the class basicDT method insertOriginalData.

private static void insertOriginalData() throws SQLException {
    String sql = "insert into " + tableName + " values( " + "?,?,?,?,?,?,?,?,?,?,?" + ")";
    SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareCall(sql);
    pstmt.setObject(1, 100);
    pstmt.setObject(2, "origianl text");
    pstmt.setObject(3, "origianl text");
    pstmt.setObject(4, false);
    pstmt.setObject(5, 12.34);
    pstmt.setObject(6, 56.78);
    pstmt.setObject(7, new java.util.Date(1453500034839L));
    pstmt.setObject(8, new java.util.Date(1453500034839L));
    pstmt.setObject(9, new java.util.Date(1453500034839L));
    pstmt.setObject(10, new java.util.Date(1453500034839L));
    pstmt.setObject(11, new java.util.Date(1453500034839L));
    pstmt.execute();
    pstmt.close();
}
Also used : SQLServerPreparedStatement(com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement)

Example 2 with SQLServerPreparedStatement

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

the class JDBCEncryptionDecryptionTest method testBinary.

private void testBinary(SQLServerStatement stmt, LinkedList<byte[]> values) throws SQLException {
    String sql = "select * from " + binaryTable;
    try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) Util.getPreparedStmt(con, sql, stmtColEncSetting)) {
        try (ResultSet rs = (stmt == null) ? pstmt.executeQuery() : stmt.executeQuery(sql)) {
            int numberOfColumns = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                testGetStringForBinary(rs, numberOfColumns, values);
                testGetBytes(rs, numberOfColumns, values);
                testGetObjectForBinary(rs, numberOfColumns, values);
            }
        }
    }
}
Also used : SQLServerPreparedStatement(com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement) SQLServerResultSet(com.microsoft.sqlserver.jdbc.SQLServerResultSet) ResultSet(java.sql.ResultSet)

Example 3 with SQLServerPreparedStatement

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

the class AESetup method populateDateSetObject.

/**
 * Populate date data using set object.
 *
 * @param dateValues
 * @param setter
 * @throws SQLException
 */
protected static void populateDateSetObject(LinkedList<Object> dateValues, String setter) throws SQLException {
    if (setter.equalsIgnoreCase("setwithJDBCType")) {
        skipTestForJava7();
    }
    String sql = "insert into " + dateTable + " values( " + "?,?,?," + "?,?,?," + "?,?,?," + "?,?,?," + "?,?,?," + "?,?,?" + ")";
    try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) Util.getPreparedStmt(con, sql, stmtColEncSetting)) {
        // date
        for (int i = 1; i <= 3; i++) {
            if (setter.equalsIgnoreCase("setwithJavaType"))
                pstmt.setObject(i, (Date) dateValues.get(0), java.sql.Types.DATE);
            else if (setter.equalsIgnoreCase("setwithJDBCType"))
                pstmt.setObject(i, (Date) dateValues.get(0), JDBCType.DATE);
            else
                pstmt.setObject(i, (Date) dateValues.get(0));
        }
        // datetime2 default
        for (int i = 4; i <= 6; i++) {
            if (setter.equalsIgnoreCase("setwithJavaType"))
                pstmt.setObject(i, (Timestamp) dateValues.get(1), java.sql.Types.TIMESTAMP);
            else if (setter.equalsIgnoreCase("setwithJDBCType"))
                pstmt.setObject(i, (Timestamp) dateValues.get(1), JDBCType.TIMESTAMP);
            else
                pstmt.setObject(i, (Timestamp) dateValues.get(1));
        }
        // datetimeoffset default
        for (int i = 7; i <= 9; i++) {
            if (setter.equalsIgnoreCase("setwithJavaType"))
                pstmt.setObject(i, (DateTimeOffset) dateValues.get(2), microsoft.sql.Types.DATETIMEOFFSET);
            else if (setter.equalsIgnoreCase("setwithJDBCType"))
                pstmt.setObject(i, (DateTimeOffset) dateValues.get(2), microsoft.sql.Types.DATETIMEOFFSET);
            else
                pstmt.setObject(i, (DateTimeOffset) dateValues.get(2));
        }
        // time default
        for (int i = 10; i <= 12; i++) {
            if (setter.equalsIgnoreCase("setwithJavaType"))
                pstmt.setObject(i, (Time) dateValues.get(3), java.sql.Types.TIME);
            else if (setter.equalsIgnoreCase("setwithJDBCType"))
                pstmt.setObject(i, (Time) dateValues.get(3), JDBCType.TIME);
            else
                pstmt.setObject(i, (Time) dateValues.get(3));
        }
        // datetime
        for (int i = 13; i <= 15; i++) {
            pstmt.setObject(i, (Timestamp) dateValues.get(4), microsoft.sql.Types.DATETIME);
        }
        // smalldatetime
        for (int i = 16; i <= 18; i++) {
            pstmt.setObject(i, (Timestamp) dateValues.get(5), microsoft.sql.Types.SMALLDATETIME);
        }
        pstmt.execute();
    }
}
Also used : SQLServerPreparedStatement(com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement) DateTimeOffset(microsoft.sql.DateTimeOffset) Time(java.sql.Time) Timestamp(java.sql.Timestamp) Date(java.sql.Date)

Example 4 with SQLServerPreparedStatement

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

the class DateAndTimeTypeTest method testQueryTimestampTVP.

/**
 * Test query with date TVP
 */
@Test
public void testQueryTimestampTVP() throws SQLException {
    SQLServerDataTable tvp = new SQLServerDataTable();
    tvp.addColumnMetadata("c1", java.sql.Types.TIMESTAMP);
    tvp.addRow(TIMESTAMP_TO_TEST);
    String sPrepStmt = "select * from dateandtime where my_timestamp IN (select * from ?)";
    pstmt = connection.prepareStatement(sPrepStmt);
    ((SQLServerPreparedStatement) pstmt).setStructured(1, "timestampTVP", tvp);
    rs = pstmt.executeQuery();
    rs.next();
    assertTrue(rs.getInt(1) == 42, "did not find correct timestamp");
    rs.close();
    pstmt.close();
}
Also used : SQLServerPreparedStatement(com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement) SQLServerDataTable(com.microsoft.sqlserver.jdbc.SQLServerDataTable) Test(org.junit.jupiter.api.Test) AbstractTest(com.microsoft.sqlserver.testframework.AbstractTest)

Example 5 with SQLServerPreparedStatement

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

the class SQLVariantResultSetTest method insertTest.

/**
 * Testing inserting and reading from SqlVariant and int column
 *
 * @throws SQLException
 */
@Test
public void insertTest() throws SQLException {
    Utils.dropTableIfExists(tableName, stmt);
    stmt.executeUpdate("create table " + tableName + " (col1 sql_variant, col2 int)");
    SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareStatement("insert into " + tableName + " values (?, ?)");
    String[] col1Value = { "Hello", null };
    int[] col2Value = { 1, 2 };
    pstmt.setObject(1, "Hello");
    pstmt.setInt(2, 1);
    pstmt.execute();
    pstmt.setObject(1, null);
    pstmt.setInt(2, 2);
    pstmt.execute();
    rs = (SQLServerResultSet) stmt.executeQuery("SELECT * FROM " + tableName);
    int i = 0;
    rs.next();
    do {
        assertEquals(rs.getObject(1), col1Value[i]);
        assertEquals(rs.getObject(2), col2Value[i]);
        i++;
    } while (rs.next());
}
Also used : SQLServerPreparedStatement(com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement) Test(org.junit.jupiter.api.Test) AbstractTest(com.microsoft.sqlserver.testframework.AbstractTest)

Aggregations

SQLServerPreparedStatement (com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement)43 AbstractTest (com.microsoft.sqlserver.testframework.AbstractTest)31 Test (org.junit.jupiter.api.Test)31 ResultSet (java.sql.ResultSet)17 SQLServerDataTable (com.microsoft.sqlserver.jdbc.SQLServerDataTable)13 Connection (java.sql.Connection)9 SQLServerResultSet (com.microsoft.sqlserver.jdbc.SQLServerResultSet)8 SQLServerConnection (com.microsoft.sqlserver.jdbc.SQLServerConnection)6 Statement (java.sql.Statement)5 PreparedStatement (java.sql.PreparedStatement)4 Calendar (java.util.Calendar)4 SQLException (java.sql.SQLException)3 DisplayName (org.junit.jupiter.api.DisplayName)3 SQLServerCallableStatement (com.microsoft.sqlserver.jdbc.SQLServerCallableStatement)2 SQLServerStatement (com.microsoft.sqlserver.jdbc.SQLServerStatement)2 DBConnection (com.microsoft.sqlserver.testframework.DBConnection)2 Properties (java.util.Properties)2 SQLServerBulkCopy (com.microsoft.sqlserver.jdbc.SQLServerBulkCopy)1 SQLServerDataSource (com.microsoft.sqlserver.jdbc.SQLServerDataSource)1 SQLServerException (com.microsoft.sqlserver.jdbc.SQLServerException)1