PREPARE A SQL STATEMEMT
PREPARE A SQL STATEMEMT
(OP)
Hi all,
I have the following 4GL codes:
LET str = "INSERT INTO ", local_path, ":table1",
" VALUES ('", client_name, "' ")
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
I have the following 4GL codes:
LET str = "INSERT INTO ", local_path, ":table1",
" VALUES ('", client_name, "' ")
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
Talk To Other Members
RE: PREPARE A SQL STATEMEMT
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
RE: PREPARE A SQL STATEMEMT
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, "\") "
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, "\"", " ")
(The 4GL replace function mentioned above exists in the 4GL FAQ)
Erhard's suggestion is still the way to go.
Regards,
Ed