Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003

    Unanswered: Using Dynamic Sql in Stored Procedure

    Hi All,

    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.



  2. #2
    Join Date
    Apr 2003
    Phoenix, AZ
    No you can't.
    Fred Prose

  3. #3
    Join Date
    Dec 2003
    Are you sure? And do you know if we can call another procedure in a dynamic way from a procedure?

  4. #4
    Join Date
    Nov 2003
    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;

    Last edited by vpshriyan; 12-09-03 at 04:52.

  5. #5
    Join Date
    Dec 2003
    thanks a lot for your extense answer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts