?
Solved

Sql Server 2005 Image Field & Update Text

Posted on 2011-09-22
6
Medium Priority
?
468 Views
Last Modified: 2012-05-12
I have a field in a Sql Server 2005 database as type Picture. This field is actually storing a path to a pdf file on the server. I want to update the drive letter in the path that is stored in this image type field. Is this possible ? Is their a sql statement that can be used to update a portion of this field ?
Fieldname may say P:\Share\filename.pdf

Want to update the path to a different driveletter, but this field is of type image.
0
Comment
Question by:rjordanbots
  • 3
  • 2
6 Comments
 
LVL 4

Accepted Solution

by:
rbride earned 2000 total points
ID: 36585746
I am assuming you mean IMAGE - there is no Picture type in MSSQL.

You don't want to be storing a file name in an image field, it uses up a huge amount of storage.

The image field is for storing BLOBs. Change your table to just store a string or check out varbinary(MAX) which can store BLOBs but also small data efficiently.

Also check out the FILESTREAM functionality in 2008 if that may be useful for you in the future [http://msdn.microsoft.com/en-us/library/cc949109(v=sql.100).aspx]

Having said that, if you really want to do what you state, you first need to convert the IMAGE to varbinary, then to nvarchar and then to varchar to get to the string that is stored.

Convert the BLOB field to a string first:
declare @path varchar(1024)
select @path = cast(cast(cast(Picture as varbinary(max)) as nvarchar(max)) as varchar(1024))
from myTable

Open in new window


Then do your update (assuming 'P' is your new drive letter):
select @path = 'P' + substring(@path, charindex(':', @path), datalength(@Path))

Open in new window

0
 

Author Comment

by:rjordanbots
ID: 36588153
This information is helpful. This is a database that one of our vendors designed/created, I agree image type should not be used to simply store a filepath. We have a little over 1600 records in this table. I used the select statement above and the statement returned the value I needed in straight ascii. The value that was returned is P

I would like to write an update statement to change the value of P to another letter. We need the users to be able to use a different drive letter that is free to more people so that more users can access these files through the application. You are correct the field type is image.

I know that a update statement starts something like the following

Update Tablename set fieldname='somevalue'

The table name is invoice_blob and the fieldname is path

Want to change the value of of fieldname ivoice_blob from "P" to "R"

the invoice_blob field is of image type though, didn't know if a conversion needed to be included
in the update statement.
0
 

Author Comment

by:rjordanbots
ID: 36588848
Here's more specific information, I finally got the query needed to pull the results, below is the query

Select
CONVERT(nvarchar(max),(convert(varbinary(128),file)))

 from invoice_image where image_id=1598

Below is the results

P:\2010\D-1 Recovery\124559.pdf

The file field is of image type. I would like to conver the drive letter in this field from P to R,
is their an update script that can be used to do this ?

0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 4

Expert Comment

by:rbride
ID: 36593090
Check out the second of the two statements that I posted above.
combine that with the update:

update invoice_image
select ivoice_blob = convert(varbinary(max), 'R' + substring(CONVERT(nvarchar(max),(convert(varbinary(128),file))), charindex(':', CONVERT(nvarchar(max),(convert(varbinary(128),file)))), datalength(CONVERT(nvarchar(max),(convert(varbinary(128),file))))))
where  image_id=1598

But it's much easier to understand if you first pull out the path to a variable like I do, change it and then set the value. The rest is LFE.
0
 

Author Comment

by:rjordanbots
ID: 36593589
Thank you for the reply, I will try this script on Monday and will send a follow up to see how this works.
Thank you.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36595405
Here are another couple of ways you can do it:
UPDATE  invoice_image
SET     [file] = CAST('R' AS varbinary(1)) + SUBSTRING([file], 2, 100)      -- Change 100 to the appropriate length
WHERE   image_id = 1598

UPDATE  invoice_image
SET     [file] = STUFF(CAST([file] AS varbinary(100)), 1, 1, 'R')      -- Change 100 to the appropriate length
WHERE   image_id = 1598

You can first test out both methods as follows:
SELECT  [file],
        CAST('R' AS varbinary(1)) + SUBSTRING([file], 2, 100) Solution1,
        STUFF(CAST([file] AS varbinary(100)), 1, 1, 'R') Solution2
FROM    invoice_image
WHERE   image_id = 1598
0

Featured Post

 [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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question