A database resource represents a single SQL statement to access a database. A single database resource can be used to execute a SQL SELECT, INSERT, UPDATE or DELETE statement or any combination of these. Each of these statements will be issued by the system when the corresponding FPL script command FETCH, INSERT, UPDATE, DELETE is executed or when the FETCHTABLE or UPDATETABLE commands are issued. There is no limit to the complexity of the SQL statement that can be generated.

A single database resource can be used for either table operations (FETCHTABLE and/or UPDATETABLE) or non-table operations (FETCH, INSERT, UPDATE, DELETE) but not both. This is because non-table operations are designed to operate on a single row, and table operations are designed to work with multiple rows. (See Table Concepts for more information)

It is important to realize that data movement to and from a database is not performed automatically by the system but is under the direct control of the Ebase designer. Data is only read from the database when one of the FPL script commands FETCH <resource_name> or FETCHTABLE <table_name> is executed, and similarly for the other script commands which perform some form of update and their corresponding SQL statements. This means that data can be read, updated, deleted etc at any time during the execution of a form.

Each database resource contains two sections: a database section, and a list of fields section. The database section contains information on the SQL statement that will be issued and the database to which it will be targeted. The fields section contains a list of all the columns in the database.

To use a database resource in a form, it is necessary to go through the following steps:

1. Configure the connection to the database.

2. Create the database resource

3. Add the resource to a business view.

4. Associate the business view with the form.

5. Map the form fields to the resource fields.

The mappings mentioned in the last step are normally created automatically by the system but can be created or edited manually if required.

Database resources are sharable elements within the Ebase system and, once created, can be used by any number of forms.

Configure the connection to the database

Ebase can connect to any database system that has a JDBC driver including all levels of the JDBC specification. Nearly all database systems now supply JDBC drivers that conform to the JDBC 2 specification where a pool of database connections is created and managed by the J2EE application server. This is the preferred type of connection, but Ebase also supports the older JDBC 1 specification where database connections are managed directly by the application.

If your database system provides a JDBC 2 driver, then the first step is to configure the connection in the J2EE application server. (See How Ebase Accesses Databases for more information)

The next step is to create an Ebase database element. Once defined, this element can be referenced by any number of database resources and dynamic lists. To create a database element, open the database editor with (File -> New -> Database)

A JDBC 2 connection uses connection pooling and most of the connection parameters are defined in the J2EE application server. Ebase requires only the database type and the name of the datasource as configured to the application server, and all other parameters should be left blank.

A JDBC 1 connection does not use connection pooling and Ebase will make the connection directly, not using the application server. JDBC 1 connections are significantly slower than JDBC 2 as a database connection has to be acquired for each database access. Database updates made via JDBC 1 connections are not transactional.

JDBC 2 is the default and JDBC 1should only be specified when the JDBC driver used does not provide support for connection pooling. This should only rarely be required.

For a JDBC 1 database connection, you will additionally need to specify the JDBC URL, JDBC Driver, user and password. See the documentation for your JDBC driver for details.

The Test Connection button can be used to test the connection.

The Import from Schema button can be used to create database resource definitions directly from the database schema. This will create one database resource for each table or view selected. This mechanism cannot be used to configure more advanced database resources such as joins. (See Creating a database resource automatically)

Creating a database resource manually

Open the database resource editor by either clicking on an existing database resource in the hierarchy tree panel (IT Elements -> External Resource -> Database Resource) or selecting (File -> New -> External Resource -> Database Resource) The following example shows an existing database resource named CUSTOMER.

The debug checkbox will log all SQL statements issued to the database to the execution log.

Beneath this is the database section that is used to set the target database and to build the SQL statement. The database dropdown is a list of the defined databases to which the Ebase system has access. The special entry **Dynamic can be chosen to indicate that the database name will be supplied dynamically at runtime. (See Using Dynamic Databases)

Database Resource Options

Click the help icon to display this section of the online documentation.

Table Resource

Click this checkbox to allow this database resource to be used as a backing resource for a table. The default value is unchecked which means that this resource cannot be used as a backing resource for a table.

