UltraLite Database User's Guide
SQL Language Elements
In UltraLite, both the data types available to represent data and the SQL features available to access that data depend on the development model you adopt.
If you use a static interface (embedded SQL, static C++ API, or static Java API), the range of SQL available is wider, but all statements used by the application must be specified at compile time. If you develop your application using an UltraLite component, dynamic SQL provides a narrower range of SQL, but the SQL statements can be constructed at runtime.
When an UltraLite program attempts to use a SQL statement or feature that is not supported in UltraLite, the SQL error message Feature not available in UltraLite is reported. Dynamic SQL may also return syntax errors.
Data types UltraLite supports a subset of the data types available in Adaptive Server Anywhere.
If you create a database from an Adaptive Server Anywhere reference database, you can use a wide range of data types. Those Adaptive Server Anywhere data types not supported in UltraLite are converted by the UltraLite generator into a smaller set of base types. If you create an UltraLite database using the Schema Painter, you are restricted to the smaller set of base types.
For a listing of the UltraLite base types, see Data types in UltraLite.
For a complete listing of Adaptive Server Anywhere data types, see SQL Data Types.
Identifiers Identifiers are the names of database objects, such as columns and tables. UltraLite supports the same rules for identifiers as Adaptive Server Anywhere.
For information about identifiers, see Identifiers.
Strings Strings are used to hold character data in the database. UltraLite supports the same rules for strings as Adaptive Server Anywhere.
If you create an UltraLite database from an Adaptive Server Anywhere reference database, the rules for strings are determined by the database options in effect in the reference database when the UltraLite generator is run. The QUOTED_IDENTIFIER option is particularly important in setting rules for strings. Dynamic SQL alwaus operates as if this option is ON (the default in Adaptive Server Anywhere).
For information about strings, see Strings.
The results of comparisons on strings, and the sort order of strings, depends on both the case sensitivity of the database and the character set. These properties are set when the database is created.
For more information, see UltraLite database characteristics.
Functions UltraLite supports the same range of functions as Adaptive Server Anywhere, with a few minor exceptions. The functions supported are the same for static interfaces such as embedded SQL as they are for dynamic SQL.
For a list of supported functions, see UltraLite SQL functions.
Expressions Expressions are formed by combining data, often in the form of column references, with operators or functions.
Adaptive Server Anywhere provides a wide range of operators that it uses to form expressions. These operators are available if you develop your UltraLite application using a static interface (embedded SQL, static C++ API, or static Java API). One exception is that in Adaptive Server Anywhere you can use SQL variables to form expressions. You cannot use SQL variables (including global variables) in UltraLite applications. The @@identity global variable is an exception, and can be used within UltraLite applications.
For information about expressions in Adaptive Server Anywhere, see Expressions.
Dynamic SQL is more limited in the range of expressions it supports than is static SQL. For example, dynamic SQL does not support subqueries.
For information about the expressions available in dynamic SQL, see Dynamic SQL language elements.
Search conditions Search conditions or predicates are used in the WHERE clause, the HAVING clause, and the ON clause of SELECT statements.
Dynamic SQL is more limited in the range of conditions that it supports than is static SQL. For example, dynamic SQL does not support EXISTS conditions.
For information about search conditions available in dynamic SQL, see Search conditions.
Static interfaces have the entire range of conditions supported in Adaptive Server Anywhere available.
For information about search conditions in Adaptive Server Anywhere, see Search conditions.
Statements SQL statements are constructed from the building blocks listed above.
For a list of SQL statements available in dynamic SQL, see Dynamic SQL statements.
The following SQL statements can be used in static UltraLite applications:
Data Manipulation Language SELECT, INSERT, UPDATE, and DELETE statements can be included. You can use placeholders in these statements that are filled in at runtime.
For more information, see Writing UltraLite SQL statements.
TRUNCATE TABLE statement You can use this statement to rapidly delete entire tables.
Transaction control You can use COMMIT and ROLLBACK statements to provide transaction control within your UltraLite application.
START/STOP SYNCHRONIZATION DELETE statements These statements are used to temporarily suspend synchronization of delete operations.
For more information, see Temporarily stopping synchronization of deletes.
For information on other UltraLite limitations, see UltraLite database limitations.