Working with Databases

Documentation home

See also:   Understanding Ebase Integration

Configurable Ebase Elements

Here is a brief overview of the Ebase elements used to provide database support.

• Database Connection: as the name implies this represents a connection to a database system. The other elements shown below are each linked to a Database Connection. The Database Connection Wizard on the Tools menu can be used to create a new Database Connection. Database Connections are located within the IT Elements section of the designer tree.

• Database Resource: is used to execute SQL statements. Resource fields are mapped to form fields or tables using resource field mappings which are used to transfer data between the resource and a form. SQL statements are executed as a result of an FPL script command such as fetch, fetchtable, update, updatetable, delete, insert or an API method such as DatabaseResource.fetch(), Table.fetchTable() etc. Database Resources are located within the IT Elements/External Resources section of the designer tree.

• Stored Procedure Resource: similar to a Database Resource, but used to execute a stored procedure. Resource fields are mapped to form fields to transfer data between the resource and a form. Executed as a result of the FPL script command exec or the API method StoredProcedureResource.exec(). Stored Procedure Resources are located within the IT Elements/External Resources section of the designer tree.

• Dynamic List: is used to execute a single select SQL statement to build a list displayed to the end user. The SQL is normally executed automatically by the system as it constructs the HTML output for each page, though options exist to control this. List fields are mapped to form fields using dynamic list mappings. Dynamic Lists are located in the Lists section of the designer tree.

• XML Resource with Stored Procedure Adapter: is used to execute a stored procedure passing an XML request document and receiving an XML response document. Resource fields are mapped to form fields or tables using resource field mappings which are used to transfer data between the resource and a form. Executed as a result of the FPL script command call or the API method XmlResourceBase.call() to the XML Resource. XML Resources are located within the IT Elements/External Resources section of the designer tree.

Direct access from a script

As an alternative to using the elements above, you can also use the Java JDBC API to access a database directly; this option is available from server-side Javascript scripts. For example:

// use the getDatabaseConnection method to get a connection for any configured Database Connection (see Ebase Elements above)

var con = system.getDatabaseConnection("EBASE_SAMPLES");

var stmt;

var rs;

try

{

stmt = con.prepareStatement("select customer_id, name from customer where credit_limit > 9999");

rs = stmt.executeQuery();

// create an array for the result

var result = [];

// go through the result set and add each row to the result array

while (rs.next())

{

// create an object for each row

var row = {};

row.CUSTOMER_ID = rs.getString("customer_id");

row.NAME = rs.getString("name");

result.push(row);

}

// build a table from the results so they can be displayed

for each (var row in result)

{

tables.CUSTOMERS.insertRow();

tables.CUSTOMERS.CUSTOMER_ID.value = row.CUSTOMER_ID;

tables.CUSTOMERS.NAME.value = row.NAME;

}

}

finally

{

// it is very important to close any open result sets, statements and connection in this manner

// otherwise there will be a resource leak.

if(rs) rs.close();

if(stmt) stmt.close();

if(con) con.close();

}