Use Prepared Statement

This option changes the technique used internally by the system to access a database. Check this option to indicate that the system should use a JDBC PreparedStatement for all database accesses; if unchecked, the system will use a JDBC Statement. As a general rule, this option should always be checked unless there is a specific reason not to.

Please note that this option is required to update or insert CLOB types.

Prepared Statements have the following advantages over Statements:

· They are faster

· They provide the ability to read and write all supported types including CLOBs

· They provide protection against SQL Injection attack

· They provide a more reliable technique for escaping special characters in the data e.g. apostrophes, ampersands etc

The handling of date and time types is performed differently with PreparedStatements and occasionally this can cause problems with systems that are sensitive to time zones. Ebase is time zone independent – this means that a time as entered by the user as 11:00 is always displayed as 11:00 regardless of time zone or changes in time zone. Ebase achieves this by specifying a time zone of UTC when interacting with external systems. However, some databases including MySQL and MS SQL Server, are by default time zone sensitive and may adjust time values accordingly. To work correctly with Ebase, these databases should be configured with a time zone of UTC: this should be configured, where supported, on the JDBC connection string for the database. If time zone problems persist, they will most likely be resolved by changing the database resource to use Statements as opposed to PreparedStatements i.e. by unchecking this option.

Assisted/Native SQL:

· Assisted SQL refers to the generation of SQL statements by the system using the four boxes select columns, select from tables, where clause, additional SQL clauses. The appropriate SQL statements are generated to support the various FPL commands: fetch, update, insert, delete, fetchtable, updatetable. See examples of generated SQL for more details. All four boxes can contain variables to be substituted from a form. (See Adding variables to database resources)

· Native SQL allows the use of more complex SQL statements and is for advanced use only

Note: Only one of Assisted SQL or Native SQL is used by the system and it is not possible to mix these options. The option used is determined by the setting of the Assisted SQL/ Native SQL radio button.

Buttons

The Verify button can be used to check that the list fields have been correctly defined.

The Build Dynamic List button allows you to build a dynamic list from this resource. (See How to Use Dynamic Lists for more information)

Resource Fields

The lower section is the resource fields section. You must create one field definition for each field that you want to map to a form. The name of each field must exactly match the corresponding column name or alias (including case) in the select columns or where clause boxes, or of the database table column if '*' is specified in select columns. If you need to include a function in the list of selected columns, then assign an alias to the column and use the alias name for the name of the list field.

The field Type should be set to match the database column type. The values available in the dropdown list are the types from the JDBC standard. See your database JDBC driver documentation for how these map to your database types. The system makes use of these types when converting the values between database types and Ebase field types. If an illegal mapping is detected, you will receive an error message.

The Length and Decimal digits should match the corresponding specification in your database. These are used by Ebase to set the corresponding values for a form field when fields are imported directly into a form from a database definition. Note that Ebase does not use these values to check that inserted or updated data is valid.

The Required checkbox indicates that an FPL script command e.g. FETCH, UPDATE, should be rejected with an error unless this field has a value. When a database resource is imported, this option is automatically set to checked for all key fields.

The field Description can be used to enter meaningful information about the field. This information is visible to the Ebase user when building the form field mappings.

The Unique key checkbox indicates that the field is defined as a key field in the database. When a database resource is imported, this option is automatically set to checked for all primary key fields and all unique key fields. This will not normally need to be changed. The key option is used only in table operations when performing an UPDATETABLE command: specifically it is used to build the WHERE clause for row level update and delete statements. The key option is not used in execution of the non-table FPL commands FETCH, UPDATE, DELETE, INSERT. (See Table Concepts for more information)

The Read only option indicates that the corresponding database column can be read but not updated or inserted. When a database resource is imported, this option is automatically set to checked for all auto-increment fields. These are fields where the database system sets a sequential value e.g. id fields in SQL Server.

