|
midrange.com
Hello Geert and Richard,
The following is from example code I have created.
Here is the CUSTMAST file definition:
.....A..........T.Name++++++RLen++TDp......Functions+++++++++++++++++++
A UNIQUE
R CUSTR
*
A CUSNBR 5P 0
A CUSNAM 30A
A CUSNMF 20A
A CUSNML 20A
A CUSAD1 30A
A CUSAD2 30A
A CUSAD3 30A
A CUSPCD 10A
A CUSST 3A
A PHONE 18A
A CUSTYP 1A
A DSCNT 5P 2
*
K CUSNBR
Here is a CREATE PROCEDURE statement:
CREATE PROCEDURE XRPG_CUST ( IN CUSTOMER CHAR(5),
IN POSTCODE CHAR(10),
IN STATE CHAR(3) )
EXTERNAL NAME XRPG_CUST
LANGUAGE RPGLE GENERAL;
Here is an example RPG IV Stored Procedure using a result set. (I also
have a comparable example in COBOL and SQL for those interested.)
/TITLE XRPG_CUST : CUSTMAST Example Program
**** Start of specifications ********************************************
*:PARMS COMMIT(*NONE) OPTION(*SQL *SYSVAL) DLYPRP(*YES) USRPRF(*USER) *
* *
* Module name . . . . . . . : XRPG_CUST *
* *
* Descriptive name . . . . : CUSTMAST Example *
* *
* Function . . . . . . . . : Stored procedure to return a result set *
* containing selected customer records. *
* *
* Input . . . . . . . . . . : cust - customer number char(5) *
* postcode - post code char(10) *
* state - state char(3) *
* *
* Output . . . . . . . . . : SQL result set *
* *
* Notes: *
* Dependencies . . . . . : *
* *
* Restrictions . . . . . : This program returns an SQL result set *
* containing the selected records. Valid *
* selection criteria are: *
* o customer number *
* o post code *
* o state *
* Only one value is used. The choice is: *
* if cust not blank then use it *
* else if postcode not blank then use it *
* else if state not blank then use it *
* else all blank so return all records *
* *
* Support . . . . . . . . : This example code is supplied on an *
* "as is" basis without any warranty *
* either express or implied. *
* *
* Change activity: *
* Rlse & *
* Flag Reason Level Date Pgmr Comments *
* ---- -------- ------ ------ ---------- ------------------------------ *
* $A0= VRM440 020226 SHC Initial coding of module. *
* *
* Change activity detail: *
* A0 - *
* *
**** End of specifications **********************************************
H DEBUG(*YES)
H DATEDIT(*YMD/)
H OPTION(*SRCSTMT : *NODEBUGIO)
H DFTACTGRP(*NO)
D XRPG_CUST PR
D cust 5
D postcode 10
D state 3
D XRPG_CUST PI
D cust 5
D postcode 10
D state 3
D sql S 512
C EXSR buildSQL
C EXSR prepareSQL
C EXSR openCursor
C EXSR rtnResultSet
C SETON LR
C RETURN
/EJECT
* ============================================================ *
* Subroutine: buildSQL *
* *
* To build the dynamic SQL statement *
* *
* ============================================================ *
CSR buildSQL BEGSR
C EVAL sql =
C 'SELECT CUSNBR, CUSNAM, CUSNML, -
C CUSNMF, CUSAD1, CUSAD2, -
C CUSAD3, CUSPCD, CUSST, -
C PHONE, CUSTYP, DSCNT -
C FROM CUSTMAST'
C SELECT
C WHEN ( %ADDR(cust) <> *NULL AND
C cust <> *BLANK )
C EVAL sql = %TRIMR(sql) + ' WHERE CUSNBR = ' +
C %TRIM(cust)
C WHEN ( %ADDR(postcode) <> *NULL AND
C postcode <> *BLANK )
C EVAL sql = %TRIMR(sql) + ' WHERE CUSPCD = ''' +
C %TRIM(postcode) + ''''
+
C ' ORDER BY CUSNBR'
C WHEN ( %ADDR(state) <> *NULL AND
C state <> *BLANK )
C EVAL sql = %TRIMR(sql) + ' WHERE CUSST = ''' +
C %TRIM(state) + '''' +
C ' ORDER BY CUSNBR'
C OTHER
C EVAL sql = %TRIMR(sql) + ' ORDER BY CUSNBR'
C ENDSL
C EVAL sql = %TRIMR(sql) + ' FOR FETCH ONLY'
CSR ENDSR
/EJECT
* ============================================================ *
* Subroutine: prepareSQL *
* *
* To prepare the dynamic SQL statement *
* *
* ============================================================ *
CSR prepareSQL BEGSR
C/Exec SQL
C+ Prepare STMT From :sql
C/End-Exec
CSR ENDSR
/EJECT
* ============================================================ *
* Subroutine: openCursor *
* *
* To open the SQL cursor *
* *
* ============================================================ *
CSR openCursor BEGSR
C/Exec SQL
C+ Declare CUSTOMER Cursor For STMT
C/End-Exec
C/Exec SQL
C+ Open CUSTOMER
C/End-Exec
CSR ENDSR
/EJECT
* ============================================================ *
* Subroutine: rtnResultSet *
* *
* To create a result set for the caller *
* *
* ============================================================ *
CSR rtnResultSet BEGSR
C/Exec SQL
C+ Set Result Sets Cursor CUSTOMER
C/End-Exec
CSR ENDSR
And finally, here is an (incomplete) example Java program to invoke the
stored procedure:
import java.sql.*;
public class TestXRPG_CUST
{
public static void main(String args[])
{
Connection c;
CallableStatement cs;
ResultSet rs = null;
String cust = " ";
String postcode = " ";
String state = " ";
switch (args.length)
{
case 3: state = args[2];
break;
case 2: postcode = args[1];
break;
case 1: cust = args[0];
break;
default:
break;
}
try
{
DriverManager.registerDriver( new com.ibm.db2.jdbc.app.DB2Driver()
);
c = DriverManager.getConnection("jdbc:db2://*LOCAL/your-lib;
naming=sql;errors;full;date format=iso;");
cs = c.prepareCall("CALL XRPG_CUST (?, ?, ?)");
cs.setString(1, cust);
cs.setString(2, postcode);
cs.setString(3, state);
rs = cs.executeQuery();
while(rs.next())
{
System.out.println(rs.getString(2));
}
cs.close();
c.close();
}
catch (SQLException e) { System.out.println(e); }
return;
}
}
Regards,
Simon Coulter.
--------------------------------------------------------------------
FlyByNight Software AS/400 Technical Specialists
http://www.flybynight.com.au/
Phone: +61 3 9419 0175 Mobile: +61 0411 091 400 /"\
Fax: +61 3 9419 0175 mailto: shc@flybynight.com.au \ /
X
ASCII Ribbon campaign against HTML E-Mail / \
--------------------------------------------------------------------
As an Amazon Associate we earn from qualifying purchases.
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.
