Convert string to varbinary(max)

[hpmxxx]

Hello all,

I am having an issue saving a string to a database column of type varbinary(max) in a Microsoft SQL Server 2019.

I receive a hash value as a string via a REST endpoint. Now when I try to save this to the DB, I get the following exception:

Code: Select all

com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

The exception is thrown by the following code:

Code: Select all

var hash = '0xF6A78BA60C568C3668A6E419E9C1215A80D478CD511F892DFFDEAB051'
var sql = 'UPDATE TABLE SET TABLE.COLUMN = ? WHERE TABLE.PK = ?';
var sqlParam = [hash, pk];
var sqlSuccess = plugins.rawSQL.executeSQL(SERVERNAME, sql, sqlParam);

For example, if I convert the string as follows, the hash string is converted incorrectly:

Code: Select all

var sql = 'UPDATE TABLE SET TABLE.COLUMN = CAST(? AS varbinary(max)) WHERE TABLE.PK = ?';

The result looks like this:

Code: Select all

TABLE.COLUMN: 0x3078463641373842413630433536384333363638413645343139453943313231354138304434373843443531314638393244464644454142303531


"CONVERT" creates the same problem as "CAST".

Does anyone know how to convert a string so that hash and TABLE.COLUMN are identical?

Servoy 2020.3.3

[hpmxxx]

I found the solution.

Code: Select all

CONVERT(varbinary(max), ?, 1)

The 1 is the key. The default value in MsSql is 0.