Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sql Server 2005 Image Field & Update Text

Posted on 2011-09-22
6
Medium Priority
?
463 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

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.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

609 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