[AWA] Using SQL Variable to work with SQL variant data type

Document ID : KB000097217
Last Modified Date : 21/05/2018
Show Technical Document Details
Issue:
Customer wanted to setup SQL variable to select and update value from SQL with data type "variant" but while previewing encountered the following error message:

U2012033 Query could not be executed: 'com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not supported. (Client: '****', PREVIEW, Variable: 'VARA.***')'
Resolution:
The SQL_Variant type is not compatible with jdbc, which leads to the fact that we usually have to to convert sql_variant type to other (i.e nvarchar) for SELECT query,
while UPDATE should work (but PREVIEWING an UPDATE statement would expect a value which should not be presented in UPDATE query result).

Examples:
  • Read: to retrieve values using select query, we have to use converting function such as CAST() function to read sql_variant type for example:
SELECT CAST(Variant_value as NVARCHAR(MAX))
FROM Table_X
WHERE ...
  • Write: In case you are trying to update table & value under sql_variant type for the purpose of using with script then using UPDATE query would work but not showing any meaningful return value, so PREVIEW would not work and displays error.
UPDATE Table_X
SET Variant_Value='Success'
WHERE...
 
Additional Information:
You can test the result by using simple script like:
:SET &TEST# = GET_VAR(VARA.SQL.UPDATEVARIABLE)
:PRINT &TEST#

The result of &TEST# should be blank but the value of your UPDATE query from VARA.SQL.UPDATEVARIABLE should be updated (unfortunately you have to check this from the SQL client)