Reputation: 595
I would like to store large images in SQL Server and later use them in Reporting Services, Power BI and Analysis Services.
I found some help, but I still don't understand what is the best way to store them and how to convert these images in the correct format.
Should I convert them to Base64? How do I do that?
I found good explanations on Convert Image DataType To String in SQL Server, Storing images in SQL Server?, but none of them worked with me.
So I have on my database, the path of the image, the image it self and the extension as below:
SELECT
NM_DIRETORIO AS NM_PATH ,
NM_FOTO AS NM_PICTURE,
TP_EXTENSAO AS TP_EXTENSION
FROM D_GB_FOTOS
As I saw it on from this video SSRS - Read images from the SQL Server database, he uses varbinary(max)
to store images, but I don't how he converted to that. Also, from Chriss Webb's: Storing Large Images In Power BI Datasets, he uses Base64 to show on Power BI.
So my question is, since I'll use large images, how to I convert a simple image (path + picture) to store in my SQL Server database?
Information:
- SQL Server 2019 (v15.0.18330.0)
- SQL Server Management Objects (SMO) v16.100.37971.0
- Microsoft SQL Server Management Studio v18.5
EDIT:
Based on @Peter Schneider answer, I have created a cursor for updating the table, with that value. But I got error on the where
clause (e.g: TABLE.ID_COLUMN).
My cursor:
DECLARE @ID_FOTO INT;
DECLARE @CD_ARQUIVO VARCHAR(4000);
DECLARE @CD_ARQUIVO_VARBINARY VARCHAR(4000);
DECLARE @tsql NVARCHAR (4000);
DECLARE CUR CURSOR FOR SELECT ID_FOTO, CD_ARQUIVO, NM_DIRETORIO + '\' + NM_FOTO + TP_EXTENSAO AS CD_ARQUIVO_VARBINARY FROM D_GB_FOTOS WHERE LINORIGEM <> 'CARGA MANUAL'
OPEN CUR
FETCH NEXT FROM CUR INTO @ID_FOTO, @CD_ARQUIVO, @CD_ARQUIVO_VARBINARY
WHILE @CD_ARQUIVO IS NULL BEGIN
SET @tsql = 'UPDATE D_GB_FOTOS' +
'SET CD_ARQUIVO = (SELECT CD_ARQUIVO.* from Openrowset(Bulk' + @CD_ARQUIVO + ', Single_Blob) CD_ARQUIVO)' +
'WHERE ' + @ID_FOTO + ' = D_GB_FOTOS.ID_FOTO;'
PRINT (@tsql)
EXEC (@tsql)
FETCH NEXT FROM cur INTO @ID_FOTO, @CD_ARQUIVO
END
CLOSE cur
DEALLOCATE cur
EDIT 2:
Some adjustments to the query, but there is one final problem where it keeps updating, and doesn't stop with the final ID of the table:
DECLARE @ID_FOTO INT;
DECLARE @CD_ARQUIVO VARCHAR(4000);
DECLARE @CD_ARQUIVO_VARBINARY VARCHAR(4000);
DECLARE @tsql NVARCHAR (4000);
DECLARE @ID_FOTO_MAX INT;
SET @ID_FOTO_MAX = (SELECT MAX(ID_FOTO) AS ID_FOTO FROM D_GB_FOTOS);
DECLARE CUR CURSOR FOR SELECT ID_FOTO, CD_ARQUIVO, (NM_DIRETORIO + '\' + NM_FOTO + TP_EXTENSAO) AS CD_ARQUIVO_VARBINARY FROM D_GB_FOTOS WHERE LINORIGEM <> 'CARGA MANUAL';
OPEN CUR
FETCH NEXT FROM CUR INTO @ID_FOTO, @CD_ARQUIVO, @CD_ARQUIVO_VARBINARY
WHILE (@ID_FOTO <= @ID_FOTO_MAX) BEGIN
SET @tsql = 'UPDATE D_GB_FOTOS ' +
'SET CD_ARQUIVO = (SELECT CD_ARQUIVO from Openrowset(Bulk ''' + @CD_ARQUIVO_VARBINARY + ''', Single_Blob) CD_ARQUIVO)' +
' WHERE D_GB_FOTOS.ID_FOTO = ' + CONVERT(VARCHAR(10),@ID_FOTO) + ';'
PRINT ('ID_FOTO: ' + CONVERT(VARCHAR(10),@ID_FOTO))
PRINT ('ID_FOTO_MAX: ' + CONVERT(VARCHAR(10),@ID_FOTO_MAX))
PRINT ('SELECT STATEMENT: ' + @tsql)
EXEC (@tsql)
FETCH NEXT FROM cur INTO @ID_FOTO, @CD_ARQUIVO, @CD_ARQUIVO_VARBINARY
END
CLOSE cur
DEALLOCATE cur
Example from where I printed the code above:
(1 linha afetada) ID_FOTO: 6529 ID_FOTO_MAX: 6531
(1 linha afetada) ID_FOTO: 6530 ID_FOTO_MAX: 6531
(1 linha afetada) ID_FOTO: 6531 ID_FOTO_MAX: 6531
(1 linha afetada) ID_FOTO: 6531 ID_FOTO_MAX: 6531
(1 linha afetada) ID_FOTO: 6531 ID_FOTO_MAX: 6531
Upvotes: 0
Views: 3914
Reputation: 2939
You can use OpenRowSet to read the image from disk and insert it into your table
INSERT INTO YourTableName (ID, VarbinaryMaxColumn) VALUES (1,
(SELECT * FROM OPENROWSET(BULK N'C:\Temp\Testimage.png', SINGLE_BLOB) AS VarbinaryMaxColumn)
)
Upvotes: 1