Sybase Jilted Me Again... 
Author Message
 Sybase Jilted Me Again...

Dear Ann Landers and other Sybase gurus:

Sybase has been toying with me again.  It got my hopes up and then
dashed them against the jagged shards of broken dreams...

I have the need to be able to write to a text column from within
a stored procedure, but try as I might, I cannot get the sucker to
accept more than 255 characters.

Since there is no way to use it with a string greater than 255 characters,
what good is it?  That being the case, you might as well use a varchar(255)
column.

Here's how I reached this conclusion.  Someone please correct me if I
am wrong.  This is one instance -- the only one :-) -- where I would love
to be proven wrong:

        You cannot declare a variable of type text
        You cannot use concatenation to build up a string of length > 255

          try: select datalength("<255 characters>"+"<255 characters>")
          result: 255 (No matter how many characters you concatenate, your
          string will get truncated at the first 255 characters.)

        You cannot use the replicate function to create a string of
          length > 255 (this wouldn't be terribly useful, even if you could.)

          try: select datalength(replicate("This string", 50))
          result: 253 (replicate won't fill the 255 chars out with a
          partial string)

Therefore, there is no way to write more than 255 characters to a text
column from within Transact SQL.  That being the case, you might as well
save some processing overhead and just use INSERT or UPDATE to get data
into the text column because you can store 255 characters in the text column
this way.  Or, why not just use varchar(255)? What's the point of writetext?

Before you grab your keyboard to point out my foolish assumption that
writetext has no usefulness at all, I must confess that, yes, I know
that it provides a way for CT-Library to get text (more than 255 chars)
into a text column.  Of course Powerbuilder or C++ can create strings
much bigger than 255 characters, but is there any way to do this from
within an SP?

TIA,
Helpless in Houston
(Rusty Alderson)



Sat, 09 Oct 1999 03:00:00 GMT
 Sybase Jilted Me Again...

: I have the need to be able to write to a text column from within
: a stored procedure, but try as I might, I cannot get the sucker to
: accept more than 255 characters.

: Since there is no way to use it with a string greater than 255 characters,
: what good is it?  That being the case, you might as well use a varchar(255)
: column.

: Here's how I reached this conclusion.  Someone please correct me if I
: am wrong.  This is one instance -- the only one :-) -- where I would love
: to be proven wrong:

