RE: SQL Stored Procedure

["Salter, James" <JSalter@xxxxxxxxxx>]

Thanks Charles for both Redbook links.  

Here is what I have figured out

I need a prepare statement for the SQL Statement

I also probably need to code the statement in (as you suggest) instead
of passing it.

We are getting a return set now, so we are on our way.

I do like having more control over things instead of allowing a 3rd
party to do whatever they please.

 

Thanks.
James Salter
Systems Programmer
American Cast Iron Pipe Company
phone (205) 325-3033
fax      (205) 307-3833
 

from: Charles Wilt <charles.wilt@xxxxxxxxx>
subject: Re: SQL Stored Procedure

James,

I guarantee that creating a stored procedure like your trying to do
isn't going to help.  In fact, I'd bet a steak dinner it'd hurt.

For a stored procedure to help, what you want to do is move the SQL
statements from the client app into stored procedures in the DB.  For
example, suppose the client app is doing:
  select fld1, fld2, fld3
  from myfile
  where fld4 = someVariable

You'd want to create an SP:

CREATE PROCEDURE LIBRARY/GETRESULTS
          (IN someVariable)
           RESULT SETS 1
           LANGUAGE SQL
            BEGIN
                  DECLARE C1 CURSOR
                          WITH RETURN TO CALLER
                                 FOR select fld1, fld2, fld3
                                           from myfile
                                   where fld4 = someVariable;
                        OPEN C1;
           END


The the client app simply calls the SP.

I like the above method as it adds a layer of abstraction into the
application.  The stored procedures function as a well defined
interface into the data.

Since you're probably past the point of rewriting the app to use
stored procedures, you need to make sure that the client app is using
prepared statements instead of dynamic statements.  My bet is they're
using dynamic.

If the client app doesn't have any ? or @variable in the statements
and is not calling the DeriveParameters() method, then it probably
means everything the client app is doing is being done dynamically.
This is a bad thing.

I recommend taking a look at the Redbook Integrating DB2 Universal
Database for iSeries with Microsoft ADO .NET
http://www.redbooks.ibm.com/abstracts/sg246440.html

HTH,
Charles
_________Confidentiality Notice_______________________
This e-mail and any files transmitted with it is
confidential and is intended solely for the use of
the individual(s) or entity(ies) to whom this e-mail
is addressed.  If you are not the intended recipient 
or the person responsible for delivering the e-mail 
to the intended recipient, be advised that you have
received this e-mail in error, and that any use,
disclosure, dissemination, forwarding, printing, 
retention or copying of this e-mail is strictly 
prohibited.  If you have received this e-mail in 
error, please immediately return this e-mail to 
the sender and delete the e-mail from your system.  
Thank you.