January 13, 2012 at 9:35 am
--
INSERT INTO @TEMPTABLE (TAG_NAME,tag_data)
select TAG_NAME = 'CUST_Photo', TAG_data =
(sELECT BLOB.BLOBASIMAGE
FROM Client
LEFT OUTER JOIN BLOB ON client.PHOTO_BLOB_MONIKER=BLOB.OID
WHERE client.oid= @CLIENT_OID);
Hi Im trying to move an image to a temptable value
the SP uses this temptable for other data...
I think I have to declare a "Tag_image" as image?
if thats correct how do i get it back to tag_data..
Yes Im totally lost!!
January 13, 2012 at 9:49 am
You mention declaring Tag_image as image. Not sure what you mean. Do you have a variable or column named Tag_image, and you want to use the Image datatype for it? (I don't see Tag_image in the script you provided, but I may just be missing it.)
If so, is there a specific reason you're using the old Image datatype, instead of the newer Varbinary(max) datatype?
As for tying data back to other data, usually you use some sort of key value for that. With a table of binary data (image or varbinary, either one), you usually have a column with an identity value (column named something like ID or ImageID), or a GUID (column with datatype UniqueIdentifier, naming varies). You would usually use that to tie values together.
It might help if you start with table definitions for your source data here. Is there a table with image data in it? A table with tag data in it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2012 at 9:50 am
Images can be stored as varbinary(max) data type.
e.g.
DECLARE @myTempVar varbinary(max)
SET @myTempVar =
SELECT * FROM OPENROWSET(BULK 'X:\path\image.ext', SINGLE_BLOB)
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
January 13, 2012 at 9:58 am
I should explain better sorry..
OK blobimage is an image field on a table
the SP is used to put data on HTML forms.
Here is a better snippet...
first this gets denist
INSERT INTO @TEMPTABLE (TAG_NAME,TAG_DATA)
select TAG_NAME = 'CUST_DENTIST', TAG_DATA = (SELECT top 1 FD.FNAME + CASE WHEN FD.MI IS NULL THEN '' ELSE ' ' + FD.MI END + ' ' + FD.LNAME FROM FamilyDemographic FD (NOLOCK)
INNER JOIN CLIENT_TO_FAMILYDEMO_COLLECTION CFD (NOLOCK) ON CFD.OID_LINK = FD.OID
INNER JOIN RELATIONSHIP R (NOLOCK) ON R.OID = FD.RELATIONSHIP
WHERE R.Code= 056
And CFD.OID=@CLIENT_OID
AND FD.EXPDATE IS NULL);--
etc... other fields ....
now I want to get Cust_photo...
INSERT INTO @TEMPTABLE (TAG_NAME,tag_data)
select TAG_NAME = 'CUST_Photo', TAG_data =
(sELECT BLOB.BLOBASIMAGE
FROM Client
LEFT OUTER JOIN BLOB ON client.PHOTO_BLOB_MONIKER=BLOB.OID
WHERE client.oid= @CLIENT_OID);
But get Operand type clash: image is incompatible with varchar
January 13, 2012 at 10:48 am
There doesn't seem to be much wrong with your second snippet, though I'm not sure why you're using an outer join - you don't want nulls included surely?
The first snippet is confusing and seems to be the source of blame. It's populating your blobasimage column with varchar. Please could you post the table definitions and also format the code so it's more readable - and use code tags?
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
January 13, 2012 at 10:48 am
There doesn't seem to be much wrong with your second snippet, though I'm not sure why you're using an outer join - you don't want nulls included surely?
The first snippet is confusing and seems to be the source of blame. It's populating your blobasimage column with varchar. Please could you post the table definitions and also format the code so it's more readable - and use code tags?
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
January 13, 2012 at 10:52 am
-- Declare @TEMPTABLE to hold values to be returned to HTML Form
DECLARE @TEMPTABLE TABLE (
[ID] INT IDENTITY(1,1),
TAG_NAME varchar(255),tag_image image,
TAG_DATA varchar(8000))
I added the tag_image
January 13, 2012 at 11:05 am
Hi,
Ok so I did this:
DECLARE @TEMPTABLE TABLE (
[ID] INT IDENTITY(1,1),
TAG_NAME varchar(255),
TAG_DATA image)
INSERT INTO @TEMPTABLE (TAG_NAME,tag_data)
select TAG_NAME = 'CUST_Photo', TAG_data =
(sELECT BLOB.BLOBASIMAGE
FROM Client
LEFT OUTER JOIN BLOB ON client.PHOTO_BLOB_MONIKER=BLOB.OID
WHERE client.id= 37);
SELECT TAG_NAME, TAG_DATA FROM @TEMPTABLE ORDER BY [ID]
and get results but
this SP uses TAG_DATA as varchar(8000) for other elements as CUST_DENTIST
Should I create a different temptable or there a way swap Tag_data as a varchar to an image?
January 13, 2012 at 11:25 am
You won't be able to do text data (varchar) in the same column as binary data (image/varbinary).
It'll either need to be two separate datasets (some front end applications can deal with that easily, some can't, talk to your devs), or in two different columns.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2012 at 11:32 am
Your problem is right there, declaring tag image as varchar(8000). It needs to be type varbinary(max) or image, as you say. As far as I am aware there is no conversion function for varbinary to varchar. You would need to use a separate table or column. Why not do
alter table myTable
Add dupColumn varbinary(max)
update myTable
Set dupColumn = tag_image
Where not tag-image is null
Then change your query to use dupColumn?
The code above is rubbish I know, you might need to amend it but basically you are creating a duplicate col of a varchar into varbinary which should be ok as varbinary is flexible. Apologies for rushed reply and bad post, on my phone on way home. I will not be able to reply now until Monday so have a good weekend and hopefully another reader can pick up where I left off and polish it up for you.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
January 13, 2012 at 11:32 am
Your problem is right there, declaring tag image as varchar(8000). It needs to be type varbinary(max) or image, as you say. As far as I am aware there is no conversion function for varbinary to varchar. You would need to use a separate table or column. Why not do
alter table myTable
Add dupColumn varbinary(max)
update myTable
Set dupColumn = tag_image
Where not tag-image is null
Then change your query to use dupColumn?
The code above is rubbish I know, you might need to amend it but basically you are creating a duplicate col of a varchar into varbinary which should be ok as varbinary is flexible. Apologies for rushed reply and bad post, on my phone on way home. I will not be able to reply now until Monday so have a good weekend and hopefully another reader can pick up where I left off and polish it up for you.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
January 13, 2012 at 11:50 am
thanks Derek,
Im going to experiment
in the meatime
January 17, 2012 at 8:27 am
Hi Derek..
Still lost on this..
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply