Principles for integrating 4D and the 4D SQL engine

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: one billion x 64.
• 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 the 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.

A security property has been added for 4D project methods: Available through SQL:

When it is 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 when comparing strings (sorts and queries). For example “ABC”=“ABC” but “ABC” # “Abc.”
  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”). 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 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.

The SQL catalogue of 4D includes seven system tables, which can be accessed by any SQL user having read access rights: _USER_TABLES, _USER_COLUMNS, _USER_INDEXES, _USER_CONSTRAINTS, _USER_IND_COLUMNS, _USER _CONS_ COLUMNS and _USER_SCHEMAS.

In accordance with the customs of SQL, system tables describe the database structure. Here is a description of these tables and their fields:

Note: The system tables are assigned to a specific schema named SYSTEM_SCHEMA. This schema cannot be modified or deleted. It does not appear in the list of schemas displayed in the table Inspector palette. It can be accessed in read-only by any user.

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 column(s) (field(s)) responsible for uniquely specifying the records (rows) in a table. Setting a primary key is more particularly necessary for the record replication function in a 4D table (see the section).

4D allows you to manage the primary key of a table in two ways:

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

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.

To create a primary key:

1. Select the field(s) that will make up the primary key of the table.
2. Right click and choose the Create primary key command in the context menu:
   

The fields included in the primary key then appear underlined in the editor and their SQL description displays the PRIMARY KEY keyword. 

The field(s) belonging to the primary key must not contain any duplicated values. If any duplicated values already exist in the table records, a warning dialog box appears to indicate this. 

Note: The column(s) belonging to the primary key do not accept NULL values. 

To remove a primary key from a table:

1. Right click on the table containing the primary key and choose the Remove primary key command in the context menu:
   
   A confirmation dialog box appears. If you click OK, the primary key is immediately removed.