Running a procedure in JDBC

[jason adam]

Basically, I was given a page with a bunch of SQL stuff on it, and asked to duplicate it in JDBC. The SQL commands are basically:

CREATE OR REPLACE PROCEDURE UpdateOncallList(
p_group IN VARCHAR2,
p_primary IN NUMBER,
p_secondary IN NUMBER,
p_tertiary IN NUMBER ) AS
v_primary_alpha VARCHAR( 10 );
... //bunch of statements like this
v_tertiary_type VARCHAR( 1 );
BEGIN
      UPDATE mon_employees SET emp_status = '0' WHERE emp_type = p_group ;
      COMMIT;
      ... //a couple more update statements
      SELECT emp_alpha_nbr, emp_numeric_nbr, ...
      //three lengthy select statements
      IF v_primary_type = 'A' THEN
         UPDATE mon_oncall SET oc_pgr1...
      ELSE
         UPDATE mon_oncall SET oc_pgr1...
      END IF;
      //a couple more if/else statements
      COMMIT;
END UpdateOncallList;

First time I've really done anything with stored procedures, so new territory. Looking at it, it seems like I could create a Statement to do the CREATE OR REPLACE part, the UPDATES, and then have 3 ResultSets containing info from the SELECT statements. I would then write 3 if/else blocks for the if/else stuff. However, after doing some reading through the forums here about CallableStatement, and other searches on the web, is that going about it the wrong way?
If someone could give advice on how to proceed, or point me at some good tutorials/references for a problem like this, would be greatly appreciated.
Thanks!
Jason
[ March 06, 2002: Message edited by: jason adam ]

[Jamie Robertson]

Jason: This is a good example of when a Stored Procedure should be used. You have a bunch of related queries and updates that need to be grouped together. If you move this procedure to java code, you will have to make numerous calls to the database (three times for each query and once for each update). So you can reduce the number of calls to the database by using your stored procedure and calling it from java using a CallableStatement. If you must convert it to pure java code then you can look at samples of using Statements, PreparedStatements
Jamie

[jason adam]

I figured as much, my problem is I a) don't understand PL/SQL procedures that much, though I get the general jist, and b) not sure how to put all of that stuff into a CallableStatement.
Would I first create a Statement that holds the "CREATE OR REPLACE PROCEDURE... v_tertiary_type VARCHAR2(1);", stuff, and then have a CallableStatement with the "BEGIN UPDATE mon_employees... END UpdateOncallList"? That way reducing the number of queries and updates to just two statements (a regular Statement and a CallableStatement).
I think what they were looking for was a way to write that entire procedure in JDBC, and do away with the whole procedure. But if it makes more sense to build the procedure in JDBC and execute it that way, I'll see if I can work it.

[Jamie Robertson]

if you already have the stored procedure compiled at the database, you don't need to create it, only call it (recommended). Then the only code you would need would be

Connection conn = //create your connection
conn.setAutoCommit(false);
 
CallableStatement cstmt = conn.prepareCall ("{call yourStoredProcedure (?, ?, ...)}");
 
cstmt.setString (1, "Some String");  
cstmt.setInt (2, 20000); 
//... set the rest of the input parameters in the 
//order the appear in the stored procedure
cstmt.execute(); 
conn.commit();

That should be it (exception catching not included) if the stored procedure is already compiled on the database side.
another way, if the stored procedure could not reside on the database is to do the following:

Statement stmt = conn.createStatement();
stmt.execute("CREATE OR REPLACE PROCEDURE UpdateOncallList....... END UpdateOncallList;");
//then execute the code that I posted above.

hope that clarifies it a little.
Of course, you could translate the procedure fairly easily into a java method as well. let me know if you need doing this, I know a little about PL/SQL and might be able to help in the translation.
Jamie

[Jamie Robertson]

a quick translation of your stored procedure:

private void updateOncallList(String p_group,  long or double p_primary, long or double p_secondary , long or double p_tertiary ) throws SQLException
{

String ASv_primary_alpha;
//bunch of statements like this
String v_tertiary_type;


Statement stmt = conn.createStatement();
stmt.executeUpdate("UPDATE mon_employees SET emp_status = '0' WHERE emp_type = '" + p_group + "'");
conn.commit();
//... 
//a couple more update statements      
String emp_query = "SELECT emp_alpha_nbr, emp_numeric_nbr, ...";
//three lengthy select statements      
if (v_primary_type.equals("A")) 
{

stmt.executeUpdate("UPDATE mon_oncall SET oc_pgr1...");
}
else
{
stmt.executeUpdate("UPDATE mon_oncall SET oc_pgr1...");
}

//a couple more if/else statements      
conn.commit();
}

had a few minutes,
Jamie

[jason adam]

Thanks Jamie! I'll probably go with the method, since I'm not sure about the structure of the database and whether or not the procedure is already compiled in there, and I think that is the route they want me to go anyways.
So basically, the method is replacing the need for the procedure?

[Jamie Robertson]

Originally posted by jason adam:
So basically, the method is replacing the need for the procedure?

Yes... the method is replacing the procedure entirely (not just the need!).

[Jamie Robertson]

one last note:
This method can only be called by java programs. So if you still need to call this stored procedure from the database, you'll have to create a Java Stored Procedure. Otherwise all is fine
Jamie

[jason adam]

Yeah, this is basically going to be intergrated into a web app, will be called by some custom tag. What it's for is when someone goes to a webpage, they will see a list of oncall people for that group. They have the ability to change the order of who is oncall (primary oncall person, etc.). When they submit, this method will be called to update the appropriate tables so that the monitoring software knows who to page for certain events.
Thanks again for the help
Jason