4D SQL engine implementation

Basically, the 4D SQL engine is SQL-92 compliant. This means that for a detailed description of commands, functions, operators or the syntax to be used, you may refer to any SQL-92 reference. These can be found, for instance, on the Internet.

However, the 4D SQL engine does not support 100% of the SQL-92 features and also provides some specific additional features.

This section covers the main implementations and limitations of the 4D SQL engine.

Since the SQL engine of 4D has been integrated into the heart of the 4D database, all the limitations concerning the maximum number of tables, columns (fields) and records per database, as well as the rules for naming tables and columns, are the same as for the standard internal 4D engine (DB4D). They are listed below.

• Maximum number of tables: Theoretically two billion but for compatibility reasons with 4D: 32767.
• Maximum number of columns (fields) in a table: Theoretically two billion columns (fields), but for compatibility reasons with 4D: 32767.
• Maximum number of rows (records) in a table: one billion.
  
• Maximum number of index keys: 128 billions for alpha, text, and float indexes; 256 billlions for other index types (scalar data).
• A primary key cannot be a NULL value and must be unique. It is not necessary to index the primary key columns (fields).
• Maximum number of characters allowed for the table and field names: 31 characters (4D limitation).

Tables with the same name created by different users are not allowed. The standard 4D control mechanism will be applied.

The following table indicates the data types supported in 4D SQL and their corresponding type in 4D:

Automatic data type conversion is implemented between numeric types.
A string that represents a number is not converted to a corresponding number. There are special CAST functions that will convert values from one type to another.
The following SQL data types are not implemented:

• NCHAR
• NCHAR VARYING.

The NULL values are implemented in the 4D SQL language as well as in the 4D database engine. However, they are not supported in the 4D language. It is nevertheless possible to read and write NULL values in a 4D field using the Is field value Null and SET FIELD VALUE NULL commands.

For compatibility reasons in 4D, NULL values stored in 4D database tables are automatically converted into default values when being manipulated via the 4D language. For example, in the case of the following statement:

... if the MyAlphafield field contains a NULL value, the myAlphavar variable will contain “” (empty string).

The default values depend on the data type:

• For Alpha and Text data types: “”
• For Real, Integer and Long Integer data types: 0
• For the Date data type: “00/00/00”
• For the Time data type: “00:00:00”
• For the Boolean data type: False
• For the Picture data type: Empty picture
• For the Blob data type: Empty blob

On the other hand, this mechanism in principle does not apply to processing carried out at the level of the 4D database engine, such as queries. In fact, searching for an “blank” value (for example myvalue=0) will not find records storing the NULL value, and vice versa. When both types of values (default values and NULL) are present in the records for the same field, some processing may be altered or require additional code.
To avoid these inconveniences, an option can be used to standardize all the processing in the 4D language: Map NULL values to blank values. This option, which is found in the field Inspector window of the Structure editor, is used to extend the principle of using default values to all processing. Fields containing NULL values will be systematically considered as containing default values. This option is checked by default.

The Map NULL values to blank values property is taken into account at a very low level of the database engine. It acts more particularly on the Is field value Null command.

The Reject NULL value input field property is used to prevent the storage of NULL values:

When this attribute is checked for a field, it will not be possible to store a NULL value in this field. This low-level property corresponds exactly to the NOT NULL attribute of SQL.
Generally, if you want to be able to use NULL values in your 4D database, it is recommended to exclusively use the SQL language of 4D.

Note: In 4D, fields can also have the “Mandatory” attribute. The two concepts are similar but their scope is different: the “Mandatory” attribute is a data entry control, whereas the “Reject NULL value input” attribute works at the level of the database engine.
If a field having this attribute receives a NULL value, an error will be generated.

The integrated SQL server of 4D supports date and time constants in accordance with the ODBC API. Here is the syntax for sequences of ODBC date and time constants:

Note: fff indicates milliseconds.

For example, you can use the following constants:

The SQL date parser rejects any date expression specifying "0" as the day or month. Expressions such as {d'0000-00-00'} or CAST('0000-00-00' AS TIMESTAMP) generate an error. To perform SQL queries on blank dates (not to be confused with null dates), you must use an intermediate 4D expression. For example:

The "Available via SQL" property, available for project methods, allows you to control the execution of 4D project methods via SQL.

When checked, this option allows the execution of the project method by the 4D SQL engine. It is not selected by default, which means that 4D project methods are protected and cannot be called by the 4D SQL engine unless they have been explicitly authorized by checking this option.

