RE: SQL Stored Procedures

You should be able to execute something like this:

CREATE PROCEDURE SECLIB.test4
    (IN companyId CHAR(8)) language SQL
      DYNAMIC RESULT SETS 1
      BEGIN
      DECLARE stmt VARCHAR(256);
      SET stmt = 'select * from ' Concat Trim(companyId) Concat '.APYPFM01';
      PREPARE s1 from stmt;
      DECLARE C1 CURSOR FOR s1;
      OPEN C1;
      RETURN;
END

--- On Mon, 9/22/08, Gary L Peskin <garyp@xxxxxxxxxxxx> wrote:

From: Gary L Peskin <garyp@xxxxxxxxxxxx>
Subject: RE: SQL Stored Procedures
To: "'Java Programming on and around the iSeries / AS400'" <java400-l@xxxxxxxxxxxx>
Date: Monday, September 22, 2008, 2:17 PM

Wes --

You can PREPARE the SELECT statement using a host variable for the
companyID. Then, use the form of DECLARE CURSOR that references the
PREPAREd statement rather than the form containing the SELECT statement
itself. See Example 5 in the v5r4 InfoCenter under the topic "DECLARE
CURSOR" for an idea on how to do this.

HTH,
Gary

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx

[mailto:java400-l-bounces@xxxxxxxxxxxx]

On Behalf Of Wes Taylor
Sent: Monday, September 22, 2008 10:56 AM
To: Java Programming on and around the iSeries / AS400
Subject: RE: SQL Stored Procedures

Neill,

Right now we are building the SQL statement inside of a Java class with
the appropriate library and then submitting it.

We are setting 2 - 3 connections to the iSeries and executing 2-3 sql
statements. We thought we could speed things up with the stored
procedures. Doing all the sql selects in one connection with a single
stored procedure.

We just cannot figure out how to change the library inside the stored
procedure for our different companies.

I really didn't want to get into changing the liblist. May have to
though.

Thanks,

Wes Taylor
Senior Programmer/Analyst - Message Processing
Comporium Data Services
(803) 326-4115
wes.taylor@xxxxxxxxxxxxx