2

IBM Informix Dynamic Server Version 11.50.UC5XA.

I have a Informix database. This database contain a stored procedure with name of 'sp_agent_details' that get two DateTime Parameter. This stored procedure results about 27 columns and I want only few columns of it for reporting. I try this syntax:

select AGENT_NAME, AGENT_LOGIN_ID from 
       TABLE(sp_agent_detail('2014-02-04 04:00:00', '2014-02-04 23:00:00'))

This Result such Error:

java.sql.SQLException: Illegal SQL statement in SPL routine.

Next I try this syntax:

select AGENT_NAME, AGENT_LOGIN_ID from 
       TABLE(MULTISET{sp_agent_call_summary 
    ('2014-02-04 04:00:00', '2014-02-04 23:00:00')})

Result:

 java.sql.SQLException: Function (informix.sp_agent_call_summary) 
returns too many values.

then I tried this:

select AGENT_NAME, AGENT_LOGIN_ID from 
   TABLE(sp_agent_detail('2014-02-04 04:00:00', '2014-02-04 23:00:00'))
   (AGENT_NAME, AGENT_LOGIN_ID, other columns that are returned by SP)

Result:

java.sql.SQLException: Illegal SQL statement in SPL routine.
  • Do you have any triggers on your table?\ – Mihai Mar 8 '14 at 10:16
  • @Mihai I do'nt write this Stored Procedure. I used an application and want to use it's functions. – Hojjat Jashnniloofar Mar 9 '14 at 5:53
3

Here is a illustration how works..
This was executed with dbaccess at Informix 11.50 FC9. You can name your return parameters, but they are valid only when executed the procedure as procedure by self... not into select statement. There you need to name each column manually... check below.

References at IBM Informix 11.50 manual:


create procedure dummy ( param integer )
returning int as id , char(10) as name, date as created
define vid int;;
define vname char(10);;
define vcreate date;;

foreach c1 for 
  select first 10 tabid, tabname, created 
     into vid, vname, vcreate 
     from systables where tabid >= param

  return vid , vname, vcreate with resume ;;
end foreach ;;

end procedure
;
Routine created.

;

execute procedure dummy(10);

         id name       created

         10 syssyntabl 20/02/2014
         11 sysconstra 20/02/2014
         12 sysreferen 20/02/2014
         13 syschecks  20/02/2014
         14 sysdefault 20/02/2014
         15 syscoldepe 20/02/2014
         16 sysprocedu 20/02/2014
         17 sysprocbod 20/02/2014
         18 sysprocpla 20/02/2014
         19 sysprocaut 20/02/2014

10 row(s) retrieved.


select  * from table(dummy(10));

unnamed_col_1 unnamed_col_2 unnamed_col_3

           10 syssyntabl    20/02/2014
           11 sysconstra    20/02/2014
           12 sysreferen    20/02/2014
           13 syschecks     20/02/2014
           14 sysdefault    20/02/2014
           15 syscoldepe    20/02/2014
           16 sysprocedu    20/02/2014
           17 sysprocbod    20/02/2014
           18 sysprocpla    20/02/2014
           19 sysprocaut    20/02/2014

10 row(s) retrieved.


select  * from table(dummy(10)) as x(id,name,created);

         id name       created

         10 syssyntabl 20/02/2014
         11 sysconstra 20/02/2014
         12 sysreferen 20/02/2014
         13 syschecks  20/02/2014
         14 sysdefault 20/02/2014
         15 syscoldepe 20/02/2014
         16 sysprocedu 20/02/2014
         17 sysprocbod 20/02/2014
         18 sysprocpla 20/02/2014
         19 sysprocaut 20/02/2014

10 row(s) retrieved.


select  x.id,name from table(dummy(10)) x(id,name,created);

         id name

         10 syssyntabl
         11 sysconstra
         12 sysreferen
         13 syschecks
         14 sysdefault
         15 syscoldepe
         16 sysprocedu
         17 sysprocbod
         18 sysprocpla
         19 sysprocaut

10 row(s) retrieved.



Database closed.
  • Your Answer is correct but this statement could not solve my problem. I use this statement and I also received an error. java.sql.SQLException: Illegal SQL statement in SPL routine. – Hojjat Jashnniloofar Mar 9 '14 at 5:40
  • Hi @Hojjat , I put fews statements there... can you tell me what you tried to use? – ceinmart Mar 10 '14 at 11:37
  • I used select x.id,name from table(dummy(10)) x(id,name,created); – Hojjat Jashnniloofar Mar 12 '14 at 4:25
  • @Hoj, Did you tested your SQL at any SQL Client? Did you observed the field aliases informed after the table() should match with the all fields generated by the procedure and the "filter" will be done at 'select' projection? – ceinmart Mar 12 '14 at 15:33
2

I had exactly the same problem! I was trying to report on UCCX and use the output of the stored procedures to populate new tables. I got either Illegal SQL Statement in SPL routine or returns too many values.

After some thought I came to the conclusion the only way to achieve this is to re-write the SPLs using JOINS rather than UPDATES. You can then use a SELECT to populate tables as needed. The problem is the linear way the SPLs have been written in. I think they were translated straight over to Informix from SQL server when UCCX was ported to RHEL from Windows.

0

The "SQLException: Illegal SQL statement in SPL routine" error is due to the inclusion of certain statements in the stored procedure that cannot be used when called from a DML statement. Here's the Informix document.

A workaround is to execute the stored procedure, then fetch from it. This is not as elegant as your SELECT FROM TABLE() would have been, but it works.

Here's how I did it in Python; presumably, something similar would work in Java.

cur.execute("{call sp_agent_detail('2014-02-04 04:00:00', '2014-02-04 23:00:00')}")
for row in cur.fetchall():
    for field in row:
        whatever_you_need_to_do_with_the_fields
0

Encountered similar situation while trying to select the results of a SP.

The problem in my case was the existence of create/drop temporary table statements inside the stored procedure code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.