Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

SQL Server - how to update a IMAGE from one TABLE to another

Hi..
I have a IMAGE field that contains photos - I'm trying to update the image in another table with the following SQL

update repi set  photo =  dt.photo
from [crm].dbo.repinfo as dt
where repinfo.repno = '54K'

The image never fully updates and the IMAGE field shows just '0x'

What I'm I missing ? The source field has  0xFFD8FFE000104A46494600010201006000600000FFE1118A45786966000049492A000800000007001201030001000000010000001A01050001000000620000001B010500010000006A000000280103000100000002000000310102001D0000007200000032010200140000008F0000006987040001000000A4000000D00000...

0
JElster
Asked:
JElster
  • 9
  • 7
  • 5
1 Solution
 
RiteshShahCommented:
have a look. this is working fine.
USE [AdventureWorks]
GO
--Create Table
CREATE TABLE [dbo].[ImageTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Image] [varbinary](MAX) NULL
) ON [PRIMARY]
GO
--Insert Image
INSERT INTO [dbo].[ImageTest]([Image])
SELECT * FROM
OPENROWSET(BULK N'C:\gui.jpg', SINGLE_BLOB) AS Document
GO
 
select * from imagetest
 
CREATE TABLE [dbo].[ImageTest1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Image] [varbinary](MAX) NULL
) ON [PRIMARY]
 
insert into imagetest1 values(default)
 
update imagetest1 set [image]=img.[image]
from imagetest img WHERE img.id=1
 
 
select * from imagetest1

Open in new window

0
 
RiteshShahCommented:
or you could do something like this also.

update imagetest1 set [image]=img.[image]
from imagetest img join imagetest1 img1 on img.id=img1.id

0
 
JElsterAuthor Commented:
I tried that .. but still get just '0x'

???
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
RiteshShahCommented:
have you tried my code?

0
 
JElsterAuthor Commented:
I'm running Sql 2000 - i get errors on your code  'Max'
0
 
RiteshShahCommented:
ok than do one thing, change varbinary(max) to your BLOB field which you are using in your application and change image path in my code, try to run it.
0
 
JElsterAuthor Commented:
I don't understand...

CREATE TABLE [dbo].[ImageTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Image] [varbinary](MAX) NULL
) ON [PRIMARY]

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'MAX'.
0
 
RiteshShahCommented:
well, forget about that, Show me your table structure, can you give me your "Create Table" script. I will check with your table as I am using SQL Server 2005, varbinary(MAX) is not available in SQL Server 2000,
0
 
Anthony PerkinsCommented:
The author has already told us they are using the deprecated image data type.
0
 
JElsterAuthor Commented:
I changed it to..  to create the table

CREATE TABLE [dbo].[ImageTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Image] [varbinary] NULL
) ON [PRIMARY]

BUT i get an error on

INSERT INTO [dbo].[ImageTest]([Image])
SELECT * FROM
OPENROWSET(BULK N 't:\xfr\1.jpg', SINGLE_BLOB) AS Document
GO

incorrect syntax near BULK
0
 
Anthony PerkinsCommented:
Here is your mistake:

Change:
update repi set  photo =  dt.photo
from [crm].dbo.repinfo as dt
where repinfo.repno = '54K'

To:
update repi set  photo =  dt.photo
from [crm].dbo.repinfo as dt
where dt.repno = '54K'
0
 
Anthony PerkinsCommented:
This is how I tested it:

If I do it this way:
 
CREATE TABLE repinfo (repno varchar(10), photo image)
CREATE TABLE repi (repno varchar(10), photo image)
 
SET NOCOUNT ON
 
Insert repinfo (repno, photo) Values ('54K', '0xFFD8FFE000104A46494600010201006000600000FFE1118A45786966000049492A000800000007001201030001000000010000001A01050001000000620000001B010500010000006A000000280103000100000002000000310102001D0000007200000032010200140000008F0000006987040001000000A4000000D00000')
 
Select	photo
From	repinfo 
Where	repno ='54K'
 
Insert	repi (repno, photo) Values ('54K', Null)
 
Select	photo
From	Repi
Where	repno ='54K'
 
update	repi 
set 	photo =  dt.photo
from	repinfo as dt
where	repinfo.repno = '54K'
 
Select	photo
From	Repi
Where	repno ='54K'
 
Drop table repinfo
Drop table repi
 
I get the following error message:
Server: Msg 107, Level 16, State 3, Line 18
The column prefix 'repinfo' does not match with a table name or alias name used in the query.
 
If I change it to:
 
CREATE TABLE repinfo (repno varchar(10), photo image)
CREATE TABLE repi (repno varchar(10), photo image)
 
SET NOCOUNT ON
 
Insert repinfo (repno, photo) Values ('54K', '0xFFD8FFE000104A46494600010201006000600000FFE1118A45786966000049492A000800000007001201030001000000010000001A01050001000000620000001B010500010000006A000000280103000100000002000000310102001D0000007200000032010200140000008F0000006987040001000000A4000000D00000')
 
Select	photo
From	repinfo 
Where	repno ='54K'
 
Insert	repi (repno, photo) Values ('54K', Null)
 
Select	photo
From	Repi
Where	repno ='54K'
 
update	repi 
set 	photo =  dt.photo
from	repinfo as dt
where	dt.repno = '54K'
 
Select	photo
From	Repi
Where	repno ='54K'
 
Drop table repinfo
Drop table repi
 
I get the expected output:
photo
0x30784646443846464530303031303441343634393436303030313032303130303630303036303030303046464531313138413435373836393636303030303439343932413030303830303030303030373030313230313033303030313030303030303031303030303030314130313035303030313030303030303632303030303030314230313035303030313030303030303641303030303030323830313033303030313030303030303032303030303030333130313032303031443030303030303732303030303030333230313032303031343030303030303846303030303030363938373034303030313030303030304134303030303030443030303030
 
photo
0x
 
photo
0x30784646443846464530303031303441343634393436303030313032303130303630303036303030303046464531313138413435373836393636303030303439343932413030303830303030303030373030313230313033303030313030303030303031303030303030314130313035303030313030303030303632303030303030314230313035303030313030303030303641303030303030323830313033303030313030303030303032303030303030333130313032303031443030303030303732303030303030333230313032303031343030303030303846303030303030363938373034303030313030303030304134303030303030443030303030

Open in new window

0
 
RiteshShahCommented:
that is because it is not available in SQL 2000. but I guess, your code is working, though it has ox only. have you tried to convert that image back in .JPG from any programming language?
0
 
JElsterAuthor Commented:
This..
update repi set  photo =  dt.photo
from [crm].dbo.repinfo as dt
where dt.repno = '54K'

changed all the records to just 'Ox'
0
 
JElsterAuthor Commented:
The images where inserted using C# - that works...
But I just need to update using SQL - now all the images are gone from the target
0
 
Anthony PerkinsCommented:
>>But I just need to update using SQL - now all the images are gone from the target<<
If you take the time to read my example, you will see from my code it is using SQL, nothing else and produces the desired results, so unfortunately without knowing all your details, I have to assume there is a bug in your code.
0
 
JElsterAuthor Commented:
The images appear fine... I know they were inserted correcting using the code.
Just trying to update the fields using SQL
0
 
Anthony PerkinsCommented:
Again take the time to review my code, to help you identify the error in your code.
0
 
RiteshShahCommented:
are you getting the images from the fields which is just showing you "ox"?
0
 
JElsterAuthor Commented:
No...
0
 
JElsterAuthor Commented:
Hi..
this is a CROSS server update... could that be affecting the updating?
I works on the same database, on the same server.
thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 9
  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now