Conversion 4d To Sqlv11

Conversion of your 4D databases to 4D v11 SQL

Conversion of your 4D databases to

4D v11 SQL 01/10/2007 Page 1/45

Conversion of your 4D databases to 4D v11 SQL Required hardware and software configuration 5  Conversion to 4D v11 SQL of previous version databases 5  Conversion wizard Structure File Information: Data File Information: Segment Information: Disk Information: Option: In case of problems during conversion What happens during conversion: New files New folders: Use the Maintenance and Security Center after conversion What is no longer there after conversion: No longer used: 11  User Mode Direct access to 4D resources and to system resources Substitution system: 4DK# - STR# - cicn – Keyboard shortcuts – Macros… Subtables Former components Data segments 4D 2003/2004 Macros Customizing the ID window icon via a resource Assigning Groups and Users for operations on tables Choice of mode for background pictures No longer supported: 16  Changes in how things work 16  Table forms and Project forms CPU and small bitmap picture replicated on form background Table and field deletion possible Transactions Nested transactions Indexes New “contains keyword” operator Sets Clipboard and pasteboard Menus Special feature of Edit menu Hierarchical lists Triggers Plug-ins Plugin SDK Virtual structure Pictures Typing of picture variables Storage of information in a picture field: Compatibility Preferences: 22  Design compatibility: Web compatibility: Platform New compatibility options: Prevent drop of data not coming from 4D Nested Transactions Unicode mode Warning: To be done imperatively before converting a 4D 2004 database containing data from different languages to 4D Use system settings in numeric formats: Compatibility options no longer available: Commands 27  New commands Concerning Unicode support: 01/10/2007 Page 2/45

6  6  7  7  8  8  9  9  9  9  9  10  11  11  12  12  12  13  14  15  15  15  16  16  17  17  17  17  18  18  19  19  19  20  20  20  20  21  21  21  21  22  22  23  23  24  24  24  24  25  26  27  27  27 

Conversion of your 4D databases to 4D v11 SQL Concerning the SQL engine and the SQL server: Concerning nested transactions: Concerning queries and sorts: Concerning the new project forms: Concerning drag and drop: Concerning the pasteboard: Concerning menus: Concerning hierarchical lists: Concerning listboxes: Concerning printing: Concerning the 4D environment: Concerning the system environment: Concerning the user interface: Concerning methods: Concerning character strings, XLIFF and Regex: Concerning the structure definition: Concerning the Web: Concerning XML: Concerning pictures: Concerning tools: Commands whose syntax has been modified: Concerning menus: Concerning hierarchical lists: Concerning listboxes: Concerning printing: Concerning components: Concerning the 4D environment: Concerning the system environment: Concerning data entry and the user interface: Concerning methods: Concerning character strings: Concerning the structure definition: Concerning the Web: Concerning XML: Concerning system documents: Concerning BLOBs: Concerning resources: Concerning communications: Concerning Web services: Concerning processes: Commands whose names have been modified: Miscellaneous themes: Concerning the pasteboard: Concerning menus: Concerning hierarchical lists: Concerning the structure definition: Concerning arrays: Commands whose behavior has been modified: Commands for which the first parameter, the table, is now mandatory: Commands that change theme: Optimized commands: Obsolete commands: Commands that no longer serve any purpose: Obsolete syntaxes: Commands working with subrecords: Integrations (partial) 38  4D Insider replaced by the “System drag and drop” and by “Find-replace”: 4D Tools replaced by the MSC 4D Pack Obsolete 4D Pack commands: Commands still present but no longer recommended for use: Improved command: 01/10/2007 Page 3/45

27  28  28  28  28  28  28  29  29  29  29  29  29  29  29  29  30  30  30  30  30  30  30  30  30  30  31  31  32  32  32  33  33  33  33  33  33  34  34  34  34  34  34  34  35  35  35  35  37  37  37  37  37  38  38  38  39  40  40  41  41 

Conversion of your 4D databases to 4D v11 SQL Identical commands: Shortcuts Design Mode Records Appendix 1: Language codes:

42  44 

01/10/2007 Page 4/45

42  42  43 

Conversion of your 4D databases to 4D v11 SQL

Required hardware and software configuration • Mac: Imac Minimum / Mac OS X 10.4.5. 512 MB RAM minimum – 1 GB recommended Certified version: X 4.10 • Windows: Pentium II minimum / Windows XP 512 MB RAM minimum – 1 GB recommended Certified version: Windows Server 2003 R2, Windows XP SP2, Windows VISTA

Conversion to 4D v11 SQL of previous version databases You can convert version 6.5 (or even 6.0), 2003 or 2004 databases directly to 4D v11 SQL. It is nevertheless recommended to use 4D Tools beforehand in the original version in order to check the data and structure. Take this opportunity to regenerate the forms with the “Regenerate all the database forms” option if you have not already done so. This option is only available in 4D version 2004:

Launch 4D v11 SQL:

The 4D menu bar will then appear. You can open a database using the Open tool: or by using the menu:

01/10/2007 Page 5/45

Conversion of your 4D databases to 4D v11 SQL

And choose the .4DB file to be converted. The conversion wizard then appears.

Conversion wizard

A conversion wizard is provided. It makes a copy of the original database (structure, data, log file and blank log file) in a folder named "Replaced files (Conversion)". The different steps of the conversion are recorded in a file named "DataConversion_Log.log" that is placed next to the converted structure file.

This file can return the following error: 1012: if you have converted a database with more than one level of subtables

Structure File Information:

01/10/2007 Page 6/45

Conversion of your 4D databases to 4D v11 SQL

Data File Information:

You can create a new data file.

Segment Information:

Data segments no longer exist in 4D v11 SQL. During conversion, all the segments are grouped together. The conversion cannot be completed if a segment is missing. 01/10/2007 Page 7/45

Conversion of your 4D databases to 4D v11 SQL

Disk Information:

Option: This is where you can choose to not execute the code when opening the database:

This option is important because it provides a way of not passing by the “On Startup" database method when the converted database is launched for the first time. If any problems occur, error messages will indicate the procedure to follow as, for example, in the following screenshots that concern an error due to a data file that is not found:

01/10/2007 Page 8/45

Conversion of your 4D databases to 4D v11 SQL

In case of problems during conversion • • • • •

Generate a new data file; Select “Do not execute code when opening database” in the conversion options; Open the Maintenance Security Center: check the records and indexes, then check the application. Install any necessary plug-ins (Plugins and Win4DX/Mac4DX folder) Check in both interpreted and compiled mode.

Note: The interpreted mode in 4D v11 SQL is less tolerant than that of 4D version 2004.

What happens during conversion: New files After conversion to 4D version v11 SQL, 4D databases contain several additional files: • DatabaseName.4DIndy: contains the structure index. • DatabaseName.4DIndx (optional): contains the data indexes. • DataConversion_Log.log: This file stores the different steps of the conversion and any anomalies that occurred. If this file mentions any problems, use the MSC to repair the database. Note: Databases created in 4D v11 SQL are automatically placed in the .4dbase folder. Under Mac OS, databases are shown directly as a package.

New folders: • New folders: Replaced files: Several operations can create folders named Replaced files: these folders contain a copy of the original data files before conversion, or before compacting, or before repair.

• New folder: Macros v2 (see Macros) • New folder: Resources (see Resources)

Use the Maintenance and Security Center after conversion

01/10/2007 Page 9/45

Conversion of your 4D databases to 4D v11 SQL The Maintenance and Security Center now replaces 4D Tools. You must launch 4D Developer in order to access it:

Here you can repair and compact both the structure and the records. The following is an example of a log file generated when you click on Open log file:

What is no longer there after conversion: -

Settings of the Debug mode “Publish Database at Startup” will not be checked, even if it was checked previously A recursive relation, more specifically a relation between a field and itself, will be removed

01/10/2007 Page 10/45

Conversion of your 4D databases to 4D v11 SQL -

Subtables other than those of the first level

No longer used: User Mode The Design and User Mode have been merged to give access to both the design environment and the records:

