×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

PREPARE A SQL STATEMEMT

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


RE: PREPARE A SQL STATEMEMT

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

RE: PREPARE A SQL STATEMEMT

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, "\") "

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close