The Persistent checkbox is set to indicate that a column exists in the database with the resource field name. Disable this setting if you want to include a variable in the SQL statement WHERE clause and map this to a form field, but where a column of the same name does not exist in the database. For example if you wanted to see all policies with a maturity value between £100,000 and £200,000 you might code the WHERE CLAUSE as:

MATURITY_VALUE <= &&HIGH_VALUE AND MATURITY_VALUE >= &&LOW_VALUE

HIGH_VALUE and LOW_VALUE do not have corresponding columns in the database and therefore the persistent option should be unchecked. If the persistent option is left checked in this scenario, the system will attempt to read columns named HIGH_VALUE and LOW_VALUE from the database and an error will result.

The Dynamic Sql checkbox indicates that this field contains part of a SQL statement. Selecting this option automatically sets a number of other options: Persistent is unchecked, Required is set, Type is set to CHAR. See Using Dynamic SQL for more information.

Creating a database resource automatically

Table schema definitions can be imported directly from the database using the database editor (See Configure the connection to the database). This has the advantage that all column types, lengths, decimal specifications etc will be correctly set. Also, if the table being imported has a primary key or unique index, the appropriate fields will be added to the WHERE clause and set as unique key fields, read only fields will be detected etc. However this procedure can only be used for single table definitions and cannot be used for joins or views or more advanced SQL constructs.

When the Import from schema button is pressed, the dialog first asks for the database schema (if the database system supports schemas) and then presents the following dialog which supports the importing of tables, views and stored procedures (if the database system supports stored procedures).

When selections have been made and the OK button is clicked, the following dialog is displayed:

The Import as Resource Name column allows you to assign a different name to the resource being imported - the default resource name is the table name.

Selecting the Tables Only checkbox does 2 things:

1. It makes this database resource a Table Resource.

2. It provides a method of changing the columns to be used to build the database resource's where clause. Clicking this checkbox then clicking in the Columns for Where Clause table cell will allow you to change the where clause fields. By default, all primary key or unique key fields will be included in the resource's where clause.

Adding variables to database resources

Values from a form can be dynamically substituted into any of the boxes used to create the SQL statement: select columns, select from tables, where clause, additional SQL clauses. A variable can be specified using two possible syntaxes &&VAR1 or &&{VAR1}.

&&VAR1 syntax: when using this syntax, replacement values are enclosed in single quotes for all character field types. e.g. : where EMPLOYEE_NAME = &&EMPLOYEE_NAME will be resolved as something like where EMPLOYEE_NAME = ‘SMITH’, whereas where EMPLOYEE_ID = &&EMPLOYEE_ID will be resolved as something like where EMPLOYEE_ID = 12345 assuming that the resource field for EMPLOYEE_ID is a numeric type.

&&{VAR1} syntax: when using this syntax, replacement values are never enclosed in single quotes. This syntax can be used to concatenate two variables together e.g. &&{VAR1}&&{VAR2} or when the quoting of variables is not required e.g. LIKE ‘%&&{VAR1}%’ might be used in a WHERE clause to perform a search.

In both cases, the field variable name is interpreted as meaning a resource field name, and the value is obtained from the form field mapped to the resource field.

Adding the resource to a business view

This is done using the business view editor and clicking the Add Resource button.

Associating the business view with a form

In the form editor, select the business view from the dropdown list in form properties.

Map the form fields to the resource fields

To create mappings automatically between form fields and a resource fields, simply import the resource fields into the form using the Import fieldsfrom external resource button on the fields toolbar of the form editor. This will create new form fields of the appropriate type and length and will also create the mapping.

To create mappings manually, click on the field mappings button on the form toolbar of the form editor, select the resource to be mapped, then select the form fields for each of the resource fields from the dropdown list.

Add script commands to the form

As mentioned above, an operation to the database is initiated when the appropriate script command is executed. These commands can be executed from any script corresponding to any of the Ebase events. However, we recommend that for transactional reasons, all updates are executed within the after form event. The syntax of the supported script commands is:

To read a single record from a database:

fetch resource_name;

Following a fetch, system variable $FOUND will contain 'Y' or 'N' depending on whether a record was found.

