Return a column with more than 16384 bytes

Question

I am using a sybase ase 15 database. And I have a column of type image (this column stores SQL querys) so I have to convert it to varchar to return the text. The problem is that I can't return more than 16834 bytes, and it returns the query trimmed.

Is there any way to get the whole column? I am currently using a script with the pymssql library in python.

And the query I am using is

select (convert varchar(16834), convert(varbinary(16834), FIELD)) as 'SQL' from TABLE WHERE ...

I've tried to put max instead 16834, but it returns error as don't recognize as variable, cast it to TEXT type, but it doesn't return the full size of the column either. The column datatype is IMAGE and the heavier field is 40kb, but it only returns 16kb

tdy · Accepted Answer · 2024-05-18 19:30:55Z

3

As markp-fuso said you can use readtext, the only thing i added is datalength to use the size correctly.

select @len = datalength (field) ... 
readtext table.column @val 0 @len

edited May 18, 2024 at 19:30

tdy

42k42 gold badges124 silver badges125 bronze badges

answered May 16, 2024 at 15:23

PeByte

534 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

markp-fuso Over a year ago

good point re: using datalength() so you only need a single readtext call; while I understand the16K limits on convert() calls I got sidetracked thinking you had some explicit reason for pulling the data back in 16K chunks hence the mention of a loop to repeatedly run readtext

markp-fuso · Accepted Answer · 2024-05-16 15:35:21Z

2

In transact-sql you can just reference the column (no functions necessary) and ASE will return the entire contents of the column, eg, the following should be sufficient:

select FIELD from TABLE

NOTE: I don't work with python/pmssql but I would expect it to know how to process the long stream of data returned from a text/image datatype.

If for some reason python/pmmsql doesn't have the ability to process a single, long stream of data then you may want to look at the readtext command to break the data into chunks.

A basic example of using readtext to read a chunk of data from a column:

declare @val varbinary(16) 

select @val = textptr(copy)        -- obtain the text pointer for <column_name>
from   blurbs                      -- from <table_name>
where  au_id = "648-92-1872" 

readtext blurbs.copy               -- <table_name>.<column_name> being read
@val 1 5                           -- text pointer, offset, length

In this case we're reading the first 5 bytes. In your case you'd use a loop to repeatedly call readtext for subsequent 16K chunks (ie, @val 1 16384, @val 16385 16384, @val 32769 16384, ...). (or as PeByte has figured out: make a single readtext call where the last arg is the result of datelength(<column_name>))

References:

edited May 16, 2024 at 15:35

answered May 16, 2024 at 13:26

markp-fuso

38.5k5 gold badges24 silver badges48 bronze badges

4 Comments

SQLpro Over a year ago

Image datatype is BINARY not TEXT...

markp-fuso Over a year ago

@sqlpro yes, I'm aware of the difference between the image (binary) and text (text) datatypes; what's your point?

PeByte Over a year ago

Thanks! It worked, when I executed the query with pymssql the result is already parsed

markp-fuso Over a year ago

@PeByte yeah, that should be a (somewhat) basic feature of any database/query tool; good to hear it was just a simple change

Collectives™ on Stack Overflow

Return a column with more than 16384 bytes

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related