Extracting binary data from an image field

Question

Post reply

Extracting binary data from an image field

v_krishnan

Say Hey Kid

Points: 676
More actions
January 19, 2005 at 8:25 am

#165749

I have an image field which contains some information stored in a custom-defined format in binary form. I am trying to prepare a TSQL procedure which will intepret this field, and return the meaningful information stored in it.
How do I read the required bytes from the field? I checked up the READTEXT function, but could not find how to store the read data into a variable?
Krishnan
January 24, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

See if this helps:

http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1161.mspx

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

v_krishnan Say Hey Kid Points: 676 More actions · Answer 2

It does not tell me how to store the value returned from READTEXT into a variable. Could you please help there?

David Burrows SSC Guru Points: 65142 More actions · Answer 3

Use SUBSTRING to get data, eg

DECLARE @wanted varchar (1000)

SELECT @wanted = SUBSTRING(pr_info,1,1000)

FROM pubs.dbo.pub_info

WHERE pub_id = 9901

SELECT @wanted

Of course you are limited to 8000 (varchar), 4000 (nvarchar)

Far away is close at hand in the images of elsewhere.
Anon.

v_krishnan Say Hey Kid Points: 676 More actions · Answer 4

Although SUBSTRING would do the job, I am afraid till will slow down the performance a lot. My text data is > 8000 bytes, hence, if use SUBSTRING to get my require piece of information (which varies as ulitmately I would be reading almost the whole of the text data) from the table I will to query the database for that every time.

As an alternative I was trying out a solution, which I guess I can get going with a little bit of help. Since, my table name and the column name from which I have to get the data would not be changing at all, I have created a stored procedure which does the following -

CREATE PROCEDURE

dbo.GetDataChunk

(

@ptr

varbinary(16),

@start

int,

@len

int

)

AS

READTEXT MYTABLE.REMARKS @ptr @start @len

RETURN

Now If execute this proc with the correct parameters it returns me a returns me a result set, with a single column named REMARKS and required substring of the text column.

To satisfy my requirement, all I need is to get this result set stored via a cursor/local variable in the calling stored procedure. I am not able to do this. Is it not possible to store the result from a proc in the calling proc?

Thanks,

Krishnan

Sergio Campos SSC-Addicted Points: 459 More actions · Answer 5

Sergio Campos

SSC-Addicted

Points: 459

March 13, 2006 at 1:56 am

#626167

use sp_textcopy is very usefull and fast

Darwin Hatheway SSC Veteran Points: 242 More actions · Answer 6

I don't know what your limitations and requirements are but we usually store binary and image data in files and then just store the file name in the database.

The front-end application is then responsible for locating and processing the file.

David Burrows SSC Guru Points: 65142 More actions · Answer 7

To satisfy my requirement, all I need is to get this result set stored via a cursor/local variable in the calling stored procedure. I am not able to do this. Is it not possible to store the result from a proc in the calling proc?

Providing the text being selected is not greater than 8000 bytes

DECLARE @ptr binary(16), @REMARKS varchar(8000)

SELECT @ptr = TEXTPTR(REMARKS) FROM MYTABLE

CREATE TABLE #temp (REMARKS varchar(8000))

INSERT INTO #temp (REMARKS)

EXEC dbo.GetDataChunk @ptr, 7990, 20

SELECT @REMARKS = REMARKS FROM #temp

DROP TABLE #temp

Far away is close at hand in the images of elsewhere.
Anon.