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

Example 16 with SQLServerBulkCopy

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

the class BulkCopyCSVTest method testBulkCopyCSV.

private void testBulkCopyCSV(SQLServerBulkCSVFileRecord fileRecord, boolean firstLineIsColumnNames) {
    DBTable destTable = null;
    try (BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(filePath + inputFile), encoding))) {
        // read the first line from csv and parse it to get datatypes to create destination column
        String[] columnTypes = br.readLine().substring(1).split(delimiter, -1);
        br.close();
        int numberOfColumns = columnTypes.length;
        destTable = new DBTable(false);
        try (SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy((Connection) con.product())) {
            bulkCopy.setDestinationTableName(destTable.getEscapedTableName());
            // add a column in destTable for each datatype in csv
            for (int i = 0; i < numberOfColumns; i++) {
                SqlType sqlType = null;
                int precision = -1;
                int scale = -1;
                String columnType = columnTypes[i].trim().toLowerCase();
                int indexOpenParenthesis = columnType.lastIndexOf("(");
                // skip the parenthesis in case of precision and scale type
                if (-1 != indexOpenParenthesis) {
                    String precision_scale = columnType.substring(indexOpenParenthesis + 1, columnType.length() - 1);
                    columnType = columnType.substring(0, indexOpenParenthesis);
                    sqlType = SqlTypeMapping.valueOf(columnType.toUpperCase()).sqlType;
                    // add scale if exist
                    int indexPrecisionScaleSeparator = precision_scale.indexOf("-");
                    if (-1 != indexPrecisionScaleSeparator) {
                        scale = Integer.parseInt(precision_scale.substring(indexPrecisionScaleSeparator + 1));
                        sqlType.setScale(scale);
                        precision_scale = precision_scale.substring(0, indexPrecisionScaleSeparator);
                    }
                    // add precision
                    precision = Integer.parseInt(precision_scale);
                    sqlType.setPrecision(precision);
                } else {
                    sqlType = SqlTypeMapping.valueOf(columnType.toUpperCase()).sqlType;
                }
                destTable.addColumn(sqlType);
                fileRecord.addColumnMetadata(i + 1, "", sqlType.getJdbctype().getVendorTypeNumber(), (-1 == precision) ? 0 : precision, (-1 == scale) ? 0 : scale);
            }
            stmt.createTable(destTable);
            bulkCopy.writeToServer((ISQLServerBulkRecord) fileRecord);
        }
        if (firstLineIsColumnNames)
            validateValuesFromCSV(destTable, inputFile);
        else
            validateValuesFromCSV(destTable, inputFileNoColumnName);
    } catch (Exception e) {
        fail(e.getMessage());
    } finally {
        if (null != destTable) {
            stmt.dropTable(destTable);
        }
    }
}

Example 17 with SQLServerBulkCopy

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

the class BulkData method testBinaryColumnAsByte.

/**
 * Test binary out of length (sending length of 6 to binary (5))
 *
 * @throws Exception
 */
@Test
public void testBinaryColumnAsByte() throws Exception {
    variation = "testBinaryColumnAsByte";
    BulkData bData = new BulkData(variation);
    query = "CREATE TABLE " + destTable + " (col1 binary(5))";
    stmt.executeUpdate(query);
    try (SQLServerBulkCopy bcOperation = new SQLServerBulkCopy(connectionString)) {
        bcOperation.setDestinationTableName(destTable);
        bcOperation.writeToServer(bData);
        fail("BulkCopy executed for testBinaryColumnAsByte when it it was expected to fail");
    } catch (Exception e) {
        if (e instanceof SQLException) {
            assertTrue(e.getMessage().contains("The given value of type"), "Invalid Error message: " + e.toString());
        } else {
            fail(e.getMessage());
        }
    }
}

Example 18 with SQLServerBulkCopy

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

the class BulkData method testSmalldatetimeOutofRange.

/**
 * Testing that setting out of range value for small datetime is throwing the proper message
 *
 * @throws Exception
 */
@Test
public void testSmalldatetimeOutofRange() throws Exception {
    variation = "testSmalldatetimeOutofRange";
    BulkData bData = new BulkData(variation);
    query = "CREATE TABLE " + destTable + " (smallDATA smalldatetime)";
    stmt.executeUpdate(query);
    try (SQLServerBulkCopy bcOperation = new SQLServerBulkCopy(connectionString)) {
        bcOperation.setDestinationTableName(destTable);
        bcOperation.writeToServer(bData);
        fail("BulkCopy executed for testSmalldatetimeOutofRange when it it was expected to fail");
    } catch (Exception e) {
        if (e instanceof SQLException) {
            assertTrue(e.getMessage().contains("Conversion failed when converting character string to smalldatetime data type"), "Invalid Error message: " + e.toString());
        } else {
            fail(e.getMessage());
        }
    }
}

Example 19 with SQLServerBulkCopy

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

the class BulkData method testSendValidValueforBinaryColumnAsString.

/**
 * Verify that sending valid value in string format for binary column is successful
 *
 * @throws Exception
 */
@Test
public void testSendValidValueforBinaryColumnAsString() throws Exception {
    variation = "testSendValidValueforBinaryColumnAsString";
    BulkData bData = new BulkData(variation);
    query = "CREATE TABLE " + destTable + " (col1 binary(5))";
    stmt.executeUpdate(query);
    try (SQLServerBulkCopy bcOperation = new SQLServerBulkCopy(connectionString)) {
        bcOperation.setDestinationTableName(destTable);
        bcOperation.writeToServer(bData);
        try (ResultSet rs = stmt.executeQuery("select * from " + destTable)) {
            while (rs.next()) {
                assertEquals(rs.getString(1), "0101010000");
            }
        }
    } catch (Exception e) {
        fail(e.getMessage());
    }
}

Example 20 with SQLServerBulkCopy

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

the class BulkData method testBinaryColumnAsString.

/**
 * Test sending longer value for binary column while data is sent as string format
 *
 * @throws Exception
 */
@Test
public void testBinaryColumnAsString() throws Exception {
    variation = "testBinaryColumnAsString";
    BulkData bData = new BulkData(variation);
    query = "CREATE TABLE " + destTable + " (col1 binary(5))";
    stmt.executeUpdate(query);
    try (SQLServerBulkCopy bcOperation = new SQLServerBulkCopy(connectionString)) {
        bcOperation.setDestinationTableName(destTable);
        bcOperation.writeToServer(bData);
        fail("BulkCopy executed for testBinaryColumnAsString when it it was expected to fail");
    } catch (Exception e) {
        if (e instanceof SQLException) {
            assertTrue(e.getMessage().contains("The given value of type"), "Invalid Error message: " + e.toString());
        } else {
            fail(e.getMessage());
        }
    }
}