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

[glenn_barber]

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?

[glenn_barber]

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.

[former_member188493]

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.

[glenn_barber]

Thanks Breck

Already did the investigation with SAP Support. Looking at the Interface with the log.  Apparently they have known about this since version 6.  Very poorly documented, awaiting the unwary.  It happens with any varchar where it sees something like a control sequence - so All text fields are subject to alteration on their way to the database (not just rich text).  Type a \n in the middle of some text and see what is does.

If users occasionally use \n anywhere in their text data, it will be eaten - and they may never notice - only the new rich text control exposed this - but it happens everywhere.  So while we could use blobs for rich text to get around this - we can have only one blob update per row and the datawindow in powerbuilder won't let you update it like any other field - a lot of gymnastics.

So this means we go back to disablebind = 0.  However this affects how defaults work in PowerBuilder.  With Disable Bind = 0, the datawindow will set a column with no value to Null and the database default will not operate.  This means we have to supply defaults in the datawindow if the column is available in the update.  A lot of backtracking.

There are also places where the size of our update exceeds the ODBC SQL buffer size with the inclusion of Bind Variables.  We have to research thousands of these to see which they are and then dynamically change the Bind Variable setting during the Insert/Update.

My upset with this is that I naively assumed that what you put in a varchar column on an insert is not altered on its way into the database, and there were absolutely no warning flags in any documentation.

This could create some conundrums for us where the update won't work with DisableBind=0 but we need to preserve the integrity of the text data...

BTW - I would argue the point about rich text not being character data - all the definitions I saw said it had to be

[glenn_barber]

Hey Breck

Remember this one?  Was there ever anyway to turn this off in the interface or are they still stonewalling?

PowerBuilder has escape characters, so does SQL Anywhere. The

inventors of PowerBuilder made a good choice... with SQL Anywhere, not

so much. I don't think SQL Anywhere uses \ just to make our lives

difficult, but that is the effect.

SQL Anywhere treats \ in a string literal as an escape character. For

example, \n is interpreted as a single new line character. This means

if you want to store a \ followed by an n, you have to escape the \

itself, by coding it as two: \\n will be interpreted as a \ followed

by an n, instead of new line.

So, the solution may be ugly but it is simple: \\\\prodserver\\test

Note that this applies to strings stored inside PB variables and sent

to the database, because when they hit SQL Anywhere they very well may

be parsed as string literals.

Your suggestion (an option, perhaps a temporary connection option, to

turn off escape character parsing in string literals) has been made

before, and refused/ignored.

But maybe you will have better luck: Try posting your suggestion on

the sybase.public.sqlanywhere.product_futures_discussion newsgroup.

[former_member188493]

Are the non-bind values being passed to SQL Anywhere as quoted string literals? If so, then \n is well documented in the SQL Anywhere docs, and yes, \n embedded inside any string literal will be interpreted as a new line. The important context is string literal. For example, here is what happens using dbisql:

CREATE TABLE t ( s VARCHAR ( 100 ) );
INSERT t VALUES ( 'Hello \n World' );
COMMIT;
SELECT * FROM t;

1 row(s) inserted
s                                                                                                    
-------------------
Hello 
 World                                                                                        
(1 rows)

[former_member188493]

Other than the WITH ESCAPES ON OFF clause being added to EXECUTE IMMEDIATE, nothing comes to mind.

[glenn_barber]

Looking at the SQL Anywhere Documentation I only saw control of escapes in the INPUT statement.

When I looked for Escapes in the documentation all I found was the INPUT statement which does allow the ability to control this behavior by turning it on or off or remapping the escape character.

I am not sure how I would get under the covers in the Data Window Created SQL to introduce the ESCAPES OFF Syntax

[glenn_barber]

The trace looks like this  indeed without bind variables they include the text in single quotes

PB126           192c-2584	ENTER SQLExecDirectW
		HSTMT               0x01D41540
		WCHAR *             0x02AF363C [      -3] "INSERT INTO "notes" ( "notedesc", "notes" ) VALUES ( '12.6 Test with Trace', '{\rtf1\ansi\ansicpg1252\uc1\deff0{\fonttbl\ d\ a{\f0\fnil\fcharset0\fprq2 Arial;}\ d\ a{\f1\fswiss\fcharset0\fprq2 Tahoma;}\ d\ a{\f2\froman\fcharset2\fprq2 Symbol;}}\ d\ a{\colortbl;\red0\green0\blue0;\red255\green255\blue255;}\ d\ a{\stylesheet{\s0\itap0\nowidctlpar\f0\fs24 [Normal];}{\*\cs10\additive Default Paragraph Font;}}\ d\ a{\*\generator TX_RTF32 15.0.530.503;}\ d\ a\deftab1134\paperw12240\paperh15840\margl0\margt0\margr0\margb0\widowctrl\formshade\sectd\ d\ a\headery720\footery720\pgwsxn12240\pghsxn15840\marglsxn0\margtsxn0\margrsxn0\margbsxn0\pard\itap0\nowidctlpar\plain\f1\fs20 Test}' )\ 0"
		SDWORD                    -3

[former_member188493]

It's not stonewalling, or a bug, it's documented behavior that does not meet your needs.

The behavior of DisableBind=1 with respect to string values inserted via DataWindow is described with an example in the following excerpt from the PowerBuilder 10.2.1 Help.

The excerpt is discussing a different topic (how default values are handled) but it does explicitly show that the customer_ID value "A-123" is passed to SQL Anywhere as the SQL string literal 'A-123' in a SQL INSERT statement.

=====

PowerBuilder 10.2.1 Help

- DisableBind database parameter

- Setting a default column value when binding is disabled 

If you are not using bind variables (DisableBind set to 1) and want the

back-end DBMS to set a column to its default value when your application

user does not explicitly enter a value in a new row, you do not need to

set an initial value for the DataWindow column.

This is because with bind variables disabled, the DataWindow painter

generates a SQL INSERT statement for each row added to the DataWindow

object. If a column does not contain an explicit value, it is not

included in the SQL INSERT statement.

Using the Order_T table example, if your application user enters 123

as the value for the Order_ID column and A-123 as the value for the

Customer_ID column, the DataWindow painter generates the following

SQL INSERT statement when DisableBind is set to 1 (binding disabled):

INSERT INTO Order_T(Order_ID, Customer_ID)

        VALUES(123, 'A-123')

Your back-end DBMS would then set the Order_Date column to its default

value as expected, since a value for Order_Date is not explicitly set

in the SQL INSERT statement generated by the DataWindow painter.

=====

On the SQL Anywhere side of things, the handling of \n inside 'quoted string literals' has been documented in the same location of the Help ever since (at least) Watccom 5.5:

====

SQL Anywhere User's Guide

- PART 6. SQL Anywhere Reference

- CHAPTER 40. Watcom-SQL Language Reference

- Watcom-SQL language elements

string - Any sequence of characters enclosed in apostrophes ('single quotes').

An apostrophe is represented inside the string by two apostrophes in a row.

A new line character is represented by a backslash followed by an n (\n).

Hexadecimal escape sequences can be used for any character, printable or not.

A hexadecimal escape sequence is a backslash followed by an x followed by two

hexadecimal digits (for example, \x6d represents the letter m).

A backslash character is represented by two backslashes in a row (\\).

The following are valid strings:

'This is a string.'

'John''s database'

'\x00\x01\x02\x03'

=====

Sooo... the rule is, and has always been, this: If you don't want \n to be treated as a new line character then don't embed it inside a 'quoted string literal' in Watcom SQL.

Although there are a handful of exceptions, they don't apply to INSERT statements generated by PowerBuilder.

[glenn_barber]

Thanks Breck

What is documented in SQL Anywhere is the behavior of literal strings, what is documented in Powerbuilder is the effect of disable bind on defaults.

Nowhere does it say that if if you are using a data control (e.g datawindow) with disableBind=1, that your character text columns will be corrupted if the database sees character sequences that look like escapes.  This is the Open Manhole...

So while those that hand code their SQL statements and use literal strings to do so may have had more of a heads up on this, those that rely on data controls which obscure the syntax created may have had this happen without being immediately aware.

We've already been doing research on changing the disable bind in our applications and we confirmed the issue where our database defaults now fail to function despite the column being supplied with Null values.  That means every transaction that was relying on default database behavior has to have its defaults coded.  A serious amount of work in many hundreds of transactions.

