4D v11 SQL Release 1 (11.1) ADDENDUM

(1)

4D v11 SQL Release 1 (11.1)

A

DDENDUM

Welcome to release 1 of 4D v11 SQL. This document outlines the new features and modifications provided in this version of 4D.

Query Analysis

4D provides three new commands that can be used to analyze with precision the execution of queries carried out on the data. These commands are intended for developers seeking to optimize their applications.

These three new commands are found in the “Queries” theme.

DESCRIBE QUERY

EXECUTION

DESCRIBE QUERY EXECUTION (status)

The DESCRIBE QUERY EXECUTION command can be used to enable or disable the query analysis mode for the current process. The command takes into account both queries carried out via the 4D language or by SQL.

Calling the command with the status parameter set to True enables the query analysis mode. In this mode, the 4D engine records internally two specific pieces of information for each subsequent query carried out on the data:

■ _{A detailed internal description of the query just before its execution, in}

other words, what was planned to be executed (the query plan),

■ A detailed internal description of the query that was actually executed

(the query path).

Parameter Type Description

status Boolean → True=Enable internal query analysis False=Disable internal query analysis

(2)

4D v11 SQL Release 1 (11.1) Addendum

The information recorded includes the type of query (indexed, sequential), the number of records found and the time needed for every query criteria to be executed.

You can then read this information using the new Get Last Query Plan

and Get Last Query Path commands.

Usually, the description of the query plan and its path are the same, but they may nevertheless differ because 4D might implement dynamic optimizations during the query execution in order to improve performance. For example, an indexed query may be converted dynamically into a sequential query if the 4D engine estimates that this might be faster — this is sometimes the case, more particularly, when the number of records being queried is low.

Pass False in the status parameter when you no longer need to analyze queries. The query analysis mode can slow down the application.

▼ _{The following example illustrates the type of information obtained}

using these commands in the case of an SQL query:

C_TEXT($vResultPlan;$vResultPath) ARRAY TEXT(aTitles;0)

ARRAY TEXT(aDirectors;0)

DESCRIBE QUERY EXECUTION(True) `analysis mode Begin SQL

SELECT ACTORS.FirstName, CITIES.City_Name FROM ACTORS, CITIES

WHERE ACTORS.Birth_City_ID=CITIES.City_ID ORDER BY 1

INTO :aTitles, :aDirectors; End SQL

$vResultPlan:=Get Last Query Plan(Description in Text Format) $vResultPath:=Get Last Query Path(Description in Text Format) DESCRIBE QUERY EXECUTION(False) `End analysis mode

After this code is executed, $vResultPlan and $vResultPath contain descriptions of the queries carried out, for example:

■ $vResultPlan:

(3)

Query Analysis

■ vResultPath:

And

[Merge] : ACTORS with CITIES

[Join] : ACTORS.Birth_City_ID = CITIES.City_ID (1227 records found in 13 ms)

--> 1227 records found in 13 ms --> 1227 records found in 14 ms

If the Description in XML format constant is passed to the Get Last Query Path command, $vResultPath contains the description of the query expressed in XML:

</steps> </steps>

</QueryExecution>

Get Last Query Plan

Get Last Query Plan (descFormat) → String

The Get Last Query Plan command returns the internal description of the query plan for the last query carried out on the data. For more information about query descriptions, please refer to the

documentation of the DESCRIBE QUERY EXECUTION command. This description is returned in Text or XML format depending on the value passed in the descFormat parameter. You can pass one of the following constants, found in the “Queries” theme:

This command returns a significant value if the DESCRIBE QUERY

descFormat Longint → Description format: Text or XML Function result String ← Description of last executed query

plan

Constant Type Value

Description in Text Format Longint 0 Description in XML Format Longint 1

(4)

The description of the last query plan can be compared to the

description of the actual path of the last query (obtained using the Get Last Query Path command) for optimization purposes. For more information, please refer to the description of the DESCRIBE QUERY EXECUTION command.

Get Last Query Path

Get Last Query Path (descFormat) → String

The Get Last Query Path command returns the detailed internal

description of the actual path of the last query carried out on the data. For more information about query descriptions, please refer to the documentation of the DESCRIBE QUERY EXECUTION command. This description is returned in Text or XML format depending on the value passed in the descFormat parameter. You can pass one of the following constants, found in the “Queries” theme:

This command returns a significant value if the DESCRIBE QUERY EXECUTION command has been executed during the session. The description of the last query path can be compared to the description of the query plan provided for the last query (obtained using the Get Last Query Plan command) for optimization purposes. For more information, please refer to the description of the DESCRIBE QUERY EXECUTION command.

SQL Engine

The new features described in this paragraph concern the integrated SQL engine of 4D.

descFormat Longint → Description format: Text or XML Function result String ← Description of last executed query

path

Constant Type Value

