sqlstored-proceduresprepared-statementinformix

Execute Prepared Statement in a Stored Procedure in Informix

I am using Informix 12.10 on most of my systems, and I am trying to run this using dbaccess on a Windows server. I am trying to understand how to work with stored procedures and prepared statements. The code below is an example of what I am trying to do.

drop procedure if exists findIdReferences;
create procedure "uqed".findIdReferences(
    u_idnum like persons.idnum
)
    define my_count integer;
    
    define my_tablename like systables.tabname;
    define my_stmt lvarchar(2250);

    drop table if exists idnumTableSearch;
    create table idnumTableSearch (
        tableName   char(256) not null,
        count       integer not null
    );

    foreach 
        SELECT tabname
        into my_tablename
        FROM systables a, syscolumns b 
        WHERE a.tabid = b.tabid 
            and colname = "idnum"

        -- might be able to use a prepared statement here instead.
        let my_stmt = "select count(*) from " || trim(my_tablename) || " where idnum = " || u_idnum || ";";

        prepare p from my_stmt;
        execute p into my_count;

        if (my_count > 0) then
            insert into idnumTableSearch (tableName, count)
            values (my_tablename, count);
        end if

    end foreach;

    foreach
        select tableName, count
        into my_tablename, my_count
        from idnumTableSearch
        
        return my_tablename, mycount with resume;
    end foreach;
        
    drop table if exists idnumTableSearch;
end procedure;

I am getting a 201: A syntax error has occurred with the statement execute p into my_count. According to the documentation I found, that should work for a statement that just returns 1 row. Unfortunately, I am getting a syntax error on the execute. I don't see, or understand, what I am doing wrong with that execute statement. In C/C++ using ODBC, I could prepare the statement and bind the parameters to variables.

UPDATE: The documentation on this page says:

Tip: To execute a singleton SELECT, the EXECUTE...INTO statement is more efficient than using the DECLARE, OPEN, and FETCH statements.

Unfortunately, I have not gotten this to work properly.

Solution

  • The documentation for dynamic SQL in SPL is hit and miss. There is documentation on using DECLARE in SPL (https://www.ibm.com/docs/en/informix-servers/12.10?topic=statement-declaring-dynamic-cursor-in-spl-routine). The EXECUTE statement doesn't have a corresponding sub-section and the page on EXECUTE INTO is officially for ESQL/C, not SPL. I'm planning to report this as a documentation bug.

    Anyway, the tried and trusted method is to use: