Add image to SQL server 2019

Question

0.00/5 (No votes)

See more:

I would like to insert an image to SQL Server table contains other columns and imagepath columns ( varbinary(MAX) ) using stored procedure , how can I do that?

Thanks

What I have tried:

Create procedure insert_test
AS
BEGIN
INSERT test(
   Id,
logo

)
     VALUES (
         @Id,
(SELECT  BulkColumn
    FROM Openrowset(Bulk '''+ @imagepath +''', Single_Blob) as img),
END

Posted 4-Nov-20 21:29pm

abdou_31

Updated 4-Nov-20 21:51pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Answer 1 · 2020-11-04T21:45:00

Solution 1

See: Simple Image Import and Export Using T-SQL for SQL Server[^]

Posted 4-Nov-20 21:45pm

RickZeeland

Comments

abdou_31 5-Nov-20 4:18am

I see this , but I need to use stored procedure on table contains other columns not a table for the image

OriginalGriff · Answer 2 · 2020-11-04T21:52:00

That's a bad idea.
If we ignore that you don't define either of the stored procedure parameters you are using (and that means it won't work at all anyway), the problem is that that code relies on your SQL Server instance having both the actual image files stored on it, and that the SQL Server process has access to the folders containing them.
In production, neither of those is likely to be true: the SQL Server instance is likely to be a separate machine (or farm of machines) to which users have no direct access for security reasons, and for similar reasons, the server process will be restricted to only those folders it needs to do it's job, such as the DB storage directory.

A much better idea is to pass the image data as a parameter instead of the path from your presentation software when you call the stored procedure. That's easy to do in most languages, this shows how to do it in C#: Why do I get a "Parameter is not valid." exception when I read an image from my database?[^]