Solved

Sql Server 2005 Image Field & Update Text

Posted on 2011-09-22
6
441 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
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

867 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

20 Experts available now in Live!

Get 1:1 Help Now