Skip to main content
Question

How to put string literal in sql statement

  • January 31, 2019
  • 5 replies
  • 1045 views
M
Forum|alt.badge.img

Hi,

 

so I have an update SQL query that the sql executor transformer executes. It looks like UPDATE *TABLE_NAME* SET COLUMN_NAME = @VALUE(ATTRIBUTE_1) WHERE OTHER_COLUMN_NAME = '@VALUE(ATTRIBUTE_2)'

 

The first attribute is always a number, but the second one is a string so I have to put quotes in. But, the string can contain quotes itself, which causes the query to fail for those cases. If I put double quotes around it I get a SQL error: it thinks I'm setting it equal to the value of another column in my database table instead of a FME attribute.

 

How do I put the whole string value of attribute 2 in my where clause, quotes and all?

Thanks!

 

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

5 replies

francis_m
Contributor
Forum|alt.badge.img+8
  • francis_mContributor
  • Contributor
  • 35 replies
  • January 31, 2019

To update in SQL your query must be like :

UPDATE <tbl_nm>

 

SET <att_nm1> = ( your attribute name)', <att_nm2> = ( your attribute name)'

 

FROM <tbl_nm>

 

WHERE<att_nm3> = '( your attribute name)'

If it is an integer, you dont need the quotes ('). But if your update or where statement is a string, the @Value() must be between quotes.

Then, if your string contains quotes, you must double them ...

example : 'It's cold outside' become 'It'' cold outside'

It look like in your string you dont have the FROM <tbl_nm> even if you specified it in the UPDATE statement.

 

Good luck!

david_r
Celebrity
  • david_rCelebrity
  • 8394 replies
  • January 31, 2019

If it's Oracle you can specify an alternative quote character to avoid collisions with quotes in your attribute values. Here's an example where the quote character is set to #:

 ... WHERE OTHER_COLUMN_NAME = q'#@VALUE(ATTRIBUTE_2)#' 

It's documented here: https://livesql.oracle.com/apex/livesql/file/content_CIREYU9EA54EOKQ7LAMZKRF6P.html

 

david_r
Celebrity
  • david_rCelebrity
  • 8394 replies
  • January 31, 2019

To update in SQL your query must be like :

UPDATE <tbl_nm>

 

SET <att_nm1> = ( your attribute name)', <att_nm2> = ( your attribute name)'

 

FROM <tbl_nm>

 

WHERE<att_nm3> = '( your attribute name)'

If it is an integer, you dont need the quotes ('). But if your update or where statement is a string, the @Value() must be between quotes.

Then, if your string contains quotes, you must double them ...

example : 'It's cold outside' become 'It'' cold outside'

It look like in your string you dont have the FROM <tbl_nm> even if you specified it in the UPDATE statement.

 

Good luck!

Which SQL variant is this? The regular SQL syntax for updating table contents usually does not include the FROM clause:

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE condition;

 

M
Forum|alt.badge.img
  • mvdorst
  • Author
  • 17 replies
  • February 4, 2019

If it's Oracle you can specify an alternative quote character to avoid collisions with quotes in your attribute values. Here's an example where the quote character is set to #:

 ... WHERE OTHER_COLUMN_NAME = q'#@VALUE(ATTRIBUTE_2)#' 

It's documented here: https://livesql.oracle.com/apex/livesql/file/content_CIREYU9EA54EOKQ7LAMZKRF6P.html

 

Thanks, this worked! 

nielsgerrits
VIP
Forum|alt.badge.img+60
  • nielsgerritsVIP
  • 2940 replies
  • February 4, 2019

A StringConcatenator can help with creating SQL statements as well.

Buckle your seatbelt Dorothy, cause Kansas is going bye-bye...

Community Stats

32,083
Posts
121,828
Replies
39,717
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.