Convert string to varbinary(max)

Questions and answers regarding general SQL and backend databases

Convert string to varbinary(max)

Postby hpmxxx » Thu May 25, 2023 11:24 am

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
Hans-Peter Minnig
Nextree GmbH
hpmxxx
 
Posts: 86
Joined: Wed Sep 10, 2003 5:50 pm
Location: Switzerland

Re: Convert string to varbinary(max)

Postby hpmxxx » Thu May 25, 2023 7:11 pm

I found the solution.

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

The 1 is the key. The default value in MsSql is 0.
Hans-Peter Minnig
Nextree GmbH
hpmxxx
 
Posts: 86
Joined: Wed Sep 10, 2003 5:50 pm
Location: Switzerland


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 2 guests