topic Re: Missing escape character in Talend Studio

Missing escape character

Anonymous — Tue, 23 Apr 2019 21:08:15 GMT

I'm using the below statement in Talend, but it says that I'm missing an escape character.

"update "EMS"."PUBLIC"."MAILCHIMP"
set LATITUDE = replace (LATITUDE,'\'','')"

Re: Missing escape character

Anonymous — Wed, 24 Apr 2019 05:51:36 GMT

Hi,

Could you please try like below?

"update EMS.PUBLIC.MAILCHIMP
set LATITUDE = replace (LATITUDE,'\'','')"

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Missing escape character

Anonymous — Thu, 25 Apr 2019 15:17:20 GMT

When I tried it, it still errored out.

Re: Missing escape character

Anonymous — Thu, 25 Apr 2019 15:39:56 GMT

The problem is that the data in the field has a leading single quote. That's the character I'm trying to eliminate.

Re: Missing escape character

Anonymous — Fri, 26 Apr 2019 06:00:35 GMT

Hi,

Why don't you extract the data and do the replace in a tMap and push it back to table using primary key column?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Missing escape character

Anonymous — Fri, 26 Apr 2019 06:02:20 GMT

Hi,

Please also try the below method specified in the post.

https://stackoverflow.com/questions/5800448/remove-single-quote-from-the-column-value-in-oracle-sql

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Missing escape character

Anonymous — Fri, 26 Apr 2019 18:45:12 GMT

I can run the statement in the database, but not in Talend. When I run it in Talend I have a double quote at the end and the beginning of course, but I get this error

[statistics] connecting to socket on port 4080
[statistics] connected
[ERROR]: org.talend.components.snowflake.runtime.SnowflakeRowStandalone - Query execution has failed. Please validate your query.
net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
parse error line 1 at position 78 near '<EOF>'.
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:135)
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:60)
at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:503)
at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:370)
at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:474)
at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:230)
at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:172)
at net.snowflake.client.core.SFStatement.execute(SFStatement.java:663)
at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:161)
at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQuery(SnowflakeStatementV1.java:92)
at org.talend.components.snowflake.runtime.SnowflakeRowStandalone.runAtDriver(SnowflakeRowStandalone.java:79)
at local_project.ems_pga_audience_copy_copy_copy_0_1.EMS_PGA_AUDIENCE_Copy_Copy_Copy.tDBRow_1Process(EMS_PGA_AUDIENCE_Copy_Copy_Copy.java:1152)
at local_project.ems_pga_audience_copy_copy_copy_0_1.EMS_PGA_AUDIENCE_Copy_Copy_Copy.tDBConnection_1Process(EMS_PGA_AUDIENCE_Copy_Copy_Copy.java:547)
at local_project.ems_pga_audience_copy_copy_copy_0_1.EMS_PGA_AUDIENCE_Copy_Copy_Copy.runJobInTOS(EMS_PGA_AUDIENCE_Copy_Copy_Copy.java:1513)
at local_project.ems_pga_audience_copy_copy_copy_0_1.EMS_PGA_AUDIENCE_Copy_Copy_Copy.main(EMS_PGA_AUDIENCE_Copy_Copy_Copy.java:1326)
[statistics] disconnected

Job EMS_PGA_AUDIENCE_Copy_Copy_Copy ended at 12:40 26/04/2019. [exit code=0]