Store more than 32767 characters SQLA 11

[Greg]

How do I store more than 32767 characters in 1 column? I'm
using SQLAnywhere 11.0.0.1549 and PB 11.2 (for the
datawindows). varchar only goes to 32767 and long varchar
only goes to 32700. Other documentation I've found say that
a CLOB data type will store 2,147,483,647 but I don't see
that data type in SQLAnywhere 11.

[Chris Keating (Sybase iAnywhere)]

You need to use LONG (N)VARCHAR to get to 2GB. Varchar is limited to 32k.

-chris

[Greg]

I've tried using a long varchar and it is also limited to
32700, according to the documentation. I also created an
example program where I created a long varchar column in a
table and created a string where the first 32767 characters
are a "1" and the next 1000 characters are a "2" and did an
UPDATE sql command and it only writes "1". I've verified
using the PB 11.2 debugger that by copying from the variable
and pasting into a text editor, the string variable in the
program does have
33767 characters where the last 1000 characters are a "2",
but no joy when updating the column, only "1"'s.

[Brad Wery[TeamSybase]]

You'll most likely have to use the UPDATEBLOB command.

--

*********************************************************************************************************
www.PowerToTheBuilder.com
- Take control of your GUI with a free PB user control library
- Get updates from my blog - blog.PowerToTheBuilder.com
- Get support from - forum.PowerToTheBuilder.com

*********************************************************************************************************
www.Werysoft.com
- Full featured database development tool for ASE, SQL Server, SQL
Anywhere and Oracle

Get productive today!

*********************************************************************************************************

[Greg]

Well, it doesn't work using the UPDATEBLOB command either.

Would you mind trying this?

string s_strg, s_strg2
long i
s_strg = ""
for i = 1 to 33767
if(i <= 32767)then
s_strg += "1"
else
s_strg += "2"
end if
next

UPDATEBLOB mytable
SET clue = :s_strg
WHERE appid = 1
USING SQLCA;
COMMIT;

SELECT clue
INTO :s_strg2
FROM mytable
WHERE appid = 1
USING SQLCA;

mytable is a table with 2 columns(created in Sybase Central)
column 1 is an integer called "appid" with 1 existing row
where appid = 1. The second column is either a long
nvarchar or a long varchar (I've tried both).

In the debugger if you break after the last line and copy
the values of s_strg into a text editor that counts bytes (I
use Ultra Edit) it will show exactly 33767 bytes (32767
bytes of "1" and 1000 bytes of "2"). Then copy the contents
of s_strg2 into the text editor (s_strg2 is the variable
that is filled when retireved from the database) and it is
truncated to exactly 32767 bytes of "1". This seems like a
very straight forward test that clearly shows that the long
varchar or long nvarchar will only go to 32767. What am I
missing?

[Brad Wery[TeamSybase]]

Convert your string to a blob then pass that to UPDATEBLOB. Next, use
SELECTBLOB to get the data back. The SELECT command won't help you here.

--

*********************************************************************************************************

[Greg]

That does work, thanks for the help. A bit convoluted mising
Blobs and Strings but what the heck.

One more piece of information I'd like to pass on to those
who might read this in the future.

If you are using a long varchar column in a datawindow, when
you add the column to the datawindow is shows correctly in
the "Data Source" painter as being a long varchar in the
database; however the datawindow treats it as a char(32767)
behind the sceens. In order to get it to work you have to
export the datawindow, manually change the data type from
what it assigned as a char(32767) to char(100000) or
whatever you want, and then import it back into your pbl.