There may also be situations where we either have to tolerate user entered text data being corrupted or the the application failing because of the size of the SQL statement generated. This may have been the reason we changed the disablebind in the first place and I'll have to do some historical research to see if this remains an issue..  Certainly scanning every text field and escaping the escape on the insert and update is not an option.

Personally, I was surprised that this issue has been known to SA development for so long without responding with a way to mitigate it by allowing it to be turned off or the \ escape mapped to a different character.  Your comment on this, which I quoted above, goes back some years.  It looks as if there is a feature to change the escape character and I have asked support for more information on it (the documentation warns against it).

Lately, as a SA partner, our annual partner support fees and customer license fees have skyrocketed, and the licensing processes ever more byzantine.  We noticed Microsoft SQL Server apparently does not have these interface issues and it is definitely fueling some re-evaluation of our technology choices.

Thanks again, I have always appreciated having you here in the forum.  I just wish the company itself took as much interest as you do.

[former_member188493]

It's too bad that PowerBuilder REPLACE doesn't work like SQL Anywhere's version (which does a search-and-replace of all occurrences of a substring). If it existed, you could use it to replace all occurrences of \ with \\ before the value was passed to SQL Anywhere inside the 'quoted string literal'... then, SQL Anywhere would replace every \\ pair with a single \ and you'd be good to go.

If memory serves, there is a DataWindow event where you can capture (and edit?) the actual SQL statement before it is sent to the DBMS.

...perhaps a combination of the SQLPreview event and SetSQLPreview method?

You are right about Transact SQL not having \n. Google searches reveal enormous numbers of Q&A discussions about the lack of \n and how hard it is to insert line feeds into SQL Server string literals ( pretty funny, given your experience : )

[glenn_barber]

We have a lot of functionality on our library - and could adapt the global replace function to do this - but we would have to write a service that we would install in our ancestor class datawindow that could inspect all character string columns prior to an insert or update for escape characters and if present escape them..  A fair amount of work with the impact of retesting a huge system.  Also string functions re not particularly fast - most windows based compilers inherit microsoft's routines which are famously slow - so one must consider the overall effect of adding this overhead to every transaction and the extensive retesting involved.

For the short term, I will need to address the use of rich text in few places, or extended multi line text fields that may have \.

One mitigating feature is that PB support says that we can dynamically change the disablebind behaviour during the unit of work and individually test.  Much easier than string replacement.  As it's very unlikely that the same connection will contend with itself in multiple transaction UOW's, this may be the most acceptable solution - but does require we assert defaults in the datawindow - or remove them from the update.

I would be OK with generally mapping the escape character to so obscure character - but not sure of the impact on the system.

No matter which we we go - looks like a fair amount of work to eliminate man holes...

What do you know about the size of the SQL buffer and how the use of bind variables affects the size of the transaction?

Also any tools for translating rich test to standard text?

[former_member188493]

I think prepared statements and bind variables make things go faster, otherwise why bother?

I don't know of any tools for translating rich text to standard text, but they must exist since Windows copy-and-paste does a pretty good job of preserving as much formatting as the target supports (e.g., copy and paste from browser to notepad).

[glenn_barber]

Hi Breck

After a lot of discussion with SQL Anywhere Support and PowerBuilder Support we reached the conclusion that we were pretty much on our own - I still feel that when using a data control like PowerBuilder and disabling Bind variables - there should be a way in the connection to turn off escape processing in the interface.  Otherwise you may find "\" entered by users in text fields (or hidden in RichText) being translated without much visibility.

With that in mind, on your suggestion, and the sample code from Victor Reinhart who also ran into this, we have coded the following which we plan to put in the datawindow sql preview event.  We've just started testing - I would appreciate any comment.

====================================================

// Datawindow SQL Preview Event

//  Fix (Escape) SQL Anywhere Escape: it changes \\ to \ for any text when DisableBind=1

integer li_rc

string ls_sqlsyntax, ls_sql

// If database is SQL Anywhere and we are doing an Insert or Update

// Then Escape the Escape Character

IF gs_db='SA' AND Request=PreviewFunctionUpdate! AND &

