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
BedrockMission!

Learn More

View all

Sign in to view all badges

Single Quote causing problem in XPATH

Avatar

Avatar
Validate 1
Level 4
Aditya_S_
Level 4

Like

1 like

Total Posts

315 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile

Avatar
Validate 1
Level 4
Aditya_S_
Level 4

Like

1 like

Total Posts

315 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile
Aditya_S_
Level 4

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
Boost 500
Level 10
pguerett
Level 10

Likes

611 likes

Total Posts

7,689 posts

Correct Reply

692 solutions
Top badges earned
Boost 500
Boost 50
Boost 5
Boost 3
Boost 250
View profile

Avatar
Boost 500
Level 10
pguerett
Level 10

Likes

611 likes

Total Posts

7,689 posts

Correct Reply

692 solutions
Top badges earned
Boost 500
Boost 50
Boost 5
Boost 3
Boost 250
View profile
pguerett
Level 10

17-03-2009

Try using double quotes in your expression.

Avatar

Avatar
Validate 1
Level 4
Aditya_S_
Level 4

Like

1 like

Total Posts

315 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile

Avatar
Validate 1
Level 4
Aditya_S_
Level 4

Like

1 like

Total Posts

315 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile
Aditya_S_
Level 4

17-03-2009

Nope - No luck



Error :




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



Aditya

Avatar

Avatar
Boost 500
Level 10
pguerett
Level 10

Likes

611 likes

Total Posts

7,689 posts

Correct Reply

692 solutions
Top badges earned
Boost 500
Boost 50
Boost 5
Boost 3
Boost 250
View profile

Avatar
Boost 500
Level 10
pguerett
Level 10

Likes

611 likes

Total Posts

7,689 posts

Correct Reply

692 solutions
Top badges earned
Boost 500
Boost 50
Boost 5
Boost 3
Boost 250
View profile
pguerett
Level 10

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
Boost 5
Level 9
HowardTreisman
Level 9

Likes

5 likes

Total Posts

936 posts

Correct Reply

9 solutions
Top badges earned
Boost 5
Boost 3
Boost 1
Affirm 5
Affirm 3
View profile

Avatar
Boost 5
Level 9
HowardTreisman
Level 9

Likes

5 likes

Total Posts

936 posts

Correct Reply

9 solutions
Top badges earned
Boost 5
Boost 3
Boost 1
Affirm 5
Affirm 3
View profile
HowardTreisman
Level 9

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
Validate 1
Level 4
Aditya_S_
Level 4

Like

1 like

Total Posts

315 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile

Avatar
Validate 1
Level 4
Aditya_S_
Level 4

Like

1 like

Total Posts

315 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile
Aditya_S_
Level 4

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
Boost 5
Level 9
HowardTreisman
Level 9

Likes

5 likes

Total Posts

936 posts

Correct Reply

9 solutions
Top badges earned
Boost 5
Boost 3
Boost 1
Affirm 5
Affirm 3
View profile

Avatar
Boost 5
Level 9
HowardTreisman
Level 9

Likes

5 likes

Total Posts

936 posts

Correct Reply

9 solutions
Top badges earned
Boost 5
Boost 3
Boost 1
Affirm 5
Affirm 3
View profile
HowardTreisman
Level 9

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