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