Neoview Guide to Stored Procedures in Java (R2.3, R2.4)

PROJECTTEAM Procedure
The PROJECTTEAM procedure accepts a project code and returns the employee number, first
name, last name, and location of the employees assigned to that project.
Java Method: projectTeam()
Example A-11 projectTeam() Method
public static void projectTeam(int projectCode,
ResultSet[] members)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement getMembers = conn.prepareStatement(
" SELECT E.empnum, E.first_name, E.last_name, " +
" D.location " +
" FROM persnl.employee E, persnl.dept D, persnl.project P " +
" WHERE P.projcode = ? " +
" AND P.empnum = E.empnum " +
" AND E.deptnum = D.deptnum ");
getMembers.setInt(1, projectCode);
members[0] = getMembers.executeQuery();
}
Creating the Procedure: PROJECTTEAM
To create this procedure in the PERSNL schema, upload the Payroll.jar file to the Neoview
platform, navigate to the PERSNL schema in DB Admin, and then enter or select these values in
the Create Procedure Wizard of DB Admin. For instructions, see “Uploading SPJ JAR Files to
the Neoview Platform” (page 35) and “Creating SPJs” (page 43).
projectteamName:
Payroll.jar > Payroll > projectTeamCode:
• First SQL parameter:
— Name: projectcode
— Direction: IN
— SQL Data Type: INTEGER
— Java Data Type: int
Parameters:
• Dynamic result sets: 1
• Accesses Database: selected
Attributes:
Calling the Procedure: PROJECTTEAM
To invoke the PROJECTTEAM procedure in NCI:
SQL>call persnl.projectteam(5000);
The PROJECTTEAM procedure returns this information about the employees assigned to project
number 5000:
EMPNUM FIRST_NAME LAST_NAME LOCATION
------ --------------- -------------------- ------------------
65 RACHEL MCKAY NEW YORK
203 KATHRYN HALL NEW YORK
... ... ...
--- 6 row(s) selected.
--- SQL operation complete.
Procedures in the PERSNL Schema 97