Single Quote causing problem in XPATH

Avatar

Avatar

Aditya_S_

Avatar

Aditya_S_

Aditya_S_

17-03-2009

The workflow was working fine until one of the fields had a single quote in the string and caused the update action to stall.



Using the Execute SQL query module I am updating data in an Oracle table. The query looks like this




UPDATE GSI_FEEDBACK_FORM_HEADERS



SET SITE_STREET = '{$ /process_data/xfaform/object/data/xdp/datasets/data/form1/Details/site_street $}',

SITE_STREET2 = '{$ /process_data/xfaform/object/data/xdp/datasets/data/form1/Details/site_street2 $}'




One of the forms had the street value
"Connah's Quay Deeside". I figured out that the single quote is causing the module to read it as the end of the XPATH, hence causing a "SQL command not ended properly" error.



Any suggestions to oversome this issue.



Aditya

Replies

Avatar

Avatar

pguerett

Avatar

pguerett

pguerett

17-03-2009

Try using double quotes in your expression.

Avatar

Avatar

Aditya_S_

Avatar

Aditya_S_

Aditya_S_

17-03-2009

Nope - No luck



Error :




ORA-00904: "1302 n 19th s't": invalid identifier



Aditya

Avatar

Avatar

pguerett

Avatar

pguerett

pguerett

17-03-2009

I searched the web and found a forum that discusses how to build a SQl statement with a quote in the query. Most DBs will expect you to escape it with a /. There are other techniques discussed here:



http://it.toolbox.com/wiki/index.php/How_do_I_escape_single_quotes_in_SQL_queries%3F



In either case it looks like you wll have to search you input string for such chars beforehand.

Avatar

Avatar

HowardTreisman

Avatar

HowardTreisman

HowardTreisman

21-03-2009

Hi

Just use a parameterized query, this solves the problem.

Something like:

UPDATE GSI_FEEDBACK_FORM_HEADERS

SET SITE_STREET = ?,

SITE_STREET2 = ?



Then put the xpath expressions into the table below as parameters 1 and 2.

Howard

http://www.avoka.com

Avatar

Avatar

Aditya_S_

Avatar

Aditya_S_

Aditya_S_

23-03-2009

Howard - Thank you so much for the awesome suggestion. It worked perfectly !!



You have saved me a lot of work.



Aditya

Avatar

Avatar

HowardTreisman

Avatar

HowardTreisman

HowardTreisman

07-04-2009

No problem

It also:

  • Makes your SQL look much neater.
  • You can type in "test" values for the parameters, so that you can test your SQL statement and see the results.

Howard

http://www.avoka.com