External Data Source Commands

version 2004

This theme contains commands that allow 4th Dimension to access, via standard protocols, data stored in other applications. In the current version of 4th Dimension, only ODBC commands are available.

The ODBC (Open DataBase Connectivity) standard specifies a library of standardized functions. These functions allow an application such as 4th Dimension to access any ODBC-compatible data management system (databases, spreadsheets, etc.) via SQL language.

Note: 4th Dimension also allows data to be imported from and exported to an ODBC source in User mode. For more information, please refer to the 4th Dimension User Reference manual.

The high-level ODBC commands in the "External Data Source" theme of 4th Dimension can be used to implement simple solutions allowing 4th Dimension applications to communicate with ODBC data sources. If your applications require more extensive support of ODBC standards, you will need to have the "low level" ODBC plug-in for 4D, 4D ODBC Pro.

The built-in ODBC commands of 4th Dimension implement the following principles:

• The scope of a connection is the process. If you want to manage several simultaneous connections, you must start a process by ODBC LOGIN.

The ODBC CANCEL LOAD command can be used to execute several SELECT requests in the same connection.

• You can intercept any ODBC errors generated during the execution of one of the ODBC commands using the ON ERR CALL command. The ODBC GET LAST ERROR command can be used in this case to obtain additional information.

The following table lists the correspondences that are automatically established by 4th Dimension between 4D and SQL data types:

4th Dimension provides two ways for inserting 4D objects into ODBC requests: direct association and the setting of parameters using ODBC SET PARAMETER.

Direct association requires the 4D object to be used (variable, array or field) to be inserted between the << and >> characters in the text of the request. For example:

INSERT INTO emp (empnum,ename) VALUES (<<vEmpnum>>,<<vEname>>)

In this example, the current values of the 4D vEmpnum and vEname variables will replace the parameters when the request is executed. This solution also works with 4D fields and arrays.

This easy-to-use syntax nevertheless has the drawback of not being compliant with the SQL standard and of not allowing the use of output parameters. To remedy this, you can use the ODBC SET PARAMETER command. This command can be used to set each 4D object to be integrated into a request as well as its mode of use (input, output or both). The syntax produced is thus standard. For more information, please refer to the description of the ODBC SET PARAMETER command.

1. This example executes an ODBC request that directly uses the associated 4D arrays:

   ARRAY TEXT(MyTextArray;10)
   ARRAY LONGINT(MyLongintArray;10)

   For(vCounter;1;Size of array(MyTextArray))
      MyTextArray{vCounter}:="Text"+String(vCounter)
      MyLongintArray{vCounter}:=vCounter
   End for
   ODBC LOGIN("mysql";"root";"")
   SQLStmt:="insert into app_testTable (alpha_field, longint_field) VALUES (<<MyTextArray>>, <<MyLongintArray>>)"
   ODBC EXECUTE(SQLStmt)

2. This example can be used to execute an ODBC request that directly uses the associated 4D fields:

   ALL RECORDS([Table 2])
   ODBC LOGIN("mysql";"root";"")
   SQLStmt:="insert into app_testTable (alpha_field, longint_field) VALUES (<<[Table 2]Field1>"+">,<<[Table 2]Field2>>)"
   ODBC EXECUTE(SQLStmt)