Direct access to 4D resources and to system resources In conformity with the direction specified by Apple and implemented in the most recent Mac OS versions, the concept of resources in the strictest sense is now obsolete and will be abandoned progressively. Certain developers make use of 4D internal resources for their interfaces (for example, resources containing the names of the months or those of the language commands). This practice, which was already not recommended previously, is now strictly forbidden. In most cases, it is possible to use other means instead of 4D internal resources (constants, language commands, and so on). It is now forbidden (and impossible) to call system or 4D application resources regardless of whether it is via commands or using the syntax <:15000,5>. The following code is now obsolete: $Structure :=Structure file $Resources :=Open resource file ($Structure) In 4D v11 SQL the Open resource file command always returns 0 because the resources are always opened by 4D. A substitution mechanism has been implemented in order to limit the impact of this modification on the functioning of databases that make use of these resources. In order to maintain compatibility, the former mechanisms will continue to work with just a few adaptations: • When present, resource files are still supported by 4D and the principle of the string of resource files (successive opening of several resource files) remains valid. This string includes the .rsr and .4dr files and the custom files opened using 4D commands. But it is no longer possible to access 4D resources. This practice, which was already not recommended previously, is now strictly forbidden. • To limit the impact of this modification on existing databases, a substitution system has been implemented, based on the externalization of the resources that are most frequently used.

01/10/2007 Page 11/45

Conversion of your 4D databases to 4D v11 SQL

It is recommended to copy them if you want to use them.

Substitution system: • Put all the necessary files into a folder named “Resources” that you will need to create next to the structure. However, it is strongly recommended to change converted databases and remove any calls to 4D internal resources that they may contain. Note: Databases created in 4D v11 SQL no longer have .RSR and .4DR files.

4DK# - STR# - cicn – Keyboard shortcuts – Macros… To recover your resources, a “4DPop Migration” component will be available for download free of charge. It will allow you to recover: • your STR# and convert them to an .XLIFF file • your 4DK# constants taken from the .rsr file that will then be converted to a User_Contants.bundle plugin • your keyboard shortcuts, in order to modify them: 4DShortcuts.xml file in the “Extensions” folder • your version 2004 macros in order to change them to UTF-8 (cross-platform) and convert the characters that are now forbidden (// ----- <> etc.) • and so on. For more information about this component, please consult us.

Subtables It is no longer possible to create subtables in 4D version v11 SQL. During the conversion of a database to v11, the first level subtables are transformed into standard tables with a special relation. Warning, any subsequent levels will simply be truncated. For example, the former structure:

01/10/2007 Page 12/45

Conversion of your 4D databases to 4D v11 SQL Becomes:

The subtable becomes a Many table and the original table becomes the One table. The name of the newly-created table will be: ParentTableName_SubTableName and will be truncated to 31 characters! A primary key field is added to the Many table named: id_added_by_converter Many-to-One relation: SubtableName_id_added_by_converter One-to-Many relation: id_added_by_converter_subtable_1_Children The order of tables in v11 is no longer the same as in version 2004; subtables are added to existing tables. If you have subtables in your database: BE AWARE of the following points: • The number of tables increases after migration to 4D v11 SQL. •

If you convert a database with more than one level of subtables, the higher-level subtables will not be recovered and all the subtables other than those of the first level will simply be truncated. In this case, error 1012 appears in the ‘DataConversion_Log.log’ as well as in the error result window displayed at the end of conversion.

• As long as you do not remove the special relation created during conversion, the commands on the subrecords will continue to work. However, the removal of this special relation is irreversible: you must redraw a standard relation and you will have to replace the “subrecord” commands with commands on tables. You will need to plan on rewriting your code if you use table numbers or if you remove the relation. Also be aware that the behavior of certain commands has changed: DUPLICATE RECORD no longer duplicates subrecords, SEND RECORD no longer sends subtables and RECEIVE RECORD no longer receives them. On the other hand, from now on the Field and Field name commands return the number and name of the subfield when the pointer is unpointed (and no longer those of the main table).

Former components The component architecture has undergone in-depth modification. A component now consists of a regular structure file, compiled or not, in the form of a package (.4dbase folder). You simply need to put it into the “Components” folder next to the structure. It is no longer possible to use tables in a 4D v11 SQL component (a database created in 4D v11 SQL does not contain an initial Table 1 and you can now use project forms that are not related to a table). If a former version component is installed in your database, a warning message will inform you that conversion is not possible:

01/10/2007 Page 13/45

Conversion of your 4D databases to 4D v11 SQL

Former version components must be uninstalled from the original version with 4D Insider before conversion. Note: In a 4D v11 SQL component, tables and fields, table forms and their associated form methods as well as user forms, database methods and triggers cannot be used. Components are now 4D databases: .4db, .4dc or .4dbase. For professional applications, the .4dbase is recommended. You can get it using the Build application dialog box:

Data segments The size of the data file is virtually unlimited (except for limits imposed by the system). It is no longer possible to create and use several data segments. Previous segments are grouped during conversion. Check that you have a sufficient amount of space on your disk to do this. If a segment is missing, the conversion wizard will ask you to locate it manually. If it cannot be found, the conversion cannot be completed.

01/10/2007 Page 14/45

Conversion of your 4D databases to 4D v11 SQL

4D 2003/2004 Macros 4D v11 SQL loads the macros of a new “Macros v2” folder (in the active 4D folder, next to the structure, or in the “Component” folder for the macros of a component). Macros used with versions 2003 and 2004 of 4D are not compatible with 4D v11. The v11 macros must be entirely in conformity with XML standards. Therefore the following mentions: < ?xml version= « 1.0 »… ?> must be present. The dtd is located: Under Mac OS: 4D Developer:app:Contents:Resources:DTD:macros.dtd Under Windows: 4D Developer/Resources/DTD/macros.dtd No longer compatible: // for comments ----- dashes < or > which must be encoded: < and > The <macros> tag is mandatory. …. An automatic conversion mechanism has been implemented. 4D v11 SQL automatically copies the 4D 2003 or 4D 2004 macros into the “Macros v2” folder. The declaration statements are added to the document. However, occasional adjustments may be needed in order to comply entirely with the XML standard. New commands: GET MACRO PARAMETER SET MACRO PARAMETER

Customizing the ID window icon via a resource This functionality no longer works. It is replaced by simply copying a .png file. Just name your picture “LoginImage.png” and drag it onto the “Resources” folder next to the structure.

Assigning Groups and Users for operations on tables For operations on data such as Loading, Adding, Saving and Deleting, a group can no longer be assigned. Similarly, tables no longer have owner groups. These controls become ineffective in the case of converted

01/10/2007 Page 15/45

Conversion of your 4D databases to 4D v11 SQL databases. From now on, controls are carried out at different levels (forms, menus, method, etc.) or via specific options, for example, concerning the SQL server (in the Preferences).

Note: Watch out for security holes caused by 4D Open.

Choice of mode for background pictures It is no longer possible to select the interaction mode between the background and foreground colors of a picture (On Background format) via the “Choice of mode” window. This was implemented for black and white pictures and is no longer suitable for pictures which are now managed natively.

No longer supported: • Passing a 2D array element to a command requesting a variable: now returns an error: e.g. Type(My2DArray{1}{1}). • Fake arrays in the Mac OS 7 pop-up menus: if the pop-up variable was a real and was named, for example, “pop”, the contents of the variable were indicated as “pop”+string(pop). • The paragraph operator § which was used to address a variable by its name (a sort of pointer) for example: a:= § (“vtoto”). This former operator was already rejected by the compiler. Example: ValueOfA :=§("A") must now be written: ValueOfA :=Get pointer("A")-> • CLEAR VARIABLE no longer accepts the syntax allowing a variable name to be given between quotation marks. It now operates the same way in both interpreted and compiled. • The fake arrays for which arr{“42”} or arr{42} were equivalent to arr42. • Tab associated with an array “toto” and a Boolean array “toto” in order to activate/deactivate each item of the tab. • DDETools: This product is no longer supported. The alternatives are: o VBA and LAUNCH EXTERNAL PROCESS under Windows o Applescript and LAUNCH EXTERNAL PROCESS under Mac OS.

