Page 1 of 1

Convert string to varbinary(max)

PostPosted: Thu May 25, 2023 11:24 am
by 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 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 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

Re: Convert string to varbinary(max)

PostPosted: Thu May 25, 2023 7:11 pm
by hpmxxx
I found the solution.

Code: Select all
CONVERT(varbinary(max), ?, 1)

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