Skip to main content
Question

How to put string literal in sql statement

  • January 31, 2019
  • 5 replies
  • 824 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!

 

5 replies

francis_m
Contributor
Forum|alt.badge.img+7
  • francis_mContributor
  • Contributor
  • 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
Evangelist
  • david_rEvangelist
  • 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
Evangelist
  • david_rEvangelist
  • January 31, 2019
francis_m wrote:

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
  • February 4, 2019
david_r wrote:

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

A StringConcatenator can help with creating SQL statements as well.

Reply


Helpful Members This Week

Community Stats

30,909
Posts
117,110
Replies
38,708
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
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.