As of 6.3.0, the JDBC driver supports the sql_variant datatype. Sql_variant is also supported with using features such as TVP and BulkCopy with some limitations mentioned later on this page. Please note that not all data types can be stored in the sql_variant data type. For a list of supported data types with sql_variant, please check the SQL Server Docs.
Populating and retrieving a table:
Assuming one has a table with sql_variant column as:
CREATE TABLE sampleTable (col1 sql_variant)
A sample script to insert values using statement:
Statement stmt = connection.createStatement();
stmt.execute("insert into sampleTable values (1)");
Inserting value using prepared statement:
PreparedStatement pstmt = con.prepareStatement("insert into sampleTable values (?)");
pstmt.setObject(1, 1);
pstmt.execute();
If you know the underlying type being passed, you can use the respective setter. For instance, if you want to insert an integer value, you could use pstmt.setInt()
PreparedStatement pstmt = con.prepareStatement("insert into table values (?)");
pstmt.setInt (1, 1);
pstmt.execute();
For reading values from table, you can use the respective getter, if you know the underlying or getObject() or getString() methods:
SQLServerResultSet rs = (SQLServerResultSet) stmt.executeQuery("select * from sampleTable ");
rs.next();
rs.getInt(1); //or rs.getString(1); or rs.getObject(1);
Using stored procedures with sql_variant:
Having a stored procedure such as:
String sql = "CREATE PROCEDURE " + inputProc + " @p0 sql_variant OUTPUT AS SELECT TOP 1 @p0=col1 FROM sampleTable ";
You need to register the output parameters:
CallableStatement cs = con.prepareCall(" {call " + inputProc + " (?) }");
cs.registerOutParameter(1, microsoft.sql.Types.SQL_VARIANT);`
cs.execute();
Limitations of sql_variant:
1- If using TVP to populate a table with a datetime/smalldatetime/date value stored in sql_variant, calling getDateTime()/getSmallDateTime()/getDate() on resultset does not work and throws the following exception:
Java.lang.String cannot be cast to java.sql.Timestamp
Workaround: use getString() or getObject() instead.
2- Using TVP to populate a table and sending null value in sql_variant is not supported and throws exception.
Inserting null value with column type sql_variant in TVP is not supported.