how to change execution context in the SPL procedure

phan · #1 (**permalink**) 02-17-11, 10:42

Hi,

I am new to Informix. I just want to change the execution context in the SPL by:

Database myDBName;

however, it gave me an error:

Illegal SQL statement in stored procedure

Does anybody know how to change the execution context so that I do not need to prefix all object by "myDBName:" ?

Any help will be appreciated.

thanks,
Paul

InformixWilli · #2 (**permalink**) 03-11-11, 15:38

In 11.50 is thats not possible: "...you cannot close the current database or connect to a new database within an SPL routine."

SQL Statements Valid in SPL Statement Blocks

You must create the stored procedure in the right database or you write "<db>:<tabname>" or you try something like that:

Code:

create procedure test (db char(50)) returning int;
  define x int;
                   
  prepare xsql from 'select count(*) from '||db||':customer';
  declare xcur cursor for xsql;
  OPEN xcur;
  FETCH xcur into x;
  CLOSE xcur;
  FREE xcur;
  FREE xsql;
  
  return x;
end procedure;

execute procedure test("stores_demo");
execute procedure test("stores_demo_big");

Result:

Code:

(expression)

          28

(expression)

      458752

phan · #3 (**permalink**) 03-15-11, 10:46

thanks. That is really helpful.

Internet Services

Web Development

Digital Marketing

Consumer Tech