?
Solved

Sql Server 2005 Image Field & Update Text

Posted on 2011-09-22
6
Medium Priority
?
460 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

770 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