Stored procedures: hints & tips

Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.

This set of topics includes tips to help you

Discussion of stored procedure escape sequences, SQL statements, and server-specific procedure calls

When entering information in the Stored Procedure Escape or SQL Statement field in the procedure property editor, or in code, you have three options for the type of statement to enter. These are

In both of the last two examples, the parameter markers, or question marks, may be replaced with named parameters of the form :ParameterName. For an example using named parameters, see "Example: using parameters with Oracle PL/SQL stored procedures". For an example using InterBase stored procedures, see "Example: using InterBase stored procedures".


Creating tables and procedures for the tutorial manually

Stored procedures consist of a set of SQL statements. These statements can easily be written and compiled in JBuilder by creating a Java file, entering the statements, then compiling the code. If you do not have access to the sample project SimpleStoredProcedure or if you would like to learn how to create a table and insert, update, and delete procedures from JBuilder, follow these steps:
  1. Select File|Close All from the menu.

  2. Select File|New Project.

  3. Change the file directory and project name to SimpleStoredProcedure/ProcSetUp/ProcSetUp.jpr in the Project Wizard.
  4. Select File|New, then select Class.

  5. Change the Class Name to ProcSetUp in the Class wizard. Click OK to create the file ProcSetUp.java.

  6. Edit the code in the Source window or copy and paste from online help to match the code below:
    package ProcSetUp;
    
    import com.borland.dx.dataset.*;
    import com.borland.dx.sql.dataset.*;
    import java.sql.*;
    
    public class CreateProcedures {
    
      public static void main(String[] args) throws DataSetException {
        Database database1 = new Database();
        database1.setConnection(new ConnectionDescriptor("jdbc:interbase://<IP address or localhost>/<path to .gdb file>", "SYSDBA", "masterkey", false, "interbase.interclient.Driver"));
        try { database1.executeStatement("DROP PROCEDURE GET_COUNTRIES"); } catch (Exception ex) {};
        try { database1.executeStatement("DROP PROCEDURE UPDATE_COUNTRY"); } catch (Exception ex) {};
        try { database1.executeStatement("DROP PROCEDURE INSERT_COUNTRY"); } catch (Exception ex) {};
        try { database1.executeStatement("DROP PROCEDURE DELETE_COUNTRY"); } catch (Exception ex) {};
        database1.executeStatement(getCountriesProc);
        database1.executeStatement(updateProc);
        database1.executeStatement(deleteProc);
        database1.executeStatement(insertProc);
        database1.closeConnection();
      }                                            
    
      static final String getCountriesProc = 
      
    "CREATE PROCEDURE GET_COUNTRIES RETURNS (     /r/n"+
    "  COUNTRY VARCHAR(15),                       /r/n"+
    "  CURRENCY VARCHAR(10) ) AS                  /r/n"+
    "BEGIN                                        /r/n"+
    " FOR SELECT c.country, c.currency            /r/n"+
    "  FROM country c                             /r/n"+
    "  INTO :COUNTRY,:CURRENCY                    /r/n"+
    " DO                                          /r/n"+
    " BEGIN                                       /r/n"+
    "  SUSPEND;                                   /r/n"+
    " END                                         /r/n"+
    "END;";
    
    
      
      static final String updateProc = 
      
    "CREATE PROCEDURE UPDATE_COUNTRY(             /r/n"+
    "  OLD_COUNTRY VARCHAR(15),                   /r/n"+
    "  NEW_COUNTRY VARCHAR(15),                   /r/n"+
    "  NEW_CURRENCY VARCHAR(20) ) AS              /r/n"+
    "BEGIN                                        /r/n"+
    "  UPDATE country                             /r/n"+
    "    SET country = :NEW_COUNTRY               /r/n"+
    "    WHERE country = :OLD_COUNTRY;            /r/n"+
    "END;";
    
    
    
      static final String insertProc = 
      
    "CREATE PROCEDURE INSERT_COUNTRY(             /r/n"+
    "  NEW_COUNTRY VARCHAR(15),                   /r/n"+
    "  NEW_CURRENCY VARCHAR(20) ) AS              /r/n"+
    "BEGIN                                        /r/n"+
    "  INSERT INTO country(country,currency)      /r/n"+
    "    VALUES (:NEW_COUNTRY,:NEW_CURRENCY);     /r/n"+
    "END;";
    
    
      static final String deleteProc = 
      
    "CREATE PROCEDURE DELETE_COUNTRY(             /r/n"+
    "  OLD_COUNTRY VARCHAR(15) ) AS               /r/n"+
    "BEGIN                                        /r/n"+
    "  DELETE FROM country                        /r/n"+
    "    WHERE country = :OLD_COUNTRY;            /r/n"+
    "END;";
    }                           
    
    
  7. Right-click ProcSetUp.java in the project pane, then select Run. This step creates the tables and procedures on the server.

  8. Select File|Close from the menu.

This is a very simple procedure. For tips on writing more complex stored procedures, consult your database documentation.