Forum Discussion

ebarner's avatar
ebarner
Former Employee
9 years ago

RDBMS - BLOB Data Type

The post below was designed for the BLOB data type in SQL Server (2014) but similar concepts should work for other data sources as well.

Setup:
in SQL Server Mgmt Studio

CREATE TABLE [BLOBS](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Data] varbinary NULL,
[type] varchar NULL
CONSTRAINT [PK_BLOBS] PRIMARY KEY CLUSTERED
(
[Id] ASC
) ) ON [PRIMARY]

insert a few records based on the date types you want to try:

INSERT BLOBS( Data, type )
SELECT *,‘jpg’ FROM OPENROWSET (BULK ‘C:\FOLDER\FILENAME.jpg’, SINGLE_BLOB) rs

INSERT BLOBS( Data, type )
SELECT *,‘csv’ FROM OPENROWSET (BULK ‘C:\FILDER\FILENAME.csv’, SINGLE_BLOB) rs

The data itself when try to look at in SQL Server is not very meaningful:

With a snapLogic pipeline though, you can control what you want to have happen with it in a simple peipline that selects the data and converts $data into $content.

SQL Select:

data to content:

Router

Document to Binary (both same configuration)

CSV Parser (data preview):

File Writer

I built in the bottom path as a future catch bucket for other data types added to my BLOB storage table.

  • angie's avatar
    angie
    New Contributor

    Hi, @arunnp

    1. The service url needs to start with “http(s)://” and then specify ble/incident. For example http://ble/incident
    2. For better reading of the URL, instead of concatenating the strings under Service URL, you can pass the input parameters under “Query parameters”. So depending how many query parameters will be used, you can add more by using +. See the example in the picture

    Regards,
    Angela.

    • arunnp's avatar
      arunnp
      New Contributor II

      @viktor_n replacing the field value using Expression Builder worked, but I guess my Service URL is long
      I got error Invalid URI.
      Later I tried with Query Parameters suggested by @angie it worked well
      Thanks a lot for the your help.