Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2011
    Posts
    5

    Unanswered: how to change execution context in the SPL procedure

    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

  2. #2
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    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
    WilhelmW

  3. #3
    Join Date
    Feb 2011
    Posts
    5
    thanks. That is really helpful.

Posting Permissions

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