To update a single existing record:

update resource_name;

To insert a new record:

insert resource_name;

to delete a record:

delete resource_name;

to load multiple records into a table:

fetchtable table_name;

to update from a table (performing deletes, updates and insertions as necessary):

updatetable table_name;

(See FPL Script Command Syntax and Table Concepts and Understanding Ebase Events for more information)

Caution: both the update and delete commands have the capability of affecting multiple records. Please check that the WHERE CLAUSE is correctly specified if you are using these commands.

Supported database column types

All database column types are supported with the exception of the following: binary types BINARY, LONGBINARY, IMAGE etc, plus BLOB, REF, STRUCT, JAVA_OBJECT.

Examples of generated SQL for Assisted SQL

The SQL generated and executed by Ebase for the EMPLOYEES database resource shown above is as follows:

SELECT (script statement: FETCH <resource_name>)

select * from demo.employee where employee_id =<mapped form field value> order by last_name

UPDATE (script statement UPDATE <resource_name>)

update demo.employee

set col1=<mapped form field value>,

col2==<mapped form field value>,

col3=<mapped form field value>

where employee_id =<mapped form field value>

Note that only changed columns will be included.

INSERT (script statement INSERT <resource_name>)

insert into demo.employee (commission, department_id, employee_id, first_name, job_id, hire_date, last_name, manager_id, middle_initial, salary)

values( <mapped value 1>, <mapped value 2>, <mapped value 3>, <mapped value 4>, ......)

DELETE (script statement DELETE <resource_name>)

delete from demo.employee where employee_id =<mapped form field value>

For UPDATETABLE statements, the system will generate a combination of UPDATE, INSERT and DELETE SQL statements depending on the changes made by the end-user. If no changes have been made, no SQL statements are generated. Each of these SQL statements will be similar to those shown above. In addition the generated WHERE clause for each statement will identify a single row; this is achieved by including all columns identified as unique key columns in the Resource fields section of the database resource in the WHERE clause. If no unique key columns exist, all columns will be included in the WHERE clause. To display the generated SQL statements for an UPDATETABLE, check the debug checkbox in the top right-hand corner of the resource. All SQL is then logged and can be viewed in the execution log.

Using dynamic databases

Instead of supplying a specific database from the dropdown list within the editor, a dynamic specification can be chosen by selecting the special name **Dynamic from the list. This indicates to the system that the database name will be supplied dynamically at runtime using the system variable $DATABASE. When using dynamic database specification, the $DATABASE variable must be set prior to the first call to a database resource or dynamic list where **Dynamic has been selected.

When using this technique, it is recommended to set $DATABASE as early as possible within form execution, preferably during the before form event. e.g.

if [CLIENT='SMITH']

set $DATABASE = 'SMITHDB';

endif

if [CLIENT='JONES']

set $DATABASE = 'JONESDB';

endif

Once set, the $DATABASE value will be used for all database accesses where **Dynamic is specified, either for database resources or dynamic lists, until either the form ends or $DATABASE is set to a different value.

Using Native SQL

Native SQL is used when the Native SQL radio button is selected. This option is intended for use by advanced users and the developer assumes responsibility for database integrity when used for update operations. When this option is selected, up to 4 SQL statements representing select, update. Insert and delete operations can be entered.

The Build from assistedSQL button provides an assistant that will generate starting SQL from the Assisted SQL boxes. Note that this button will replace any existing SQL without warning.

The following table shows the SQL statements required for use of each of the FPL commands. Note that it is not necessary to always provide all 4 statements.

FPL command	Required SQL statements
FETCH	Select
UPDATE	Update
DELETE	Delete
FETCHTABLE	Select
UPDATETABLE	Update (always) Delete (if rows can be deleted) Insert (if rows can be added)

Substitution of form field variables into the SQL follows the same rules as for assisted SQL. Note in particular that unbracketed variables will be enclosed in single quotes when the resource field type is a character type e.g. LAST_NAME=&&LAST_NAME will become, for example, LAST_NAME=’SMITH’. If bracketed variables are used, it would be necessary to add single quotes to the SQL to achieve the same result i.e. LAST_NAME=’&&LAST_NAME’.