Changes in how things work Table forms and Project forms There is now a type of form that is not related to any table: the Project form. It can be used both as an input and output form, contrary to what the selection pop-up menu, which only provides the choice of Detail Form and Detail Form for Printing (or None), may lead you to believe. 01/10/2007 Page 16/45

Conversion of your 4D databases to 4D v11 SQL

CPU and small bitmap picture replicated on form background Due to the modification of picture handling, you may notice a significant increase in CPU used by 4D v11 when using a form with a small bitmap picture replicated on its background (the smaller the picture, the more it must be duplicated, thus the greater the increase in CPU use). The solution is to use a bigger picture (128 x 128 min) which will use a little more memory but will save CPU time.

Table and field deletion possible It is now possible to delete tables and fields in 4D v11 SQL. The forms of a deleted table are changed to Project forms and are placed in the Trash of the Explorer. The numbers of deleted tables and fields can only be re-assigned on creation of new ones. Obsolete commands: Count tables Count fields

Replaced by: Get last table number Get last field number

Commands added: Is table number valid Is field number valid

Transactions VALIDATE TRANSACTION and CANCEL TRANSACTION no longer affect the current selection. Previous versions reduced the selection to zero if at least one record was created during the transaction. From now on, records created during a transation no longer receive a temporary number (beginning at 18,000,000) but instead receive a standard number, corresponding to the numbering underway in the table. This number becomes permanent when the transaction is validated and is released if the transaction is cancelled (the records created are then deleted). Commands working with record numbers like SCAN INDEX, RELATE ONE SELECTION, etc, can now be used in transactions.

Nested transactions A preference can be used to activate nested transactions in 4D. With 4D 2004, you could not nest transactions. If you began one transaction within another, 4D ignored the second one. This option only appears for converted databases. By default, it is not checked.

This option has no effect on any transactions in the SQL engine of 4D v11 SQL, which is always multitransactional. 01/10/2007 Page 17/45

Conversion of your 4D databases to 4D v11 SQL

Indexes It is no longer mandatory to index fields used by a relation. By default, your indexes are converted into B-tree.

Fields added to a former subtable which has been transformed into a table are in Cluster B-Tree. • B-tree standard: multipurpose indexing. Used in previous versions of 4D. • Cluster B-tree: more efficient when the same values occur often in the data (for example: a Boolean field or a Title field containing Mr., Mrs. or Ms.). • Automatic (default value on creation): 4D selects the architecture according to the data. Here is an example of index verification using the Maintenance and Security Center:

• Keywords Index:

This new type of index is available for Alpha and Text fields. It can be combined with a standard index. 4D will use the one most suitable depending on the context.

New “contains keyword” operator For this new type of index, there is a new operator: % that corresponds to “contains keyword.” Contains the word “easy”: 01/10/2007 Page 18/45

Conversion of your 4D databases to 4D v11 SQL QUERY([Products];[Products]Description%"easy") Contains a word that begins with “easy”: QUERY ([Products];[ Products]Description%"easy@") • Composite Indexes: Composite indexes are indexes made up of several fields (two or more). They can only be created from the Index list dialog box (called from tool bar of Structure editor):

Sets When a set is no longer up to date because it contains a reference to a record that has been deleted and we use the USE SET command, 4D v11 SQL returns the following error: -10503: "Record number not valid." With 4D 2004, this was not the case: no error number was returned. Is in set: took the selection pointer (sometimes pointing to another record with PUSH RECORD for example) but not the current record if it was loaded, unlike ADD TO SET – from now on its behavior is identical. For information, CREATE SET always keeps the selection pointer without dealing with the loaded record.

Clipboard and pasteboard The Pasteboard commands can be used to manage the pasteboard (former Clipboard) and drag and drop data. 4D v11 SQL actually uses two types of “pasteboards”: one for cut (or copied) data and another for data being dragged and dropped. The pasteboard for drag and drop data can only be accessed in the context of the On Begin Drag Over, On Drag Over or On Drop form events, or in the On Drop database method. The “Clipboard” theme has been renamed “Pasteboard.” The No such data in clipboard constant has been renamed No such data in pasteboard.

Menus From now on in 4D v11 SQL, menus are hierarchical. You can create menus and menu bars on the fly without them actually existing in Design mode. The menus and menu bars are processed in the same way. The maximum number of characters changes from 31 to 150. The menuItem=-1 parameter can be used to indicate the last item added.

01/10/2007 Page 19/45

Conversion of your 4D databases to 4D v11 SQL Former names (4D 2004.x)

New names (4D v11SQL)

MENU BAR HIDE MENU BAR SHOW MENU BAR SET ABOUT SET MENU ITEM KEY

SET MENU BAR Moved to the “User Interface” theme Moved to the “User Interface” theme Moved to the “User Interface” theme SET MENU ITEM SHORTCUT

No change: Menu selected Count menus Count menu items DISABLE MENU ITEM ENABLE MENU ITEM APPEND MENU ITEM INSERT MENU ITEM DELETE MENU ITEM

Special feature of Edit menu The v 6.8 option is no longer available for menus in 4D v11 SQL. This option was used, in converted databases, to maintain a previous functioning based on the automatic addition of an Edit menu managed by the system. When a database where the v 6.8 option was checked is converted, the Edit menu is automatically added and a warning dialog box will notify you of this:

Warning: it will then be necessary to modify the value of the menu parameter in the statements for managing and executing menus. In fact, with the previous functioning, the Edit menu was not counted; however, in 4D v11 SQL, it is counted.

Hierarchical lists Structural modification. Greater flexibility.

Triggers Minor change in the operation of the On saving new record trigger: in 4D 2004, when the Record number command was called in this trigger, it returned -3. In 4D v11 SQL, the command returns the record number that will be assigned to the newly-created record.

Plug-ins Plug-ins of version 2004 (4D or third party) are compatible with 4D v11 SQL. MacIntel: All the 4D v11 SQL plug-ins are MacIntel native, except for 4D Draw and 4D for OCI that must be used under Rosetta. To do so, choose Get Info on the 4D Developer.app application and check “Open using Rosetta”. 01/10/2007 Page 20/45

Conversion of your 4D databases to 4D v11 SQL

Plugin SDK EX_FIND_PROCID and EX_CALL_BY_PROCID worked with indexes in the methods array. These entry points now work with internal Ids, both positive and/or negative, but not null values.

Virtual structure A virtual structure uses the names of tables and fields set with SET TABLE TITLES and SET FIELD TITLES. These names are used by the standard 4D dialog boxes (query, order by, quick report, etc.). This has been modified in 4D v11 SQL: from now on, the virtual structure is only used when these dialog boxes are called with language commands. Plug-ins, too, always access the virtual structure: Design mode Plug-ins Application mode

Structure Real structure Virtual structure Virtual structure

Invisible table or fields Visible Invisible Invisible

Pictures Pictures are now stored in native format. The former PICTURE TYPE LIST command is kept for reasons of compatibility. However, it requires the presence of QuickTime and does not allow access to formats that are managed natively by 4D. It is of limited interest and can be favorably replaced by PICTURE CODEC LIST.

Typing of picture variables In the interpreted mode of 4D v11 SQL, if you notice that your picture variables are not displayed correctly, make sure that they are typed correctly: There are two ways to do this: • You can type them at the form level via the pop-up menu of the Property list:

01/10/2007 Page 21/45

Conversion of your 4D databases to 4D v11 SQL

• Or using a compiler directive: C_PICTURE(vPict) that must be executed before the form is loaded, in other words, even before the On Load form event. Previous versions of 4D were more tolerant regarding the initialization of variables in general, and picture variables in particular whose display in forms is governed by new native mechanisms.

Storage of information in a picture field: At the end of a picture, 4D added the X/Y info and the display mode, which are no longer necessary. These 6 bytes are removing during conversion. If for legitimate reasons you have stored Blobs in Picture fields, be aware that they will lose part of their information during conversion.

Compatibility Preferences:

Design compatibility: Compatibility options already present in 4D 2004: •

Fields are not enterable in dialog boxes: (checked in converted databases and unchecked for created databases)

•

Radio buttons grouped by name: It is no longer necessary to begin radio button variables by the same string in order to associate

01/10/2007 Page 22/45

Conversion of your 4D databases to 4D v11 SQL them. You can now simply group them. This option only exists in converted databases. It is checked by default. Databases created in version 2004 use the new functioning. •

Reload form for each record during PRINT SELECTION. This option only appears for converted databases. It is checked by default. This allows the automatic resetting of all the object parameters that the developer could have modified by language in the On Printing Detail form event. In order to optimize performance, this mechanism was removed in 4D 2004. The 4D developer must now reset the desired parameters manually in the form method — this functioning is identical to that of list forms with the On Display Detail event. You can nevertheless keep the former mechanism in place by checking the “Reload form for each record during PRINT SELECTION” option. Databases created in 4D v11 use the new functioning.

•

Automatic Transactions during Data Entry: This option only appears for former databases converted to version 2004. It is unchecked by default. It was used to automatically start a transaction when an input form was opened and had an included form. This option was designed for 4D First users that switched to 4D. This option was already not recommended in version 2003.

•

Always allow all commands and project methods in the Formula editor: In the Formula editor, now only a few 4D command themes are displayed and no project methods; except when you use the SET ALLOWED METHODS command (see documentation) or when you check this option.

Web compatibility: • • • •

Use 4DVAR Comments instead of Brackets: In previous versions of the Web server, the following syntax was used: [MyVar], proprietary solution to be replaced by . Use new context referencing mode: Keeps the 4D Web server from sending the context number to the browser for each element on a page. Remove "/" on unknown URLs: e.g. http://127.0.0.1/Contacts/Add in previous versions returned in $1: “Contacts/Add.” In 4D 2004, all URLs begin with "/" (except when this option is checked). Keep-Alive Connections: If an HTML page included 10 pictures, this formerly lead to 10+ 1 connections in http 1.0. This option allows a single connection to be maintained.

Platform In this Preference dialog box, this area only appears converted databases. In previous versions of 4D, it was possible to “force” the appearance, at the database level, of forms and/or objects — with each level by default inherited from the previous one. This mechanism is kept for compatibility reasons. In databases created with 4D version 2004 and higher, the interface is automatically chosen by 4D according to the current platform. The forms and objects used are drawn in a native manner. The interface can be configured only at the form or object level and the only options available are: System and Printing for forms, Inherited from form for objects. In databases converted from previous versions of 4D, additional options are available (Automatic, Mac OS 7, Windows 3.11, NT 3.51, Windows 95/98/2000, NT 4, Mac OS 9 and Mac Theme). These options correspond to the former platform interface support mode in 4D and it is no longer recommended to use them. The same goes for the Get platform interface and SET PLATFORM INTERFACE commands, both kept for compatibility reasons and which only work in converted databases. These commands are ignored in databases created with 4D 2004 since the platform interface is managed automatically. In the Preferences of converted databases, it is strongly recommended to select the System option in order to implement this functioning. The two other pop-up menus are then disabled and the new mechanism is activated. Note that it may be necessary to modify the “Platform” property of your forms in order to take advantage of this mechanism, for example by choosing the Inherited from Database option. 01/10/2007 Page 23/45

Conversion of your 4D databases to 4D v11 SQL

New compatibility options: Prevent drop of data not coming from 4D On the Application/Compatibility page: 4D v11 SQL allows dragging and dropping of selections, objects and/or files external to 4D, such as picture files for example. This possibility must be supported by the database code. In databases converted from a previous version of 4D, this possibility may lead to malfunctioning if the existing code is not adapted. For this reason, a new option in the Preferences can be used to prevent the dropping of external objects into the database: Prevent drop of data not coming from 4D. Checked by default in converted databases.

Nested Transactions 4D v11 SQL now accepts an unlimited level of nested transactions. This new functioning is disabled by default in converted databases (transactions remain limited to a single level). If you want to be able to use transactions on more than one level in a converted database, you need to explicitly indicate it by checking this option. For info: SQL transactions are always multi-level.

Unicode mode This option is checked by default in a new 4D v11 SQL database and not checked in converted databases. 4D v11 SQL uses Unicode for: • • • •

The database engine and the SQL server Variables The Web Numerous structural objects: menus, lists, the structure itself, users and groups, help tips, etc. (but not for forms – use XLIFF for the localization – nor for methods).

Preferences: Support for Unicode characters has been extended 4D v11. Many commands have been rewritten. From now on, the database engine, language and many interface elements (menus, lists, form objects, etc.) can store and work with Unicode characters. When the Unicode mode is enabled, the “Keyboard Layout” property of form objects is ignored. For compatibility reasons, 4D v11 SQL can still operate with the former mode, based on Mac ASCII characters, via a new option in the Preferences:

4D v11 SQL now uses UTF-16 (and thus conforms with Windows and Mac OS) and sometimes UTF-8, more particularly for the Web and for imports/exports, which can offer certain advantages (better readability for common characters - a-Z, 0-9 – and more compact). In Unicode, the values 1 to 127 correspond exactly to the ASCII characters. 01/10/2007 Page 24/45

Conversion of your 4D databases to 4D v11 SQL This support of Unicode leads to different modifications: change of parameters or new parameters for certain commands and even new commands:

Non-Unicode Alpha field Text field C_TEXT C_STRING

Alpha field Text field Text type String variable type

ARRAY TEXT ARRAY STRING

Text array type String array type

Char

Char(Ascii Code) 0-255 Character code Returns an ASCII code

Ascii becomes Character code Length, String, Position, Delete string, Substring, Change string

Unicode – UTF 16 Alpha field Text field Text type Text type String length parameter ignored Text array type Text array type String length parameter ignored Char(UTF-16 value) 0-65535 Character code Returns a Unicode character Works with UTF-16 characters

Syntax [[1]] Returns the Xth UTF-16 character and no longer the Xth byte No longer work

Mac to Win, Win to Mac, Mac to ISO, ISO to Mac New commands: CONVERT FROM TEXT Convert to text New constant names and new constants: BLOB to text TEXT TO BLOB

-

CONVERT FROM TEXT (4Dtext;charSet;convertedBLOB) Convert to text(blob;charSet)-> Text

C String Pascal string Text with length Text without length

Mac C String Mac Pascal string Mac text with length Mac text without length UTF8 C String UTF8 text with length UTF8 text without length

These new mechanisms do not lead to any particular modifications for languages whose characters are coded on a single byte (Western languages), but will simplify processing in languages coded on two-bytes (such as Japanese).

Warning: To be done imperatively before converting a 4D 2004 database containing data from different languages to 4D Warning: if you use multiple languages (in particular languages stored on 2 bytes like Chinese, Japanese, etc.) in your fields, here is the procedure to follow: During the 4D 2004 -> 4D v11 SQL conversion, 4D converts the data to Unicode. In order to not lose any information, it is important to know the original character set. This cannot be done in a completely automatic manner. The developer must specify the character set. This information is given via a text file placed next to the data file to be converted and named “multilang.txt.” The encoding must be ANSI or Mac Roman (not UTF8 or Unicode). Description of this file: Table no.; Field no.; Subfield no. (optional); language code. Each line must end with a carriage return (CR or CRLF). Empty lines and spaces are allowed. This file must be located next to the data file AT THE TIME OF CONVERSION. 01/10/2007 Page 25/45

Conversion of your 4D databases to 4D v11 SQL Example: If the developer wants to force the following languages: Greek in Table3-Field3 and Table4-Subfield7–Field1, Russian in Table3-Field4 and Table4-Subfield7–Field2, The “multilang.txt” file will contain the following information: 3;3;1049 3;4;1032 4;7;1;1049 4;7;2;1032 The language codes are provided in the “keyboardmapping.xml” file (see appendix 1 of the present document). Database converted to v11 without the “multilang.txt” file:

The same database converted to v11 with the “Multilang.txt” file next to the data file during the conversion:

With this solution, the conversion will work for all the languages present.

Use system settings in numeric formats: This option is checked by default for new databases created with 4D v11 SQL. In this case, 4D automatically replaces the “.” and “,” characters in numeric display formats by, respectively, the thousands separator and the decimal separator defined in the operating system. In previous versions of 4D, the “###,##0.00” format was a valid format for an American system, but when it was applied to a numeric value displayed on a French or Swiss system, the result was incorrect. From now on, by checking this new option in the Preferences and by systematically using the period and comma as separators in numeric formats, 4D will replace these characters by their equivalents as defined in the system.

