Skip to Content

SQL Anywhere and DisableBind=1, An Open Manhole Cover?

I posted this in the PowerBuilder Forum and posting it here to get more information from the SQL Anywhere Community and also to expose an issue which might unknowingly be affecting others.

I have been tracking down the issue of corruption of our Rich text in our PB 12.6 app and discovered via communications with PowerBuilder and SQL Anywhere Support that if you set DisableBind=1 in your connection, that SQL Anywhere will look for character string sequences inside varchar columns and translate them to special characters when storing them in the database.

So for example a \n anywhere in the string sent to the database via a datawindow column will be converted to a newline in the database.  If you didn't want this to happen then you need to find and replace \n's with \\n's which is will translate to \n as stored.

We had seen this previously in our data occasionally, but assumed it was a data entry error, however it shows up most obviously with Rich Text stored in long varchars, because in PB 12.6 the Rich text Control sequences include some formatting instructions which have a \no... string in it - and the \n is replaced on the way to the database.  When you re- retrieve the data you will see the characters owidctlpar preceding your text.  This is a corruption of \nowidctlpar.

I have yet to discover what other character sequences can be corrupted - but its safe to say that you cannot trust the integrity of any varchar column inserted into the SQL Anywhere database (6 through 16)  if your connection includes DisableBind=1.

Your options are pre parse the strings and replace the \n with \\n , Set DisableBind=0, or use a Blob.

So it looks like we are in store for some system wide re-engineering.  I know we needed to DisableBind Variables as some point in our development history.  Some of it may have had to do with the size of some of our SQL Queries.  There is some light mention of  Bind Variables in relationship to the datawindow in PowerBuilder, is there any robust documentation on the impact of Bind Variables or how a datawindow interacts with the database?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • May 12, 2016 at 08:12 PM

    Caveat Emptor: guesswork follows...

    If bind variables aren't used, then what choice does the poor interface have, for a database column defined as long varchar? Probably just a 'quoted string literal value', and it is the syntax for quoted string literals that defines that funky \n thing.

    Long binary may be a better choice for rich text values since they aren't really character strings.

    Investigative testing is highly recommended, including tracing of the actual SQL commands being sent from PowerBuilder to SQL Anywhere.

    Add comment
    10|10000 characters needed characters exceeded

    • Love the cartoon.  I think I know someone who just got a job like that!

      You are right -  the transition from being a much appreciated Sybase customer (they sponsored us at the Computer World Honors) to being generally ignored by SAP and having our costs and administration effort skyrocket, has made me an unhappy camper - and my grumbling about that in the forum is probably inappropriately directed.

      This escape problem is the first technical issue with SQL Anywhere that I felt was poorly addressed - for the most part I have been a Watcom /SQL Anywhere supporter back to the early 90's. I used to introduce it as part of my courses at UCLA.

      We pay a fairly hefty partner fee every year, plus purchase licenses for hundreds of users, that doesn't put us up there with the biggies like intuit - but it's our largest annual technology cost.

      -- BTW - No Joke - who have you found who actually reads and responds to letters?  Please forward the name and address privately if you can.

  • May 14, 2016 at 09:45 PM

    I think our original problem with bind variables had something to do with inserts on very large (column wise) tables and overwhelming the SQL buffer...  That's the area which is foremost in my mind when we address  the effect of generally changing the use of Bind Variables.  I am asking support for more information on that - and best ways to test and trap the issue.

    Its interesting that there is a fair amount of comment in the MS SQL forum about the Rich text > Plain text translation issue with some exotic stored procedures.  I can already tell you the current PB rich text format is different from the current one and Rich text doesn't export to excel very well - so we either need to come up with an "on-the-fly" database solution for reporting of rich text or simultaneously store plain text and rich text with the data.

    I guess this should be its own forum topic.

    Add comment
    10|10000 characters needed characters exceeded