2

I am trying to use SQLServerBulkCopy for migrating data from PostgreSQL to MS-SQL Server. I am facing problem for non-English characters (Unicode). To store these data, in PostgreSQL text datatype and for MS-SQL, nvarchar is used.

This is the code snippet

public class MssqlBulkLoad {
    public static void main(String args[]) throws SQLException {
        String sql = "select id, data::varchar from test_data";

        Connection pgConn = getPgConnection();
        Statement pgStmt = pgConn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
        pgStmt.setFetchSize(200);

        Connection sqlServerConn = getSQLServerConnection();
        SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(sqlServerConn);
        bulkCopy.setBulkCopyOptions(options());
        bulkCopy.setDestinationTableName("TEST_DATA2");

        ResultSet pgResultSet = pgStmt.executeQuery(sql);
        printMeta(pgResultSet);

        bulkCopy.writeToServer(pgResultSet);

        pgResultSet.close();
        bulkCopy.close();
        sqlServerConn.close();
        pgStmt.close();
        pgConn.close();
    }

    private static void printMeta(ResultSet pgResultSet) throws SQLException {
        ResultSetMetaData rsmd = pgResultSet.getMetaData();
        int c = rsmd.getColumnCount();
        for (int i = 1; i <= c; i++) {
            System.out.println(rsmd.getColumnType(i) + "\t" + rsmd.getColumnTypeName(i));
        }
    }

    private static SQLServerBulkCopyOptions options() throws SQLException {
        SQLServerBulkCopyOptions options = new SQLServerBulkCopyOptions();
        options.setBulkCopyTimeout(60000);
        options.setKeepIdentity(true);
        options.setBatchSize(50);
        return options;
    }
}

The newly migrated data is in an unknown format, see below

enter image description here

But, the actual data should be as shown below.

enter image description here

In my Java code, to select data from PostgreSQL, I tried using simple select query, select * from test_data but I am getting exception

com.microsoft.sqlserver.jdbc.SQLServerException: Data type other is not supported in bulk copy.

From this, it seems that when selecting data of text datatype, the type of the column in ResultSet is Types.OTHER. So, to avoid about mentioned exception, I case text type to varchar, which returns types as Types.VARCHAR.

I am using sqlserver-jdbc driver 7.0.0

Can someone help me with this.

suraj1291993
  • 474
  • 1
  • 5
  • 15
  • Could this be a collation mismatch? What is the collation specified (or defaulted) in your Postgresql DB and what is it in SQL Server? – user1443098 Sep 17 '18 at 14:48
  • One thing you could do is use use SSIS. See https://www.mssqltips.com/sqlservertip/2619/export-data-from-postgres-to-sql-server-using-ssis/ – user1443098 Sep 17 '18 at 14:49
  • @user1443098 I cant use SSIS. – suraj1291993 Sep 17 '18 at 15:02
  • Why can't you use it? Do you need training, or other help? – user1443098 Sep 17 '18 at 15:05
  • I didn’t mean like that. We will be using it for an automated process. So we need to integrate a java based solution into our product. – suraj1291993 Sep 17 '18 at 15:07
  • SSIS shines at automated processes. Used by thousands of businesses every day for that (I don't work for Microsoft!) You know you can call SSIS from an .NET application, right? – user1443098 Sep 17 '18 at 15:10
  • @user1443098 Collation of SQL Server is `SQL_Latin1_General_CP1_CI_AS` and PostgreSQL is `English_India.1252`. Also, I haven't looked into .net side for this current problem because, we cant incorporate .net into our current design. For this reason only I am looking into Java BulkCopy. – suraj1291993 Sep 17 '18 at 15:28
  • Have you tried the collation Latin1_General_100_CI_AS? See more here: https://learn.microsoft.com/en-us/sql/t-sql/statements/windows-collation-name-transact-sql?view=sql-server-2017 – user1443098 Sep 17 '18 at 15:31
  • In the default collation itself, when inserting data via SQL, it works fine (refer the snapshot attached) – suraj1291993 Sep 17 '18 at 15:33
  • Have you tried casting "other" to NVARCHAR? assuming you may need unicode – user1443098 Sep 17 '18 at 15:38
  • I hope you mean in the select query which is getting executed in Postgres. If that's the case, in Postgres there is nothing specific for nvarchar. So I can't directly cast it to nvarchar. – suraj1291993 Sep 17 '18 at 15:41
  • I mean when building the bulk copy command. You should be able to specify the target datatypes and collations – user1443098 Sep 17 '18 at 15:46
  • @user1443098 I can't find any such Java API . Can you direct me to that API – suraj1291993 Sep 17 '18 at 15:53
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/180223/discussion-between-user1443098-and-suraj1291993). – user1443098 Sep 17 '18 at 17:22

0 Answers0