Comparison of Dynamic Lists and Database Resources
See also: Dynamic Lists, Database Resources, FPL Script Command
Syntax
Dynamic lists and Database Resources are the two Verj.io elements that can be used to access relational
databases. The following chart compares their features:
Feature |
Dynamic List |
Database
Resource |
Read from database |
a |
a |
Write to database |
x |
a |
Display as a dropdown list |
a |
x |
Can be used with tables |
Note 1 |
a |
Note 1: when using an API based language e.g. Javascript, a list (this will be a Custom List as opposed to a Dynamic List) can be constructed from tables and vice versa. When using the FPL language, a list can be constructed from a table using the buildlistfromtable() function, but a table cannot be constructed from a list.
Each
Dynamic List or Database Resource represents a single SQL statement. This SQL
statement can be built automatically by the system or it can be manually
created using either the Dynamic List editor or the Database Resource editor.
Dynamic
Lists only support reading from a database so SELECT statements are the only
SQL statements that are issued. A Dynamic List is normally built - and the
SELECT statement executed - automatically by the system after all scripts have
been executed and the page is being prepared for presentation.
In contrast
Database Resources can issue the full range of SQL statements SELECT, UPDATE,
INSERT, DELETE. The SQL statements are executed when
the corresponding script command is executed as shown in the table below. This
means that accesses to Database Resources are under the full control of the developer.
API based language |
FPL command |
SQL statements
executed |
DatabaseResource.fetch() |
fetch |
select |
DatabaseResource.update() |
update |
update |
DatabaseResource.insert() |
insert |
insert |
DatabaseResource.delete() |
delete |
delete |
Table.fetchtable() |
fetchtable |
select |
Table.updatetable() |
updatetable |
update, insert, delete |
Both Dynamic
Lists and Database Resources can be created using wizards or can be created
manually. Database Resources can be imported directly from the database using
the Database Resource Wizard on the
main Tools menu at the top of the designer window - this creates one Database
Resource for each database table imported. A Dynamic List can be created from a
Database Resource using the build dynamic
list icon on the toolbar of the Database Resource editor.
There is no
limit to the complexity that can be used in SQL statements for either Dynamic
Lists or Database Resources.