ASE Database forum - help with readtext()?

Author

help with readtext()?

VMI

2005-10-31, 1:23 pm

How can I use readText() in order to dump all the records of
a Text field into a temp table as varchar? The strings are
greater than 255 chars, and I can't upgrade to a newer
version.

I've tried it several times but it only displays one record.

Thanks.

Carl Kayser

2005-10-31, 1:23 pm

So what release are you on?

<VMI> wrote in message news:43665936.5aae.1681692777@sybase.com...
> How can I use readText() in order to dump all the records of
> a Text field into a temp table as varchar? The strings are
> greater than 255 chars, and I can't upgrade to a newer
> version.
>
> I've tried it several times but it only displays one record.
>
> Thanks.

Bret Halford

2005-10-31, 1:23 pm

VMI wrote:

> How can I use readText() in order to dump all the records of
> a Text field into a temp table as varchar? The strings are
> greater than 255 chars, and I can't upgrade to a newer
> version.
>
> I've tried it several times but it only displays one record.
>
> Thanks.

ASE pretty much only supports storing and retrieving text data. The
expected
way for you to handle this would be to write an Open Client client that
would
read the text blob into a buffer, break it apart, and reinsert the
smaller
rows into ASE varchar strings.

That said, there is a kludgy way of doing it on the server side using
proxy tables.
I wrote it for 11.5; it may require some tweaking to work on 12.x or
15.x.

How to break a text field into multiple 255 character fields.

Note: The following document describes a technique for
breaking down a text field entirely within T-SQL, something
that formerly could only be done by a client. kludgy code and
can likely be improved in many ways. In
many ways, it is a "hack". Please feel free to send any
suggestions for improvement to bret@sybase.com.

General overview:

The new CIS features in ASE 11.5 allow you to bind the results
from an rpc call to a proxy table, as long as the result set
of the rpc is fairly simple. As it happens, such a result set can
consist of a single readtext command. So by having an rpc that does
a readtext for n characters (n <256) at a certain offset,
we can get that substring of the text to show up as a row in
a proxy table, where it can be selected and inserted into
other tables, variables, etc.

The usual method for passing parameters to such an rpc-
based proxy table is to have columns on the proxy table
that correspond to the parameters of the rpc, and these
columns must also be included in the result set output
by the rpc. The values specified in where clauses against
the table are converted to input parameters for the procedure.
The use of readtext, though, limits the rpc's result set to a
single column, so parameters cannot be passed the usual way.

This technique uses a shared table that holds information
about the row from which to retrieve text, the starting
offset, and the number of characters to read [note: readtext
fails when asked to read past the end of the blob, so the
last read has to have the exact number of characters remaining].

The calling process give a primary-key value (in my example au_id),
a starting offset, and the number of characters to pull.
A read from the proxy table now calls the rpc, which reads the values
from the table and returns the corresponding substring from the text.
The calling process can then update the offset and length to
walk through the text. When done, the row should be deleted.

To work properly, the control table may only contain one row.
Because there are two processes involved, the table cannot
be exclusively locked [note: it may be possible to have
the calling process exclusively lock it and have the rpc
read it with an isolation level 0 "dirty" read, but I haven't
tried this and it may be undependable]. Some control mechanism
is needed to be sure that only one user at a time uses this table
and give appropriate feedback to users trying to use it while it
is in use.

Encapsulating everything into a stored procedure that checks that
the control table is empty as a first step is probably a good way
to go. But I haven't done it.

-- THE FOLLOWING STEPS ARE THE GENERAL SETUP SECTION
-- you will need to go through these steps only once to enable your
-- server to break text into char fields.

-- If server has not been named within sysservers, name it.
-- You can use any name here, but it is customary to use the
-- DSQUERY name this server is usally known by.
-- My servername is REL115_BRET
-- [we will have to reboot later to cause change to take effect.]

if (@@servername is null)
begin
exec sp_addserver REL115_BRET, local
end

-- ensure the cis layer is enabled:
-- [this is also a static change that requires a later reboot]

sp_configure "enable cis", 1

-- configure cis remote connections
-- If already configured for a higher number, don't set it lower.
-- "5" here is arbitrary

sp_configure "max cis remote connections", 5