This property applies to all SQL queries, both internal and external — whether executed via the ODBC driver, or via SQL code inserted between the Begin SQL/End SQL tags, or via the QUERY BY SQL command.

Notes:

• Even when a method is given the “Available through SQL” attribute, the access rights set at the Database Settings level and at the level of the method properties are nevertheless taken into account when it is executed.
• The ODBC SQLProcedure function only returns project methods having the “Available through SQL” attribute.

• Auto-commit Transactions: This option can be used to activate the auto-commit mechanism of the SQL engine. The purpose of the auto-commit mode is to preserve the referential integrity of the data. When this option is checked, any SELECT, INSERT, UPDATE and DELETE (SIUD) queries not already carried out within a transaction are automatically included in an ad hoc transaction. This guarantees that the queries will be executed in their entirety or, in the case of an error, completely cancelled.
  Queries already included in a transaction (custom management of referential integrity) are not affected by this option.
  When this option is not checked, no automatic transaction is generated (except for the SELECT... FOR UPDATE queries, please refer to the SELECT command). By default, this option is not checked.
  You can also manage this option by programming using the SET DATABASE PARAMETER command.
  Note: Only local databases queried by the 4D SQL engine are affected by this parameter. In the case of external connections to other SQL databases, the auto-commit mechanism is handled by the remote SQL engines.
• Case-sensitive String Comparison: This option can be used to modify the case sensitivity of characters in SQL queries. It is checked by default, which means that the SQL engine differentiates between upper and lower case letters as well as between accented characters when comparing strings (sorts and queries). For example “ABC”=“ABC” but “ABC” # “Abc” and "abc" # "âbc."
  In certain cases, for example to align the functioning of the SQL engine with that of the 4D engine, you may want string comparisons not to be case sensitive (“ABC”=“Abc"="âbc"). To do this, you simply need to deselect this option.
  You can also manage this option by programming using the SET DATABASE PARAMETER command.

4D implements the concept of schemas. A schema is a virtual object containing the tables of the database. In SQL, the purpose of schemas is to assign specific access rights to different sets of database objects. Schemas divide the database into independent entities which together make up the entire database. In other words, a table always belongs to one and only one schema.

• To create a schema, you must use the CREATE SCHEMA command. You can then use the GRANT and REVOKE commands to configure the types of access to the schemas.
• To associate a table with a schema, you can call the CREATE TABLE or ALTER TABLE commands. You can also use the "Schemas" pop-up menu of the Inspector palette in the Structure editor of 4D. This menu lists all the schemas defined in the database.
• The DROP SCHEMA command can be used to delete a schema.

Note: The control of access via schemas only applies to connections from the outside. The SQL code executed within 4D via Begin SQL/End SQL tags, SQL EXECUTE, QUERY BY SQL, and so on, always has full access.

Multi-database architecture is implemented at the level of the 4D SQL server. From within 4D it is possible:

• To connect to an existing database using the SQL LOGIN command.
• To switch from one database to another using the 4D SQL LOGIN and SQL LOGOUT commands.
• To open and use another 4D database instead of current database using the USE DATABASE command.

In the SQL language, a primary key is used to identify the table column(s) (field(s)) responsible for uniquely specifying the table records (rows). In particular, setting a primary key is required for the record replication function in a 4D table (see Replication via SQL) and for logging 4D tables as of v14.

4D allows you to manage primary keys in two ways:

• Via the SQL language
• Using the 4D Structure editor.

Note:

• You can also set primary keys using the 4D Primary key manager in the Design mode.
• For a description of the use of primary keys, see the Rules for using primary key fields.

You can set a primary key when a table is created (via the CREATE TABLE command) or when adding or modifying a column (via the ALTER TABLE command). The primary key is specified using the PRIMARY KEY clause followed by the column name or a list of columns. For more information, refer to the section.

4D lets you create and remove primary keys directly via the context menu of the structure editor.

For more information about this point, refer to Primary keys in the 4D Design Reference manual.

The integrated SQL engine of 4D supports standard SQL views. A view is a virtual table with data that may come from several different database tables. Once a view is defined, you can use it in a SELECT statement just like a real table.

Data found in a view are defined using a definition query based on the SELECT command. Real tables used in the definition query are called "source tables". An SQL view contains columns and rows just like a standard table, but it does not actually exist; it is only a representation resulting from processing and stored in memory during the session. Only the definition of the view is actually saved temporarily.

Two SQL commands are used to manage views in 4D v14: CREATE VIEW and DROP VIEW.