PREPARE A SQL STATEMEMT 1

[ttkong]

Hi all,

I have the following 4GL codes:

LET str = "INSERT INTO ", local_path, ":table1",
" VALUES ('", client_name, "' &quot

PREPARE qid FROM str
EXECUTE qid

It executes perfectly. However, when there is a single quote (') in "client_name" variable, Syntax Error would occur.

For example,
if client_name = "King's Name",
str = INSERT INTO db@dbtcp:table1 VALUES ('King's Name')

The system would take "King" as the first input field, and the subsequent "s Name'" as invalid syntax.

I need to know is there any way to resolve the above problem. It is urgent. Thank you very much.

Rgrds,
TTKong

 

[ErhardEiselen]

Hi

The best way is to change your statement to read as follows:

LET str = "INSERT INTO ", local_path, ":table1",
" VALUES (?) "

The "?" acts as a place holder for a program variable you
will be using during the EXECUTE statement.

The PREPARE statement stays the same, but the execute statement then changes by using a program variable, e.g.

EXECUTE qid using m_client_name

This also a cleaner way of doing it.

Hope this helps

 

[olded]

Hi:

Interesting problem. First, Erhard's post is correct; Use host variables.

But, if for some reason, you don't, use double quotes and escape them when building str instead of single quotes:

LET str = "INSERT INTO ", local_path, ":table1",
" VALUES (\"", client_name clipped, "\&quot "

Of course, potentially you still have the same problem with the double quote. I haven't figured a way to escape " in the insert statement, so delete or replace it before building str:

LET client_name = replace(client_name, "\"", " &quot

(The 4GL replace function mentioned above exists in the 4GL FAQ)

Erhard's suggestion is still the way to go.

Regards,

Ed