01/10/2007 Page 26/45

Conversion of your 4D databases to 4D v11 SQL The Num command now accepts a new parameter that can be used to set a specific decimal separator. In addition, the new GET SYSTEM FORMAT command can be used to find out the current value of the regional system parameters. Warning: In databases newly created in 4D v11 SQL, with the option checked, in order for your database to be multiOS, the thousands separator to be used is the “,” and the decimal separator to be used in the formats is “.”, thus for example “###,##0.00 €.” You will then have a format that is operational under any OS. If you want to continue to work with conventional formats like “### ##0,00 €,” uncheck this option in the Preference; otherwise, modify your formats to “###,##0.00 €.”

Compatibility options no longer available: • Use V3.x.x Startup Method Scheme: from now on the code that needs to be executed on opening must be placed in the On Startup and/or On Server Startup database methods. • Use V3.x.x File Procedure Scheme: to be replaced by Triggers. • 6.8 compatibility for text rendering (used for former raised and shadow styles, incompatible with Quartz) – If you notice font smoothing problems, check that you are not still using bitmap fonts, which are obsolete. • Former Edit menu mechanism (v 6.8 option) The v 6.8 option is no longer available for menus in 4D v11 SQL. This option could be used, in converted databases, to maintain a former functioning based on the automatic addition of an Edit menu that was managed by the system. From now on, the Edit menu must be managed exactly like other menus. It will be automatically added in menu bars where it does not exist (see information concerning the Edit menu in this document).

Commands New commands Many new commands have been added in order to manage the new functionalities:

Concerning Unicode support: Get localized string Match regex CONVERT FROM TEXT Convert to text

Concerning the SQL engine and the SQL server: GET DATA SOURCE LIST USE EXTERNAL DATABASE USE INTERNAL DATABASE Get current data source QUERY BY SQL Is field value Null SET FIELD VALUE NULL GET LAST SQL ERROR START SQL SERVER STOP SQL SERVER

01/10/2007 Page 27/45

Conversion of your 4D databases to 4D v11 SQL

Concerning nested transactions: Transaction level

Concerning queries and sorts: In order to benefit from the new engine and the new types of searches and indexes: CREATE INDEX DELETE INDEX SET QUERY AND LOCK Find in field Query by keywords

Concerning the new project forms: NO DEFAULT TABLE

Concerning drag and drop: 4D can be used to drag and drop selections, objects and/or external files. New On Begin Drag Over form event New database method: On Drop

Concerning the pasteboard: New 4D signatures: SET FILE TO PASTEBOARD Get file from pasteboard GET PASTEBOARD DATA TYPE Com.4d.text.native Com.4d.text.utf16 Com.4d.text.rtf Com.4d.picture.pict Com.4d.picture.png Com.4d.file.url Etc. 4-character types (TEXT, PICT, etc. are kept for compatibility).

Concerning menus: Create menu RELEASE MENU GET MENU ITEMS Get menu bar reference GET MENU ITEM ICON SET MENU ITEM ICON Get menu item method SET MENU ITEM METHOD GET MENU ITEM PROPERTY SET MENU ITEM PROPERTY Get menu item modifiers Dynamic pop up menu SET MENU ITEM REFERENCE Get menu item reference Get selected menu item reference

01/10/2007 Page 28/45

Conversion of your 4D databases to 4D v11 SQL

Concerning hierarchical lists: SET LIST ITEM FONT Get list item font Find in list SET LIST ITEM ICON GET LIST ITEM ICON SET LIST ITEM PARAMETER GET LIST ITEM PARAMETER LIST OF CHOICE LISTS

Concerning listboxes: INSERT LISTBOX COLUMN FORMULA SET LISTBOX TABLE SOURCE GET LISTBOX TABLE SOURCE

Concerning printing: OPEN PRINTING JOB CLOSE PRINTING JOB

Concerning the 4D environment: VERIFY DATA FILE VERIFY CURRENT DATA FILE Compact data file OPEN SECURITY CENTER Get current database localization COMPONENT LIST

Concerning the system environment: Select RGB Color: displays the color selection system window. GET SYSTEM FORMAT to get the regional parameter values.

Concerning the user interface: Tool bar height

Concerning methods: EXECUTE METHOD with parameters

Concerning character strings, XLIFF and Regex: CONVERT FROM TEXT Convert to text Get localized string (only for XLIFF) Match regex

Concerning the structure definition: Is table number valid Is field number valid CREATE INDEX DELETE INDEX

01/10/2007 Page 29/45

Conversion of your 4D databases to 4D v11 SQL

Concerning the Web: Validate Digest Web Password (for the On Web Authentication database method in Digest mode)

Concerning XML: DOM Find XML element by ID DOM EXPORT TO PICTURE

Concerning pictures: PICTURE CODEC LIST TRANSFORM PICTURE (continues and improves the functionalities of picture operators) COMBINE PICTURES (idem) CONVERT PICTURE Native picture formats

Concerning tools: Choose (criterion; value1{ ;value N}) Boolean or number Example: VTitle:= Choose ([Contact]Masculine “Mr”;“Mrs”) GET MACRO PARAMETER SET MACRO PARAMETER

Commands whose syntax has been modified: Concerning menus: All the commands concerning menus now accept a MenuRef type string in the menu parameter and allow working with hierarchical submenus. Menu selected can be used to work with hierarchical menus.

Concerning hierarchical lists: The commands accept the object syntax which is mandatory in the case of multiple representations of a list. The use of standard 4D commands (FONT, FONT STYLE, FONT SIZE, SET SCROLLBAR VISIBLE, SCROLL LINES, SET COLOR, SET RGB COLOR, SET FILTER, SET ENTERABLE) with hierarchical lists, using the object name or the variable name, has been extended.

Concerning listboxes: INSERT LISTBOX COLUMN INSERT LISTBOX ROW DELETE LISTBOX ROW GET LISTBOX ARRAYS Focus object in the context of a listbox: Column associated with a field: points to the field Column associated with a variable: points to the variable Column associated with an expression: points to the variable of the listbox Displayed line number: now works with a listbox.

Concerning printing: PRINT SETTINGS(dialType) dialType: 0 all, 1: print format, 2: printing

Concerning components: Structure file (*): returns the structure file of the host database (for a component). 01/10/2007 Page 30/45

Conversion of your 4D databases to 4D v11 SQL Get 4D folder(folder;{*}) with * returns the host database folder. Is compiled mode (*) information about host database.

Concerning the 4D environment: SET DATABASE PARAMETER Get database parameter New selectors: 41: Unicode mode 42: Temporary memory size 43: SQL Autocommit

Modified selectors: 17: Character set in Unicode mode, uses the character set specified by the IANA 29: Web Log Recording 30: Client Web Log Recording With new log file formats (CLF, DLF, ELF, WLF)

Deleted selectors: Selector 1 Seq Order Ratio 2 Seq Access Optimization 3 Seq Distinct Values Ratio 4 Index Compacting 5 Seq Query Select Ratio 26 Cache Writing Mode

OPEN 4D PREFERENCES 4D 2004 keys: /Application/Compatibility/Structure Compatibility /Design Mode/Method Editor/Styles for Syntax Elements /Database/Script Manager /Client-Server/Publishing/Allow-Deny Table Configuration

Modifications or New keys /Application/Compatibility/Design Compatibility /Design Mode/Method Editor/Syntax Styles /Database/International /Client-Server/Publishing/Allow-Deny Configuration Table /Application/Access/General Settings /Design Mode/Structure/Automatic Form Creation /Moving /Moving/Default Actions during the Copy if Dependent Objects /Moving/Moving Dialog /Database/International/Right-to-left Languages /Database/International/Numeric Display Format /Web/Options/Options /Web/Log Format /Web/Log Format/Web Log Type /Web/Log Format/Web Log Token Selection /Web/Log Scheduler /Web/Log Scheduler/Backup Frequency for Web Log File /SQL /SQL/Configuration /SQL/Configuration/SQL Server Access

