Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2006
    Posts
    13

    Unanswered: Using Text Datatype

    Hi All,

    I've a column of TEXT datatype in one of the tables. I've to retrieve the value of this column corresponding to a particular record based on some condition and then assign it to a variable in a stored procedure. Since SELECT statement will not display the TEXT column value, what should be used? What should be the datatype of the variable to which this value can be assigned?

    Any help on this would be appreciated.

    Thanks in advance
    Sumesh

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    If you are using 12.5 or later, and if the TEXT column is less than 16k in size you can do:

    Code:
    declare @var varchar(16384)
    
    select @var = text_column from table where....
    If the TEXT variable is larger than 16k then things get a lot more difficult. You may be able to use the readtext call, but in general TEXT columns were not designed to be used inside stored procedures...

    Michael

  3. #3
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    Text and Image columns are used for supporting BLOBs. The verbs to use are READTEXT/WRITETEXT and of course, you provide either a file or a memory location, to read/write up to 2 trillion (2**32) bytes. Text/Image cols are not designed to be loaded into a @var, which is finite and has a hard datatype. thus you cannot use it as a passed parameter for sprocs.

    The 12.5 feature is a recent new feature, which extends the max size of CHAR/VARCHAR datatypes and then loads (the first x bytes) of the column into it. of course, it is still not reasonable to display (via select) the contents, unless it is ascii. For getting any idea of what the column contains or whether it is empty, use the functions:
    - textptr()
    - textvalid()

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  4. #4
    Join Date
    Dec 2006
    Posts
    5
    What do you mean by loading (the first x bytes)of the coulmn Derek. Do you mean to say this feature of 12.5 can be used to accomodate a varchar of maxsize more than 255 and if that so, then what is the max size for a vachar in 12.5

  5. #5
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    If you had read my message you'd have seen that in 12.5 and later the max size for a varchar/varbinary is 16k (16384)

    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •