Re: Dynamic SQL

>From: scornd7@solomon.technet.sg (Tang Chang Thai) >Subject: Dynamic SQL >Date: 29 Jul 1994 03:39:25 GMT >X-Informix-List-Id: <news.7888> > >Hi ... I have this problem with dynamic SQL: >If I need to insert into a table t1 with a single character field, one way to >do it is as follows: >sprintf (buffer, "insert into t1 values ('%s')", "Sample") and get buffer >to be prepared and executed. In cases where the value is not static, ie the >value "Sample" is actually user-supplied, then the string may contain >characters like ', " and lf/cr. This character will then cause the buffer to >be messed up which confuses the dynamic SQL mechanism. >Is there any way to overcome this? This is close to an RTFM question, but we'll give you the benefit of the doubt. There are several things you could do. One is to use something other than sprintf() to format up the string; this alternative would escape any troublesome characters in the string. However, a much better solution is to use a statement with ? in place of the string literal you want inserted, and to then supply the value via an ordinary ESQL/C host variable: EXEC SQL BEGIN DECLARE SECTION; char *sample = "Sample"; EXEC SQL END DECLARE SECTION; EXEC SQL INSERT INTO T1 VALUES (:sample); Or: EXEC SQL BEGIN DECLARE SECTION; char buffer[40]; char *sample = "Sample"; EXEC SQL END DECLARE SECTION; sprintf(buffer, "insert into t1 values(?)"); EXEC SQL PREPARE p_insert FROM :buffer; EXEC SQL EXECUTE p_insert USING :sample; (Warning: code above not submitted to scrutiny of compiler.) You can get fancier using either SQLDA structures or DESCRIPTORS if you really want to, but they are both overkill for the example. Read the 5.00 ESQL/C Programmers' Manual, Chapter 1 'Programming with Informix-ESQL/C' (sketchy information on this subject) and Chapter 9 'Dynamic SQL statements and Management Techniques' (much more detailed). Also see the Informix Guide to SQL Reference Manual (Dec 1991) Chapter 7 'Syntax' on PREPARE, EXECUTE, DECLARE, DESCRIBE, FETCH, etc, and Chapter 6 'Using Descriptors'. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>