Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Text Field Manipulation

4 posts in Windows NT Last posting was on 1999-11-27 01:10:16.0Z
Hemant Lad Posted on 1999-11-24 16:08:33.0Z
Reply-To: "Hemant Lad" <hemant_lad@unity-software.com>
From: "Hemant Lad" <hemant_lad@unity-software.com>
Subject: Text Field Manipulation
Date: Wed, 24 Nov 1999 16:08:33 -0000
Lines: 27
Organization: Unity Software Systems
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <7HwrqapN$GA.311@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: host.unity-software.com 194.200.1.188
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2929
Article PK: 1090365

I am in the process of creating a procedure that reads in a text column and
breaks the value
up into varchar(255) chunks.

The code I am using is as follows:

declare @length int
declare @txtptr varbinary(16)

select @length = datalength(columnName) from tableName where
<..............>
select @txtptr = textptr(columnName) from tableName where <...............>

readtext tableName.columnName @txtptr 0 255 using chars
readtext tableName.columnName @txtptr 0 511 using chars
etc...

The problem that I am having is that I cannot assign a temporary variable to
the result of the readtext,
i.e. select @tempVar1 = readtext tableName.columnName @txtptr 0 255 using
chars

Does anybody know a solution?


Bret Halford Posted on 1999-11-24 17:56:52.0Z
Message-ID: <383C26E4.6D25@sybase.com>
Date: Wed, 24 Nov 1999 10:56:52 -0700
From: Bret Halford <bret@sybase.com>
Organization: Customer Service & Support
X-Mailer: Mozilla 3.0 (X11; U; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
To: Hemant Lad <hemant_lad@unity-software.com>
Subject: Re: Text Field Manipulation
References: <7HwrqapN$GA.311@forums.sybase.com>
Content-Type: multipart/mixed; boundary="------------2E8753727628"
Newsgroups: sybase.public.sqlserver.nt
Lines: 221
NNTP-Posting-Host: 157.133.214.40
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2928
Article PK: 1090364


Hemant Lad wrote:
>
> I am in the process of creating a procedure that reads in a text column and
> breaks the value
> up into varchar(255) chunks.
>
> The code I am using is as follows:
>
> declare @length int
> declare @txtptr varbinary(16)
>
> select @length = datalength(columnName) from tableName where
> <..............>
> select @txtptr = textptr(columnName) from tableName where <...............>
>
> readtext tableName.columnName @txtptr 0 255 using chars
> readtext tableName.columnName @txtptr 0 511 using chars
> etc...
>
> The problem that I am having is that I cannot assign a temporary variable to
> the result of the readtext,
> i.e. select @tempVar1 = readtext tableName.columnName @txtptr 0 255 using
> chars
>
> Does anybody know a solution?

Yes, I've posted one a few times before. Here it is again
(no changes, for those who have seen it before).
--
Bret Halford
Sybase Technical Support
3665 Discovery Drive
Boulder, CO 80303

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. It is still
very fresh 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 yet, and I'm going on vacation
tomorrow. :-)

Here goes:


-- 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


Hemant Lad Posted on 1999-11-25 11:35:15.0Z
Reply-To: "Hemant Lad" <hemant_lad@unity-software.com>
From: "Hemant Lad" <hemant_lad@unity-software.com>
References: <7HwrqapN$GA.311@forums.sybase.com> <383C26E4.6D25@sybase.com>
Subject: Re: Text Field Manipulation
Date: Thu, 25 Nov 1999 11:35:15 -0000
Lines: 286
Organization: Unity Software Systems
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <gO#6LjzN$GA.203@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: host.unity-software.com 194.200.1.188
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2924
Article PK: 1090360

Bret,

Thanks for your prompt response. Unfortunately, our client is on version
11.0.3, so I gather your solution
will not work. Have you a solution that we could use for version 11.0.3?

Kind Regards

Hemant

Bret Halford <bret@sybase.com> wrote in message
news:383C26E4.6D25@sybase.com...
> Hemant Lad wrote:
> >
> > I am in the process of creating a procedure that reads in a text column
and
> > breaks the value
> > up into varchar(255) chunks.
> >
> > The code I am using is as follows:
> >
> > declare @length int
> > declare @txtptr varbinary(16)
> >
> > select @length = datalength(columnName) from tableName where
> > <..............>
> > select @txtptr = textptr(columnName) from tableName where
<...............>
> >
> > readtext tableName.columnName @txtptr 0 255 using chars
> > readtext tableName.columnName @txtptr 0 511 using chars
> > etc...
> >
> > The problem that I am having is that I cannot assign a temporary
variable to
> > the result of the readtext,
> > i.e. select @tempVar1 = readtext tableName.columnName @txtptr 0 255
using
> > chars
> >
> > Does anybody know a solution?
>
>
> Yes, I've posted one a few times before. Here it is again
> (no changes, for those who have seen it before).
> --
> Bret Halford
> Sybase Technical Support
> 3665 Discovery Drive
> Boulder, CO 80303
>

----------------------------------------------------------------------------
----


> 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. It is still
> very fresh 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 yet, and I'm going on vacation
> tomorrow. :-)
>
> Here goes:
>
>
> -- 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
>


Bret Halford Posted on 1999-11-27 01:10:16.0Z
Message-ID: <383F2F78.752F@sybase.com>
Date: Fri, 26 Nov 1999 18:10:16 -0700
From: Bret Halford <bret@sybase.com>
Organization: Customer Service & Support
X-Mailer: Mozilla 3.0 (X11; U; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
Subject: Re: Text Field Manipulation
References: <7HwrqapN$GA.311@forums.sybase.com> <383C26E4.6D25@sybase.com> <gO#6LjzN$GA.203@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 12
NNTP-Posting-Host: 157.133.214.40
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2920
Article PK: 1090356


Hemant Lad wrote:
>
> Bret,
>
> Thanks for your prompt response. Unfortunately, our client is on version
> 11.0.3, so I gather your solution
> will not work. Have you a solution that we could use for version 11.0.3?

Nothing on the server-side. Prior to ASE 11.5 you pretty much
had to do all the text manipulation on the client side.

-
--
Bret Halford
Sybase Technical Support
3665 Discovery Drive
Boulder, CO 80303