Calling a stored procedure!! 1

[Elton1984]

Greetings everyone..
I did a simple stored procedure which would enable mi to delete some records from the DB..
STORED PROCEDURE
----------------
CREATE Procedure purgeRecords
As
DELETE FROM tblLoan WHERE CollectionStatus = 'Uncollected'
GO
----------------

Can anyone help with calling this stored procedure from a JSP page?
Or is there any problems wif my stored procedure?

Thanx in advance
Elton Wong

 

[venur]

Hi,

Can call the strored procedure from java using CallableStatement.

CallableStatement cs;
    try {
      // Call a procedure with no parameters
        cs = connection.prepareCall("{call myproc}");
        cs.execute();
    
      // Call a procedure with one IN parameter
        cs = connection.prepareCall("{call myprocin(?)}");
    
        // Set the value for the IN parameter
        cs.setString(1, "a string");
    
        // Execute the stored procedure
        cs.execute();
    
      // Call a procedure with one OUT parameter
        cs = connection.prepareCall("{call myprocout(?)}");
    
        // Register the type of the OUT parameter
        cs.registerOutParameter(1, Types.VARCHAR);
    
        // Execute the stored procedure and retrieve the OUT value
        cs.execute();
        String outParam = cs.getString(1);     // OUT parameter
    
      // Call a procedure with one IN/OUT parameter
        cs = connection.prepareCall("{call myprocinout(?)}");
    
        // Register the type of the IN/OUT parameter
        cs.registerOutParameter(1, Types.VARCHAR);
    
        // Set the value for the IN/OUT parameter
        cs.setString(1, "a string");
    
        // Execute the stored procedure and retrieve the IN/OUT value
        cs.execute();
        outParam = cs.getString(1);            // OUT parameter
    } catch (SQLException e) {
    }

Hope this will help you...

Cheers
Venu