---------------------------------------
Using Dynamic Sql in Stored Procedure
---------------------------------------
vpshriyan
Location: Mumbai, India
---------------------------------------

One of the major drawback of Informix Stored Procedure is it's inability to perform dynamic SQL. A SP is parsed & optimized when compiled, preventing the use of dynamic SQL.
Here is an attempt to make a workaround stored procedure which attempts to insert a row for any table, any column dynamically. The inbound parameters are table name and dynamic number of column values.
Sample SP considers max of 5 column values, but you can edit it to suit according to your need. Data once passed, committed. There no provision to rollback. Should something go wrong, the exception handler pushes an internal rollback mechanism, hence data will not be committed. In such cases SP returns non-zero value, implying failure. Please note that the invoked SYSTEM command creates a separate transaction of its own.
The Informix SYSTEM statement provides the ability to execute any operating system command from within a stored procedure. A word of caution though, of the overhead required to execute an operating system command, the SYSTEM command should be avoided in routines with tight performance requirements. Use this procedure on your own cost and risk.

create dba procedure dynamic_insert(xtabname varchar(18),x1 varchar(64),x2 varchar(64),x3 varchar(64),x4 varchar(64),x5 varchar(64)) returning smallint;

define xowner, xdb varchar(18);
define xcols, xcoltype, xtabid, i smallint;
define xsql varchar(255);

on exception
--check for failure.
raise exception -746, 0, xtabname||": insert failed.";
return 1;
end exception

select owner,ncols,tabid into xowner,xcols,xtabid from systables where tabname=xtabname;
if xowner is null or xowner[1,1]=" " then
raise exception -746, 0, xtabname||" table does not exists.";
return 1;
end if

let xsql="insert into "||xtabname||" values (";

for i=1 to xcols
select coltype into xcoltype from syscolumns where tabid=xtabid and colno=i ;
if ( xcoltype=0 or xcoltype=7 or xcoltype=10 or xcoltype=13 ) then
if i=1 then
let x1="'"||x1||"'" ;
end if
if i=2 then
let x2="'"||x2||"'" ;
end if
if i=3 then
let x3="'"||x3||"'" ;
end if
if i=4 then
let x4="'"||x4||"'" ;
end if
if i=5 then
let x5="'"||x5||"'" ;
end if
end if
end for

if xcols=1 then
let xsql=xsql||x1 ;
end if
if xcols=2 then
let xsql=xsql||x1||","||x2 ;
end if
if xcols=3 then
let xsql=xsql||x1||","||x2||","||x3 ;
end if
if xcols=4 then
let xsql=xsql||x1||","||x2||","||x3||","||x4 ;
end if
if xcols=5 then
let xsql=xsql||x1||","||x2||","||x3||","||x4||","||x5;
end if

let xsql=xsql||")";

select trim(odb_dbname) into xdb from sysmaster:sysopendb
where odb_sessionid=dbinfo('sessionid') and odb_iscurrent='Y';

system 'echo "begin;set lock mode to wait;'||xsql||';commit"'|| '|$INFORMIXDIR/bin/dbaccess '||xdb ;
return 0;

end procedure;

Regards,
Shriyan

Last edited by vpshriyan on 12-09-03 at 07:52
-----