I want to insert images into an Image field, preferably using a stored procedure which will accept a path to an image. After hacking around I came up with this;
-- functional DECLARE @parameters nvarchar(max) = ''; DECLARE @sql_string nvarchar(max) = N'UPDATE MyTable SET MyImageField = (SELECT BulkColumn FROM Openrowset(Bulk ''' + @PathToMyImage + ''', Single_Blob) ImageData) WHERE MyPrimaryKey = ' + CAST(@PrimaryKey AS NVARCHAR(max)); EXECUTE sp_executesql @sql_string, @parameters
I did this because when I tried;
--Not functional INSERT INTO MyTable (MyImageField) VALUES ((SELECT BulkColumn FROM Openrowset(Bulk @PathToMyImage, Single_Blob) ImageData));
SQL Server throws an error, complaining that Bulk expects a string. I'd prefer to not have to resort to sp_executesql for maintainability / readability, is there a better way to go about this?
You do have to use dynamic sql but you can use quotename() to avoid embedded quote nightmares and you should pass the primary key in as an actual parameter.
DECLARE @sql_string nvarchar(max) = N'UPDATE MyTable SET MyImageField = (SELECT BulkColumn FROM Openrowset(Bulk ' + quotename(@PathToMyImage,nchar(39)) + ', Single_Blob) ImageData) WHERE MyPrimaryKey = @PrimaryKey'; EXECUTE sp_executesql @sql_string, N'@PrimaryKey int', @PrimaryKey
nchar(39) is a single quote. All single quotes within the path name will be properly escaped and the path will be enclosed in single quotes for safe concatenation.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!Donate Us With