:       You cannot declare a variable of type text
:       You cannot use concatenation to build up a string of length > 255
:       You cannot use the replicate function to create a string of
:         length > 255 (this wouldn't be terribly useful, even if you could.)

True :-(.

: Therefore, there is no way to write more than 255 characters to a text
: column from within Transact SQL.  That being the case, you might as well
: save some processing overhead and just use INSERT or UPDATE to get data
: into the text column because you can store 255 characters in the text column
: this way.  Or, why not just use varchar(255)? What's the point of writetext?

: Before you grab your keyboard to point out my foolish assumption that
: writetext has no usefulness at all, I must confess that, yes, I know
: that it provides a way for CT-Library to get text (more than 255 chars)
: into a text column.  Of course Powerbuilder or C++ can create strings
: much bigger than 255 characters, but is there any way to do this from
: within an SP?

No.

The only use of writetext that I see is when you want to use the
non-logging version (I don't know if anyone is doing this).

ISUG has an enhancement request for allowing text/image parameters
in procedures (9410_044), but it's not planned anymore ...

Luc.
-

+-----------------------------------------------------------+
| Luc Van der Veurst            ISUG Enhancements Co-Chair  |
| Academic Hospital, VUB        Voice : 32 - 2 477 69 80    |
| Laarbeeklaan 101              Fax   : 32 - 2 477 69 75    |

+-----------------------------------------------------------+
Next ISUG Conference :   Vienna, Austria  3 - 6 November 1997



Sun, 10 Oct 1999 03:00:00 GMT
 Sybase Jilted Me Again...

Quote:


> : I have the need to be able to write to a text column from within
> : a stored procedure, but try as I might, I cannot get the sucker to
> : accept more than 255 characters.

> : Since there is no way to use it with a string greater than 255 characters,
> : what good is it?  That being the case, you might as well use a varchar(255)
> : column.

> : Here's how I reached this conclusion.  Someone please correct me if I
> : am wrong.  This is one instance -- the only one :-) -- where I would love
> : to be proven wrong:

> :   You cannot declare a variable of type text
> :   You cannot use concatenation to build up a string of length > 255
> :   You cannot use the replicate function to create a string of
> :    length > 255 (this wouldn't be terribly useful, even if you could.)

> True :-(.

For *sorta* workaround check out:

    http://reality.sgi.com/pablo/Sybase_FAQ/Q8.12.html
--

--------------+-----------------+--------------------------------------------



Sun, 10 Oct 1999 03:00:00 GMT
 Sybase Jilted Me Again...

Quote:

> Dear Ann Landers and other Sybase gurus:

> Sybase has been toying with me again.  It got my hopes up and then
> dashed them against the jagged shards of broken dreams...

See this?  <(finger and thumb rubbing against each other)>
I'm playing the world's smallest violin for you.

:-)

Quote:
> I have the need to be able to write to a text column from within
> a stored procedure, but try as I might, I cannot get the sucker to
> accept more than 255 characters.

> Since there is no way to use it with a string greater than 255 characters,
> what good is it?  That being the case, you might as well use a varchar(255)
> column.

That's true, but at least it's available for use with the
Open/Client calls from C or COBOL.

We use text columns all over the place, since our application
is written almost exclusively in C.  Very little use of SP's.

Look at it from this POV: at least you can have as many text
columns as you want.  When we converted the app to Oracle, we
discovered that Oracle limits you to no more than one text
column per table.  This was, and still is, the cause of all
sorts of grief!

Otherwise, all I can do is wish you the best of luck, and
remind you it can always be worse ... you could be trying
to do it in Oracle!

:-)

A.
Sorry for the anti-spam address: see the Organization line
for e-mail.



Sun, 10 Oct 1999 03:00:00 GMT
 Sybase Jilted Me Again...

Quote:

> Dear Ann Landers and other Sybase gurus:

> Sybase has been toying with me again.  It got my hopes up and then
> dashed them against the jagged shards of broken dreams...

> I have the need to be able to write to a text column from within
> a stored procedure, but try as I might, I cannot get the sucker to
> accept more than 255 characters.

> Since there is no way to use it with a string greater than 255 characters,
> what good is it?  That being the case, you might as well use a varchar(255)
> column.

> Here's how I reached this conclusion.  Someone please correct me if I
> am wrong.  This is one instance -- the only one :-) -- where I would love
> to be proven wrong:

>         You cannot declare a variable of type text
>         You cannot use concatenation to build up a string of length > 255

>           try: select datalength("<255 characters>"+"<255 characters>")
>           result: 255 (No matter how many characters you concatenate, your
>           string will get truncated at the first 255 characters.)

>         You cannot use the replicate function to create a string of
>           length > 255 (this wouldn't be terribly useful, even if you could.)

>           try: select datalength(replicate("This string", 50))
>           result: 253 (replicate won't fill the 255 chars out with a
>           partial string)

> Therefore, there is no way to write more than 255 characters to a text
> column from within Transact SQL.  That being the case, you might as well
> save some processing overhead and just use INSERT or UPDATE to get data
> into the text column because you can store 255 characters in the text column
> this way.  Or, why not just use varchar(255)? What's the point of writetext?

> Before you grab your keyboard to point out my foolish assumption that
> writetext has no usefulness at all, I must confess that, yes, I know
> that it provides a way for CT-Library to get text (more than 255 chars)
> into a text column.  Of course Powerbuilder or C++ can create strings
> much bigger than 255 characters, but is there any way to do this from
> within an SP?

> TIA,
> Helpless in Houston
> (Rusty Alderson)

Hello Rusty,

        Unfortunately there is now way to select more than 255 chars of a text
field at this time nor can you select text/image data into a sproc
variable.  But, the good news is that these itmes will most likely be
implemented in a later release.

        This doesn't help much though.



Tue, 12 Oct 1999 03:00:00 GMT
 Sybase Jilted Me Again...

The original expired off my newsreader before I got a chance to read it,
but hopefully I found it in enough context...


Quote:

> : I have the need to be able to write to a text column from within
> : a stored procedure, but try as I might, I cannot get the sucker to
> : accept more than 255 characters.
> : Therefore, there is no way to write more than 255 characters to a text
> : column from within Transact SQL.  That being the case, you might as well
> : save some processing overhead and just use INSERT or UPDATE to get data
> : into the text column because you can store 255 characters in the text column
> : this way.  Or, why not just use varchar(255)? What's the point of writetext?

A stored procedure can accept 255 parameters of 255 characters.  I don't
know if this will help in your case, but I had one case where we ended
up breaking the text into individual varchar(255) parameters to upload
the text through a stored procedure, then recombined it into the the
text field on the server.  It's still working fine after 3 years.  Of
course this was for C openclient programs contacting a C openserver so
we had more control over what was happening and this may not work for you.

Dan
--
+----------------------------------+-------------------------------------+


+----------------------------------+-------------------------------------+
If you believe I speak for my employer...
   I have some prime oceanfront real estate in Arkansas to sell you.



Tue, 19 Oct 1999 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Selecting Sybase - Would you do it again?

2. Sybase and ODBC (again)

3. sybase performance again

4. Stored Procedure Debugger for Sybase (again)

5. Summary: (again) using BLOB's in Sybase

6. Dataserver/Sybase again

7. Summary: (again) of BLOB's with Sybase

8. Licensing Again Again

9. Connection Again and Again

10. multithreading, again and again!

11. Synonyms - again (Graeme hits again!)

12. open ADO cnt again and again


 
Powered by phpBB® Forum Software