|
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 |
|
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. Thanks! That is very much what we were looking for. Cheers, Dan
(03 Feb '13, 17:48)
Dan Cleyne
@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.
(28 Feb '13, 18:59)
Trevor Powell
Replies hidden
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 = ...;
(01 Mar '13, 06:18)
Volker Barth
|
