Highlighted

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

Highlighted

Avatar

Avatar

pguerett

Total Posts

7.7K

Likes

611

Correct Answer

692

Avatar

pguerett

Total Posts

7.7K

Likes

611

Correct Answer

692
pguerett

17-03-2009

Try using double quotes in your expression.
Highlighted

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
Highlighted

Avatar

Avatar

pguerett

Total Posts

7.7K

Likes

611

Correct Answer

692

Avatar

pguerett

Total Posts

7.7K

Likes

611

Correct Answer

692
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.
Highlighted

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
Highlighted

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
Highlighted

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