Insert Image as a column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert Image as a column

Author

Topic

elyanivson
Starting Member

6 Posts

Posted - 2011-09-17 : 04:58:08

Hi,
In my project I have "Images" folder in c:\Project\Images.
I have a column that should contain images.
now, I know the statement that insert the image into the database.
The statement ends like this:
"...
(select *
FROM OPENROWSET(BULK 'C:\YourHotel\images\tp_logo.JPG', SINGLE_BLOB) AS img)GO "

My problem will be when I want to move the Database from my computer to other one and the project wont be at c:\, but in F:\.
can anyone help me with the general statement that just point the "local" or something?
Thank you very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-17 : 05:53:45

why not make path a variable and then pass it in dynamic sql?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

elyanivson
Starting Member

6 Posts

Posted - 2011-09-17 : 06:20:38

I think it's a great idea, but I wish i knew how to do that.
can you please help me with explanation for this?
thank you in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-17 : 06:32:41

make it like


DECLARE @Sql varchar(8000),@Path varchar(1000)
SET @Path ='C:\YourHotel\images\tp_logo.JPG'--give any path here
SET @Sql='...(select * 
FROM OPENROWSET(BULK '''+@Path + ''', SINGLE_BLOB) AS img)'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

elyanivson
Starting Member

6 Posts

Posted - 2011-09-17 : 06:53:10

Thank you so much!
I'll try it and let you know if i succeed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-17 : 07:07:30

good luck

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

elyanivson
Starting Member

6 Posts

Posted - 2011-09-17 : 07:52:00

Hi,
I tried to execute this statement according to what you recommended:

DECLARE @Sql varchar(8000),@Path varchar(1000)
SET @Path ='C:\YourHotel\images\tp_logo.JPG'--give any path here
SET @Sql='Pallazo,bla bla,216546,65,65465,7,15,sdsd,65465,palazzoH,111,(select *
FROM OPENROWSET(BULK '''+@Path + ''', SINGLE_BLOB) AS img)'

The problem is that I didn't insert anywhere the table the values should be inert in.

Where should it be?
thanks for your patience.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-17 : 10:36:32

Hello,

The statements provided only build the SQL command and store it in the variable @Sql, but does not call or execute the command. For that, have a look at 'sp_executeSQL' or 'Execute' operation;

http://msdn.microsoft.com/en-us/library/ms188001.aspx
http://msdn.microsoft.com/en-us/library/ms188332.aspx

HTH.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-17 : 12:29:14

quote:
Originally posted by elyanivson

Hi,
I tried to execute this statement according to what you recommended:

DECLARE @Sql varchar(8000),@Path varchar(1000)
SET @Path ='C:\YourHotel\images\tp_logo.JPG'--give any path here
SET @Sql='Pallazo,bla bla,216546,65,65465,7,15,sdsd,65465,palazzoH,111,(select *
FROM OPENROWSET(BULK '''+@Path + ''', SINGLE_BLOB) AS img)'

The problem is that I didn't insert anywhere the table the values should be inert in.

Where should it be?
thanks for your patience.

for that do
EXEC(@sql)

you have just formed string not executed it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-09-18 : 03:29:56

In case you have not considered it: in most circumstances it is be better to design the database to hold the path / filename to the image, rather than the actual image itself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources