Database Resources

Documentation home

Introduction. 1

Database Resource Editor 2

Database Resource Options 4

Table Operations 4

Use Prepared Statements 4

Assisted/Native SQL 5

Resource Fields 5

Resource Fields Toolbar 6

Database Resource Toolbar 6

How Database Resources Work 7

Adding variables to Database Resources 8

Supported database column types 8

Examples of generated SQL for Assisted SQL 8

Using dynamic databases 9

Using Native SQL 9

Using Dynamic SQL 11

Dynamic SQL with LIKE clauses 11

Dynamic SQL and SQL Injection attack 12

 

See also:       Working with Databases, Database Connections, How Resources Work, Comparing Dynamic lists and Database Resources

 

Introduction

 

This document describes how to use Verj.io Database Resources to read and update data in a database. To display database data as a dropdown list, you should use a Dynamic List. (See also Comparing Dynamic Lists and Database Resources).  All other database operations can be performed using Database Resources as described below.

 

A Database Resource represents a SQL statement to access a database. A single database resource can be used to execute any or all of SQL select, insert, update or delete statements. These statements are executed when the corresponding FPL script commands or API methods are executed e.g. an FPL fetch command or API DatabaseResource.fetch() method will issue a SQL select statement, and an FPL update command or API DatabaseResource.update() method will issue a SQL update statement etc.

 

Each Database Resource contains two sections: a database section, and a resource 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 resource fields section contains a list of all the columns in the database.

 

Steps to use a database resource in a form:

 

1.      Configure the Database Connection

2.      Create the Database Resource

3.      Add the Database Resource to the Resources View in the form

4.      Map the form fields or process attributes to the resource fields

5.      Write scripting statements such as FPL fetchtable, updatetable, fetch, update or API methods Table.fetchtable(), Table.updatetable(), DatabaseResource.fetch(), DatabaseResource.update()

 

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

 

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

                      

It is important to realize that data movement to and from a database is not performed automatically by the system, but is initiated by script commands/methods. Data is only read from the database when executing one of the FPL script commands fetch or fetchtable, or API methods  DatabaseResource.fetch() or Table.fetchtable(), and similarly for the other script commands/methods which perform some form of update and their corresponding SQL statements.

 

 

 Database Resource Editor

 

         

 

Desciption

An optional description for the resource.

 

Debug

When checked, all SQL statements are logged to the execution log.

 

Database Connection

Select the Database Connection to be used for all SQL operations. 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 the right of this options panel  to display this section of the online documentation.

 

Table Operations 

Each Database Resource can be used for either table operations or non-table operations (but not both). This is a most important distinction that determines how the resource is used and which script statements/methods can be executed.

 

·         For use with table operations: used when working with multiple records e.g. a select statement is expected to return multiple rows. The Database Resource is used by a form, integration service or workflow process by creating a table and configuring this Database Resource as the “backing” resource for the table. Columns within this table are mapped to Database Resource fields. FPL script command fetchtable or API method Table.fetchtable() is used to load the table from the database, and updatetable or API method Table.updatetable() is used to update the database, issuing SQL update, delete and insert statements as required. See Table Concepts for more information on tables.

 

·         For use with non-table operations: used when working with a single row. The Database Resource is used by a form, integration service or workflow process by mapping form fields or process attributes to Database Resource fields. FPL script commands fetch, update, insert, delete (API DatabaseResource methods: fetch(), update(), insert(), delete()) are used which execute SQL select, update, insert and delete statements respectively.

 

 

Use Prepared Statements

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 to update or insert a database table containing CLOB types, this option should be checked.

 

Use of Prepared Statements is recommended. Prepared Statements have the following advantages over Statements (which represent the alternative):

·         They are faster

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

·         They provide protection against SQL Injection attack. Click here for further details.

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

 

The following paragraph applies only when timezone support is disabled:

 

The handling of date and time types is performed differently with PreparedStatements and occasionally this can cause problems with systems that are sensitive to timezones. Verj.io is timezone independent – this means that a time as entered by the user as 11:00 is always displayed as 11:00 regardless of timezone or changes in timezone. Verj.io achieves this by specifying a timezone of UTC when interacting with external systems. However, some databases including MySQL and MS SQL Server, are by default timezone sensitive and may adjust time values accordingly. To work correctly with Verj.io, these databases should be configured with a timezone of UTC: this should be configured, where supported, on the JDBC connection string for the database. If timezone 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

