SQL Anywhere Long Varchar/Binary Column Length Limited to 32767

blobsybase-sql-anywhere

Attempting to set up a database table (Sybase SQL Anywhere 11.0.1) to store user uploaded documents as either BLOBs or base64 encoded strings, but running into an issue where the length of my LONG BINARY and LONG VARCHAR columns is being limited to 32767.
Using the following create table statement:

CREATE TABLE blobTest (
blobCol LONG BINARY,
vcharCol LONG VARCHAR
)

The table is added to the database with no issue, however, running a query of the syscolumns table:

SELECT cname,coltype,length
FROM sys.syscolumns
WHERE tname LIKE 'blobTest'
ORDER BY cname ASC

Yields the result:

blobCol, long binary, 32767

vcharCol, long varchar, 32767

Storing a value in either of these columns shows that this is indeed the length of the columns, with anything larger than 32Kb* getting truncated.

Looking through the SAP documentation, it's my understanding that both of these data types should have a limit of roughly 2Gb. I've checked into the server/database configuration options but I can't find anything that looks like it would be limiting the length of these columns, am I missing something?

-edit-

The amount that the data was being truncated was not 32000, but 64000, per the admin datasource settings. Further testing showed that the results of:

SELECT LEN(vcharCol)
FROM blobTest

Would be the correct number of characters expected for an uploaded file.

Best Answer

Found the answer:

The issue was not with SQL Anywhere, but rather in our server datasource administration settings.

We use ColdFusion 10 on our web server, and the datasources have a Long Text Buffer setting which was limiting the size of the string being returned from a query of the database to 64000

Changing this value to 2000000 solved the issue.