4D v11 SQL Release 3 (11.3) ADDENDUM

4D v11 SQL Release 3 (11.3)

A

DDENDUM

Welcome to release 3 of 4D v11 SQL. This document describes the new features and modifications found in this new version of the program, as summarized below:

„ Several new features concerning the capacities of the 4D SQL engine and server, with, more particularly, the support of schemas and direct connections (without the ODBC driver) of 4D databases via SQL.

„ The procedure for converting databases from 4D 2004 to 4D v11 has been modified in order to facilitate successive conversions of the same structure,

„ Error management via programming has been simplified: now a single generic command, GET LAST ERROR STACK, intercepts all the errors generated by the 4D engine,

„ Data exchanges between 4D databases via SOAP Web services have been optimized,

„ New XML DOM commands have been added.

Note: 4D v11 SQL r3 comes with a new SVG component that facilitates the use of the integrated SVG rendering engine of 4D. This component is covered in a separate documentation.

SQL Engine

Schemas

The integrated SQL engine of 4D v11 SQL implements the concept of

schemas. This implementation has resulted in interface modifications and the support of new SQL commands. Schemas are created, modified and deleted via SQL commands. A new option in the Inspector palette

Overview A schema is a virtual object containing database tables. 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.

When a database is created or converted with 4D v11 SQL r3 or a subsequent version, a default schema is created in order to group together all the tables of the database. This schema is named "DEFAULT_SCHEMA." It cannot be deleted or renamed.

In previous versions of 4D, access rights via SQL were set globally for the database. From now on, they will be set per schema. Each schema can be assign one of the following access types:

„ Read only (data)

„ Read/Write (data)

„ Full (data and structure)

Note: - When converting a previous database to version 11.3 or subsequent versions, the global access rights (as defined on the SQL page of the application Preference) are transferred to the default schema. - As in previous versions, access control only applies to external connections. The SQL code executed within 4D via the Begin SQL/End SQL tags, SQL EXECUTE, QUERY BY SQL, etc., still have full access.

Only the Designer and/or Administrator of the database can create, modify or delete schemas.

If the access management system of 4D is not activated (in other words, if no password has been assigned to the Designer), all users can create and modify schemas with no restriction.

Creation of a schema Schemas can only be created by programming, using the following SQL command:

SQL Engine

When you create a new schema, by default the associated access rights are as follows:

„ Read-only (Data): Everybody

„ Read/Write (Data): Everybody

„ Full (Data & Structure): Nobody

` Example of creating a schema named "Accounting_Rights":

CREATE SCHEMA Accounting_Rights Assigning tables to

schemas Each table belongs to only one schema. You can assign a schema to a _{table either using the Inspector, or by programming.} „ Assigning in structure

You can assign a schema to a table in the SQL area of the table Inspec-tor palette (structure window), via a pop-up menu listing the schemas defined in the database:

„ Assigning by programming

It is also possible to assign a table to a schema when it is created using the SQL language.

` Creating a table and assigning it to the MySchema1 schema:

CREATE TABLE MySchema1.MyTable

If the MYSCHEMA1 schema does not exist, an error is returned and the table is assigned to the default schema.

` Creating a table and assigning it to the default schema:

CREATE TABLE MyTable „ Modification of assignment

To modify the current assignment of a schema, you can use the SQL

ALTER TABLE command:

ALTER TABLE Table_Name SET SCHEMA Schema_Name

` Transferring the "MyTable" table to the "MySchema2" schema:

ALTER TABLE MyTable SET SCHEMA MySchema2

Note: System tables (_USER_TABLES, _USER_COLUMNS, _USER_INDEXES, _USER_CONSTRAINTS, _USER_IND_COLUMNS and

_USER_CONS_COLUMN) are assigned to a specific schema named

SYSTEM_SCHEMA. This schema cannot be removed or modified by a user. It does not appear in the list of schemas displayed in the table Inspector. It is read-only for all users.

Renaming a schema You can rename a schema using the SQL ALTER SCHEMA command:

ALTER SCHEMA former_name RENAME TO new_name

` Renaming the "MyFirstSchema" schema to "MyLastSchema":

ALTER SCHEMA MyFirstSchema RENAME TO MyLastSchema

Modifying access rights You can modify the access rights associated with a schema using the SQL GRANT command:

SQL Engine 4D_User_Group represents the name of the 4D user group to which you want to assign access rights to the schema.

Note: 4D allows group names to include spaces and/or accented characters that are not accepted by standard SQL. In this case, you must put the name between the [ and ] characters. For example: GRANT READ ON [my schema] TO [the admins!]

The READ, READ-WRITE and ALL keywords correspond to the types of access specified on the SQL page of the Preferences:

„ READ establishes Read-only access (data)

„ READ_WRITE establishes Read/Write access (data)

„ ALL establishes full access mode (data and structure).

` You want to grant access in read/write mode to data of the MySchema1 schema to the "Power_Users" group:

GRANT READ_WRITE ON MySchema1 TO POWER_USERS

