How to insert file in the DB table as a BLOB

[nikhil_khanolkar]

Hello,

We are tryng to Insert an .CSV file as a Blob object in the Table in MS SQL server.

A) SAS is installed on the UNIX sever and We are using SQL Passthrough facility to insert the file in the database table.
B) We are facing an error while doing this task.

ERROR: CLI prepare error: [SAS/ACCESS][ODBC 20101 driver][Microsoft SQL Server]Cannot bulk load because the file
       "/opt/sasinside/SASDATA/EXPORT_LOGS/TESTEXPORT.csv" could not be opened. Operating system error code 3(failed to retrieve
       text for this error. Reason: 15105).

It seems like system is not able to find the path specified (Error code 3?)

To insert file as a blob in the Database, is it necessary to store the file in the same server/location? Is there any workaround to do this task?

Code Used:

proc sql;
   connect to sqlsvr as myconn (datasrc="MSSQLDSN" user=WinProfileSSRReader password='xxxxxxxxxxxxxxxxxx');

    select * from connection to myconn
     (

DECLARE @file AS VARBINARY(MAX);
SELECT @file = CAST(bulkcolumn AS VARBINARY(MAX))
      FROM OPENROWSET(
            BULK
            '/opt/sasinside/SASDATA/EXPORT_LOGS/TESTEXPORT.csv',
            SINGLE_BLOB ) AS x;

/*insert into WinProfileRefUser.SSR_REPORTS (id,tnuid,FILE)*/
/*          values (NEWID(),'CCCCCC',@file)*/
         );

   disconnect from myconn;
quit;

Thanks in Advance,
Nikhil

[Patrick · Accepted solution]

There are a few things wrong with your SQL.

select * from connection to myconn
     (

DECLARE @file AS VARBINARY(MAX);
SELECT @file = CAST(bulkcolumn AS VARBINARY

The SELECT... statement is for a query and not for what you're trying to do. Look-up the EXECUTE statement.

http://support.sas.com/documentation/cdl/en/sqlproc/68053/HTML/default/viewer.htm#n0tpd3yaqvep53n1g8...

You can't have two statements in a single EXECUTE() block. You need either to wrap this into PL/SQL or into multiple EXECUTE() blocks.

You're sending statements to the database. Loading happens from there so the external file inclusive path must be as "seen" from the database server.

Not sure about your statements. Googling a bit it looks as if this wouldn't need to be that complicated.

https://msdn.microsoft.com/en-us/library/a1904w6t(VS.80).aspx

And last but not least: Why do you want to store a textual file a BLOB and not as CLOB?

[nikhil_khanolkar]

Hi Patrick,

thanks a lot for your thoughts. You are right, database  server does not have access to the File location and there are challeges in providing access beacuse of security concerns. So it would take a while before we have required access in place.

I was thinking about the workaround where

• store .CSV file into the SAS server memory as a binary object variable.
• and then stream this object from SAS memory into the SQL table column as a BLOB

Is this feasible to do in SAS?

Please share your thoughts/suggestions.

Thanks in advance,

Nikhil

[Patrick]

I don't believe your idea can work for multiple reasons.

Would it be possible to copy your files to a location where SQL Server can read them from (eg. via a SFTP) and then dynamically generate the pass-through SQL code for loading?

[nikhil_khanolkar]

Hi Patrick,

That approach is being considered where in SQL reading a file from the location and then inserting in a table. But that would involve creating a area that is accesible to SQL and SAS. We would take that path if any other approach does not work.  🙂

Can you please advice reasons why idea discussed wouldn't work? Just for my understanding.

Is it because concept of storing a file as a binary object in memory is not native to SAS among other reasons?

Regards,

Nikhil

[Patrick]

Hi Nikhil

I'm a bit out of my depth here that why I wrote "believe". So just a few thoughts.

1. The SAS/Access engine supports loading a SAS table into a data base but there is no SAS variable type which allows you to store a BLOB and there is no support of loading into a BLOB documented.

2. The pass-through code you've posted and everything I could find on the Internet uses a path - and as this executes on the database server this path or url must be accessible from the database server.

Having said that it appears to be possible to load BLOBs via ODBC but it will impact heavily on performance AND you would have to write this on your own (=without the SAS/Access engine). So: I don't know if it's possible at all but I do know that it won't be easy and certainly something you need to write on your own as it's not supported by SAS.