Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

Single Quote causing problem in XPATH

Aditya_S_
Level 7
Level 7
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
6 Replies
pguerett
Level 10
Level 10
Try using double quotes in your expression.
Aditya_S_
Level 7
Level 7
Nope - No luck



Error :




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



Aditya
pguerett
Level 10
Level 10
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.
HowardTreisman
Level 9
Level 9
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
Aditya_S_
Level 7
Level 7
Howard - Thank you so much for the awesome suggestion. It worked perfectly !!



You have saved me a lot of work.



Aditya
HowardTreisman
Level 9
Level 9

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