Deleting access rights You can delete specific access rights associated with a schema using the SQL REVOKE command:

REVOKE [READ | READ_WRITE | ALL] ON Schema_Name

When you execute this command, you assign the Nobody pseudo-group of users to the access right specified.

` You want to delete all read/write access to the MySchema1 schema:

REVOKE READ_WRITE ON MySchema1

Deleting schemas It is possible to delete any schema, except for the default one. When you delete a schema, all the tables that were assigned to it are transferred to the default schema. The transferred tables inherit the access rights of the default schema.

Deleting a schema is carried out using the SQL DROP SCHEMA

command:

DROP SCHEMA Schema_name

` You want to delete the MyFirstSchema schema (to which Table1 and Table2 are assigned):

After this operation, both Table1 and Table2 will be reassigned to the default schema.

If you attempt to delete a schema that does not exist or that has already been deleted, an error is generated.

Referential integrity 4D ensures the principle of referential integrity independently from access rights. For example, suppose that you have two tables, Table1 and Table2, connected by a Many-to-One type relation (Table2 -> Table1). Table1 belongs to schema S1 and Table2 to schema S2. A user that has access rights to schema S1 but not to S2 can delete records in Table1. In this case, in order to respect the principles of referential integrity, all the records of Table2 that are related to records deleted from Table1 will also be deleted.

System table The implementation of SQL schemas in 4D has led to a system table named _USER_SCHEMAS being added:

Furthermore, the SCHEMA_ID column was added to the _USER_TABLES

system table in order to store which tables belong to the schemas.

Importing and

exporting schemas 4D allows schemas defined in the database to be exported and _{imported using system tables. This is useful in particular when}

updating the structure: simply import the schema definition into the new structure so that it is operational immediately.

_USER_SCHEMAS Describes the database schemas

SCHEMA_ID INT32 Schema number

SCHEMA_NAME VARCHAR Name of schema

READ_GROUP_ID INT32 Number of group having read-only access

READ_GROUP_NAME VARCHAR Name of group having read-only access

READ_WRITE_GROUP_ID INT32 Number of group having read-write access

READ_WRITE_GROUP_NAME VARCHAR Name of group having read-write access

ALL_GROUP_ID INT32 Number of group having full access

ALL_GROUP_NAME VARCHAR Name of group having full access

SQL Engine

To do this, simply use the new system table named _USER_SCHEMAS. Data related to schemas can be stored in an encrypted BLOB using the following type of method:

Begin SQL

SELECT * from [_USER_SCHEMAS] INTO Array1,Array2,Array3…; End SQL

VARIABLE TO BLOB(Array1;SchemaBlob;*) VARIABLE TO BLOB(Array2;SchemaBlob;*) VARIABLE TO BLOB(Array3;SchemaBlob;*) …

The BLOB can then be saved into a text file or a field.

Importing schemas into a database is carried out according to the principle illustrated below:

`Initialization of array variables

BLOB TO VARIABLE(SchemaBlob;Array1;Offset) BLOB TO VARIABLE(SchemaBlob;Array2;Offset) BLOB TO VARIABLE(SchemaBlob;Array3;Offset) …

`Replace the current _USER_SCHEMAS system table by the one `that was stored in the BLOB

$Execute:="INSERT into [_USER_SCHEMAS] ID, SchemaName, ...VALUES ("…)

Begin SQL

EXECUTE IMMEDIATE $Execute; End SQL

Note: Naturally, the user that executes the import method must have adequate access rights (they must be the Designer or the Administrator).

INSERT

The INSERT command has two new features:

„ Possibility of inserting file contents

Insertion of file contents

(INFILE) The _{set the values of a new record. To do this, the command accepts the} INSERT command can now use the contents of an external file to

new optional INFILEkeyword: INSERT INTO {sql_name | sql_string} [(column_ref, ..., column_ref)]

{VALUES([INFILE]{arithmetic_expression |NULL}, ..., [INFILE]{arithmetic_expression |NULL}) |subquery}

The INFILE keyword must only be used with VARCHAR type expressions. When the INFILE keyword is passed, the

arithmetic_expressionvalue is evaluated as a file pathname; if the file is found, the contents of the file are inserted into the corresponding column. Only fields of the Text or BLOB type can receive values from an INFILE. The contents of the file are transferred as raw data, with no interpretation.

The file searched for must be on the computer hosting the SQL engine, even if the query comes from a remote client. Similarly, the pathname must be expressed respecting the syntax of the operating system of the SQL engine. It can be absolute or relative.

Multi-row insertions The SQL engine integrated into 4D now allows the insertion of multi-row values, which can simplify and optimize the code. Executing code that uses the multi-row insertion syntax is particularly optimized when inserting large quantities of data.

The multi-row insertion syntax is as follows:

INSERT INTO {sql_name | sql_string} [(column_ref, ..., column_ref)]

VALUES(arithmetic_expression, ..., arithmetic_expression), ..., (arithmetic_expression, ..., arithmetic_expression);

SQL Engine

This syntax allows you to avoid the repetition of rows, which was necessary in previous versions of 4D. For example:

Begin SQL

INSERT INTO MyTable (Field1,Field2,BoolField,DateField,TimeField, InfoField) VALUES (1,1,1,'11/01/01','11:01:01',’First row’); INSERT INTO MyTable (Field1,Field2,BoolField,DateField,TimeField,

InfoField) VALUES (2,2,0,'12/01/02','12:02:02',’2nd row’), ………

INSERT INTO MyTable (Field1,Field2,BoolField,DateField,TimeField, InfoField) VALUES (7,7,1,'17/01/07','17:07:07',’7th row’); End SQL

Now you can write:

Begin SQL

INSERT INTO MyTable

(Field1,Field2,BoolField,DateField,TimeField, InfoField) VALUES (1,1,1,'11/01/01','11:01:01',’First row’), (2,2,0,'12/01/02','12:02:02',’2nd row’), (3,3,1,'13/01/03','13:03:03',’3rd row’), ……… (7,7,1,'17/01/07','17:07:07',’7th row’); End SQL

You can also use variables, for example:

vid1:=1 vidx1:=1 vbol1:=1 vdate1:= !11/01/01! vtime1:=?11:01:01? vtext1:=”First row” `Multi-row insertion Begin SQL

INSERT INTO MyTable

(Field1,Field2,BoolField,DateField,TimeField, InfoField) VALUES

(:vid1, :vidx1, :vbol1, :vdate1, :vtime1, :vtext1), (2,2,0,'12/01/02','12:02:02',’2nd row’),

………

(7,7,1,'17/01/07','17:07:07',’7th row’); End SQL

It is also particularly useful to use arrays with this syntax: ARRAY TEXT(vArrId;0) ARRAY TEXT(vArrIdx;0) ARRAY TEXT(vArrText;0) ARRAY BOOLEAN(vArrbool;0) ARRAY DATE(vArrdate;0) ARRAY LONGINT(vArrL;0) ... Begin SQL

INSERT INTO MyTable

(Field1,Field2,BoolField,DateField,TimeField, InfoField) VALUES

( :vArrId, :vArrIdx, :vArrbool, :vArrdate, :vArrL, :vArrText), End SQL

Note: You cannot combine simple variables and arrays in the same INSERT

statement.

SELECT

The SELECT command now accepts references to 4D variables in the LIMIT and OFFSET clauses. For example, you can now write:

SELECT... OFFSET :var1 LIMIT :var2 ...

SQL Server

Direct Connection

Between Two 4D

Databases via SQL

4D v11 SQL r3 allows a 4D application to connect to and directly exchange data with another 4D database via SQL. In prior versions of 4D, this operation required passing via the ODBC protocol. The main advantage of direct connections is that exchanges are faster.

Overview Direct SQL connections are based on the SQL LOGIN1 command. In addition to ODBC data sources or the current database, this command can now be used to directly specify a 4D Server database as the target for SQL queries that are made subsequently. The 4D Server database can be defined via its IP address or its publication name.

1. SQL LOGIN is the new command name for the existing ODBC LOGIN com-mand (see the “Renaming of ODBC Commands” paragraph on page 13).

SQL Server

The SQL queries concerned are those made via the SQL EXECUTE

command as well as those framed by the Begin SQL / End SQL tags (when the * parameter is passed). The SQL LOGOUT command can then be used to end the session.

For a detailed description of the syntax of the SQL LOGIN command, please refer to the “SQL LOGIN” paragraph on page 15.

Note that these principles have led to a reorganization of the existing commands (see the “Renaming of ODBC Commands” paragraph on page 13).

Configuration Only a 4D Server v11 SQL application can reply to direct SQL queries coming from other 4D applications.

Similarly, only 4D applications of the "Professional" product line can open a direct connection to another 4D application.

The possibilities of external access to the 4D SQL server are now as follows:

Notes on functioning During a direct connection, data exchange is automatically carried out in synchronous mode, which eliminates questions related to

synchronization and data integrity.

Only one connection is authorized per process. If you want to establish several simultaneous connections, you must create as many processes as needed. 4D ODBC Driver Excel Crystal Report, Others... 4D Application SQL Clients Driver Manager 4D 4D Server 4D Application 4D Server Application New in v 11.3

Direct connections are made via the TCP/IP protocol. If the Enable SSL

option is checked for the target side of the connection (4D Server) on the "SQL" page of the Preferences, the communication will be secured. Direct connections are only authorized by 4D Server if the SQL server is started.

Encoding of Queries

The encoding used by 4D for external SQL queries can now be configured using the SQL SET OPTION command.

SQL SET OPTION

SQL SET OPTION (option; value)

Note: SQL SET OPTION is the new name of the ODBC SET OPTION command

(see the “Renaming of ODBC Commands” paragraph on page 13).

The SQL SET OPTION command can now be used to set the encoding of text used for queries sent to external sources (via the SQL pass-through).

Note: The SQL SET OPTION command can be used to find out the current encoding.

To do so, a new constant has been added to the "SQL" theme: SQL Charset (value 100). When you pass this constant in the option

parameter, you must pass, in the value parameter, the MIBEnum ID of the character set to be used. The MIBEnum numbers are referenced at the following address: http://www.iana.org/assignments/character-sets. For example, if you want to use UTF-7 type encoding, you must execute the following statement:

SQL SET OPTION(SQL Charset;103)

By default, for internal use, 4D uses UTF-8 encoding (value 106). When you modify the encoding using the SQL SET OPTION command, the modification affects the current process and the current

connection.

Parameter Type Description

option Longint Æ Number of option to set value Longint Æ New value of option

SQL SET OPTION

If the command has been executed correctly, the OK system variable is set to 1. Otherwise, for example if you execute the SQL SET OPTION

command when there is no valid external connection, OK is set to 0.

Renaming of ODBC

Commands

The implementation of direct external connections to 4D Server databases led to a reorganization of existing commands. The scope of the "External Data Source" theme commands has been extended. A new prefix and the abandoning of certain commands was necessary.

„ All the commands of the "External Data Source" theme, previously pre-fixed by "ODBC," have been renamed. They are now prepre-fixed by "SQL". For example, ODBC LOGIN is now SQL LOGIN.

This global modification was necessary because these communication commands can now be used to work with "direct" SQL queries and no longer necessarily use the ODBC protocol.

„ For better clarity, all the commands now prefixed by "SQL" have been moved to the "SQL" theme. The "External Data Source" theme has been removed.

„ The constants associated with this theme have also been prefixed with "SQL" and the "External Data Source" constants theme has been renamed "SQL".

„ The SQL LOGIN command can now be used to direct SQL queries to the required data source: internal database, ODBC source or 4D Server database. As for the SQL LOGOUT command, it can be used to close a connection that has been opened previously.

The USE INTERNAL DATABASE and USE EXTERNAL DATABASE commands are therefore no longer necessary. Their use is not recommended and they will not be maintained in future versions of 4D.

The following table summarizes these modifications:

4D v11 SQL 4D v11 SQL r3

Commands "External Data Source" theme Deleted

Commands transferred to the "SQL" theme

ODBC CANCEL LOAD ODBC End selection ODBC EXECUTE ODBC EXPORT

ODBC GET LAST ERROR ODBC GET OPTION ODBC IMPORT ODBC LOAD RECORD ODBC LOGIN

ODBC LOGOUT ODBC SET OPTION ODBC SET PARAMETER

SQL CANCEL LOAD SQL End selection SQL EXECUTE SQL EXPORT

SQL GET LAST ERROR SQL GET OPTION SQL IMPORT SQL LOAD RECORD SQL LOGIN SQL LOGOUT SQL SET OPTION SQL SET PARAMETER "SQL" theme

USE INTERNAL DATABASE USE EXTERNAL DATABASE

Obsolete, use SQL LOGOUT Obsolete, use SQL LOGIN

Constants

"External Data Source" theme Renamed "SQL" ODBC All Records

ODBC Asynchronous

ODBC Connection Time Out ODBC Max Data Length ODBC Max Rows ODBC Param In ODBC Param In Out ODBC Param Out ODBC Query Time Out

SQL All Records SQL Asynchronous

SQL Connection Time Out SQL Max Data Length SQL Max Rows SQL Param In SQL Param In Out SQL Param Out SQL Query Time Out

SQL LOGIN

SQL LOGIN

SQL LOGIN{(dataEntry; userName; password{;*})}

Note: SQL LOGIN is the new name of the ODBC LOGIN command.

The SQL LOGIN command can be used to open a connection with the SQL data source specified in the dataEntry parameter. It designates the target of the SQL code executed subsequently in the application:

„ via the SQL EXECUTE command

„ via the code placed within the Begin SQL / End SQL tags (if the * parameter is passed, see below).

The SQL data source can either be:

„ an external 4D Server database (new feature in 4D v11 SQL r3),

„ an external ODBC source,

„ the internal SQL engine.

In dataEntry, you can pass one of the following values:

„ an IP address

Syntax: IP:<IPAddress>{:<TCPPort>}

In this case, the command opens a direct connection with the 4D Server database executed on the machine with the IP address specified. On the "target" machine, the SQL server must be started. If you pass a TCP port number, it must have been specified as the publication port of the SQL server in the "target" database. If you do not pass a TCP port number, the default port will be used (19812). The TCP port number of the SQL server can be modified on the SQL/Configuration page of the application Preferences.

Refer to examples 1 and 2.

Parameter Type Description

dataEntry Text Æ Name of external database or IP address of external database or

Name of ODBC data source or "" to display selection dialog userName Text Æ Name of user

password Text Æ User password

* * Æ Apply to Begin SQL/End SQL If omitted: no (internal database) If passed: yes

„ a 4D database publication name

Syntax: 4D:<Publication_Name>