Description in Text Format Longint 0 Description in XML Format Longint 1

(5)

SQL Engine

Handling Character

Case

A new preference can be used to modify the way character cases are handled in SQL queries: “Case-sensitive String Comparison.” This option is found on the SQL/Configuration page of the Preferences:

This option is checked by default, which means that the SQL engine differentiates between upper and lower case when comparing strings (sorts and queries). For example “ABC”= “ABC” but “ABC” # “Abc.” In certain cases, for example so as to align the functioning of the SQL engine with that of the 4D engine, you may wish for string

comparisons to not be case-sensitive (“ABC”=“Abc”). To do so, you can simply uncheck this option.

New Selector for SET DATABASE PARAMETER and Get Database Parameter

The case-sensitive option for the SQL engine can be set and read by programming using the SET DATABASE PARAMETER and Get database parameter commands. For this purpose, a new selector has been added:

■ _Selector_{= 44 (}_{SQL Engine Case Sensitivity}₎

■ _{Possible values: 0 or 1 (0 = case not taken into account, 1 =}

case-sen-sitive)

■ Description: Activates or deactivates case-sensitivity for string

com-parisons carried out by the SQL engine.

(6)

Referencing Pointer

Type Expressions

It is now possible to reference pointer type expressions directly (dereferenced or not) in SQL queries.

■ _{Passing a variable via a dereferenced pointer:}

C_LONGINT($vLongint) C_POINTER($vPointer) $vLongint:=1

$vPointer:=->$vLongint Begin SQL

SELECT Col1 FROM TEST WHERE Col1=:$vPointer->; End SQL

■ _{Passing a variable via a pointer that is not dereferenced:}

C_LONGINT($vLongint) C_POINTER($vPointer) $vLongint:=1

$vPointer:=->$vLongint Begin SQL

SELECT Col1 FROM TEST WHERE Col1=:$vPointer; End SQL

This works with all types of SQL queries: ODBC commands, Begin/End SQL tags and the QUERY BY SQL command.

Note Only one pointer level is taken into account; pointers that reference other pointers cannot be used.

Unicode

4D v11 SQL Release 1 includes several new features concerning the extended support of Unicode.

Menus

In a 4D application, menus and their items can now be displayed in Unicode. This means that it is possible to use, for example, Japanese and Greek characters in the same menu. This new feature concerns all types of menus, whether they were created by the Menu bar editor or by programming.

Of course this function is only available when Unicode mode is activated in the database.

(7)

Unicode

Default Alpha Type

Option

The “Default Alpha Type” compilation option on the Design Mode/Compiler page is removed when the database operates in Unicode mode:

In Unicode mode, the Text type is automatically used.

Replace string

Replace string (source; oldString; newString{; howMany}{; *}}) → String

The Replace string command now accepts the asterisk * as the last parameter. If you pass this parameter, you indicate that the evaluation of the characters must be diacritical, in other words, it must be case sensitive and take any accented characters into account (a#A, a#à, etc.).

This is illustrated in the following examples:

vResult:=Replace string("Crème brûlée";"Brulee";"caramel") `vResult is "Crème caramel"

vResult:=Replace string("Crème brûlée";"Brulee";"caramel";*) `vResult is "Crème brûlée"

Compatibility mode (non-Unicode)

Unicode mode

source String → Original string

oldString String → Character(s) to replace newString String → Replacement string

howMany Number → How many times to replace

* * → If passed: diacritical evaluation

(8)

4D View

In the new version of the 4D View plug-in, the display and entry of characters is now carried out in Unicode. This mode is active regardless of the state of the “Unicode” option of 4D.

4D View v11.1 can open documents created with previous versions. However, 4D View documents created with version 11.1 cannot be reopened with version 11 or 2004.x.

Other New Features

Quick Reports and

Print Settings

To maintain the consistency of your interfaces, 4D has now made the pring settings uniform for both quick report areas and those of the application.

In previous versions of 4D, the print settings for quick report areas were stored separately for each report. Any modifications of 4D print settings were not carried over to quick reports and vice versa.

Beginning with version 11.1, the print settings are always identical in 4D and in the quick report areas. Any changes made to 4D print settings are carried out in those of the quick reports. Conversely, when loading a quick report, the print settings of the report replace those of 4D.

Coordinates for

Clicks on a Picture

4D now lets you retrieve the local coordinates of a click on a picture field or variable, even if a scroll or zoom has been applied to the picture.

The click coordinates are returned in the MouseX and MouseY system variables. The coordinates are expressed in pixels with respect to the top left corner of the picture (0,0). You must get the value of these variables as part of the On Clicked or On Double Clicked form event. In order for this mechanism to work properly, the display format must be set to “Truncated (non-centered).”

This mechanism, similar to that of a picture map, can be used, for example, to handle scrollable button bars or the interface of cartography software.