Insert NULL into varbinary(max) field throws

Ignition
1

Ignition 8.3.3, SQL Server 2025 DB
Using Microsoft SQLServer driver as shipped with Ignition.

I have a table with two columns:

[name] [varchar](50) NOT NULL,
[data] [varbinary](max) NULL,

Attempting to insert a NULL/None into varbinary field:

    query = 'insert into groups (name,data) values (?,?)'
    args = [ 'foo', None ]
    system.db.runPrepUpdate(query, args)

Throws with:

Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

Any ideas?

-Shane

2

The error message says you must explicitly cast the value. Something like this:

insert into groups (name,data) values (?,cast(? as varbinary))
1 Like
3

Directly from SSMS or from the Designer’s built-in database query browser:

insert into groups (name,data) values (‘foo’,NULL)

works without the cast/convert.

I suspect that the parameter binding (?,?) is getting confused and casting the None to a string (or other bytes type) before calling the SQL driver.

-Shane

4

IIRC, Ignition's guts simply hands the null to the JDBC driver. Null is fundamentally untyped in java, so there's no way to indicate to SQL unless supplied in the query as a constant.

SSMS doesn't use JDBC, so it isn't comparable anyways.

The cast doesn't hurt anything if you pass an actual byte array. Consider it a "type hint" for cases that JDBC cannot figure out on its own.

2 Likes
5

It may well be an issue with the JDBC driver. However, every other type I’ve tried (int, varchar, etc.) allowed passing and inserting a NULL/None without having to cast/convert in the SQL itself.

I thought it worth reporting in case is was something specific to way Ignition was handling it.

-Shane

6

No, it occurs for other types, too--I've often had to use it with datetime types, and for other DB flavors. (Only for nullables.) Getting away without it relies on implicit conversion, which varies by DB brand.

(I prefer PostgreSQL's double colon cast operator as a more readable alternate, but that is non-standard, unfortunately.)