Insert and retrieving varbinary(max) field in stored procedure

Question

Post reply

Insert and retrieving varbinary(max) field in stored procedure

Larry Kruse

SSCrazy

Points: 2617
More actions
February 8, 2011 at 3:00 pm

#388810

Folks,
We have a need to store large files from a web application in our database. I've followed the many steps necessary to implement the FILESTREAM data storage in our database. I've got the table defined as:
CREATE TABLE [dbo].[incident_files](
[file_id] [int] IDENTITY(1,1) NOT NULL,
[file_name] [nvarchar](50) NULL,
[file_type] [nvarchar](50) NULL,
[file_size] [int] NULL,
[file_loaded] [datetime] NULL,
[incident_id] [int] NULL,
[file] [varbinary](max) FILESTREAM NULL,
[file_GUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
PRIMARY KEY CLUSTERED
(
[file_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] FILESTREAM_ON [ccFILESTREAM],
UNIQUE NONCLUSTERED
(
[file_GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [ccFILESTREAM]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[incident_files] ADD DEFAULT (newid()) FOR [file_GUID]
GO
All is well and good so far. I'm attempting to set up some stored procedures that an application can call to INSERT or SELECT entries in the "incident_files" table.
I have the following as my Add procedure, however it is generating an error when I attempt to run the CREATE PROCEDURE step.
CREATE PROCEDURE usp_Add_Incident_File
@file_name nvarchar(254) = '',
@file_type nvarchar(50),
@file_size int = null,
@incident_id INT = 0
AS
BEGIN
SET NOCOUNT ON;
DECLARE @fn varbinary(max)
SET @fn = CONVERT( varbinary(max), @file_name )
DECLARE @SQL varbinary(max)
SELECT @SQL ='CAST( bulkcolum AS VARBINARY(max) ) FROM ' +
' OPENROWSET( BULK ''' + @fn + ''', SINGLE_BLOB ) AS x'
PRINT @SQL
BEGIN TRANSACTION insTrans
Insert into dbo.incident_files (
[file_name],
[file_type],
[file_size],
[file_loaded],
[incident_id],
[file]
) VALUES (
@file_name,
@file_type,
@file_size,
GETDATE(),
@incident_id,
@SQL
)
COMMIT TRANSACTION insTrans;
END
GO
The error message is: Msg 402, Level 16, State 1, Procedure usp_Add_Incident_File, Line 20
The data types varchar and varbinary(max) are incompatible in the add operator.
Most of the difficulties seem to lie with the fact that the OPENROWSET( ) doesn't support parameters, so I'm having to fudge around with the single quote marks in order to embed the @file_name parameter into the OPENROWSET( ) function. [Obviously, I haven't been too successful at it.]
Can anyone help me figure out what the correct syntax should look like?
I'm also completely open to any suggestions about a better way to implement this FILESTREAM jazz - there seems to be very little in the way of working sample code.
Many thanks for taking a look at it!
Larry

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

SELECT @SQL = 'CAST( bulkcolum AS VARBINARY(max) ) FROM ' +

' OPENROWSET( BULK ''' + @fn + ''', SINGLE_BLOB ) AS x'

the above is your problem. I am not sure what you are trying to accomplish but you can't use the add operator '+' to join varchar and varbinary data, which is what this statement is trying to do.

perhaps you need to construct everything first as varchar data then cast the whole thing to varbinary when assigning to the @SQL variable.

The probability of survival is inversely proportional to the angle of arrival.

Larry Kruse SSCrazy Points: 2617 More actions · Answer 2

Thank you STurner.

I took your suggestions and did exactly what you suggested and I finally got it to work!

The following represents the "fixed" version of the stored procedure which now appears to work.

ALTER PROCEDURE usp_Add_Incident_File

@file_name nvarchar(254) = '',

@file_type nvarchar(50),

@file_size int = null,

@incident_id INT = 0

AS

BEGIN

SET NOCOUNT ON;

DECLARE @SQL varbinary(max)

DECLARE @STR nvarchar(400)

SET @STR = N'SELECT @SQL = CAST( bulkcolumn AS VARBINARY(max) ) FROM '

SET @STR += N' OPENROWSET( BULK '''

SET @STR += @file_name

SET @STR += N''', SINGLE_BLOB ) AS x'

--PRINT @STR

EXEC sp_executeSQL @STR, N'@SQL varbinary(max) OUT', @SQL OUTPUT;

--PRINT 'The @SQL parm after: '

--PRINT CAST( @SQL AS varchar(50))

BEGIN TRANSACTION insTrans

Insert into dbo.incident_files (

[file_name],

[file_type],

[file_size],

[file_loaded],

[incident_id],

[file]

) VALUES (

@file_name,

@file_type,

@file_size,

GETDATE(),

@incident_id,

@SQL

)

COMMIT TRANSACTION insTrans;

END

GO

I can't tell you how many times I stubbed my toes in trying to get this all working correctly. Geez...the OPENROWSET( ) macro/function could be a little friendlier to work with if: A) It accepted SQL parameters, and if it could handle non-unicode strings. <-- Are you listening Microsoft?

Thanks for getting me going in the right direction. I may have to return here when I start working on the sp to extract the database field back into a file! Yikes! :w00t:

sanjay kulkarni Valued Member Points: 65 More actions · Answer 3

How this helps me while fetching VARBINARY(MAX) field and generate insert scripts?

Thanks in advance.

Larry Kruse SSCrazy Points: 2617 More actions · Answer 4

I'm not sure what you are asking for Sanjay.

We've got all of the pieces working in production now to store and retrieve FILESTREAM data into and out of the database.

A lot of the issues I faced was in figuring out where the code was going to be running. If I call the sp from code running on the web-server, then the file I want to insert into the FILESTREAM storage has to originate on that web-server drive. What we do, is to transfer a file from a client desktop into temp folder on the webserver and then a web service calls the usp_Create_Filestream_File stored procedure.

We are intending to store streaming video into the FILESTREAM data-store too. There, we have two scenarios: 1. A video file has already been created and needs to be stored for playback purposes. 2. A camera is opened and we want to store the video file direct from the camera.

The first scenario is identical to the code listed above. The second scenario requires a sp that basically creates a "blank" (empty) FILESTREAM file and then returns the file_id to the caller. The caller then executes compiled C# code which executes a BEGIN TRANSACTION statement, then runs a GET_FILESTREAM_TRANSACTION_CONTEXT( ), opens a SqlFileStream( ) object, read the binary data into a byte[] buffer, closes the ouput file, and then commits the transaction.

Not sure if this helps you or not, but if not, let me know and I'll see what I can do to help out.

malban Valued Member Points: 65 More actions · Answer 5

Thanks SSC Journeyman, I was working for hours in a similar code and receiving this error:

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

The problem was on the declaration: varchar(400) instead of nvarchar(400)

1.- Bad code:

DECLARE @file_name nvarchar(1000) = 'D:\temp\myfile.doc'

DECLARE @SQL varbinary(max)

DECLARE @STR varchar(400)

SET @STR = N'SELECT @SQL = CAST( bulkcolumn AS VARBINARY(max) ) FROM '

SET @STR += N' OPENROWSET( BULK '''

SET @STR += @file_name

SET @STR += N''', SINGLE_BLOB ) AS x'

EXEC sp_executeSQL @STR, N'@SQL varbinary(max) OUT', @SQL OUTPUT

2.- Good code