-- add a remote server entry alias "THIS" for the local server
-- The choice of "THIS" is arbitrary, but has a neat parallel
-- in object oriented programming. "REL115_BRET" is the name of my
-- server, substitute the name of your server.

sp_addserver "THIS", "sql_server", "REL115_BRET"
go

-- allow the remote login. Normally you would do this through
-- sp_addremotelogin, but that does not allow you to do so for
-- the local server, so do it by direct insert.

insert sysremotelogins values (0,null,-1,0)
go

-- reboot server if necessary

shutdown

-- after restarting sql server, you should be able to
-- send an rpc to yourself using the "THIS" alias:

exec THIS...sp_who

-- THIS FINISHES THE GENERAL SETUP SECTION

-- ====================
====================
====================
=

-- THE FOLLOWING SECTION IS A SPECIFIC EXAMPLE OF
-- THE SETUP REQUIRED FOR PULLING TEXT FROM ONE TEXT COLUMN
-- OF ONE TABLE
-- In this case, pulling data from the "copy" field of the
-- pubs2..blurbs table

-- if pubs2 is not installed, install it from the OS:

$SYBASE/bin/isql -Usa -P -i $SYBASE/scripts/installpubs2

-- back in REL115_BRET:

use pubs2
go

-- drop objects that may have been created by an earlier run of this
script

drop table blurb_strings
drop table blurbs_control_table

drop procedure blurb_blob_breaker
go
-- create a table to hold the text broken down into char(255) fields

create table blurb_strings( au_id char(11), row int, copy char(255))

-- create a table that is used to tell the blurb_blob_breaker stored
procedure
-- which text to return.

create table blurbs_control_table
( au_id char(11), beginchar int,
length int)
go

create procedure blurb_blob_breaker as
set nocount on

declare @au_id char(11)
declare @beginchar int
declare @length int

-- determine which au_id to pull text for, as the begining point
-- and length to return

select @au_id = au_id, @beginchar = beginchar, @length = length
from blurbs_control_table

declare @val binary(16)
select @val = textptr(copy) from blurbs where au_id = @au_id
readtext blurbs.copy @val @beginchar @length
go

-- Set up an object definition for a proxy table binding it to the rpc

sp_addobjectdef "pubs2..blurbs_as_chars",
"THIS.pubs2..blurb_blob_breaker", "rpc"
go

-- Create the proxy table definition

create existing table blurbs_as_chars (copy char(255))
go
-- THIS FINISHES THE SETUP NEEDED FOR blurbs2..copy

-- THE FOLLOWING CODE shows how to use the objects created above
-- to pull the text for a single author-id.

set nocount on
declare @au_id char(11)
select @au_id = "998-72-3567"
declare @length_of_text int
select @length_of_text = datalength(copy) from blurbs where au_id =
@au_id
declare @row int
select @row = 0
declare @rowlength int
select @rowlength = 255
insert blurbs_control_table
values (@au_id, 0-@rowlength, @rowlength )
while ((@row+1)*@rowlength
< @length_of_text)
-- process all the full chunks of 255 characters
begin
-- the following select is diagnostic, it just shows the values in the
control table
select ((@row+1)*@rowlength
), @length_of_text
update blurbs_control_table
set beginchar = beginchar + length
insert blurb_strings select @au_id, @row, copy from
pubs2..blurbs_as_chars
select @row = @row+1
end
-- process the last row, which probably has a smaller rowlength
update blurbs_control_table
set
beginchar = beginchar + @rowlength,
length = @length_of_text % @rowlength
insert blurb_strings select @au_id, @row, copy from
pubs2..blurbs_as_chars
go
select * from blurbs_control_table

delete blurbs_control_table

go

-- And here are the final results...

select * from blurb_strings
go

VMI

2005-10-31, 1:23 pm

The DBA is not entirely sure, but he says it's 11.5 or 11.9.
He knows that we're not in 12 yet.

Thanks.

> So what release are you on?
>
> <VMI> wrote in message
> version. >
> record. >
>
>

Bret Halford

2005-10-31, 1:23 pm

You can find out for sure by running "select @@version"

VMI wrote:
[color=darkred]
> The DBA is not entirely sure, but he says it's 11.5 or 11.9.
> He knows that we're not in 12 yet.
>
> Thanks.
>

Sponsored Links