SQLServerBulkCopy does not accept the ISQLServerConnection during initialization while running in websphere liberty server

[GayathriNagarajan18]

Driver version

sqljdbc42.jar

SQL Server version

SQL Server 2014

Client Operating System

Windows 10

JAVA/JVM version

JDK 1.8

Table schema

I wanna just store the complete raw data directly from a text file to the corresponding columns using SQLServerBulkCopy API, so I have not kept any keys (primary/unique/foreign) for my table

Problem description

I am using SQLServerBulkCopy API to store millions of records. My app runs in websphere-liberty. I use SQLServerConnectionPoolDataSource. So when I try to pass the connection to the bulkCopy like SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(conn); I get an exception stating com.microsoft.sqlserver.jdbc.SQLServerException: Destination connection must be a connection from the Microsoft JDBC Driver for SQL Server. where the conn is unwrapped using ISQLServerConnectioninterface from app server created connection namely WsJdbcConnection.

But passing a physical connection using the connection url to the SQLServerBulkCopy works good.

Reproduction code

if(connection.isWrapperFor(ISQLServerConnection.class)) {
       ISQLServerConnection conn = connection.unwrap(ISQLServerConnection.class);
       SQLServerBulkCopy bulkCopy =  new SQLServerBulkCopy(conn);
}

[cheenamalhotra]

Hi @GayathriNagaranjan18

Preview Release v7.3.0 contains the fix for the same introduced in PR #968. Please test with this version of the driver and let us know if you face any issues.

Thanks!

[GayathriNagarajan18]

I downloaded mssql-jdbc-7.3.0.jre-preview.jar. Still I face the same issue. Logs:

[6/3/19 12:56:52:129 UTC] 000000e5 SystemOut    O  12:56:52.129 [Default Executor-thread-183] INFO  COMMON - SQLServerBulkCSVFileRecord: com.microsoft.sqlserver.jdbc.SQLServerBulkCSVFileRecord@59d2e086

[6/3/19 12:56:52:129 UTC] 000000e5 SystemOut    O  12:56:52.129 [Default Executor-thread-183] INFO  COMMON - SQLServerBulkCopyOptions: com.microsoft.sqlserver.jdbc.SQLServerBulkCopyOptions@4092c85d

[6/3/19 12:56:52:142 UTC] 000000e5 SystemOut    O  Driver name: Microsoft JDBC Driver 7.3 for SQL ServerDriver version: 7.3.0.0Driver Major version: 7Driver Minor version: 3

[6/3/19 12:56:52:142 UTC] 000000e5 SystemOut    O  Connection Metadata: com.ibm.ws.rsadapter.jdbc.WSJdbcDatabaseMetaData@3d985d2e

[6/3/19 12:56:52:143 UTC] 000000e5 SystemOut    O  Connection: Proxy@3fb13701 className: class com.sun.proxy.$Proxy252 interfaces: [Ljava.lang.Class;@bf4dca3 superClass: class java.lang.reflect.Proxy

[6/3/19 12:56:52:186 UTC] 000000e5 SystemOut    O  12:56:52.186 [Default Executor-thread-183] ERROR COMMON - Exception at bulkCopy: com.microsoft.sqlserver.jdbc.SQLServerException: Destination connection must be a connection from the Microsoft JDBC Driver for SQL Server.

[6/3/19 12:56:52:186 UTC] 000000e5 SystemErr    R  com.microsoft.sqlserver.jdbc.SQLServerException: Destination connection must be a connection from the Microsoft JDBC Driver for SQL Server.

[6/3/19 12:56:52:186 UTC] 000000e5 SystemErr    R   at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:228)

[cheenamalhotra]

Can you share me your Java code to reproduce error as well?

[GayathriNagarajan18]

public class BulkCopy {

    @Inject
    private ConnectionFactory connectionFactory;
    private Query query;