The SQL statements executed by the system can be supplied in two ways:

 

·         Assisted: the SQL is constructed from four fields in the Database Resource: Select columns, Select from tables, Where clause, Additional SQL statement clauses. From these, the system builds the appropriate SQL statements to be used for fetch, update, insert and delete operations. Click here for more details on the SQL generated.

 

·         Native: the SQL for fetch, update, insert and delete operations is supplied explicitly. The system substitutes values into this supplied SQL. Click here for more details.

 

Resource Fields

 

You should 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 or join multiple tables, then assign an alias to the column and use the alias name for the name of the resource 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 Verj.io 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 Verj.io to set the corresponding values for a form field when fields are imported into a form from a Database Resource. Note that Verj.io does not use these values to check that inserted or updated data is valid.

 

The Required checkbox indicates that a script command/method should be rejected with an error unless this field has a value. When a Database Resource is imported from a database schema, this option is automatically checked for all key fields.

 

The field Description can be used to enter meaningful information about the field. This information is visible to the Verj.io Studio 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 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 executing an UPDATETABLE command or API Table.updatetable() method : 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  or API DatabaseResource methods: fetch(), update(), insert(), delete(). See Table Concepts for more information.

 

The Read only option indicates that the corresponding database column can be read but not updated or inserted. This option must be checked for auto-increment database columns. These are columns where the database system sets a sequential value e.g. SQL Server identity columns, MySQL columns with auto_increment specified.

 

·         Auto-increment fields: the system treats all resource fields marked as both read only and persistent as potential auto-increment fields. When a record is inserted to the database, the system determines the value assigned to the corresponding column by the database, and automatically updates the mapped field or table column with this new value.

 

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 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.

 

Resource Fields Toolbar

 

* Add a resource field

* Delete selected resource fields

 

 

Database Resource Toolbar

 

 

 

*  Save: saves the Database Resource.

*  Verify Database Resource: checks that the resource fields have been defined for all references to &&variables in the SQL input fields.

  Runs the database schema wizard to import a table or view directly from 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/view definitions and cannot be used for joins or more advanced SQL constructs.

*  Builds a Dynamic List from this Database Resource. (See How to Use Dynamic Lists for more information).

*  Create database table: generates and executes SQL that can be used to create a table for this Database Resource in the database system.

*  Maintain Documentation

*  Show information: dates for creation, last update and import of this Database Resource.

*  Shows this help page.

  Close the editor

 

 

How Database Resources Work

FPL script commands fetch, update, insert and delete or API methods: DatabaseResource.fetch(), DatabaseResource.update(), DatabaseResource.insert() and DatabaseResource.delete() are processed as follows:

 

1.      The SQL statement is built using either the assisted or native SQL input fields in the Database Resource Editor. At this point, the SQL probably contains one or more variables e.g. &&var1.

2.      Any variables (prefixed with &&) that appear in the SQL statement are replaced with the corresponding values from the form, integration service or workflow process using resource field mappings.

3.      The SQL statement is executed.

4.      Additionally for fetch operations - FPL fetch command or API DatabaseResource.fetch() method -  the system extracts column values for all resource fields with the persistent flag checked. It does this using the name assigned to each resource field e.g. if a resource field is named customer_id, the system expects the result set returned by the database to contain a column with the name customer_id. 

 

The FPL fetchtable command and API Table.fetchtable() method works in the same way as fetch commands except that step 4 is repeated for each row returned in the result set from the database.

 

Updatetable operations - FPL updatetable command or API Table.updatetable( ) method - are more complex. Internally, the system maintains three sets of rows: rows to be inserted, rows to be deleted and rows to be updated. SQL statements for each of these operations are then built: if native SQL is configured, the supplied statements are used; if assisted SQL is configured, the statements are generated. When assisted SQL is configured, the WHERE clause for update operations is constructed using all resource fields that have the Unique Key flag set; if there are no such resource fields, all resource fields are added to the 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 or any of the native SQL boxes. A variable can be specified using two possible syntaxes &&VAR1 or &&{VAR1}.

 

&&VAR1 syntax: when the Use Prepared Statements option is not selected, 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.

 

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 the system for the EMPLOYEES database resource shown above is as follows:

 

