[BUG] Data type float is not supported in bulk copy.

[pkgajulapalli]

Driver version

7.0.0.jre8

SQL Server version

Microsoft SQL Server 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64)
Sep 13 2018 22:16:01
Copyright (c) Microsoft Corporation

Client Operating System

Mac OS

JAVA/JVM version

java version "1.8.0_101"

Problem description

I have to transfer data from one database server to a SQL Server. I'm using SQLServerBulkCopy to do that:
// connection1 is with the source system and
// connection2 is with the destination SQL Server
Statement statement = connnection1.createStatement();
`ResultSet resultSet = statement.executeQuery("select * from db.table");`
SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(connection2);
bulkCopy.setDestinationTableName("tableName");
bulkCopy.writeToServer(resultSet);

JDBC trace logs

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

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:226)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.getDestTypeFromSrcType(SQLServerBulkCopy.java:1443) at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.createInsertBulkCommand(SQLServerBulkCopy.java:1464) at
com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.sendBulkCopyCommand(SQLServerBulkCopy.java:1611) at
com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.doInsertBulk(SQLServerBulkCopy.java:1553) at
com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.access$200(SQLServerBulkCopy.java:63) at
com.microsoft.sqlserver.jdbc.SQLServerBulkCopy$1InsertBulk.doExecute(SQLServerBulkCopy.java:705) at
com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240) at
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869) at
com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.sendBulkLoadBCP(SQLServerBulkCopy.java:733) at
com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:1669) at
com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeResultSet(SQLServerBulkCopy.java:641) at
com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:579)

[peterbae]

Hi @pkgajulapalli, thanks for contacting us. I just tried a quick bulk copy operation (from one connection, using the same SQL Server) that bulk copies data from one table to another (both tables having the same schema, with only one float column) using the 7.0.0.jre8 driver, and it worked for me. Could you provide the table schema from both data sources, and perhaps the connection string as well? Thanks.

[bryanayers]

Hi @peterbae , I've run into the same issue, SQL Server 2017, Win 10 client, mssql-jdbc-7.0.0.jre8:

$ java -version
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)

com.microsoft.sqlserver.jdbc.SQLServerException: Data type float is not supported in bulk copy.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:226)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.getDestTypeFromSrcType(SQLServerBulkCopy.java:1443)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.createInsertBulkCommand(SQLServerBulkCopy.java:1464)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.sendBulkCopyCommand(SQLServerBulkCopy.java:1611)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.doInsertBulk(SQLServerBulkCopy.java:1553)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.access$200(SQLServerBulkCopy.java:63)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy$1InsertBulk.doExecute(SQLServerBulkCopy.java:705)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.sendBulkLoadBCP(SQLServerBulkCopy.java:733)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:1669)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeResultSet(SQLServerBulkCopy.java:641)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:592)
at net.redpoint.SsbcFloat.App.loadRecs(App.java:73)
at net.redpoint.SsbcFloat.App.main(App.java:29)

Full source to reproduce below, just create the table beforehand and assign the appropriate values to the variables used in the connection string.

package test;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetMetaDataImpl;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;

import com.microsoft.sqlserver.jdbc.SQLServerBulkCopy;
import com.microsoft.sqlserver.jdbc.SQLServerBulkCopyOptions;


/*
 * Create table before running:
CREATE TABLE strfloat(
    STRINGVAL varchar (64),
    FLOATVAL float(53),
)
 */

public class App 
{
    public static void main( String[] args )
    {
		loadRecs(5, 10);
    }

	private static void loadRecs(int batchSize, int totalRecords) {
		int		numRecsInBatch = batchSize, total = totalRecords;

		boolean	tableLocking = false;
		String	tableName = "strfloat";
		String	DBSERVER = "", DBNAME = "", DBUSER = "", DBPASS = "";
		String	connectionString = String.format("jdbc:sqlserver://%s:1433;databaseName=%s;user=%s;password=%s;", DBSERVER, DBNAME, DBUSER, DBPASS);

		try (
			Connection conn = DriverManager.getConnection(connectionString);
			SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(conn);
		) {
			RowSetFactory	rowFac = RowSetProvider.newFactory();
			CachedRowSet	crowSet = rowFac.createCachedRowSet();

			bulkCopy.setDestinationTableName(tableName);
			crowSet.setTableName(tableName);	// May not be necessary

			RowSetMetaDataImpl			rsMetaData = createRowsetMetadata(tableName);
			SQLServerBulkCopyOptions	bcOpts = new SQLServerBulkCopyOptions();

			if(tableLocking) {
				bcOpts.setTableLock(true);		// Will release lock between blocks, according to: https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-bulk-copy-operations/managing-bulk-copy-batch-sizes?view=sql-server-2017
			}
			//bcOpts.setKeepIdentity(true);
			bcOpts.setKeepNulls(true);
			crowSet.setMetaData(rsMetaData);

			while(total > 0) {
				numRecsInBatch = (batchSize <= total) ? batchSize : total;
				bcOpts.setBatchSize(numRecsInBatch);
				crowSet.moveToInsertRow();

				for (int jj = 0; jj < numRecsInBatch; jj++) {
					addRec(crowSet);
					crowSet.insertRow();
				}
				crowSet.moveToCurrentRow();

				bulkCopy.setBulkCopyOptions(bcOpts);
				try {
					bulkCopy.writeToServer(crowSet);
				}
				catch(SQLException wts) {
					wts.printStackTrace();
					throw(new SQLException("writeToServer failed: " + wts.getMessage()));
				}

				crowSet.release();
				total -= numRecsInBatch;
			}
		}
		catch(SQLException e) {
			System.err.println("Exception in loadRecs:  " + e);
		}
	}

	private static RowSetMetaDataImpl createRowsetMetadata(String tableName) {
		RowSetMetaDataImpl	rsMetaData = new RowSetMetaDataImpl();
		final int			numCols = 2;

		try {
			rsMetaData.setColumnCount(numCols);

			rsMetaData.setColumnName(1, "STRINGVAL");	rsMetaData.setColumnType(1, java.sql.Types.VARCHAR);
			rsMetaData.setColumnName(2, "FLOATVAL");	rsMetaData.setColumnType(2, java.sql.Types.FLOAT);		// Throws "SQLServerException: Data type float is not supported in bulk copy." in writeToServer()

			for(int ii = 0; ii < numCols; ii++) {
				rsMetaData.setTableName(ii, tableName);
			}
		}
		catch(SQLException e) {

		}

		return(rsMetaData);
	}

	private static boolean addRec(CachedRowSet crowSet) {
		boolean		retval = true;

		try {
			crowSet.updateString(1, "uid1");
			crowSet.updateFloat(2, 3.14f);
		}
		catch(SQLException e) {
			retval = false;
		}

		return retval;
	}

}

[bryanayers]

Hi @pkgajulapalli, did you find a workaround for this?

Hi @peterbae & @cheenamalhotra, can I provide any more info to move this out of "Waiting for Customer"? Thanks in advance!

-Bryan

[cheenamalhotra]

Hi @bryanayers

We do reproduce the error now and investigations are in progress. Thanks for the repro code.

[pkgajulapalli]

Hi @pkgajulapalli, did you find a workaround for this?

Hi @peterbae & @cheenamalhotra, can I provide any more info to move this out of "Waiting for Customer"? Thanks in advance!

-Bryan

Hi @bryanayers, I had to cast the float values to double in the query explicitly and load it to the destination table. And that solution worked for me.

[peterbae]

Fixed in #986. Closing issue.