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

asked 31 Jan '13, 02:42

Dan%20Cleyne's gravatar image

Dan Cleyne
486101627
accept rate: 25%


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.

permanent link

answered 31 Jan '13, 04:07

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 32%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×413
×90

question asked: 31 Jan '13, 02:42

question was seen: 941 times

last updated: 01 Mar '13, 06:18