(SQLType=PreviewDelete! OR SQLType=PreviewUpdate! OR SQLType=PreviewInsert!) THEN

    IF Pos(sqlsyntax,'\')>0 THEN

        ls_sql=f_globalreplace(sqlsyntax,'\','\\')

        li_rc=SetSQLPreview(ls_sql)

    END IF

END IF

RETURN AncestorReturnValue

======================================================

I've created a simple function for global replace  f_globalreplace which should work to handle replacement in PB much like the SA version you mentioned - this is a simplified version of the pfc string object function.

// Global Function f_globalreplace

//////////////////////////////////////////////////////////////////////////////

//

// Function:   of_GlobalReplace

//

// Access:   public

//

// Arguments:

// as_Source The string being searched.

// as_Old The old string being replaced.

// as_New The new string.

//

// Returns:   string

// as_Source with all occurrences of as_Old replaced with as_New.

// If any argument's value is NULL, function returns NULL.

//

// Description:   Replace all occurrences of one string inside another with

// a new string.

//

//////////////////////////////////////////////////////////////////////////////

//

// Revision History

//

// Version

// 5.0   Initial version

//

//////////////////////////////////////////////////////////////////////////////

//

/*

* Open Source PowerBuilder Foundation Class Libraries

*

* Copyright (c) 2004-2005, All rights reserved.

*

* Redistribution and use in source and binary forms, with or without

* modification, are permitted in accordance with the GNU Lesser General

* Public License Version 2.1, February 1999

*

* http://www.gnu.org/copyleft/lesser.html

*

*/

//

//////////////////////////////////////////////////////////////////////////////

Long ll_Start

Long ll_OldLen

Long ll_NewLen

String ls_Source

//Check parameters

If IsNull(as_source) or IsNull(as_old) or IsNull(as_new)  Then

  string ls_null

  SetNull(ls_null)

  Return ls_null

End If

//Get the string lengths

ll_OldLen = Len(as_Old)

ll_NewLen = Len(as_New)

ls_source = as_source

//Search for the first occurrence of as_Old

ll_Start = Pos(ls_Source, as_Old)

Do While ll_Start > 0

  // replace as_Old with as_New

  as_Source = Replace(as_Source, ll_Start, ll_OldLen, as_New)

  ls_source = as_source

  // find the next occurrence of as_Old

  ll_Start = Pos(ls_Source, as_Old, (ll_Start + ll_NewLen))

Loop

Return as_Source

[former_member188493]

Caveat: I have not tested your code, or even checked it against the PB docs... but having said that, it looks OK. Depending on the size of the strings being edited and the frequency of slash characters (e.g., 10,000 slashes embedded in each of 1000 documents that are 100M in size) the as_Source = Replace statement may not perform as expected.

[glenn_barber]

Yes - Although the "\" is not common in most user entered data (but common in Rich Text" which is less commonly used) my biggest concern with this code is the speed of the initial pos statement searching through a significant volume of text.  (Although with disable bind only updated values are written).   As you say if there is a lot of text and "\" and a lot of replacement, this could impair performance.

Considering this, it may be wise to have some service flag in the control to be able to turn this off when required for significant volume of write activity.

A point to note, is that the underlying microsoft string routines from C++ that PB may be relying on internally are notoriously slow compared to other functions - to the point that microsoft does not recommend them in high volume situations.  There are various postings offering assembly replacements for such string handling.  This is why I would wish for PowerBuilder to address this as an option which is managed by the datawindow - or even more helpful, to have SQL Anywhere give us the ability to turn it off as other vendors have.

[former_member188493]

Yabbut... it is unlikely that your suggestion will be seen by anyone who matters, unless you post it as a new topic in this forum, or in the friendly easy-to-use forum.

[glenn_barber]

As a partner, we send them a lot of money every year - you'd think we could somehow get some attention.  I will give it one more try with support then maybe try a new post..

[former_member188493]

A lot of money in SAP's eyes? Wow! That's a lot of money, I can't even imagine how much money that might be! : )

Seriously, you'll might have better luck if you lose the attitude. I know that for a fact, I have a bad attitude myself and if it wasn't for the dunning letters, nobody at SAP would be paying any attention to me at all : )

[glenn_barber]

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.