Additional notes:

1. For UPDATETABLE operations, it is essential that the WHERE clause of the update and delete statements uniquely identifies a single row in the database table. It is strongly recommended that these statements have identical WHERE clauses.

2. For non-table UPDATE and DELETE operations, it is recommended (but not required) that the WHERE clause uniquely identifies a single row in the database table.

3. FETCH and FETCHTABLE operations both work as follows:

· Form field variables are substituted into the SQL statement

· The statement is issued to the database.

· Values are extracted from the result set using the resource field names (only resource fields marked as persistent are retrieved).

4. For UPDATE, DELETE and INSERT operations, form field variables are substituted into the SQL statement, and the statement is then issued to the database.

Using Dynamic SQL

There may be occasions when you want to build up a SQL statement dynamically. For example, to implement a search application, you might set the where clause to a variable such as &&WHERE and then build this up programmatically in a script e.g.

if [CITY != null]

set WHERE = WHERE + ' AND CITY = &&CITY';

endif

if [POSTCODE != null]

set WHERE = WHERE + ' AND POSTCODE = &&POSTCODE';

endif

To achieve this, the Dynamic SQL option on the WHERE resource field is checked. When this option is checked, the system performs two subtitution phases as follows:

1. Dynamic SQL statements are substituted. In the example above, “&&WHERE” is substituted with “… AND CITY = &&CITY AND POSTCODE = &&POSTCODE”

2. && variables are then substituted. In the example above, “… AND CITY = &&CITY AND POSTCODE = &&POSTCODE” is then substituted with “… AND CITY = LONDON AND POSTCODE = EC1A9ZZ”

Dynamic SQL with LIKE clauses

The following example illustrates building up a SQL search statement containing LIKE clauses.

· Two database columns – address and comments – can be searched if data from the end user is entered.

· SEARCH_ADDRESS and SEARCH_COMMENTS are the user input fields.

· W_SEARCH contains the complete statement; this must be mapped to a corresponding resource field in the database resource.

· W_SQL_ADDRESS and W_SQL_COMMENTS are work fields used to contain the SQL LIKE strings; these must also be mapped to corresponding resource fields in the database resource.

· In the database resource, W_SEARCH, W_SQL_ADDRESS and W_SQL_COMMENTS have the Persistent flag unchecked to indicate that columns with these names do not exist in the database; W_SEARCH has the Dynamic Sql option checked.

set W_SEARCH = '';

// search address

if [ SEARCH_ADDRESS != null]

set W_SQL_ADDRESS = '%' + SEARCH_ADDRESS + '%';

set W_SEARCH = W_SEARCH + ' address like &&W_SQL_ADDRESS';

// add an "AND" to the SQL if necessary

if [ SEARCH_COMMENTS != null ]

set W_SEARCH = W_SEARCH + ' AND ';

endif

// search comments

if [ SEARCH_COMMENTS != null ]

set W_SQL_COMMENTS = '%' + SEARCH_COMMENTS + '%';

set W_SEARCH = W_SEARCH + ' comments like &&W_SQL_COMMENTS';

endif

// search..

fetchtable CUSTOMERS;

show CUSTOMERS;

Dynamic SQL and SQL Injection attack

WARNING!

Building a SQL statement dynamically can open the system to SQL Injection attack. To ensure that this is not possible, please follow these guidelines:

1. Always set the database resource to use the PreparedStatement option.

2. Always set any resource fields that will contain dynamic SQL to use the Dynamic SQL option.

3. In the script that builds the SQL dynamically, include references to form field variables by referring to them as variables prefixed with && (as illustrated in the examples above).

Do not write your code as follows:

if [CITY != null]

set WHERE = WHERE + ' AND CITY = \'' + CITY + '\'';

endif

if [POSTCODE != null]

set WHERE = WHERE + ' AND POSTCODE = \'' + POSTCODE + '\'';

endif