Solved

Sql Server 2005 Image Field & Update Text

Posted on 2011-09-22
6
437 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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 4

Expert Comment

by:rbride
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now