    public void persist(String filePath, int batchSize, String persistenceType) {
        final Connection connection = connectionFactory.getConnection();
        try {
            //Will print WsJdbcConnection
            Log.info("connection: " + connection.toString());

            //Delete
            query = new Query("DELETE FROM TBL", connection);
            query.executeUpdate();

            //Persist
            long startTime = System.currentTimeMillis();

            SQLServerBulkCSVFileRecord fileRecord = getSqlServerBulkCSVFileRecord(filePath);
            SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
            copyOptions.setBatchSize(batchSize);
            copyOptions.setTableLock(true);

            Log.info("SQLServerBulkCSVFileRecord: " + fileRecord);
            Log.info("SQLServerBulkCopyOptions: " + copyOptions);

            ISQLServerConnection conn = null;

            if(connection.isWrapperFor(ISQLServerConnection.class)) {
                conn = connection.unwrap(ISQLServerConnection.class);
                System.out.println("Driver name: " + conn.getMetaData().getDriverName() +
                        "Driver version: " + conn.getMetaData().getDriverVersion() +
                        "Driver Major version: " + conn.getMetaData().getDriverMajorVersion() +
                        "Driver Minor version: " + conn.getMetaData().getDriverMinorVersion());
                System.out.println("Connection Metadata: " + conn.getMetaData().toString());
            }
            System.out.println("Connection: " + conn.toString() + " className: " + conn.getClass().toString()
                    + " interfaces: " + conn.getClass().getInterfaces().toString()
                    + " superClass: " + conn.getClass().getSuperclass());

            SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(conn);
            Log.info("bulkCopy initialized: " + bulkCopy.toString());

            bulkCopy.setBulkCopyOptions(copyOptions);
            bulkCopy.setDestinationTableName("DEALER_WEEKLY_FTR_COUNTS");
            bulkCopy.writeToServer(fileRecord);

            long endTime   = System.currentTimeMillis();
            System.out.println("BulkCopy took: " + (endTime - startTime) + "ms");

        } catch(Exception e) {
            Log.exception("Exception at bulkCopy: " + e);
            e.printStackTrace();
            throw new ServerException(e.getMessage());
        } finally {
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new ServerException(e.getMessage());
                }
            }
        }
    }

    private SQLServerBulkCSVFileRecord getSqlServerBulkCSVFileRecord(String filePath) throws SQLServerException {
        SQLServerBulkCSVFileRecord fileRecord = null;
        fileRecord = new SQLServerBulkCSVFileRecord(filePath, null, "\t", true);

        fileRecord.addColumnMetadata(1, null, Types.VARCHAR, 20, 0);
        fileRecord.addColumnMetadata(2, null, Types.VARCHAR, 20, 0);

        fileRecord.addColumnMetadata(3, null, Types.INTEGER, 0, 0);
        fileRecord.addColumnMetadata(4, null, Types.INTEGER, 0, 0);
        fileRecord.addColumnMetadata(5, null, Types.INTEGER, 0, 0);

        fileRecord.addColumnMetadata(6, null, Types.DECIMAL, 18, 0);
        fileRecord.addColumnMetadata(7, null, Types.DECIMAL, 18, 0);
        fileRecord.addColumnMetadata(8, null, Types.DECIMAL, 18, 0);
        return fileRecord;
    }

}

[cheenamalhotra]

Hi @GayathriNagarajan18

Looks like you're providing an object of WsJdbcConnection which is not driver's connection class instance. The response is as expected, as Bulk Copy is currently designed to work with Driver's own connection classes. Reason being there are internal APIs implemented by our driver Connection classes that are use in Bulk Copy implementation.

Is there a specific requirement where you need to pass this implemented class object, and could you pass SQLServerConnection object instead?

[GayathriNagarajan18]

App server created the WSJdbcConnection. Here, the conn- is unwrapped from WSJdbcConnectionas ISQLServerConnection. And the interface prints as conn: Proxy@7cfc3172 So I expected that, SQLServerBulkCopywould accept the interface. But it didn't.

My scenario is quite similar to the example at https://docs.microsoft.com/en-us/sql/connect/jdbc/wrappers-and-interfaces?view=sql-server-2017#interfaces.

Is SQLServerBulkCopy not supposed to handle the SQLServerConnection wrapped by the application server?