In this case, the command opens a direct connection with the 4D Server database whose publication name on the network corre-sponds to the name specified. The network publication name of a data-base is set on the Client-Server/Configuration page of the application Preferences.

Refer to example 4.

Note: The TCP port number of the target 4D SQL server (that publishes the 4D database) and the TCP port number of the SQL server of the 4D application that opens the connection must be the same.

„ a valid ODBC data source name

Syntax: ODBC:<My_DSN> or <My_DSN>

In this case, the dataEntry parameter contains the name of the data source as it has been set in the ODBC driver manager. This principle corresponds to the previous functioning of the ODBC LOGIN com-mand.

The syntax without the "ODBC:" prefix has been kept in order to ensure compatibility with previous versions of 4D; however, for rea-sons of code readability, it is recommended to use the "ODBC:" prefix. Refer to example 4.

„ an empty string

Syntax: ""

In this case, the command displays the connection dialog box so that the data source to be connected to can be entered manually:

SQL LOGIN

This dialog box includes several pages. The TCP/IP page includes the following elements:

„ Target Name: This menu is built using two lists:

- The list of databases that have been opened recently in direct con-nection. The mechanism for updating this list is the same as that of the 4D application, except that the folder containing the .4DLink files is named "Favorites SQL v11" instead of "Favorites v11". - The list of 4D Server applications whose SQL server is started and whose TCP port for SQL connections is the same as that of the source application. This list is dynamically updated on each new call to the SQL LOGIN command without the dataEntry parameter. If the "^" character is placed before a database name, this indicates that the connection has been made in secured mode via SSL.

„ Network Address: This area displays the IP address and possibly the TCP port of the database selected in the Target Name menu.

You can also enter an IP address in this area and then click on the

Connection button in order to connect to the corresponding 4D Server database. You can also specify the TCP port by entering a colon (:) followed by the port number after the address. For exam-ple: 192.168.93.105:19855.

„ User Name and Password: These areas can be used to enter the con-nection identifiers.

The User DSN and System DSN pages display, respectively, the list of user and system ODBC data sources specified in the ODBC driver of the machine. These pages can be used to select a data source and enter the identifiers in order to open a connection with an external ODBC data source.

If the connection is established, the OK system variable is set to 1. Otherwise, it is set to 0 and an error is generated. This error can be intercepted via an error-handling method installed by the ON ERR CALL command.

„ the SQL_INTERNAL constant Syntax: SQL_INTERNAL

In this case, the command redirects subsequent SQL queries to the internal SQL engine of the database.

The optional * parameter has been added for compatibility reasons. In previous versions of 4D, the ODBC LOGIN command did not affect the SQL code included within the Begin SQL/End SQL tags (to do so, it was necessary to use the USE INTERNAL DATABASE and USE EXTERNAL DATABASE commands). In order not to modify the functioning of existing databases, a call to SQL LOGIN without the * parameter will not change the target of the SQL code executed within the Begin SQL/End SQL tags.

If you want for the code placed within the Begin SQL/End SQL tags to be applied to the source specified by the SQL LOGIN command, it is necessary to pass the * parameter.

Note: In the case of a direct connection, if you pass empty strings in the

userName and password parameters, the connection will only be accepted if 4D passwords are not activated in the target database. Otherwise, the connection will be refused.

To close the current connection and free the memory, simply execute the SQL LOGOUT command. All the SQL queries are then sent to the internal SQL engine of the database.

If you call SQL LOGIN again without having explicitly closed the current connection, it will be closed automatically.

` Example 1: Opening of a direct connection with the 4D Server v11 SQL application executed on the machine having the IP address

192.168.45.34 and replying on the default TCP port. The SQL queries executed via the SQL EXECUTE command will be redirected to this con-nection; the queries included within the Begin SQL/End SQL tags will not be redirected.

SQL LOGIN("IP:192.168.45.34";"John";"azerty")

` Example 2: Opening of a direct connection with the 4D Server v11 SQL application executed on the machine having the IP address

192.168.45.34 and replying on the TCP port 20150. The SQL queries executed via the SQL EXECUTE command and the queries included within the Begin SQL/End SQL tags will be redirected to this connection.

SQL LOGIN

` Example 3: Opening of a direct connection with the 4D Server v11 SQL application which publishes, on the local network, a database whose publication name is "Accounts_DB." The TCP port used for the SQL server of both databases (set on the SQL/Configuration page of the Preferences) must be the same (19812 by default). The SQL queries executed via the SQL EXECUTE commandwill be redirected to this connection; the queries included within the Begin SQL/End SQL tags will not be redirected.

SQL LOGIN ("4D:Accounts_DB";"John";"azerty")

` Example 4: Opening of a connection via the ODBC protocol with an external data source named "MyOracle." The SQL queries executed via the SQL EXECUTE command and the queries included within the Begin SQL/End SQL tags will be redirected to this connection.

SQL LOGIN ("ODBC:MyOracle";"Scott";"Tiger";*)

