SQL READTEXT statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL READTEXT statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-15 : 08:27:29

Lance writes "Greetings,

I am using a READTEXT statement within a stored procedure to extract a blob of text that has been stored as a binary image. I need to then convert that binary representation back to Ascii. My question is.... is there a way to assign the 'returned' binary from the READTEXT statement to a variable so that I may use the variable in the CONVERT statement? I then need to have the Ascii result of the CONVERT command assigned to a variable for return to the calling routine.

Here is an example of the code I have been trying to create.

DECLARE @bodyID uniqueidentifier
DECLARE @chunkindex int
DECLARE @chunksize int
DECLARE @myval varchar(8000)
DECLARE @val binary(16)
DECLARE @chunkText varchar(8000) OUTPUT

SET @bodyID = '{77E9D68C-9985-4365-8580-FFFC82553562}'
SET @chunkindex = 0
SET @chunksize = 2597

SELECT @val = TEXTPTR(chunkdata)
FROM relaystorebodychunks
WHERE bodyid = @bodyID order by chunkid
READTEXT relaystorebodychunks.chunkdata @val @chunkindex @chunksize

SET @myval = [READTEXT RESULT VARIABLE]
SELECT CONVERT(char(8000), @myval)

RETURN @chunkText

The code above is taken from a stored procedure. [READTEXT RESULT VARIABLE] represents a variable which holds the result of the READTEXT statement. RETURN @chunkText represents an Ascii translation of the stored data to be returned to the calling program.

I have attempted to use statements such as "SET @myvar = READTEXT relaystorebodychunks.chunkdata @val @chunkindex @chunksize" and "SET @chunkText = SELECT CONVERT(char(8000), @myval)" however doing so returns errors on the READTEXT statement and the CONVERT statement respectively.

I would greatly appriciate any assistance that can be provided.

Thanks and take care,

Lance"

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-15 : 12:39:12

Can't help thinking there is an easier way of doing this but you can get the part of the image into a binary variable by

create table #a (t image)
insert #a select 'asdfasdfasdfsadfsda'

declare @ptr binary(16)
create table #ptr (ptr binary(16))

insert #ptr
select TEXTPTR(t)
from #a

declare @sql nvarchar(1000)
select @sql = 'declare @ptr binary(16) select @ptr = ptr from #ptr readtext #a.t @ptr 1 10'

create table #c (b binary(1000))
insert #c
exec sp_executesql @sql

declare @b binary(1000)
select @b = b from #c

select @b

and manipulate it from there.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-15 : 13:18:25

How about using SUBSTRING() function? As to READTEXT seems the only
way to use its result is to insert it into #temp table and then retrieve and
assign its value to a local variable in usual manner.

DECLARE @myval varchar(8000)
------------- DECLARE @val binary(16)
DECLARE @chunkText varchar(8000) OUTPUT

SET @bodyID = '{77E9D68C-9985-4365-8580-FFFC82553562}'
SET @chunkindex = 1
SET @chunksize = 2597

SELECT @myval = SUBSTRING(chunkdata, @chunkindex, @chunksize)
FROM relaystorebodychunks
WHERE bodyid = @bodyID order by chunkid

Lance
Starting Member

2 Posts

Posted - 2003-10-28 : 08:53:51

Hey Gentleman,

Sorry for the delay in responding to the help. I thought the forum would email any reply's to me and after having not received any for a couple weeks I figured no one did.

The example that stoad provided was just what I needed. Worked like a champ. I appreciate it guys.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-28 : 09:19:48

Oho!! Really... exactly 2 weeks...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources