Invoke SPL using select * from table(SPL_NAME)

Informix version: 11.70FC7 OS : Linux One of my customer using java to connect to informix and it work well. Recently, he introduce a stored procedure with signature: CREATE PROCEDURE get_pending_round_robin(minutes_offset INT) RETURNING INT AS id, VARCHAR(255) AS name.... .... .... .... He was using the following method to invoke the stored procedure. SELECT id, name FROM table(get_pending_round_robin(?))(id, name) in his javacode and hit with the following error: Causing: org.skife.jdbi.v2.exceptions.UnableToCreateStatementException: java.sql.SQLException: Cannot determine the return types of a routine routine_name acting on a collection-derived table during PREPARE. [statement:"SELECT id, name FROM table(get_pending_round_robin(:since))(id, name)", located:"SELECT id, name FROM table(get_pending_round_robin(:since))(id, name)", rewritten:"SELECT id, name FROM table(get_pending_round_robin(?))(id, name)", arguments:{ positional:{}, named:{since:10000}, finder:[]}] I do remember this is due to the returning data type is not define with the invoke method and it need to cast/tell the java return data type. Just wonder any way to make this work? Thanks

Have you tried casting the returns ? Or get it to return a defined rowtype ? Cheers Paul Paul Watson Oninit www.oninit.com +1 913 387 7529 On unit® is a Registered Trademark of Oninit LLC On Feb 17, 2016, at 9:54 PM, LEY PATRICK <patrickley@gmail.com> wrote: > Informix version: 11.70FC7 > OS : Linux > > One of my customer using java to connect to informix and it work well. > > Recently, he introduce a stored procedure with signature: > > CREATE PROCEDURE get_pending_round_robin(minutes_offset INT) RETURNING INT AS > id, VARCHAR(255) AS name> ..... > ..... > ..... > ..... > > He was using the following method to invoke the stored procedure. > SELECT id, name FROM table(get_pending_round_robin(?))(id, name) in his java> code and hit with the following error: > > Causing: org.skife.jdbi.v2.exceptions.UnableToCreateStatementException: > java.sql.SQLException: Cannot determine the return types of a routine > routine_name acting on a collection-derived table during PREPARE. > [statement:"SELECT id, name FROM table(get_pending_round_robin(:since))(id, > name)", located:"SELECT id, name FROM > table(get_pending_round_robin(:since))(id, name)", rewritten:"SELECT id, name > FROM table(get_pending_round_robin(?))(id, name)", arguments:{ positional:{}, > named:{since:10000}, finder:[]}] > > I do remember this is due to the returning data type is not define with the > invoke method and it need to cast/tell the java return data type. > > Just wonder any way to make this work? > > Thanks > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.

Paul's nailed the answer: SELECT id::INT, name::VARCHAR(255) FROM table(get_pending_round_robin(?))(id, name) Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 17, 2016 at 10:54 PM, LEY PATRICK <patrickley@gmail.com> wrote: > Informix version: 11.70FC7 > OS : Linux > > One of my customer using java to connect to informix and it work well. > > Recently, he introduce a stored procedure with signature: > > CREATE PROCEDURE get_pending_round_robin(minutes_offset INT) RETURNING INT> AS > id, VARCHAR(255) AS name > ..... > ..... > ..... > ..... > > He was using the following method to invoke the stored procedure. > SELECT id, name FROM table(get_pending_round_robin(?))(id, name) in his> java > code and hit with the following error: > > Causing: org.skife.jdbi.v2.exceptions.UnableToCreateStatementException: > java.sql.SQLException: Cannot determine the return types of a routine > routine_name acting on a collection-derived table during PREPARE. > [statement:"SELECT id, name FROM table(get_pending_round_robin(:since))(id, > name)", located:"SELECT id, name FROM > table(get_pending_round_robin(:since))(id, name)", rewritten:"SELECT id, > name > FROM table(get_pending_round_robin(?))(id, name)", arguments:{ > positional:{}, > named:{since:10000}, finder:[]}] > > I do remember this is due to the returning data type is not define with the > invoke method and it need to cast/tell the java return data type. > > Just wonder any way to make this work? > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013a230ad457d6052c099995