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
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
Select an existing procedure.
To browse the database for an existing procedure, click Browse Procedures in the procedure
property editor. A list of available procedure names for the database you are connected to is displayed. If the server is InterBase and you select a procedure that does not return data, you receive a notice to that effect. If you select a procedure that does return data, JBuilder attempts to generate the correct escape syntax for that procedure call. However, you may need to edit the automatically-generated statement to correspond correctly to your server's syntax. For other databases, only the procedure name is inserted from the Select Procedure dialog.
If the procedure is expecting parameters, you have to match these with the column names of the parameters.
To enter a JDBC procedure escape sequence, use the following formatting:
Enter server-specific syntax for procedure calls.
When a server allows a separate syntax for procedure calls, you can enter that syntax instead of an existing stored procedure or JDBC procedure escape sequence. For example, server-specific syntax may look like this:
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".
SimpleStoredProcedure/ProcSetUp/ProcSetUp.jpr
in the Project Wizard.Select File|New, then select Class.
ProcSetUp
in the Class wizard. Click OK to create the file ProcSetUp.java
.
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;"; }
ProcSetUp.java
in the project pane, then select Run. This step creates the tables and procedures on the server.
This is a very simple procedure. For tips on writing more complex stored procedures, consult your database documentation.