Hi,

Any takers on this one (both parts would be great!).

A typical sql call I need to turn into a stored proc is:

SELECT id, title, ExpirationDateTime FROM forms INNER JOIN formcategory ON forms.id=formcategory.form_id WHERE category_id=1 ORDER BY UpperTitle;

where the category_id will vary. How do I get a result set back?

Thanks for any help,

David




---------------------- Forwarded by David Hay/Lex/Lexmark on 21/10/2003
05:31 PM ---------------------------


David Hay
20/10/2003 02:19 PM

To: firebird-support@yahoogroups.com
cc:

Subject: Stored Procedures? (Document link: David Hay)

Hi,

I have a question as to when using a stored procedure will give a speed
increase? If that's too general a question (!), I have an app that
primarily does reads on the database (once it is set up). It only has a
table of 100 records, another of 1000, another of about 20 and then foreign
key tables that connect them. So most of the queries are selecting on
joins, and often only the parameter is different. Hence I'm wondering if a
stored proc would help here (I would use prepared statements, but I am
using a connection pool, so there is no way, as far as I know, to prepare
the statements 'across the pool'!). I am trying to match the speed that we
saw on SAP, but Firebird 1.5 seems to be approx half as slow in my app. :-(

Also, I have searched the docs, but can't figure out how to return a result
set from a stored proc. Can anyone give me an example please (I'll call it
through JDBC).

Many thanks,

David