Database
Resources
Adding
variables to Database Resources
Supported
database column types
Examples
of generated SQL for Assisted SQL
Dynamic
SQL and SQL Injection attack
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 =
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 + "'"; } |