This topic contains 0 replies, has 1 voice, and was last updated by  Jeff Drumm 5 years, 8 months ago.

Working with VARBINARY() In MS SQL Server

  • I was recently presented with a challenge where a PDF document included in a Result message as a base64-encoded string needed to be INSERTed, in PDF form, into a database table as a BLOB. In Microsoft SQL Server parlance, a BLOB is a column defined as VARBINARY(MAX).

    Finding no obvious mechanisms in the Iguana documentation for handling such an INSERT, I dug into Microsoft’s on-line documentation and discovered that the hexadecimal representation of the image, prefixed by the characters ‘0x’ and included in unquoted form, will work as the ‘binary’ value for the field.

    Assuming the variable obx5val contains the base64-encoded version, the code to convert it to the MSSQL format would be:

    pdfSql = '0x' .. filter.hex.enc(filter.base64.dec(obx5val))

    It can then be inserted into the appropriate VARBINARY(MAX) field with SQL similar to:

    conn:execute("INSERT INTO LabResults (LabName, OrderNumber, MetaReportContents, MetaReportSetup) VALUES (" .. conn:quote(labID) .."," .. conn:quote(labOrdNo) .. "," .. pdfSql .. "," .. conn:quote('PDF'))")

    Other databases have similar mechanisms for inserting binary data in BLOB type fields.

    Jeff Drumm ◊ VP and COO ◊ HICG, LLC. ◊

You must be logged in to reply to this topic.