Concerning the system environment: PLATFORM PROPERTIES 4D 2004 Constants Power Macintosh Windows Other G3 and above Pentium INTEL 386

New constants Mac OS Windows PowerPC Intel Compatible

01/10/2007 Page 31/45

Conversion of your 4D databases to 4D v11 SQL INTEL 486 Macintosh 68K PowerPC 601 PowerPC 603 PowerPC 604 PowerPC G3

Concerning data entry and the user interface: DIALOG: new * parameter, the form is then loaded in the last window opened in the current process (allows having a floating palette without launching a new process). Open window: new Metal Look parameter under Mac OS SET FORMAT($MyThermometer; “;;;;128”) - Used to activate Barber shop mode by programming for thermometers (flag 128). SET FORMAT ($MyButton ;;;#MyPictures/MyPicture.jpg) - Used to reference, associated with a 3D button, a .jpg picture loaded in a “MyPictures” subfolder in the “Resources” folder of the database. GOTO AREA(* ;“”) no object has the focus.

Concerning methods: Current method name for an object method returning a name such as “[Table1].Form.var,” that becomes “[Table1]Form.var” (the “.” has been removed).

Concerning character strings: Num now accepts a new parameter that can be used to specify a decimal separator. By default, the command uses the decimal separator defined by the operating system. When this parameter is passed, the command does not take the system decimal separator into account. You can pass one or more characters. String now accepts Alphas and Booleans (“True” “False”) in parameters. •

Lowercase, Uppercase New * parameter to specify that accents are kept Lowercase (string{ ;*}) Uppercase (string{ ;*}) In version 2004.x Uppercase ("è") Lowercase ("È") In 4D v11 Uppercase ("é") Lowercase ("È") Uppercase ("é";*) Lowercase ("È";*)

-> E : accent lost -> è : accent kept -> E -> e -> È -> è

Example: Lowercase (“ÉÚÂÆ éABC”) changed to lower case Lowercase (“ÉÚÂÆ éABC”; *) changed to lower case Uppercase (“éà”) Uppercase (“éà”; *)

: accent lost (idem version 2004) : accent lost : accent kept : accent kept (idem version 2004) -> euaæ eabc the accents have disappeared and everything is -> éúâæ éabc the accents have been kept and everything is -> EA -> ÉÀ

: the accents have disappeared : the accents have been kept

01/10/2007 Page 32/45

Conversion of your 4D databases to 4D v11 SQL •

Position New parameters such as start and * have been added. The * parameter can be used to specify that a search is diacritic-sensitive. Position has been rewritten and uses the ICU (International Components for Unicode) library which can lead to an incompatibility since Position now ignores all characters ignored by Unicode like ASCII NULL or Char(1). Warning: remember to use the new * parameter to keep the same functioning as in 4D 2004: $p:=Position(Char(0);vText) $p -> 1 To get 0, you must use the * character $p:=Position(Char(0);vText;*) $p -> 0

  Note: in Unicode, the following character codes are reserved and must never be included in a text: 0 65534 (FFFE) 65535 (FFFF)

Concerning the structure definition: Field (if subfield pointer, returns the number or name of subfield) Field name (idem) SET INDEX (takes new index types into account)

Concerning the Web: SET HTML ROOT (from now on the command does not take the Preferences into account) – see Changes in how things work.

Concerning XML: DOM Find XML element DOM Parse XML source DOM Parse XML variable SAX ADD XML ELEMENT VALUE APPLY XSLT TRANSFORMATION

Concerning system documents: Select folder: new defaultPath parameter. Open document(document{; fileType{; mode}}) Create document Append document The fileType parameter has been modified for these three commands. It is now possible to pass a list of document types, separated by semi-colons (;) in this parameter.

Concerning BLOBs: TEXT TO BLOB BLOB to text

Concerning resources: Get indexed string STRING LIST TO ARRAY Support of XLIFF

01/10/2007 Page 33/45

Conversion of your 4D databases to 4D v11 SQL

Concerning communications: SEND PACKET (allows BLOBs) RECEIVE PACKET (allows BLOBs) USE CHARACTER SET (in Unicode, IANA name of character set)

Concerning Web services: CALL WEB SERVICE (Keep-alive possible with new * parameter)

Concerning processes: New constants: Web server Process Execute on Client Process 4D Server Process On Quit Process Method editor macro Process Internal 4D Server Process Backup Process Log File Process Restore Process MSC Process Timer Process SQL Method Execution Process Server Controller Process Monitor Process

Longint Longint Longint Longint Longint Longint Longint Longint Longint Longint Longint Longint Longint Longint

-13 -14 -15 -16 -17 -18 -19 -20 -21 -22 -23 -24 -25 -26

Commands whose names have been modified: Miscellaneous themes: EXECUTE Find index key Ascii GET PLUGIN LIST USE ASCII MAP

becomes

EXECUTE FORMULA Find in field works on non-indexed field and @ can be used in the string (forced sequential mode in this case). Character code PLUGIN LIST USE CHARACTER SET

Concerning the pasteboard: Test clipboard GET CLIPBOARD CLEAR CLIPBOARD APPEND TO CLIPBOARD SET PICTURE TO CLIPBOARD GET PICTURE FROM CLIPBOARD SET TEXT TO CLIPBOARD Get text from clipboard

becomes

Test pasteboard GET PASTEBOARD DATA CLEAR PASTEBOARD APPEND DATA TO PASTEBOARD SET PICTURE TO PASTEBOARD GET PICTURE FROM PASTEBOARD SET TEXT TO PASTEBOARD Get text from pasteboard

becomes

SET MENU BAR SET MENU ITEM SHORTCUT

Concerning menus: MENU BAR SET MENU ITEM KEY

01/10/2007 Page 34/45

Conversion of your 4D databases to 4D v11 SQL

Concerning hierarchical lists: INSERT LIST ITEM DELETE LIST ITEM

becomes

INSERT IN LIST DELETE FROM LIST

becomes

Get last table number Get last field number

becomes

DELETE FROM ARRAY INSERT IN ARRAY

Concerning the structure definition: Count tables Count fields

Concerning arrays: DELETE ELEMENT INSERT ELEMENT

Commands whose behavior has been modified: •

ALL RECORDS no longer accepts an unpointed field as parameter.

•

SET HTML ROOT(rootFolder) This command now works without restriction in non-contextual mode. It modifies the root folder for all the Web processes during the current session. The HTML root folder pathname set in the Preferences is not modified. The rootFolder parameter therefore now accepts a string of the type: Under Mac OS Under Windows

Disk:Applications:myserv:folder C:\Applications\myserv\folder

The previous syntax (relative “URL” type) can still be used. •

GET WEB FORM VARIABLES now requires the declaration of arrays used.

•

Create document In 4D v11SQL, the Document variable returns the complete pathname, whereas in 4D 2004 it returned the name indicated during Create document. Example: $Ref:= Create document ("MyGreatDocument") Document returns v2004 -> MyGreatDocument v11 SQL -> C:\MyFolder\MyDatabase\MyGreatDocument

•

Management of break levels when printing: ACCUMULATE and BREAK LEVEL: now mandatory in interpreted mode (was already so in compiled mode). ACCUMULATE no longer works with interprocess variables. For text fields and variables, only the first 80 characters were taken into account; with 4D v11, the whole text is taken into account.

•

CLEAR VARIABLE: This command now works the same way in both interpreted and compiled mode. It reinitializes variables to their default value in both modes. In 4D 2004: in interpreted, it removed the variables from memory and in compiled mode it reinitialized them. No longer accepts the CLEAR VARIABLE(“toto”) syntax.

•

Field and Field name: If you pass a pointer to a field, these functions return the number and name of the field of the subtable instead of the source field (this was already the case in 4D 2004, but it was not documented).

01/10/2007 Page 35/45

Conversion of your 4D databases to 4D v11 SQL •

Selected record number: in the framework of PRINT RECORD, returned 0. In 4D v11, it returns the real position.

•

SAVE SET: uses a new format: sets stored in 4D v11 cannot be read by previous versions, but LOAD SET can read the previous format.

•

SEND RECORD: uses a new format. Records sent with 4D v11 cannot be used by previous versions, but RECEIVE RECORD can read the previous format so long as it does not contain subtables (reminder: subtables are no longer supported).

•

QUIT 4D now works in asynchronous mode.

•

DELAY PROCESS now works on the user mode process.

•

SELECTION TO ARRAY no longer changes the current records, the selections of the main table and of related tables. Automatic relations are activated. The current records of related tables are no longer unloaded.

•

ARRAY TO SELECTION ignores fields that do not belong to the first table. Returns an error if the arrays are not the same size.

•

Self: the Self pointer in an object method calling a project method: in version 2004 in compiled mode, it returned the pointer to the object but in interpreted it returned Nil. In 4D v11, does not return null value in interpreted or compiled.

•

RELATE ONE SELECTION: in version 2004, took the first path possible. In 4D v11, takes the most optimized path: this can lead to incompatibilities: resolve them by specifying the list of fields by which to pass in the RELATE ONE SELECTION command (new feature in v11).

•

Overflow with index [[i]]: error message (absent in 4D 2004).

•

QUERY [Table1] Field1->

[Table2] [Table3] Field 1 Field2 -> Field 1 QUERY ([Table3];[Table1] Field 1=1) `regardless of field type In 4D v2004 -> No records found in [Table3] In 4D v11 SQL -> Records are found in [Table3] •

Unload-reload of current record: GOTO RECORD: only reloads current record if the READ ONLY / READ WRITE mode has changed. Same for the loading of the first record after a QUERY. RELATE ONE SELECTION: 4D 2004 unloaded the current record. 4D v11 SQL no longer does this.

•

Drop position: now works for variables, text fields and combo boxes.

•

Pointers to process variables. Now functioning is identical in both interpreted and compiled mode.

•

Modifications concerning 2D arrays: - If you pass a 2D array as a command parameter that is expecting a variable, 4D generates an error. In previous versions, no error was generated but the second dimension was not taken into account. - When the Type function is applied to the “row” of a 2D array, it now returns the actual type of the 2D array and no longer the “Array 2D” type. - 2D string arrays: it is no longer possible to change the length of a string in interpreted mode.

01/10/2007 Page 36/45

Conversion of your 4D databases to 4D v11 SQL

Commands for which the first parameter, the table, is now mandatory: APPLY TO SELECTION QUERY BY FORMULA QUERY SELECTION BY FORMULA EDIT FORMULA

Commands that change theme: “User Interface” Theme HIDE MENU BAR SHOW MENU BAR SET ABOUT SHOW TOOL BAR HIDE TOOL BAR “4D Environment” Theme SET DATABASE PARAMETER Get database parameter BUILD APPLICATION PLUGIN LIST “Tools” Theme LAUNCH EXTERNAL PROCESS SET ENVIRONMENT VARIABLE “Users and Groups” Theme Is license available “Formulas” Theme EXECUTE FORMULA

Optimized commands: Many commands have been optimized in 4D v11 SQL. More particularly: QUERY BY FORMULA QUERY SELECTION BY FORMULA QUERY WITH ARRAY

Obsolete commands: To be replaced by: SEARCH BY INDEX SORT BY INDEX SAVE OLD RELATED ONE Get component resource ID DATA SEGMENT LIST ADD DATA SEGMENT Version type

QUERY ORDER BY SAVE RELATED ONE -

Commands that no longer serve any purpose: REDRAW LIST (hierarchical list)

01/10/2007 Page 37/45

Conversion of your 4D databases to 4D v11 SQL

Obsolete syntaxes: •

If (Record number([Table])=-3) This syntax is not recommended since 4D v 6.5. In the On saving new record trigger, this function no longer returns -3, but rather the record number. To be replaced by: If (Is new record([Table]))

• The syntaxes If(Before)… (During)… (After) in form methods must be replaced by a test on the form event: If(Form event= On Load) etc. • CLEAR LIST (list{ ;*}) used to delete sublists should be replaced by the DELETE FROM LIST command (which is the new name of the former DELETE LIST ITEM command). • $Structure :=Structure file $Resources :=Open resource file ($Structure)

Commands working with subrecords: After the conversion of a subtable into a standard table, the commands working with subrecords continue to function as long as you do not remove the special relation between the two tables. If you remove this relation, you will need to use the commands concerning tables. Warning: Commands like DUPLICATE RECORD and SEND RECORD no longer take subtables into account. On the other hand, RECEIVE RECORD receives subtables.

Integrations (partial) 4D Insider replaced by the “System drag and drop” and by “Findreplace”: Can be moved from one database to another: (partial integration of 4D Insider) • • • • • • • • • • • •

Choice lists Style sheets Formats and filters Pictures from picture library Menus and menu bars Help tips Project forms with form methods Table form with form methods Project methods Folders/subfolders Tables with fields and triggers All form objects (with object method – 4D 2004)

01/10/2007 Page 38/45

Conversion of your 4D databases to 4D v11 SQL

•

Objects having a naming conflict appear in red. Dependent objects are in italics. In a menu, the project method reference is kept. The form inheritance is also kept. Access rights are not kept. ALT CLICK (Win) OPTION CLICK (Mac) to select and deselect all. Use another object: only exists for dependent objects. You can set moving options in the Preferences:

4D Tools replaced by the MSC The Maintenance and Security Center now replaces 4D Tools. It is integrated into 4D and you can access it via the tool bar: 01/10/2007 Page 39/45

Conversion of your 4D databases to 4D v11 SQL

4D Pack With V11, and due to migration problems related to the elimination of certain 4D Pack commands, the loading of 4D Pack 2004 is allowed in order to make the migration easier. We nevertheless do not accept any responsibility concerning the functioning of 4D Pack 2004 in v11.

Obsolete 4D Pack commands: After conversion of existing databases, these commands must be replaced: 4D Pack Commands:

To be replaced with:

AP PICT DRAGGER AP PICT UPDATER

4D drag and drop 4D Pack external area %AP Pict displayer to be replaced by a picture field READ PICTURE FILE

AP Read Picture File AP Save BMP 8 bits, AP Save GIF

CONVERT PICTURE and WRITE PICTURE FILE

AP Read Picture BLOB,

Serves no purpose since pictures are stored in native format Or use: BLOB TO PICTURE

AP PICT DRAGGER AP Rect dragger

MouseDown, MouseX and MouseY system variables

AP SET PICT MODE

Use: SET FORMAT

AP Select document

Use: Select document

AP Set palette

Use: Select RGB color

01/10/2007 Page 40/45

Conversion of your 4D databases to 4D v11 SQL AP ShellExecute

Use: LAUNCH EXTERNAL PROCESS

AP Sublaunch

Use: LAUNCH EXTERNAL PROCESS

Already obsolete in 4D 2004: AP PrintDefault AP Text to PrintRec AP PrintRec to text AP PrValidate

Use: AP Blob to print setting and AP Print settings to Blob

AP Toolbar installed

Use: Tool bar height (returns 0 if not installed)

AP SET WEB FILTERS

Use: SET DATABASE PARAMETER

AP Add table and fields

Use new SQL capacities of 4D v11 SQL example: CREATE TABLE Client (Last name char(50), First name char(50), Address char(50), City char(50), Country char(25), Birthdate date)

AP Create relation

no work-around for the moment (implementation planned for a future version)

Commands still present but no longer recommended for use: AP Save BMP 8 bits AP Get picture type AP GET PARAM AP SET PARAM

many obsolete parameters and can be replaced by SET DATABASE PARAMETER

AP AVAILABLE MEMORY: warning, the values are now in KB AP FCLOSE AP fopen AP FPRINT AP fread

can be replaced by SET CHANNEL and SEND PACKET and RECEIVE PACKET

AP CLOSE HELP AP HELP INDEX AP HELP ON HELP AP HELP ON KEY

No longer work under Windows XP, not VISTA compatible

Improved command: AP Create method

01/10/2007 Page 41/45

Conversion of your 4D databases to 4D v11 SQL The propertiesArray parameter can contain 2 additional values: - pass 1 in propertiesArray {5} if the method must be shared between a component and a host database. - pass 1 in propertiesArray {6} if the method must be used in the context of SQL.

Identical commands: AP Get templates AP Rect dragger AP SET CLOCK AP Timestamp to GMT AP FULL SCREEN AP NORMAL SCREEN AP BLOB to print settings AP Print settings to BLOB AP Get tips state AP SET TIPS STATE AP Get field infos AP Get table info

New commands • AP Get File MD5 Digest (filePath;digest;fork) -> Error Parameter filePath digest fork

Type Text Longint Longint

-> <->

Description Complete pathname of file MD5 Digest of file 0: Datafork or 1: Resource fork

Description The AP Get File MD5 Digest command returns the MD5 key of a document. C_TEXT($document) C_LONGING(<>digest;$fork) $fork:=0 $document:=Select document("" ; "" ; "" ;0) $error:= AP Get File MD5 Digest ($document;<>digest;$fork) • AP Does method exist

Shortcuts ALT on opening a database in order to create a choose a data file.

Design Mode Print: CTRL/CMD P Page Setup: CTRL/CMD SHIFT P Open Form: CTRL/CMD L Open Explorer: CTRL/CMD SHIFT E Open Tool Box: CTRL/CMD SHIFT T Compiler: CTRL/CMD * Find in Design: CTRL/CMD SHIFT F Preferences: CTRL/CMD /

01/10/2007 Page 42/45

Conversion of your 4D databases to 4D v11 SQL Test Application: CTRL/CMD I Run Method: CTRL/CMD R Restart Interpreted: CTRL/CMD ALT I Restart Compiled: CTRL/CMD SHIFT I Minimize Window: CTRL/CMD M Minimize All Windows: CTRL/CMD ALT SHIFT M Structure: Select All (table or field): CTRL/CMD A Continuous or discontinuous selection (table or field): CLICK SHIFT CLICK or CLICK CTRL/CMD CLICK Tab and Shift Tab to select Next table and Previous table Carriage return to create a field Method: Run Method: CTRL/CMD K In the Method editor, to open a method: ALT DOUBLE CLICK Show/Hide Line Numbers: CTRL/CMD ALT SHIFT N Next Error: CTRL/CMD = Previous Error: CTRL/CMD ALT = Select Enclosing Block: CTRL/CMD B Comment/Uncomment: CTRL/CMD / Swap Expression: CTRL/CMD ALT T Form: Move to Front: CTRL/CMD F Move to Back: CTRL/CMD SHIFT B Group: CTRL/CMD G Ungroup: CTRL/CMD SHIFT G Help tip on object: CTRL/CMD SHIFT on object

Records List of Tables: CTRL/CMD SHIFT U Show Current Table: CTRL/CMD U New Record in List: CTRL/CMD SHIFT N New Record: CTRL/CMD ALT N Show All: CTRL/CMD + Show Subset: CTRL/CMD – Order By: CTRL/CMD SHIFT Y Apply Formula: CTRL/CMD SHIFT R Find: CTRL/CMD Y Flush Data Buffers: CTRL/CMD ALT SHIFT S

01/10/2007 Page 43/45

Conversion of your 4D databases to 4D v11 SQL

Appendix 1: Language codes: The language codes langues are as follows (as mentioned in the keyboardmapping.xml file). DC_AFRIKAANS = 1078 DC_ALBANIAN = 1052 DC_ARABIC_SAUDI_ARABIA = 1025 DC_ARABIC_IRAQ = 2049 DC_ARABIC_EGYPT = 3073 DC_ARABIC_LIBYA = 4097 DC_ARABIC_ALGERIA = 5121 DC_ARABIC_MOROCCO = 6145 DC_ARABIC_TUNISIA = 7169 DC_ARABIC_OMAN = 8193 DC_ARABIC_YEMEN = 9217 DC_ARABIC_SYRIA = 10241 DC_ARABIC_JORDAN = 11265 DC_ARABIC_LEBANON = 12289 DC_ARABIC_KUWAIT = 13313 DC_ARABIC_UAE = 14337 DC_ARABIC_BAHRAIN = 15361 DC_ARABIC_QATAR = 16385 DC_BASQUE = 1069 DC_BELARUSIAN = 1059 DC_BULGARIAN = 1026 DC_CATALAN = 1027 DC_CHINESE_TRADITIONAL = 1028 DC_CHINESE_SIMPLIFIED = 2052 DC_CHINESE_HONGKONG = 3076 DC_CHINESE_SINGAPORE = 4100 DC_CROATIAN = 1050 DC_CZECH = 1029 DC_DANISH = 1030 DC_DUTCH = 1043 DC_DUTCH_BELGIAN = 2067 DC_ENGLISH_US = 1033 DC_ENGLISH_UK = 2057 DC_ENGLISH_AUSTRALIA = 3081 DC_ENGLISH_CANADA = 4105 DC_ENGLISH_NEWZEALAND = 5129 DC_ENGLISH_EIRE = 6153 DC_ENGLISH_SOUTH_AFRICA = 7177 DC_ENGLISH_JAMAICA = 8201 DC_ENGLISH_CARIBBEAN = 9225 DC_ENGLISH_BELIZE = 10249 DC_ENGLISH_TRINIDAD = 11273 DC_ESTONIAN = 1061 DC_FAEROESE = 1080 DC_FARSI = 1065 DC_FINNISH = 1035 DC_FRENCH = 1036 DC_FRENCH_BELGIAN = 2060 DC_FRENCH_CANADIAN = 3084 DC_FRENCH_SWISS = 4108 DC_FRENCH_LUXEMBOURG = 5132 DC_GERMAN = 1031 DC_GERMAN_SWISS = 2055 DC_GERMAN_AUSTRIAN = 3079 DC_GERMAN_LUXEMBOURG = 4103 01/10/2007 Page 44/45

Conversion of your 4D databases to 4D v11 SQL DC_GERMAN_LIECHTENSTEIN = 5127 DC_GREEK = 1032 DC_HEBREW = 1037 DC_HUNGARIAN = 1038 DC_ICELANDIC = 1039 DC_INDONESIAN = 1057 DC_ITALIAN = 1040 DC_ITALIAN_SWISS = 2064 DC_JAPANESE = 1041 DC_KOREAN_WANSUNG = 1042 DC_KOREAN_JOHAB = 2066 DC_LATVIAN = 1062 DC_LITHUANIAN = 1063 DC_NORWEGIAN = 1044 DC_NORWEGIAN_NYNORSK = 2068 DC_POLISH = 1045 DC_PORTUGUESE = 2070 DC_PORTUGUESE_BRAZILIAN = 1046 DC_ROMANIAN = 1048 DC_RUSSIAN = 1049 DC_SERBIAN_LATIN = 2074 DC_SERBIAN_CYRILLIC = 3098 DC_SLOVAK = 1051 DC_SLOVENIAN = 1060 DC_SPANISH_CASTILLAN = 1034 DC_SPANISH_MEXICAN = 2058 DC_SPANISH_MODERN = 3082 DC_SPANISH_GUATEMALA = 4106 DC_SPANISH_COSTA_RICA = 5130 DC_SPANISH_PANAMA = 6154 DC_SPANISH_DOMINICAN_REPUBLIC = 7178 DC_SPANISH_VENEZUELA = 8202 DC_SPANISH_COLOMBIA = 9226 DC_SPANISH_PERU = 10250 DC_SPANISH_ARGENTINA = 11274 DC_SPANISH_ECUADOR = 12298 DC_SPANISH_CHILE = 13322 DC_SPANISH_URUGUAY = 14346 DC_SPANISH_PARAGUAY = 15370 DC_SPANISH_BOLIVIA = 16394 DC_SPANISH_EL_SALVADOR = 17418 DC_SPANISH_HONDURAS = 18442 DC_SPANISH_NICARAGUA = 19466 DC_SPANISH_PUERTO_RICO = 20490 DC_SWEDISH = 1053 DC_SWEDISH_FINLAND = 2077 DC_THAI = 1054 DC_TURKISH = 1055 DC_UKRAINIAN = 1058 DC_VIETNAMESE = 1066

01/10/2007 Page 45/45