Convert SQL Server varbinary(max) to Float

Ignition
,
1

I have a float value stored in a SQL Server database as varbinary(max) [from a COTS product] that I need to retrieve in Ignition 8.1.47.

How can I get the float value?

I’m getting different values using CONVERT and CAST and it looks like I’ll need a script to convert it to a Float after I get something I trust from the database. The value, Convert1 and Convert2 have different values with every execution though the database value remains the same.

SQL Server value = 0x014052C00000000000

Named Query:

SELECT TOP(1)
    ,value
    ,CONVERT(varbinary(64), value, 1) AS Convert1
    ,CONVERT(varbinary(64), value, 2) AS Convert2
    ,CAST(value AS nvarchar(max)) AS Cast1
    ,CAST(value AS varchar(120)) AS Cast2
FROM property_vtq
WHERE name = 'TargetValue'
AND id = :name

Sample output of the named query:

image

2

Doh! realized I was using the wrong data type in the convert statement.

,CONVERT(nvarchar(100), value, 1) AS TargetString

Consistently gives me the same value that SQL Server shows.

Now use Jython to turn it into a Float

import struct
targetString='0x014052C00000000000'
binaryData = bytearray.fromhex(targetString[2:])  # Remove '0x', convert to bytes

floatValue = struct.unpack('>d', binaryData[1:])[0]

print floatValue
3

If you don’t cast it at all, you should get a byte array that struct can handle directly. You can also wrap such a byte array in a Java ByteBuffer, then use .readDouble(), which should be much more efficient than the jython struct stdlib module.

4

Thanks.

I’m still confused that without a CONVERT, the NamedQuery returns a different result every time.

Here’s 5 returns from NamedQuery:

SELECT value FROM property_vtq WHERE name = 'TargetValue' AND id = :name

  • [B@8370ee
  • [B@7cee8095
  • [B@53f91801
  • [B@404d0384
  • [B@77a3ddc

Edit: TargetValue and id is unique, only returns one row

5

The default stringification of a byte array object is its address in memory. That has no relevance to the content. Add print str(binary_data) to your script to see what I mean.

2 Likes