Using SQL escape characters in Powerbuilder inline SQL

Question

I am trying to replace single quote characters in an inline SQL statement.

In SSMS:

SELECT REPLACE('test''test', '''', '')
Result: testtest

In Powerbuilder:

SELECT REPLACE(fieldname, '''', '')
INTO :ls_string
FROM tablename;
Result: 'Incorrect syntax near ''.

Basically, Powerbuilder complains about syntax because it doesn't know what to do with SQL's escape character. If I use Powerbuilder's escape character instead ('~'' versus '''' as the second argument for REPLACE), SQL Server complains about open quotes.

Any suggestions are appreciated.

Matt Balent Matt Balent · Accepted Answer · 2018-11-08T17:27:14

You probably will have to either put the statement in a datawindow object (using the SQL quote escape method) which you use in the application as a datawindow or datastore OR do the SQL in a function or stored proc. If you go the function route you could call it in an imbedded SQL statement within Powerscript like your examples.

Something like

SELECT ufReplaceTwoQuotes INTO :ls FROM myTable USING SQLCA;

A third option would be to retrieve your data then do the replace within Powerscript (but then you have to wrestle with escaping the single quote within PowerBuilder).

Using SQL escape characters in Powerbuilder inline SQL

1 Answers