Insert and image to the database table

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

SQL Server 2005 Forums

Transact-SQL (2005)

Insert and image to the database table

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-10-08 : 03:02:10

Hi.
Can you tell me how i can insert a jpg image located in c:\images
on an image column using TSQL?
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-08 : 03:17:27

http://weblogs.sqlteam.com/peterl/archive/2007/09/26/Insert-binary-data-like-images-into-SQL-Server-without-front-end.aspx

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 03:19:12

use image or varbinary(max) column for storing image
http://www.dbazine.com/sql/sql-articles/larsen13

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-10-08 : 03:20:49

Hi.
I have my table with id and image and i try the below

INSERT INTO personimages(Document) 
SELECT * FROM 
OPENROWSET(BULK N'C:\Image3.jpg', SINGLE_BLOB) as tt
where personimages.id = 259

But it gives my this:
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "C:\Image3.jpg" does not exist.

Image3.jpg do exist on c:\ !!!
Help?

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-10-08 : 03:22:39

Error also with imagedata

INSERT INTO personimages(imageData) 
SELECT * FROM 
OPENROWSET(BULK N'C:\Image3.jpg', SINGLE_BLOB) as tt
where personimages.id = 259

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-08 : 03:25:18

c:\image3.jpg is expected on Server not on local client.

Webfred

Planning replaces chance by mistake

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-10-08 : 03:27:46

Hi webfed...
I'm lost :(
If i only have a local client then what should i do????
Thanks.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-10-08 : 03:32:36

Also if i put it on server i get this error

INSERT INTO personimages(imageData) 
SELECT * FROM 
OPENROWSET(BULK N'C:\Image3.jpg', SINGLE_BLOB) as tt
where personimages.Personid = 259

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "personimages.Personid" could not be bound.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'personimages'.

My columns on personimages are:
Personid,Code,Image

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-10-08 : 03:37:53

Ok.My mistake(wrong database :P)

So i do this

INSERT INTO personimages([image]) 
SELECT * FROM 
OPENROWSET(BULK N'C:\Image3.jpg', SINGLE_BLOB) as tt
where personimages.Personid = 259

and i get this error
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "personimages.Personid" could not be bound.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-08 : 03:45:42

What you want to do is not an insert.
It's an update.
See this Example:
CREATE TABLE #myTable(
[id] int identity(1,1),
[Document] varbinary(max))
INSERT INTO #myTable([Document])
SELECT * FROM OPENROWSET(BULK N'C:\windows\greenstone.bmp', SINGLE_BLOB) as tt

UPDATE #myTable
set [Document] =
(select * FROM OPENROWSET(BULK N'C:\windows\zapotec.bmp', SINGLE_BLOB)as tt)
where [id]=1

drop table #myTable

Webfred

Planning replaces chance by mistake

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-10-08 : 04:04:15

Ok.
So i do this:

INSERT INTO personimages([image]) 
SELECT * FROM 
OPENROWSET(BULK N'C:\Image3.jpg', SINGLE_BLOB) as tt
where personimages.Personid = 259

UPDATE personimages
set [Image] =
(select * FROM OPENROWSET(BULK N'C:\Image3.jpg', SINGLE_BLOB)as tt)
where personimages.Personid = 259

and i get this
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "personimages.Personid" could not be bound.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-08 : 04:08:31

quote:
Originally posted by sapator

Ok.
So i do this:
INSERT INTO personimages([image])
SELECT * FROM
OPENROWSET(BULK N'C:\Image3.jpg', SINGLE_BLOB) as tt
where personimages.Personid = 259

UPDATE personimages
set [Image] =
(select * FROM OPENROWSET(BULK N'C:\Image3.jpg', SINGLE_BLOB)as tt)
where personimages.Personid = 259

and i get this
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "personimages.Personid" could not be bound.

Don't use where when inserting a new row in your table.
webfred

Planning replaces chance by mistake

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-10-08 : 04:09:07

Ok sorry the correct is

INSERT INTO personimages([image]) 
SELECT * FROM 
OPENROWSET(BULK N'C:\Image3.jpg', SINGLE_BLOB) as tt


UPDATE personimages
set [Image] =
(select * FROM OPENROWSET(BULK N'C:\Image3.jpg', SINGLE_BLOB)as tt)
where personimages.Personid = 259

and it works.

Now 2 questions

1) i get another row with <binary data>
It's like the image was inserted also to the next row
2)how can i view the images?

thanks.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-10-08 : 04:33:25

Ok i did a .net program so i can view the images.
The only problem is with the row.
It was inserted 2 times.
One on the row of id 259
and one on the next row with (NULL,NULL,<binary data>)
???

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-10-08 : 04:36:36

that's because you did it twice. you inserted it, then you also updated the row with ID 259. webfred already told you, you want an update, NOT an insert

Em

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-10-08 : 04:50:02

Yes you are right.
Thanks.

sabra
Starting Member

25 Posts

Posted - 2010-05-30 : 07:31:09

Need Help!
What's error in it's code?
CREATE TABLE BLOBTest
(
BLOBID int IDENTITY NOT NULL,
BLOBData varbinary(max) NOT NULL
)
GO
INSERT INTO BLOBTest(BLOBData)
SELECT * FROM OPENROWSET(BULK N'C:\ x.jpg', SINGLE_BLOB);

Sachin.Nand

2937 Posts

Posted - 2010-05-30 : 07:44:13

quote:
Originally posted by sabra

Need Help!
What's error in it's code?
CREATE TABLE BLOBTest
(
BLOBID int IDENTITY NOT NULL,
BLOBData varbinary(max) NOT NULL
)
GO
INSERT INTO BLOBTest(BLOBData)
SELECT * FROM OPENROWSET(BULK N'C:\ x.jpg', SINGLE_BLOB);

CREATE TABLE BLOBTest
(
BLOBID int IDENTITY NOT NULL,
BLOBData varbinary(max) NOT NULL
)
GO
INSERT INTO BLOBTest(BLOBData)
SELECT * FROM OPENROWSET(BULK N'C:\ x.jpg', SINGLE_BLOB)as T;

PBUH

sabra
Starting Member

25 Posts

Posted - 2010-06-05 : 17:20:32

Thanks Idera

sabra
Starting Member

25 Posts

Posted - 2010-06-05 : 18:21:26

My friend Idera now I have message on:
------------------------------------------------
CREATE TABLE BLOBTest
(
BLOBID int IDENTITY NOT NULL,
BLOBData varbinary(max) NOT NULL
)
GO
INSERT INTO BLOBTest(BLOBData)
SELECT * FROM OPENROWSET(BULK N'C:\x.jpg', SINGLE_BLOB)as T;
It’s message:
Msg 2714, Level 16, State 6, Line 4
There is already an object named 'BLOBTest' in the database.

(1 row(s) affected)
------------------------------------

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-06 : 03:01:03

There is already an object named 'BLOBTest' in the database.

N 56°04'39.26"
E 12°55'05.63"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources