Re: sp output to text file help

If you know the stored procedure return values it is easy even in dbaccess: create a temp table with the structure of returned result and the run 'insert into mytab execute procedure()' Then you can UNLOAD the data from temporary table. So, suppose you procedure myproc returns an integer and a char(60) data fields: dbaccess yourdbname <<EOF CREATE TEMP TABLE mytab (f1 INTEGER, f2 CHAR(60)); INSERT INTO mytab EXECUTE PROCEDURE myproc(); UNLOAD TO $filename SELECT * FROM mytab;EOF This works. At least in my 7.3 release. If you have 4GL (at least 6.x) it is simpler: LET str = "EXECUTE PROCEDURE myproc()" UNLOAD TO filename str And this works too. Checked. Best Regards, Octav On Wed, Sep 08, 1999 at 10:54:59PM -0700, Jonathan Leffler wrote: > Michael Talbot wrote: > > I have been asked to setup a shell script that will run > > about 10 sp's and save the output to a delimited text file. > > > > I am not sure if I can do something like the following: > > > > unload to 'a.txt' sp_sample_proc> > > > Does anyone have any suggestions? > > This was discussed a week or two ago. > > No, you cannot at the moment do an UNLOAD using a stored procedure > as the data generator instead of a SELECT statement. This is an > interesting oversight (it has been missing from the product for about > 9 years, but has only arisen as a question in the last month; I wonder > what that is telling us about technology transfer rates?). > > So, at the moment, you'd have to arrange for the stored procedures > to be select their data into a temp table and then UNLOAD those temp > tables. That too is probably non-trivial; AFAIK, you cannot do: > INSERT INTO temp_table EXECUTE PROCEDURE sp_sample_proc();> Nor can you do: > EXECUTE PROCEDURE sp_sample_proc() INTO TEMP temp_table;> (One reason for this is that there are no names for the returned > values, so the columns in the temp table wouldn't have names, but > that's a whole separate discussion.) > > So, you're likely to have to write some ESQL/C, or adapt some > ESQL/C (eg the sample code from the ESQL/C manuals, or the SQLCMD > program from the IIUG archives). I intend to add the UNLOAD facility > to SQLCMD at some point. > > -- > Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) > Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN > #include <disclaimer.h> -- Octav Chiriac Phone: (373) 2 22 99 67 NetInfo S.R.L. Fax: (373) 2 21 36 59 Chisinau (373) 2 22 84 88 Moldova, Republic of mailto:com@netinfo-moldova.com

Octav Chiriac wrote: > If you know the stored procedure return values it is easy > even in dbaccess: create a temp table with the structure of returned > result and the run 'insert into mytab execute procedure()' > Then you can UNLOAD the data from temporary table. So, suppose > you procedure myproc returns an integer and a char(60) data fields: > > dbaccess yourdbname <<EOF > CREATE TEMP TABLE mytab (f1 INTEGER, f2 CHAR(60)); > INSERT INTO mytab EXECUTE PROCEDURE myproc(); > UNLOAD TO $filename SELECT * FROM mytab;> EOF > > This works. At least in my 7.3 release. > > If you have 4GL (at least 6.x) it is simpler: > > LET str = "EXECUTE PROCEDURE myproc()" > UNLOAD TO filename str> > And this works too. Checked. That's useful to know. > On Wed, Sep 08, 1999 at 10:54:59PM -0700, Jonathan Leffler wrote: > > Michael Talbot wrote: > > > I have been asked to setup a shell script that will run > > > about 10 sp's and save the output to a delimited text file. > > > > > > I am not sure if I can do something like the following: > > > > > > unload to 'a.txt' sp_sample_proc> > > > > > Does anyone have any suggestions? > > > > This was discussed a week or two ago. > > > > No, you cannot at the moment do an UNLOAD using a stored procedure > > as the data generator instead of a SELECT statement. This is an > > interesting oversight (it has been missing from the product for > > about 9 years, but has only arisen as a question in the last month; > > I wonder what that is telling us about technology transfer rates?). > > > > So, at the moment, you'd have to arrange for the stored procedures > > to be select their data into a temp table and then UNLOAD those temp > > tables. That too is probably non-trivial; AFAIK, you cannot do: > > INSERT INTO temp_table EXECUTE PROCEDURE sp_sample_proc(); I didn't know far enough :-( > > Nor can you do: > > EXECUTE PROCEDURE sp_sample_proc() INTO TEMP temp_table;> > (One reason for this is that there are no names for the returned > > values, so the columns in the temp table wouldn't have names, but > > that's a whole separate discussion.) > > > > So, you're likely to have to write some ESQL/C, or adapt some > > ESQL/C (eg the sample code from the ESQL/C manuals, or the SQLCMD > > program from the IIUG archives). I intend to add the UNLOAD > > facility to SQLCMD at some point. I've done this -- it took 9 lines of code, I'm pleased to report. There is a new version 47 of SQLCMD on the IIUG web site which has this facility. Unfortunately, it also has a bug (independent of the change made for UNLOAD TO file EXECUTE PROCECEDURE). I will be sending SQLCMD 48 to the IIUG very soon with the bug fixed. The bug is that the code recognizes SET CONNECTION, but rejects any other SET statement as a syntax error in SET CONNECTION. This requires more than 9 lines to fix, unfortunately. SQLCMD 47 and above are autoconfiguring; that is, you should be able to type './configure; make' on pretty much any Unix platform and have the program build automatically. I expect to find some bugs in this -- please let me know. I'll probably send an independent announcement when all is ready. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>