Returning result sets from stored procedures... 
Author Message
 Returning result sets from stored procedures...
Can you return a result set from a stored procedure?
I'm trying to figure out how to use Crystal Reports with our Informix
database...
Crystal will only allow you to run one sql statement (as far as I can
see), and the report I want to create is more complicated than one sql
statement can manage.
-Ben


Sat, 05 Jun 2004 08:38:27 GMT
 Returning result sets from stored procedures...

Use SPL and/or get a decent report writer

Quote:

> Can you return a result set from a stored procedure?
> I'm trying to figure out how to use Crystal Reports with our Informix
> database...
> Crystal will only allow you to run one sql statement (as far as I can
> see), and the report I want to create is more complicated than one sql
> statement can manage.
> -Ben

--
Paul Watson             #          
Oninit Ltd              # Growing old is mandatory
Tel: +44 1436 672201    # Growing up is optional
Fax: +44 1436 678693    #
www.oninit.com          #


Sat, 05 Jun 2004 18:45:44 GMT
 Returning result sets from stored procedures...
Umm... ok, my original post was:

Quote:
>Can you return a result set from a stored procedure?
>I'm trying to figure out how to use Crystal Reports with our Informix
>database...
>Crystal will only allow you to run one sql statement (as far as I can
>see), and the report I want to create is more complicated than one sql
>statement can manage.
>-Ben

I was thinking that stored procedures were equivalent to spl...  I know you
use spl in stored procedures...
Anyway, my question is:
How do you get a stored procedure (spl) to return a result set instead of
certain predefined values?
I'm using informix 9.x dynamic server if that is important...
Please don't respond unless you can answer the question, or the thread gets
shut down because people think there was an answer...
-Ben
Quote:

> Use SPL and/or get a decent report writer


> > Can you return a result set from a stored procedure?
> > I'm trying to figure out how to use Crystal Reports with our Informix
> > database...
> > Crystal will only allow you to run one sql statement (as far as I can
> > see), and the report I want to create is more complicated than one sql
> > statement can manage.
> > -Ben

> --
> Paul Watson             #
> Oninit Ltd              # Growing old is mandatory
> Tel: +44 1436 672201    # Growing up is optional
> Fax: +44 1436 678693    #
> www.oninit.com          #



Sat, 05 Jun 2004 23:51:09 GMT
 Returning result sets from stored procedures...
you need to use return with resume and you then retrieve what is
technically known as sh1t loads of data.

create procedure a_name
returning some_data

        foreach select lots
        into somewhere
        from location  
        where as desired

        return somewhere with resume

        end foreach

end procedure

What do you mean by predefined values?  If you are asking can you
do dynamic SQL in SPL then yes if you use Paul Browns bladelet, you
can then return what ever you want.  Whether CR can understand
multisets etc is another matter - I doubt it

Quote:

> Umm... ok, my original post was:

> ?Can you return a result set from a stored procedure?
> ?I'm trying to figure out how to use Crystal Reports with our Informix
> ?database...
> ?Crystal will only allow you to run one sql statement (as far as I can
> ?see), and the report I want to create is more complicated than one sql
> ?statement can manage.
> ?-Ben

> I was thinking that stored procedures were equivalent to spl...  I know you
> use spl in stored procedures...
> Anyway, my question is:
> How do you get a stored procedure (spl) to return a result set instead of
> certain predefined values?
> I'm using informix 9.x dynamic server if that is important...
> Please don't respond unless you can answer the question, or the thread gets
> shut down because people think there was an answer...
> -Ben


> ? Use SPL and/or get a decent report writer
> ?

> ? ?
> ? ? Can you return a result set from a stored procedure?
> ? ? I'm trying to figure out how to use Crystal Reports with our Informix
> ? ? database...
> ? ? Crystal will only allow you to run one sql statement (as far as I can
> ? ? see), and the report I want to create is more complicated than one sql
> ? ? statement can manage.
> ? ? -Ben
> ?
> ? --
> ? Paul Watson             #
> ? Oninit Ltd              # Growing old is mandatory
> ? Tel: +44 1436 672201    # Growing up is optional
> ? Fax: +44 1436 678693    #
> ? www.oninit.com          #

--
Paul Watson             #          
Oninit Ltd              # Growing old is mandatory
Tel: +44 1436 672201    # Growing up is optional
Fax: +44 1436 678693    #
www.oninit.com          #


Sun, 06 Jun 2004 00:45:17 GMT
 Returning result sets from stored procedures...
    By predefined values I mean the type(s) that the stored procedure is declared
to return.
Which brings up another question, what is the type of "some_data" in your example
below?
I read some of the documentation, and saw that resume feature...  Didn't think it
would be useful in this context, guess I was wrong! :)  I don't know if you mean
what I mean when you say "dynamic sql"...
    My thought was to create a few temp tables from within the stored procedure,
(possibly calling other stored procedures along the way) and collect my final
results in another temp table.  Then return a select of that final temp table as
the result of the stored procedure.  Is this what you mean by dynamic sql?
    Another question, actually sounds like a completely new area that I have no
knowledge of...  What is Paul Browns bladelet?  Is that something to do with
using java stored procedures (I ask because of the similarity of the name to
applet).  Sounds like maybe something outside of the database server that will
call the stored procedure for you repeatedly, pakage up the results and send it
to you?
    In summary:
        What would you declare as the return type(s) of the stored procedure?
        How do you use Paul Browns bladelet?  Is it external to the database?
Where does it live?
    I would still like the answers to the previous questions, but
        It is sounding like Crystal Reports will fall short, do you have other
suggestions for reporting solutions?  The less expensive the better... :)

Thank you very much for all your help so far, and any in the future!
-Ben

Quote:

> you need to use return with resume and you then retrieve what is
> technically known as sh1t loads of data.

> create procedure a_name
> returning some_data

>         foreach select lots
>         into somewhere
>         from location
>         where as desired

>         return somewhere with resume

>         end foreach

> end procedure

> What do you mean by predefined values?  If you are asking can you
> do dynamic SQL in SPL then yes if you use Paul Browns bladelet, you
> can then return what ever you want.  Whether CR can understand
> multisets etc is another matter - I doubt it


> > Umm... ok, my original post was:

> > ?Can you return a result set from a stored procedure?
> > ?I'm trying to figure out how to use Crystal Reports with our Informix
> > ?database...
> > ?Crystal will only allow you to run one sql statement (as far as I can
> > ?see), and the report I want to create is more complicated than one sql
> > ?statement can manage.
> > ?-Ben

> > I was thinking that stored procedures were equivalent to spl...  I know you
> > use spl in stored procedures...
> > Anyway, my question is:
> > How do you get a stored procedure (spl) to return a result set instead of
> > certain predefined values?
> > I'm using informix 9.x dynamic server if that is important...
> > Please don't respond unless you can answer the question, or the thread gets
> > shut down because people think there was an answer...
> > -Ben


> > ? Use SPL and/or get a decent report writer
> > ?

> > ? ?
> > ? ? Can you return a result set from a stored procedure?
> > ? ? I'm trying to figure out how to use Crystal Reports with our Informix
> > ? ? database...
> > ? ? Crystal will only allow you to run one sql statement (as far as I can
> > ? ? see), and the report I want to create is more complicated than one sql
> > ? ? statement can manage.
> > ? ? -Ben
> > ?
> > ? --
> > ? Paul Watson             #
> > ? Oninit Ltd              # Growing old is mandatory
> > ? Tel: +44 1436 672201    # Growing up is optional
> > ? Fax: +44 1436 678693    #
> > ? www.oninit.com          #

> --
> Paul Watson             #
> Oninit Ltd              # Growing old is mandatory
> Tel: +44 1436 672201    # Growing up is optional
> Fax: +44 1436 678693    #
> www.oninit.com          #



Sun, 06 Jun 2004 02:00:42 GMT
 Returning result sets from stored procedures...

Ben,

I sure procedure like below will return result set.

In Crystal use 'execute procedure blah_blah( ... )'

create procedure blah_blah (
  f_1 type_1, ..., f_n type_m
) returning
  type_nn,
  ...
  type_mm;

define var_1...;

  foreach
     select a, ..., z
     from table_1, ..., table_n
     into var_1, ..., var_n
     where ...
         -- Do something
         return var_1, ..., var_M with resume; --- return row and continue
  end foreach;

end procedure;

And real example:

create procedure sequence (
  i_start int,
  i_end int
) returning
  int;
while i_start < i_end
      return i_start resume;
      let i_start = i_start + 1;
 end while;
end procedure;

Quote:
----- Original Message -----


Sent: Tuesday, December 18, 2001 6:51 PM
Subject: Re: Returning result sets from stored procedures...

> Umm... ok, my original post was:

> >Can you return a result set from a stored procedure?
> >I'm trying to figure out how to use Crystal Reports with our Informix
> >database...
> >Crystal will only allow you to run one sql statement (as far as I can
> >see), and the report I want to create is more complicated than one sql
> >statement can manage.
> >-Ben

> I was thinking that stored procedures were equivalent to spl...  I know
you
> use spl in stored procedures...
> Anyway, my question is:
> How do you get a stored procedure (spl) to return a result set instead of
> certain predefined values?
> I'm using informix 9.x dynamic server if that is important...
> Please don't respond unless you can answer the question, or the thread
gets
> shut down because people think there was an answer...
> -Ben


> > Use SPL and/or get a decent report writer


> > > Can you return a result set from a stored procedure?
> > > I'm trying to figure out how to use Crystal Reports with our Informix
> > > database...
> > > Crystal will only allow you to run one sql statement (as far as I can
> > > see), and the report I want to create is more complicated than one sql
> > > statement can manage.
> > > -Ben

> > --
> > Paul Watson             #
> > Oninit Ltd              # Growing old is mandatory
> > Tel: +44 1436 672201    # Growing up is optional
> > Fax: +44 1436 678693    #
> > www.oninit.com          #



Sun, 06 Jun 2004 01:38:17 GMT
 Returning result sets from stored procedures...

 I have not worked with Crystal Reports  but within the stored procedure
if  you return data with the RESUME clause ,  the stored procedure returns
a "result set" (ie: more than one row/record ).

 Having not worked with Crystal Reports, I can't say for sure that Crystal
Reports  won't let you do this.  Other reporting tools (eg IBI  Webfocus )
will.

  I'd check the documentation again.

ciao
p

******************************************************************************
Peter Wages

Work: (703) 741-7436   Home: (703) 528-9569
******************************************************************************

Quote:

> Umm... ok, my original post was:

> >Can you return a result set from a stored procedure?
> >I'm trying to figure out how to use Crystal Reports with our Informix
> >database...
> >Crystal will only allow you to run one sql statement (as far as I can
> >see), and the report I want to create is more complicated than one sql
> >statement can manage.
> >-Ben

> I was thinking that stored procedures were equivalent to spl...  I know you
> use spl in stored procedures...
> Anyway, my question is:
> How do you get a stored procedure (spl) to return a result set instead of
> certain predefined values?
> I'm using informix 9.x dynamic server if that is important...
> Please don't respond unless you can answer the question, or the thread gets
> shut down because people think there was an answer...
> -Ben


> > Use SPL and/or get a decent report writer


> > > Can you return a result set from a stored procedure?
> > > I'm trying to figure out how to use Crystal Reports with our Informix
> > > database...
> > > Crystal will only allow you to run one sql statement (as far as I can
> > > see), and the report I want to create is more complicated than one sql
> > > statement can manage.
> > > -Ben

> > --
> > Paul Watson             #
> > Oninit Ltd              # Growing old is mandatory
> > Tel: +44 1436 672201    # Growing up is optional
> > Fax: +44 1436 678693    #
> > www.oninit.com          #



Sun, 06 Jun 2004 02:02:02 GMT
 Returning result sets from stored procedures...

Quote:

>     By predefined values I mean the type(s) that the stored procedure is declared
> to return.
> Which brings up another question, what is the type of "some_data" in your example
> below?

Anything you want it to be

Quote:
> I read some of the documentation, and saw that resume feature...  Didn't think it
> would be useful in this context, guess I was wrong! :)  I don't know if you mean
> what I mean when you say "dynamic sql"...

In standard SPL you can't do

select * from variable

Quote:
>     My thought was to create a few temp tables from within the stored procedure,
> (possibly calling other stored procedures along the way) and collect my final
> results in another temp table.  Then return a select of that final temp table as
> the result of the stored procedure.  

As long as you don't want to run this on the web then it'll be OK, or
anything
that using multiplex'd connections.  I assume MaxConnect can get round
this
but I don't know.

Quote:
> Is this what you mean by dynamic sql?

No, see above

Quote:
>     Another question, actually sounds like a completely new area that I have no
> knowledge of...  What is Paul Browns bladelet?  

It's a bladelet that allows Dynamic SQL in SPL

Quote:
> Is that something to do with
> using java stored procedures (I ask because of the similarity of the name to
> applet).  

Nothing to do with Java but can be used from within JDBC, I assume

Quote:
> Sounds like maybe something outside of the database server that will
> call the stored procedure for you repeatedly, pakage up the results and send it
> to you?
>     In summary:
>         What would you declare as the return type(s) of the stored
>    procedure?

Don't know what are you trying to report on?  You can overload the
passed parameters to SPL, but I'm not aware of being able to the same
on the return set

