× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.