I need to know how we can use dynamic sql in a stored procedure. Basically i need to use the procedure with insert & update clause. My users will use the procedure in java & pass the parameters for insert & in back-end i will insert the data into the table. Basically we don't want the users to give insert/update clause in their application. Please advice.
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.
--check for failure.
raise exception -746, 0, xtabname||": insert failed.";
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.";
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||"'" ;
if i=2 then
let x2="'"||x2||"'" ;
if i=3 then
let x3="'"||x3||"'" ;
if i=4 then
let x4="'"||x4||"'" ;
if i=5 then
let x5="'"||x5||"'" ;
if xcols=1 then
let xsql=xsql||x1 ;
if xcols=2 then
let xsql=xsql||x1||","||x2 ;
if xcols=3 then
let xsql=xsql||x1||","||x2||","||x3 ;
if xcols=4 then
let xsql=xsql||x1||","||x2||","||x3||","||x4 ;
if xcols=5 then
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 ;