RE: Stored Procedure Help

To close the cursor do rs.close() before your pstmt.close()

If select will only return a single row, you can use the SELECT INTO to read
the data into a set of variables, rather than as a result set. In this case
you could then return the variables as output parms to your stored
procedure.

-----Original Message-----
From: Smith, Mike [mailto:Mike_Smith@xxxxxxxxxxxxxxxx]
Sent: 24 March 2004 14:07
To: java400-l@xxxxxxxxxxxx
Subject: Stored Procedure Help


I'm trying to learn Java and as a byproduct i'm learning about Stored
Procedures also.  I have a small java program that i'm trying to convert
the data base functions to Stored procedures.  I have a couple of
questions.   First, i'll list my first Stored procedure that i created
via STRSQL. 

create procedure SPgetInv (IN Item CHAR(5))                         
result sets 1 language sql begin                                    
declare itemid char(5);                                             
declare at_end int default 0;                                       
 declare                                                            
c1 cursor with return for select * from Inventory where itemid =    
item; open c1;                                                      
 end        

here is the code where i call the procedure. 
public static Inventory findInventory(String itemID) {
if(!isConnected)
{
connect();
isConnected=true;
}
String stored = "{call " + "SPGETINV(?)" + "}";
PreparedStatement pstmt;
String query = " Select * from inventory where itemId = ?";
Inventory Inv =null;
try
{
pstmt = aConnection.prepareCall(stored);
pstmt.setString(1,itemID); 
ResultSet rs = pstmt.executeQuery();
while (rs.next())
{
// Create the next client object, and load the fields.
Inv = new Inventory();
Inv.setItemId(rs.getString("itemId") );
Inv.setItemName(rs.getString("itemName") );
Inv.setShortDesc(rs.getString("shortDesc"));
Inv.setLongDesc(rs.getString("longDesc"));
Inv.setPrice(rs.getFloat("price") );
Inv.setPricePerd(rs.getString("pricePerD") );
Inv.setImageUrl(rs.getString("imageUrl") );
Inv.setCategory(rs.getString("category") );
Inv.setOnHand(rs.getInt("onHand") );
} 
Inventory retval = Inv;
pstmt.close();
return retval;
}
catch(SQLException ex)
{
System.out.println("Exception in DBRoutines.findInventory
method...Insert into orders ");
System.out.println(ex);
return null;
}


Now, the first time i call this procedure it works fine, but when it
gets called a second time, i get a 'cursor already open' message.
Where/How do i close the cursor.  Do i create a seperate procedure, do i
include it in this procedure?    

Secondly, since i'm only going to be selecting 1 record at any time from
the Inventory file, i suspect i should be creating a cursor(i know i
wouldn't if i were doing embedded sql in an rpg program).  How would i
create this procedure to select only 1 record.      

Any other tips,suggestions welcome.

Thanks 

Mike                                                    

_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.


=======DISCLAIMER=======
This email transmission and any attachments may contain confidential
information which is legally privileged.  You are also notified that the
contents of this email and any which you may send in reply, may be viewed by
authorised personnel on behalf of NSB Retail Systems Plc or its subsidiaries
(the Company) in accordance with the Company's Information Access Policy.
The content of this email is always subject to contract.  The information is
intended only for the individual or entities to whom it is addressed.  If
you are not an intended recipient please contact me immediately and you are
hereby notified that any disclosure, copying, distribution, storage on any
medium or use of the information contained in this transmission is strictly
prohibited.