Does Sybase SQL engine support something like the TSQL .WRITE syntax?

[Former Member]

I have a question from one of our SQL Engineers...

Does Sybase SQL engine support the .WRITE syntax for updating a specified section of a varbinary column as TSQL (MS SQL Server) does

i.e. UPDATE [mytable] SET Data .WRITE (@buffer, @start, @length) WHERE ….

Or is there some other way of achieving the same basic outcome?

Cheers, Dan

[VolkerBarth · Accepted solution]

What about the STUFF string function, such as - assuming @buffer is a string variable containing the new data portion:

UPDATE myTable SET myColumn = STUFF(myColumn, @start, @length, @buffer)
WHERE myPK = ...;

AFAIK, all SQL Anywhere string functions will handle binary data, too, and do so without character conversion (whereas that will happen to character data as desired).

Aside: IIRC, SQL Anywhere had never those funky special datatypes like TEXT/IMAGE and their not-so-internal usage of "text pointers" for BLOBs and CLOBs which require special handling in MS SQL Server and ASE (WRITETEXT, UPDATETEXT and the like). In SQL Anywhere, a LONG VARCHAR or LONG VARBINARY can use up to 2 GB of data and are treated (with minor exceptions) much like ordinary strings.

[Former Member · Accepted solution]

Thanks! That is very much what we were looking for.

Cheers, Dan

[Former Member · Accepted solution]

@VolkerBarth After discussion with Sybase support, it turns out that binary blobs passed to STUFF are reinterpreted as strings using the database's configured default character encoding. '@start' and '@length' are then interpreted as character offsets within that encoding, not as byte offsets within the binary blob. This behaviour seems to make STUFF unusable for making modifications to binary columns.

[VolkerBarth · Accepted solution]

Thanks for the feedback!

Then I hope that the internal experts like Mark and John can share their insights...

FWIW, you may use the BYTE_SUBSTR() function:

UPDATE myTable SET myColumn =
   BYTE_SUBSTR(myColumn, 1, @start - 1) || @buffer || BYTE_SUBSTR(myColumn, @start + @length)     
WHERE myPK = ...;