Quote:
>         How do you use Paul Browns bladelet?  

Install it, look at the examples, scratch your head a bit and away you
go.
Dropping tables is problematic with DSPL

Quote:
> Is it external to the database?

Yes and No, it's a non standard piece of code but uses Informix's
extensibility functionality so when you call it it is part of the
database.

Quote:
> Where does it live?

Anywhere you want but normally in INFORMIXDIR/extend It can be
downloaded from www.iiug.org

Quote:
>     I would still like the answers to the previous questions, but
>         It is sounding like Crystal Reports will fall short, do you have other
> suggestions for reporting solutions?  The less expensive the better... :)

Look at Visionary.  A little bit of heresy is to use MS Access, just
link the tables in and then use the Access reporting capabilities.
OrificeConnect will let do get the data into Excel etc easily.

Quote:

> Thank you very much for all your help so far, and any in the future!
> -Ben

[cutting]

--
Paul Watson             #          
Oninit Ltd              # Growing old is mandatory
Tel: +44 1436 672201    # Growing up is optional
Fax: +44 1436 678693    #
www.oninit.com          #



Sun, 06 Jun 2004 02:50:52 GMT
 Returning result sets from stored procedures...
Thanks to everyone pointing me to the resume feature...
I had seen this in the documentation, but I thought I was still going to have to
call the stored procedure multiple times to get all of my data back.  Especially
thanks to Sergey here for this simple example that showed me that a single call
was sufficient!  I'm sure the Datablade technology is very interesting, and far
more powerful, but this is simple, and suits my purpose, so I will pursue it
until I hit another wall, thank you everyone! :)
-Ben
Quote:

> Ben,

> I sure procedure like below will return result set.

> In Crystal use 'execute procedure blah_blah( ... )'

> create procedure blah_blah (
>   f_1 type_1, ..., f_n type_m
> ) returning
>   type_nn,
>   ...
>   type_mm;

> define var_1...;

>   foreach
>      select a, ..., z
>      from table_1, ..., table_n
>      into var_1, ..., var_n
>      where ...
>          -- Do something
>          return var_1, ..., var_M with resume; --- return row and continue
>   end foreach;

> end procedure;

> And real example:

> create procedure sequence (
>   i_start int,
>   i_end int
> ) returning
>   int;
> while i_start < i_end
>       return i_start resume;
>       let i_start = i_start + 1;
>  end while;
> end procedure;

> ----- Original Message -----


> Sent: Tuesday, December 18, 2001 6:51 PM
> Subject: Re: Returning result sets from stored procedures...

> > Umm... ok, my original post was:

> > >Can you return a result set from a stored procedure?
> > >I'm trying to figure out how to use Crystal Reports with our Informix
> > >database...
> > >Crystal will only allow you to run one sql statement (as far as I can
> > >see), and the report I want to create is more complicated than one sql
> > >statement can manage.
> > >-Ben

> > I was thinking that stored procedures were equivalent to spl...  I know
> you
> > use spl in stored procedures...
> > Anyway, my question is:
> > How do you get a stored procedure (spl) to return a result set instead of
> > certain predefined values?
> > I'm using informix 9.x dynamic server if that is important...
> > Please don't respond unless you can answer the question, or the thread
> gets
> > shut down because people think there was an answer...
> > -Ben


> > > Use SPL and/or get a decent report writer


> > > > Can you return a result set from a stored procedure?
> > > > I'm trying to figure out how to use Crystal Reports with our Informix
> > > > database...
> > > > Crystal will only allow you to run one sql statement (as far as I can
> > > > see), and the report I want to create is more complicated than one sql
> > > > statement can manage.
> > > > -Ben

> > > --
> > > Paul Watson             #
> > > Oninit Ltd              # Growing old is mandatory
> > > Tel: +44 1436 672201    # Growing up is optional
> > > Fax: +44 1436 678693    #
> > > www.oninit.com          #



Sun, 06 Jun 2004 04:19:47 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. returning result sets from stored procedures

2. returning result sets from stored procedures

3. return result set from stored procedure

4. return result set from stored procedure

5. Return result set from stored procedure

6. Return result set from stored procedure, possible ?

7. Returning Result Sets from Stored Procedures?

8. Returning result sets in oracle stored procedure (OLEDB)

9. return multiple result sets from a stored procedure

10. Can't update result set returned from stored procedure using RDO

11. Returning result set from function to stored procedure is very slooooooooow

12. best way to return a paged result set from a stored procedure


 
Powered by phpBB® Forum Software