` This example illustrates the connection possibilities provided by the

SQL LOGIN command:

ARRAY TEXT (30;aNames) ARRAY LONGINT(aAges;0)

SQL LOGIN("ODBC:MyORACLE";"Mark";"azerty") If (OK=1)

`The following query will be redirected to the external ORACLE `database

SQL EXECUTE("SELECT Name, Age FROM PERSONS";aNames; aAges) `The following query will be sent to the local 4D database

Begin SQL

SELECT Name, Age FROM PERSONS INTO :aNames, :aAges; End SQL

`The following SQL LOGIN command closes the current connection `with the external ORACLE database and opens a new connection `with an external MySQL database

SQL LOGIN ("ODBC:MySQL";"Jean";"qwerty";*) If (OK=1)

`The following query will be redirected to the external `MySQL database

`The following query will also be redirected to the external `MySQL database

Begin SQL

SELECT Name, Age FROM PERSONS INTO :aNames, :aAges; End SQL

SQL LOGOUT

`The following query will be sent to the local 4D database Begin SQL

SELECT Name, Age FROM PERSONS INTO :aNames, :aAges; End SQL

End if End if

Location of SSL Files

for SQL Connections

If you want for the 4D SQL server to use the SSL protocol for processing connections, you must now:

„ check the Enable SSL option in the application preferences (SSL/Configuration page),

„ copy the key.pem and cert.pem files into the following location:

MyDatabase/Preferences/SQL (where "MyDatabase" represents the database folder/package).

On SQL

Authentication

Database Method

The functioning of the On SQL Authentication database method has been modified in order to strengthen the security of the 4D SQL server. From now on, in order for the connection, and thus the query, to be accepted, $0 must return True AND the CHANGE CURRENT USER

command must be called and executed successfully.

In previous versions of 4D, the connection was accepted as long as $0 was True, regardless of whether the CHANGE CURRENT USER command was called or not. Calling this command and its correct execution are now mandatory for the query to be accepted.

Support of Flash

Player Queries

The new Allow Flash Player requests option, available on the

SQL/Configuration page of the Preferences, can be used to enable the mechanism for supporting Flash Player requests by the 4D SQL server.

Multiple Conversions of Databases

This mechanism is based on the presence of a file, named "socketpolicy.xml", in the preferences folder of the database

(Preferences/SQL/Flash/). This file is required by Flash Player in order to allow cross-domain connections or connections by sockets of Flex (Web 2.0) applications.

In the previous version of 4D, this file had to be added manually. From now on, the activation is carried out using the Allow Flash Player requests option: when you check this option, Flash Player requests are accepted and a generic "socketpolicy.xml" file is created for the

database if necessary.

When you deselect this option, the "socketpolicy.xml" file is disabled (renamed). Any Flash Player queries received subsequently by the SQL server are then rejected.

On opening of the database, the option is checked or not checked depending on the presence of an active "socketpolicy.xml" file in the preferences folder of the database.

Multiple Conversions of Databases

The mechanism for converting 4D databases from version 200x to version 11 has been modified in order to facilitate successive

conversions of the same structure file (with a single conversion of the data file).

This scenario occurs in the case where an application is developed in version 2004 with an initial conversion to v11 then successive

conversions of the structure file during the development phase, while the converted v11 application is in use. Only the structure file is therefore converted subsequently.

In order to allow correct functioning of the application in this context, a new file, named catalog.xml, is generated next to the v11 files during conversions. This file contains the description of the converted database structure as well as the new internal identifiers used in v11. Its presence is necessary so that you can carry out several conversions of the structure while keeping the same data file.

Removal of WEDD

Preference

In previous versions of 4D, it was possible to specify a custom character string in order to associate a structure file with a data file (WEDD option, "Link signature between structure file and data file" in the Preferences).

From now on, this mechanism is no longer used. The link between data files and structure files is mandatory and automatic via an internal number. Consequently, the WEDD option has been removed from the Preferences. 4D automatically assigns internal link numbers when databases are created or converted.

Error Management by Programming

Management of errors that occur during code execution has been optimized and simplified in 4D v11 SQL r3.

The principle for error interception remains unchanged: a method installed using the ON ERR CALL command receives the errors and can be used to process them. Similarly, the Error system variable can be used, within this method, to find out the current error number. On the other hand, a single command can now be used to get information about the intercepted error: GET LAST ERROR STACK. Called within the error-handling method, this command can be used to process all types of errors, regardless of the context (see description below).

In previous versions of 4D, it was necessary to use several different commands depending on the execution context: GET XML ERROR, GET XSLT ERROR, Get Web Service error info, etc.

Note: GET LAST ERROR STACK is the new name of the GET LAST SQL ERROR

command, already found in previous versions of 4D. Its scope has been extended and, for better clarity, it has been moved to the

GET LAST ERROR STACK

The other error information commands have been kept for compatibility but their usefulness is now limited:

„ Get Web Service error info, Web Services (Client) theme

„ ODBC GET LAST ERROR (renamed SQL GET LAST ERROR), SQL theme

(see the “Renaming of ODBC Commands” paragraph on page 13)

„ GET XML ERROR, XML Utilities theme

„ GET XSLT ERROR, XML Utilities theme

GET LAST ERROR

STACK

GET LAST ERROR STACK(codesArray; intCompArray; textArray)

Note: GET LAST ERROR STACK is the new name of the GET LAST SQL ERROR

command.

The GET LAST ERROR STACK command returns the information

concerning the current error "stack" of the 4D application. When a 4D statement causes an error, the current error stack contains a description of the error as well as any series of errors generated. For example, a "disk full" type error causes a write error in the file then an error in the record saving command: the stack will therefore contain three errors. If the last 4D statement did not generate an error, the current error stack is empty.

This generic command can be used to process any type of error that may occur.

However, it should be noted that in order to obtain detailed

information concerning the errors generated by an ODBC source, it will be necessary to use the SQL GET LAST ERROR command.

GET LAST ERROR STACK must be called within an error-handling method installed by the ON ERR CALL command.

Theme: Interruptions

Parameter Type Description

codesArray Number array Å Error numbers

intCompArray String array Å Internal component codes textArray String array Å Text of errors

Web Services (Client)

Optimization of

Inter-4D Web

Services

Data exchanges between two 4D applications via Web Services have been optimized in 4D v11 SQL r3. This optimization has resulted in new options for the SET WEB SERVICE OPTION command as well as for the SET DATABASE PARAMETER command (see the “4D Environment” paragraph on page 25).

SET WEB SERVICE

OPTION

SET WEB SERVICE OPTION(option; value)

The SET WEB SERVICE OPTION command lets you enable an internal compression mechanism for SOAP requests in order to accelerate inter-4D application exchanges.

For this purpose, there are two new constants, found in the "Web Services (Client" theme: (Web Service Deflate Compression and Web Service No Compression).

To enable compression of SOAP requests, simply execute the following statement on the 4D client of the Web Service:

SET WEB SERVICE OPTION(Web Service HTTP Compression; Web Service Deflate Compression)

Note: "Deflate" is the name of the compression algorithm used internally by 4D.

In this case, the data of the next SOAP request sent by the Web Service client will be compressed using a standard HTTP mechanism before being sent to the 4D SOAP server. The server will decompress and parse the request, then will reply automatically using the same mechanism.

Note: This mechanism cannot be used for requests sent to a 4D SOAP server whose version is earlier than 11.3.

Only the request that follows the call to the SET WEB SERVICE OPTION

command is affected. You must therefore call this command each time you want to use compression.

Parameter Type Description

option Longint Æ Code of option to set

AUTHENTICATE WEB SERVICE

By default, 4D does not compress Web Service HTTP requests.

So that you can further optimize this functioning, additional options configure the threshold and compression rate of the requests. These options can be accessed via the SET DATABASE PARAMETER command (see the “4D Environment” paragraph on page 25).

AUTHENTICATE

WEB SERVICE

AUTHENTICATE WEB SERVICE (name; password{; authMethod}{;*})

The AUTHENTICATE WEB SERVICE command now accepts an asterisk (*) as the last parameter. When this parameter is passed, you indicate that the authentication information is to be sent to an HTTP proxy.

This configuration must be implemented when there is a proxy that requires authentication between the Web Service client and the Web Service itself. If the Web Service is itself authenticated, a double authentication is required.

` Authentication with a Web Service located behind a proxy:

`Authentication to Web Service in DIGEST mode AUTHENTICATE WEB SERVICE("SoapUser";"123";2)

`Authentication to proxy in default mode

AUTHENTICATE WEB SERVICE("ProxyUser";"456";*) CALL WEB SERVICE(...)

4D Environment

SET DATABASE

PARAMETER, Get

database parameter

The SET DATABASE PARAMETER and Get database parameter commands accept two new selectors that are related to the optimization of inter-4D Web Services exchanges (see the “Optimization of Inter-4D Web Services” paragraph on page 24).

Selector = 50 (HTTP Compression Level)

„ Values: 1 to 9 (1 = faster, 9 = more compressed), -1 = best

compro-Parameter Type Description

name Text Æ User name

password Text Æ User password

authMethod Longint Æ Authentication method

„ Description: Sets the compression level for all compressed HTTP exchanges carried out for Web Services (client requests or server replies). Compressed exchanges are an optimization that you can implement when you have two 4D applications that are communi-cating via Web services (see the SET WEB SERVICE OPTION command on page 24).

This selector lets you optimize exchanges by either privileging speed of execution (less compression) or the quantity of compres-sion (less speed). The choice of a value depends on the size and type of data exchanged. Pass 1 to 9 in the value parameter where 1 is the fastest compression and 9 the highest. You can also pass -1 to get a compromise between speed and rate of compression.

By default, the compression level is 1 (faster compression).

Selector = 51 (HTTP Compression Threshold)

„ Values: Any Longint type value

„ Description: In the framework of inter-4D Web Services exchanges in optimized mode (see above), sets the size threshold for requests below which exchanges must not be compressed. This configura-tion is useful in order to avoid losing machine time by compressing small exchanges.

Pass the size expressed in bytes in value.

By default, the compression threshold is set to 1024 bytes.

XML

The "XML" command theme has been split up and the commands have been renamed; in addition, two new commands have been added.

Reorganization of

XML Commands

For better clarity, the commands of the "XML" theme have been divided into three new themes:

„ XML DOM: groups together the DOM commands

„ XML SAX: groups together the SAX commands

„ XML Utilities: groups together the generic XML commands (XSLT, error management and SVG commands).

DOM Get Root XML element

In addition, the DOM EXPORT TO PICTURE command has been renamed SVG EXPORT TO PICTURE and placed in the "XML Utilities" theme.

Finally, two new XML commands have been added in 4D v11 SQL r3 (see description below):

„ DOM Get Root XML element in the "XML DOM" theme,

„ SVG Find element ID by coordinates in the "XML Utilities" theme.

DOM Get Root XML

element

DOM Get Root XML element (elementRef) Æ String

The DOM Get Root XML element command returns a reference to the root element of the document to which the XML element passed in the parameter belongs. This reference can be used with other XML parsing commands.

If the command has been correctly executed, the OK system variable is set to 1. If an error occurs, for example if the element reference is invalid, the OK variable is set to 0 and the command causes an error. In this case, the command returns an empty string.

Theme: XML DOM

Parameter Type Description

elementRef String Æ XML element reference

Function result String Å Root element reference (16 char-acters) or "" in case of error

SVG Find element ID

by coordinates

SVG Find element ID by coordinates ({*;} pictureObject; x; y) Æ String

The SVG Find element ID by coordinates command returns the ID ("id" or "xml:id" attribute) of the XML element found at the location set by the coordinates (x,y) in the SVG picture designated by the pictureObject

parameter. This command can be used more particularly to create interactive graphic interfaces using SVG objects.

If you pass the optional * parameter, you indicate that the pictureObject

parameter is an object name (string). If you do not pass this parameter, you indicate that the pictureObject parameter is a field or variable. In this case, you do not pass a string but a field or variable reference (field or variable object only).

Note that it is not mandatory for the picture to be displayed in a form. In this case, the "object name" type syntax is not valid and you must pass a field or variable name.

The coordinates passed in the x and y parameters must be expressed in pixels relative to the top left corner of the picture (0,0). In the context of a picture displayed in a form, you can use the values returned by the

MouseX and MouseY system variables. These variables are updated in the On Clicked and On Double Clicked form events, as well as (new feature in v11.3) in the On Mouse Enter and On Mouse Move form events.

Note: In the picture coordinate system, [x;y] always specifies the same point, regardless of the picture display format, except in the case of the "Replicated" format.

Parameter Type Description

* Æ If specified, pictureObject is an

object name (string)

If omitted, pictureObject is a field or variable

pictureObject Picture Æ Object name (if * specified) or Field or Variable (if * omitted)

x Longint Æ X coordinate in pixels

y Longint Æ Y coordinate in pixels

Function result String Å ID of element found at the loca-tion X, Y

Other New Features

The point taken into account is the first point reached. For example, in the case below, the command will return the ID of the circle if the coordinates of point A are passed and that of the rectangle if the coordinates of point B are passed:

When the coordinates correspond to superimposed or composite objects, the command returns the ID of the first object having a valid ID attribute by going back, if necessary, among the parent elements. The command returns an empty string if:

„ the root is reached without an "id" attribute having been found,

„ the coordinates point does not belong to any object,

„ the "id" attribute is an empty string.

If pictureObject does not contain a valid SVG picture, the command returns an empty string and the OK system variable is set to 0.

Otherwise, if the command has been executed correctly, the OK system variable is set to 1.

Theme: XML Utilities

Other New Features

Management of

Document Size

By default, the language of 4D v11 SQL r3 now handles the size of documents using Real type values. In previous versions, these values were of the Longint type. This new feature allows the language to work with documents that are bigger than 2 GB.

The following commands of the "System Documents" theme are concerned by this modification:

„ Get document size (this command returns a Real)

„ SET DOCUMENT SIZE (size parameter)

„ SET DOCUMENT POSITION (offset parameter).

Point A Point B

Multi-row Data

Entry and Display in

List Boxes Under

Mac OS

It is now possible to enter and display text on several rows inside the same list box "cell" under Mac OS. In the previous version of 4D, this function was only available under Windows.

To add a line return in a list box under Mac OS, you can simply press the Option+Carraige return keys. Note that the height of the rows is not resized automatically.

Update of OK

Variable by

Statistical Functions

When you execute a statistical function (for example, Average) on a large selection of data, a progress thermometer appears. This thermometer has a Stop button that can be used to interrupt the operation.

Consequently, statistical functions now modify the OK system

variable: if the operation has been completed, the variable is set to 1. If the user clicks on the Stop button, the OK variable is set to 0.