How to convert and store image on SQL Server

Question

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

You cannot concatenate an int with a string... You have to convert it to a string... so .. ```WHERE ' + CONVERT(VARCHAR(10),@ID_FOTO) + '``` should be valid... — Peter Schneider, Jun 23 '20 at 18:22
@PeterSchneider It worked converting to varchar, I made 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, where I defined on EDIT 2: `WHILE (@ID_FOTO <= @ID_FOTO_MAX)` — Guilherme Matheus, Jul 04 '20 at 18:13
Your code looks right. Did you check the result of your initial DECLARE cursor statement. In your case there might be several entries with the id 6531 and the loop will execute it several times. You can use a SELECT DISTINCT ... to get unique entries... — Peter Schneider, Jul 04 '20 at 18:30
@PeterSchneider Yes, all rows were updated from my initial DECLARE cursor statement. I don't see where there is several entries for the last ID. I also tried as you said, I put `SELECT DISTINCT`, but it was the same. I don't know what to do now.. — Guilherme Matheus, Jul 05 '20 at 15:34
@PeterSchneider Since your answer was correct due to my question, I'll mark as correct and ask another one, to find why is in executing in inifite. Really thank you for your time and patient. — Guilherme Matheus, Jul 06 '20 at 23:25

score 1 · Accepted Answer · answered Jun 17 '20 at 14:24

1

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)
)

answered Jun 17 '20 at 14:24

Peter Schneider

2,879
1
14
17

Scheneider, I already have a Bulk Insert that load all the information from those pictures from a CSV file. Should I use your solution as well to load as varbinary on the same table? My script: `BULK INSERT GB_TBIMP_FOTOS_CSV FROM 'C:\Users\userbi\Desktop\Projetos-Santo-Grau\Projeto1-RelatoriodeEstoque\TBIMP_FOTOS_CSV.csv' WITH ( DATAFILETYPE = 'widechar', --FORMAT = 'CSV', FIRSTROW = 2, FORMATFILE = 'C:\Users\userbi\Desktop\Projetos-Santo-Grau\Projeto1-RelatoriodeEstoque\TBIMP_FOTOS_CSV.fmt' );` – Guilherme Matheus Jun 17 '20 at 16:07
1

No.. there are two steps. First you can bulk insert your csv data in your table. Then afterwards query the table and use my statement to insert the actual binary data. You have to take care of the filepath, because SQL Server might not have access to that path. You probably have to lookup how to iterate through rows with a cursor and how to execute a dynamic sql query with sp_executesql. Or you choose a complete different way... e.g. SSIS has a specific task (import column task) to do exactly what you want.. read from a table a path and insert it into a table. – Peter Schneider Jun 17 '20 at 18:27
@PeterShneider thanks for the reply! You gave me a good path to follow .I'm trying to use Cursor to update the rows with the value of each directory + image, but it is giving error in the query. If I update the question like what I already managed to do, can you help me to finish this question please? However your query works yes, I tested it with a single image and it was! I also tried something with SSIS, but as I don't know the tool very well, I ended up leaving that option aside, and I preferred to use SSMS. – Guilherme Matheus Jun 20 '20 at 19:55
I edited my question based on your answer, could you help me please? – Guilherme Matheus Jun 23 '20 at 17:28

How to convert and store image on SQL Server

1 Answers1