Hi all, Our experience about ASA9 TO SA12 migration is really a nightmare ... especially with regard to the sajdbc driver. After a lot of case express (serious memory bug), query rewriting (to make up for poor performance) last problem is: Updating a varchar column, any double backslash ("\\") is REPLACED with a single backslash ("\"). The same query, executed using the jconnect driver works fine (no replacement ....) What's going on? How I can disable it ? Thanks.

asked 29 Sep '12, 18:14

NCister's gravatar image

NCister
211111219
accept rate: 0%

edited 29 Sep '12, 18:16

Can you show us a JDBC sample of what you're describing? In general, backslashes in Java strings should be escaped.

(01 Oct '12, 11:57) Jeff Albion

I simply build UPDATE queries based on UI input. If the user types a double backslash in an input box the generated query is; "UPDATE [table] SET [column]='\\ .....' WHERE ....". This query, executed using JConnect driver works fine ... while executed using sajdbc4 is escaped !!

(03 Oct '12, 16:50) NCister
Replies hidden

using sajdbc4 is escaped !!

Just to understand: "Is escaped" would mean to me that the backslashes are "doubled", i.e. two backslashes in the input would be turned into four in the query and would then be stored as two in the database. - But from your question, I'd think the opposite does happen (i.e. two backslashes in the input would be turned into one.)

(03 Oct '12, 16:58) Volker Barth

It means that two backslashes in the input would be turned into one.

(04 Oct '12, 02:24) NCister

When you log in to SQL Anywhere, the system stored procedure "dbo.sp_login_environment()" will automatically be executed.

As you can see from the text of this procedure in the documentation, if you're using a "TDS" connection (e.g. jConnect), we will additionally call "dbo.sp_tsql_environment()".

Part of this specific "TSQL" stored procedure has the option:

SET TEMPORARY OPTION escape_character='OFF';

which is the reason for the behaviour differences you're observing.

If you wished your SAJDBC4 connection to "act" more like the jConnect behaviour, you could also call the sp_tsql_environment system stored procedure every time you log in to the database. You could automatically do this by changing the sp_login_environment stored procedure text to get rid of the "TDS" protocol check.


In reality though, the SAJDBC4 driver behaviour is correct from a Java point of view. You should quadruple-backslash any backslashes for correct input (\\ for the Java parsing and \\ for the SQL parsing). Without doing this, you may have trouble escaping other kinds of data (hex values, etc.).

permanent link

answered 04 Oct '12, 15:12

Jeff%20Albion's gravatar image

Jeff Albion
10.8k171175
accept rate: 25%

edited 05 Oct '12, 11:10

1

Good catch! ...this just reinforces my hatred of the sp_login_environment architecture, not because any of its settings are "right" or "wrong" but because they introduce hidden dependencies on the protocol, which in turn is a hidden setting (how many developers know what TDS is?)

Having said that, legacy considerations mean your hands are tied; i.e., nothing can be changed, and its all up to the docs, the training, and the vigilance of the developer.

(04 Oct '12, 16:50) Breck Carter
Replies hidden

Thank you Jeff ! Your suggestion works fine.

(05 Oct '12, 03:39) NCister
Replies hidden

Another interesting point: As to the docs, altering the setting of that option is not really recommended:

escape_character option
This option is reserved for system use. Do not change the setting of this option.

I surely would not alter a setting whose effect is not documented at all:)

(05 Oct '12, 04:14) Volker Barth

Feel free to "accept" Jeff's answer then...

(05 Oct '12, 04:23) Volker Barth

When the SQL Anywhere engine receives a statement that looks like this

   UPDATE [table] SET [column]='\\ .....' WHERE ....

the string literal '\\ .....' is processed according to this rule in SQL Anywhere 9

   ASA SQL Reference 
     SQL Language Elements 
       Strings
   ...
   To represent a backslash character, use two backslashes in a row (\\). 
   For example, 'c:\\temp'

and the same rule in SQL Anywhere 12: String literals

   A backslash character in a string must be escaped using an additional backslash, 
   as follows: 'c:\\november'

Is it possible that the code used with SQL Anywhere 9 was slightly different than the code used with SQL Anywhere 12? In particular, is it possible that a host variable was used instead of a 'string literal' with SQL Anywhere 9, thus avoiding the string-literal-escape-rule?

permanent link

answered 03 Oct '12, 17:05

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 03 Oct '12, 17:05

Thank you Breck ! At this point I think that the (great) JConnect driver replace any double backslash (\\) with a quadruple backslash (\\\\) to respect the original user input !!! .... What do you think about ?

(04 Oct '12, 10:16) NCister
Replies hidden

I think that would be surprising. Please show us the exact Java code that works with jconnect, and the exact Java code that doesn't work with the other driver.

(04 Oct '12, 10:39) Breck Carter
Comment Text Removed

The code is the same (our application) connected to the same DB (SA12). I've only changed the connection string (... and the driver, of course ....).

The code is something like:

....
Connection cn = DriverManager.getConnection("jdbc:sqlanywhere:uid=xxx; pwd=xxx; eng=sa12dbserv; dbn=xxx;");
CallableStatement cs = cn.prepareCall("UPDATE mgaa SET DesSec='\\' WHERE IDaa=38798");
int rc = cs.executeUpdate();
...
...
With JConnect in the code changes are only for connection string:
...
DriverManager.getConnection("jdbc:sybase:Tds:@srv:2638?ServiceName=xxx");
...

(04 Oct '12, 11:55) NCister

There is a way to fix it entirely on your end. Not sure if that is what you wanted, but it would solve the problem.

You could use a Decorator JDBC driver and transfer everything through it. A Decorator JDBC driver implements all of the methods of the JDBC specification, but most of them just pass through to the underlying driver. Then you can override certain methods, and put in code that would do whatever you want.

In your case, I would just override the Connection.createStatement() or Connection.prepareStatement() methods (depending on which ones you use to create the statement) to rewrite your SQL string. You could then search and replace all \\ with whatever you need.

I personally use Log4jdbc, which has the benefit of also providing logging support, and just overload the method I want. But there are several JDBC decorators out there, and YMMV.

permanent link

answered 03 Oct '12, 19:26

Jonathan%20Baker's gravatar image

Jonathan Baker
1961211
accept rate: 22%

Thank you Jonathan. Your suggestion is really precious ! ... becouse, as commented by Breck, the new sajdbc4 driver don't apply the same jconnect parsing rules ... so it requires an integration.

(04 Oct '12, 10:19) NCister
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:

×86
×35

question asked: 29 Sep '12, 18:14

question was seen: 3,029 times

last updated: 05 Oct '12, 11:10