May 14, 2014 at 7:03 am
I have a directory with images and a table in my DB with the path of each file. The main application allow me to create reports where I can display an image, so I was thinking to use a query like:
SELECT [ID]
,[PT_CODE]
,[FILE_PATH],
CASE WHEN [FILE_PATH] IS NOT NULL THEN
(SELECT * FROM OPENROWSET(BULK [FILE_PATH], SINGLE_BLOB) TT)
END
AS IMAGE_LOADED
FROM [DB].[dbo].[TABLE_MR_FILES]
But I keep getting the error:
Incorrect syntax near 'FILE_PATH'.
I have try multiple combinations without luck to make the OPENROWSET read the path stored in the column [FILE_PATH]. What am I missing?
Note: I am using MSSQL 2012. I don't want to import the images into the DB just load them in the fly as needed by the report runned from the application. I have full access to the DB so if a store procedure is the solution I can go with it.
May 14, 2014 at 7:43 am
OPENROWSET expects string literals, you can do this in dynamic sql.
😎
DECLARE @JPGPATH NVARCHAR(MAX) = N'''C:\temp\IMG00001.jpg''';
DECLARE @JPGBIN VARBINARY(MAX) = 0x00;
DECLARE @SQL_STR NVARCHAR(MAX) = REPLACE(N'SELECT * FROM OPENROWSET(BULK {{@JPGPATH}}, SINGLE_BLOB) AS JPG;','{{@JPGPATH}}',@JPGPATH);
EXEC (@SQL_STR);
May 14, 2014 at 8:45 am
Hi,
Thank you for your answer. 🙂
My main question is how to get the path of the image file from the table where it is stored and load the image on the fly using openrowset.
May 14, 2014 at 9:30 am
I don't know that you can do this. Perhaps an APPLY can run multiple OpenRowset commands in a single query, but I don't think your use case is one I've seen tried.
May 14, 2014 at 10:23 am
emilacosta (5/14/2014)
Hi,Thank you for your answer. 🙂
My main question is how to get the path of the image file from the table where it is stored and load the image on the fly using openrowset.
Where do you want them delivered:-D
Here is a little hack that does the job, not the cleanest but you should be able to adopt it to your needs
😎
USE tempdb;
GO
IF EXISTS (
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N'TBL_IMAGEPATH'
AND TABLE_SCHEMA = N'dbo'
)
DROP TABLE dbo.TBL_IMAGEPATH;
CREATE TABLE dbo.TBL_IMAGEPATH
(
IMAGEPATH_ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL
,IMAGEPATH_FILE NVARCHAR(MAX) NULL
);
DECLARE @IMAGES TABLE
(
IMAGEPATH_ID INT NOT NULL
,IMAGE_BIN VARBINARY(MAX) NOT NULL
);
INSERT INTO dbo.TBL_IMAGEPATH ( IMAGEPATH_FILE )
VALUES
(N'C:\Images\pic_0001.jpg')
,(NULL)
,(N'C:\Images\pic_0002.jpg');
DECLARE @SQL_STR NVARCHAR(MAX) = N'';
SELECT @SQL_STR = STUFF(
(SELECT
N'
UNION ALL
SELECT ' + CAST(IP.IMAGEPATH_ID AS NVARCHAR(12))
+ N' AS IMAGEPATH_ID, (SELECT X.BulkColumn FROM OPENROWSET(BULK '
+ NCHAR(39) + IP.IMAGEPATH_FILE + NCHAR(39)
+ N', SINGLE_BLOB) AS X) AS IMAGE_BIN'
FROM dbo.TBL_IMAGEPATH IP
WHERE IP.IMAGEPATH_FILE IS NOT NULL
FOR XML PATH(''), TYPE).value('.[1]','NVARCHAR(MAX)'),1,12,N'')
INSERT INTO @IMAGES (IMAGEPATH_ID,IMAGE_BIN)
EXEC (@SQL_STR)
SELECT * FROM @IMAGES
Note, you may want to limit the number of records.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply