Re: Simple question - can't find answer

zackary.evans@gmail.com wrote: > How do I return a set of data from a stored procedure? Basically all I > want to do is return the results of a select query which will contain > multiple rows. RFTM? Guide to SQL: Syntax > This does not work in informix, it gives error #659. I know it > basically means i have to select the result into another table, but > then how does that actually get returned? > > CREATE PROCEDURE test()> > SELECT * > FROM table> > END PROCEDURE CREATE PROCEDURE test() DEFINE i INTEGER, j CHAR(10), k DATE; FOREACH SELECT t.i, t.j, t.k INTO i, j, k FROM table AS t RETURN i, j, k WITH RESUME; END FOREACH; END PROCEDURE; Not validated by an IDS server - RTFM to get any details correct. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

I have returned multisets of a row-type before from an SPL stored procedure before but I couldn't get it to work with the JDBC driver so I tossed all of that work away. (it took a day to figure out the syntax to make it work correctly, someone smart could do it in much less time.) It seemed to give me what I wanted in dbaccess. I don't know how anything else would deal with it because I only needed it in JDBC. This was in 9.21. Jonathan Leffler wrote: > zackary.evans@gmail.com wrote: > > How do I return a set of data from a stored procedure? Basically all I > > want to do is return the results of a select query which will contain > > multiple rows. > > > RFTM? Guide to SQL: Syntax > > > This does not work in informix, it gives error #659. I know it > > basically means i have to select the result into another table, but > > then how does that actually get returned? > > > > CREATE PROCEDURE test()> > > > SELECT * > > FROM table> > > > END PROCEDURE > > CREATE PROCEDURE test()> > DEFINE i INTEGER, j CHAR(10), k DATE; > > FOREACH SELECT t.i, t.j, t.k INTO i, j, k FROM table AS t > RETURN i, j, k WITH RESUME; > END FOREACH; > > END PROCEDURE; > > Not validated by an IDS server - RTFM to get any details correct. > > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Hi :-) How did you try it with JDBC? If I just use the Stored Procedure Call like a Select, it gives me the resultset back and works just fine... Did it with 9.2 and 9.4 Regards, Dirk -- -- Dirk Gunsthoevel IT Systemanalyse phone: +49 (0)251 28446-0 -- Hammer Str. 13 fax: +49 (0)251 28446-55 -- D-48153 Muenster http://www.GunCon.de/ -- "Toto, I don't think we're in Kansas anymore..." "bozon" <curtis@crowson1.com> schrieb im Newsbeitrag news:1153751776.581459.12910@s13g2000cwa.googlegroups.com... >I have returned multisets of a row-type before from an SPL stored > procedure before but I couldn't get it to work with the JDBC driver so > I tossed all of that work away. (it took a day to figure out the syntax > to make it work correctly, someone smart could do it in much less > time.) It seemed to give me what I wanted in dbaccess. I don't know how > anything else would deal with it because I only needed it in JDBC. This > was in 9.21. > > > > Jonathan Leffler wrote: >> zackary.evans@gmail.com wrote: >> > How do I return a set of data from a stored procedure? Basically all I >> > want to do is return the results of a select query which will contain >> > multiple rows. >> >> >> RFTM? Guide to SQL: Syntax >> >> > This does not work in informix, it gives error #659. I know it >> > basically means i have to select the result into another table, but >> > then how does that actually get returned? >> > >> > CREATE PROCEDURE test()>> > >> > SELECT * >> > FROM table>> > >> > END PROCEDURE >> >> CREATE PROCEDURE test()>> >> DEFINE i INTEGER, j CHAR(10), k DATE; >> >> FOREACH SELECT t.i, t.j, t.k INTO i, j, k FROM table AS t >> RETURN i, j, k WITH RESUME; >> END FOREACH; >> >> END PROCEDURE; >> >> Not validated by an IDS server - RTFM to get any details correct. >> >> >> -- >> Jonathan Leffler #include <disclaimer.h> >> Email: jleffler@earthlink.net, jleffler@us.ibm.com >> Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ >

Can you show me an example? My java person couldn't get it to work properly. Thanks. Dirk Gunsthövel wrote: > Hi :-) > > How did you try it with JDBC? If I just use the Stored Procedure > Call like a Select, it gives me the resultset back and works just fine... > Did it with 9.2 and 9.4 > > Regards, > Dirk > > -- > -- Dirk Gunsthoevel IT Systemanalyse phone: +49 (0)251 28446-0 > -- Hammer Str. 13 fax: +49 (0)251 28446-55 > -- D-48153 Muenster http://www.GunCon.de/ > -- "Toto, I don't think we're in Kansas anymore..." > > > "bozon" <curtis@crowson1.com> schrieb im Newsbeitrag > news:1153751776.581459.12910@s13g2000cwa.googlegroups.com... > >I have returned multisets of a row-type before from an SPL stored > > procedure before but I couldn't get it to work with the JDBC driver so > > I tossed all of that work away. (it took a day to figure out the syntax > > to make it work correctly, someone smart could do it in much less > > time.) It seemed to give me what I wanted in dbaccess. I don't know how > > anything else would deal with it because I only needed it in JDBC. This > > was in 9.21. > > > > > > > > Jonathan Leffler wrote: > >> zackary.evans@gmail.com wrote: > >> > How do I return a set of data from a stored procedure? Basically all I > >> > want to do is return the results of a select query which will contain > >> > multiple rows. > >> > >> > >> RFTM? Guide to SQL: Syntax > >> > >> > This does not work in informix, it gives error #659. I know it > >> > basically means i have to select the result into another table, but > >> > then how does that actually get returned? > >> > > >> > CREATE PROCEDURE test()> >> > > >> > SELECT * > >> > FROM table> >> > > >> > END PROCEDURE > >> > >> CREATE PROCEDURE test()> >> > >> DEFINE i INTEGER, j CHAR(10), k DATE; > >> > >> FOREACH SELECT t.i, t.j, t.k INTO i, j, k FROM table AS t > >> RETURN i, j, k WITH RESUME; > >> END FOREACH; > >> > >> END PROCEDURE; > >> > >> Not validated by an IDS server - RTFM to get any details correct. > >> > >> > >> -- > >> Jonathan Leffler #include <disclaimer.h> > >> Email: jleffler@earthlink.net, jleffler@us.ibm.com > >> Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ > >

Ok. Since you are not the "java person" yourself I will give you a complete test program: Lets assume CREATE TABLE thetest(a SMALLINT, b SMALLINT), and some values INSERT INTO thetest VALUES(0,1); INSERT INTO thetest VALUES(1,2); INSERT INTO thetest VALUES(2,3); Now we create a very simple SP: create procedure test() RETURNING SMALLINT,SMALLINT;define m,n smallint; foreach select a,b into m,n from thetest RETURN m,n WITH resume; end foreach end procedure; The Java program could now look like the following: import java.lang.*; import java.sql.*; public class dbtest { static public void main(String[] args) { String jdbcdriver="com.informix.jdbc.IfxDriver"; String jdbcurl="url4yourdbserver"; Connection con=null; //DB Connect try { Class.forName(jdbcdriver).newInstance(); con=DriverManager.getConnection(jdbcurl);} catch( Exception e ) { e.printStackTrace(); System.out.println("connect to db failed"); return; } try{ Statement statement; System.out.println("selecting...."); String sql="EXECUTE PROCEDURE test()"; System.out.println("Statement: "+sql); statement=con.createStatement(); ResultSet rs=statement.executeQuery(sql); ResultSetMetaData meta=rs.getMetaData(); int n = meta.getColumnCount(); while (rs.next()) { for (int i=1; i<=n; i++) { String t=rs.getString(i); if (t==null) t=""; t=t.trim(); System.out.println(meta.getColumnName(i)+" : "+t); } System.out.println(""); } } catch (SQLException e){ System.out.println("db error"); e.printStackTrace(); return; } } } And its output is: selecting.... Statement: EXECUTE PROCEDURE test() (expression) : 0 (expression) : 1 (expression) : 1 (expression) : 2 (expression) : 2 (expression) : 3 If there are any questions left feel free to contact me. Dirk -- -- Dirk Gunsthoevel IT Systemanalyse phone: +49 (0)251 28446-0 -- Hammer Str. 13 fax: +49 (0)251 28446-55 -- D-48153 Muenster http://www.GunCon.de/ -- "Toto, I don't think we're in Kansas anymore..." "bozon" <curtis@crowson1.com> schrieb im Newsbeitrag news:1153759449.121706.150630@m73g2000cwd.googlegroups.com... Can you show me an example? My java person couldn't get it to work properly. Thanks. Dirk Gunsth'vel wrote: > Hi :-) > > How did you try it with JDBC? If I just use the Stored Procedure > Call like a Select, it gives me the resultset back and works just fine... > Did it with 9.2 and 9.4 > > Regards, > Dirk > > -- > -- Dirk Gunsthoevel IT Systemanalyse phone: +49 (0)251 28446-0 > -- Hammer Str. 13 fax: +49 (0)251 28446-55 > -- D-48153 Muenster http://www.GunCon.de/ > -- "Toto, I don't think we're in Kansas anymore..." > > > "bozon" <curtis@crowson1.com> schrieb im Newsbeitrag > news:1153751776.581459.12910@s13g2000cwa.googlegroups.com... > >I have returned multisets of a row-type before from an SPL stored > > procedure before but I couldn't get it to work with the JDBC driver so > > I tossed all of that work away. (it took a day to figure out the syntax > > to make it work correctly, someone smart could do it in much less > > time.) It seemed to give me what I wanted in dbaccess. I don't know how > > anything else would deal with it because I only needed it in JDBC. This > > was in 9.21. > > > > > > > > Jonathan Leffler wrote: > >> zackary.evans@gmail.com wrote: > >> > How do I return a set of data from a stored procedure? Basically all > >> > I > >> > want to do is return the results of a select query which will contain > >> > multiple rows. > >> > >> > >> RFTM? Guide to SQL: Syntax > >> > >> > This does not work in informix, it gives error #659. I know it > >> > basically means i have to select the result into another table, but > >> > then how does that actually get returned? > >> > > >> > CREATE PROCEDURE test()> >> > > >> > SELECT * > >> > FROM table> >> > > >> > END PROCEDURE > >> > >> CREATE PROCEDURE test()> >> > >> DEFINE i INTEGER, j CHAR(10), k DATE; > >> > >> FOREACH SELECT t.i, t.j, t.k INTO i, j, k FROM table AS t > >> RETURN i, j, k WITH RESUME; > >> END FOREACH; > >> > >> END PROCEDURE; > >> > >> Not validated by an IDS server - RTFM to get any details correct. > >> > >> > >> -- > >> Jonathan Leffler #include <disclaimer.h> > >> Email: jleffler@earthlink.net, jleffler@us.ibm.com > >> Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ > >