Data access in UltraLite

UltraLite provides the following data access methods:

Static SQL The static interfaces (embedded SQL, static C++, and static Java) use structured query language (SQL) to access data. The SQL they use is static, meaning that the statements are all specified at the time the application is built.
Dynamic SQL The UltraLite components can use dynamic SQL, which permits SQL statements to be constructed at runtime.
Table-based API Both the UltraLite components and static C++ interface provide a table-based API that accesses rows one at a time.

The distinguishing features of static SQL are as follows:

The full richness of the SQL language supported by Adaptive Server Anywhere can be used. An application will, however, use a limited number of these features, and so the supporting database engine can be quite small. By contrast, there must be support for every feature that could be used in a dynamic SQL implementation.

For more information about Adaptive Server Anywhere SQL support, see SQL Statements.
Since all SQL requirements are known at the time the application is built, the SQL statements themselves can be compiled into an executable form. They will tend to execute faster than an equivalent dynamic SQL implementation (which must be interpreted).
More extensive optimization of how to execute a query is possible since this happens when the application is built.

The distinguishing features of dynamic SQL are as follows:

The ability to define SQL statements at runtime provides additional flexibility.
There is no need for the analysis step during application development.
The data structures used to execute SQL statements can be built as required. This contrasts with static SQL in which these data structures are built when the application is built. Dynamic SQL applications are therefore larger than the corresponding static SQL implementations unless a large number (probably greater than 100) of statements are present in the application.
While the development model for static interfaces ensures that the data addressed by queries in the application is included in the UltraLite database, dynamic SQL shifts that responsibility to the application developer.

The distinguishing features of the table-based API are as follows:

You can develop applications without learning SQL.
The interface is simpler. However, you can access data in only one table at a time.