SELECT (script statement: FETCH <resource_name>)

 

select * from demo.employees where employee_id =<mapped form field value> order by employee_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.employees (employee_id, employee_name, job_title, manager, salary)

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

 

DELETE (script statement DELETE <resource_name>)

 

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

 

For the FPL updatetable statement or API Table.updatetable() method, the system will generate a combination of update, insert and delete SQL statements depending on the changes made by the end-user as described above. 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 must identify a single row; for assisted SQL , 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 such unique key columns exist, all columns will be included in the WHERE clause.

 

To display the generated SQL statements, 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 Connection 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 Connection 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.

 

FPL:

API Language (Javascript):

if [CLIENT='SMITH']

   set $DATABASE = 'SMITHDB';

endif

if [CLIENT='JONES']

   set $DATABASE = 'JONESDB';

endif

 

if (fields.CLIENT.value == "SMITH")

{

   system.variables.$DATABASE.value = "SMITHDB";

}

else if (fields.CLIENT.value == "JONES")

{

   system.variables.$DATABASE.value = "JONESDB";

}

 

 

Once set, the $DATABASE system variable will be used for all database accesses where **Dynamic is specified, either for Database Resources or Dynamic Lists, until either the form ends or the $DATABASE system variable 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 four SQL statements representing select, update, insert and delete operations can be entered.

 

 

The Build from assisted SQL 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 script commands/methods. Note that it is not necessary to always provide all four statements.

 

FPL command

API based language

Required SQL statements

FETCH

 DatabaseResource.fetch()

Select

UPDATE

 DatabaseResource.update()

Update

DELETE

 DatabaseResource.delete()

Delete

FETCHTABLE

 Table.fetchtable()

Select

UPDATETABLE

 Table.updatetable()

Update (always)

Delete (if rows are deleted)

Insert (if rows are 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 and the Use Prepared Statements option is not selected 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 FPL update and updatetable operations (API DatabaseResource.update() and API Table.updatetable()), it is essential that the WHERE clause of the update and delete statements uniquely identifies a single row in the database table. It is recommended that these statements have identical WHERE clauses. Please also note that any columns in the WHERE clause must not have changed value between a fetchtable and a subsequent updatetable (API Table.fetchtable() and Table.updatetable()).

 

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

 

3.      fetch (API DatabaseResource.fetch()) and fetchtable (API Table.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.

 

FPL:

API Language (Javascript):

if [CITY != null]

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

endif

if [POSTCODE != null]

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

endif

if (fields.CITY.value)

{

  fields.WHERE.value += " AND CITY = &&CITY";

}

if (fields.POSTCODE.value)

{

  fields.WHERE.value += " AND POSTCODE = &&POSTCODE";

}

 

 

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.

 

FPL:

API Language (Javascript):

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

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;

fields.W_SEARCH.value = "";

 

// search address

if (fields.SEARCH_ADDRESS.value)

{

  fields.W_SQL_ADDRESS.value = "%" + fields.SEARCH_ADDRESS.value + "%";

  fields.W_SEARCH.value += " address like &&W_SQL_ADDRESS";

 

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

  if (fields.SEARCH_COMMENTS.value)

  {

     fields.W_SEARCH += " AND ";

  }

}

 

// search comments

if (fields.SEARCH_COMMENTS.value)

{

  fields.W_SQL_COMMENTS.value = "%" + fields.SEARCH_COMMENTS.value + "%";

  fields.W_SEARCH.value += " comments like &&W_SQL_COMMENTS";

}

 

// search..

tables.CUSTOMERS.fetchtable();

tables.CUSTOMERS.control.show();

 

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 example above).

 

Do NOT write your code like this: (this might be susceptible to a SQL Injection attack)

 

FPL:

API Language (Javascript):

// DO NOT DO THIS!!

// DO NOT DO THIS!!

// DO NOT DO THIS!!

if [CITY != null]

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

endif

if [POSTCODE != null]

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

endif

 

// DO NOT DO THIS!!

// DO NOT DO THIS!!

// DO NOT DO THIS!!

if (fields.CITY.value)

{

  fields.WHERE.value += " AND CITY = '" + fields.CITY.value + "'";

}

if (fields.POSTCODE.value)

{

  fields.WHERE.value += " AND POSTCODE = '" + fields.POSTCODE.value + "'";

}