Overview of SQL Commands

version 11.3 (Modified)

Beginning with version 11, 4D includes an integrated SQL kernel. The program also includes an SQL server that other 4D applications or third-party applications can query (via the 4D OBDC driver).

The different ways of accessing the 4D SQL kernel, the configuration of the SQL server as well as the commands and keywords that can be used in SQL queries are detailed in a separate manual, the 4D SQL Reference manual.

The "SQL" theme groups together various 4D commands concerning the use of SQL in 4D:

• Control of the SQL server: START SQL SERVER and STOP SQL SERVER

• Direct access to the integrated SQL kernel: SET FIELD VALUE NULL, Is field value Null, QUERY BY SQL.

• Management of connections to external or internal data sources (SQL pass-through): GET DATA SOURCE LIST, Get current data source, SQL LOGIN, SQL LOGOUT.

• High-level commands for handling data in the framework of direct SQL connections or via ODBC: Begin SQL, End SQL, SQL CANCEL LOAD, SQL LOAD RECORD, SQL EXECUTE, SQL EXPORT, SQL End of selection, SQL SET OPTION, SQL SET PARAMETER, SQL IMPORT, SQL GET LAST ERROR, SQL GET OPTION.

The built-in SQL commands of 4D begin with the prefix "SQL" and implement the following principles:

• You can use these commands with the 4D internal SQL kernel or in an external connection that is opened directly or via ODBC. The SQL LOGIN command lets you specify the type of connection to open.

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

The SQL 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 high-level SQL commands using the ON ERR CALL command. The SQL GET LAST ERROR command can be used in this case to obtain additional information.

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

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

Note: The high-level SQL commands of 4D can be used to implement simple solutions allowing 4D 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 following table lists the correspondences that are automatically established by 4D between 4D and SQL data types:

4D provides two ways for inserting 4D expressions (variables, arrays, fields, pointers, valid expressions) into SQL requests: direct association and the setting of parameters using SQL SET PARAMETER.

Direct association can be carried out in two ways:

• Insertion of the name of the 4D object between the << and >> characters in the text of the request.

• Precede the reference with a colon ":".

Examples:

   SQL EXECUTE("INSERT INTO emp (empnum,ename) VALUES (<<vEmpnum>>,<<vEname>>)")
   SQL EXECUTE("SELECT age FROM People WHERE name= :vName")

In these examples, the current values of the 4D vEmpnum, vEname and vName 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 SQL 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 SQL SET PARAMETER command.

1. This example executes an SQL query 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
   SQL LOGIN("mysql";"root";"")
   SQLStmt:="insert into app_testTable (alpha_field, longint_field) VALUES (<<MyTextArray>>, <<MyLongintArray>>)"
   SQL EXECUTE(SQLStmt)

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

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

3. This example lets you execute an SQL query by directly passing a variable via a dereferenced pointer:

   C_LONGINT($vLong)
   C_POINTER($vPointer)
   $vLong:=1
   $vPointer:=->$vLong
   SQL LOGIN("mysql";"root";"")
   SQLStmt:="SELECT Col1 FROM TEST WHERE Col1=:$vPointer"
   SQL EXECUTE(SQLStmt)

Retrieving values in the 4D language that result from SQL queries is carried out in two ways:

• Using the additional parameters of the SQL EXECUTE command (recommended solution).

• Using the INTO clause in the SQL query itself (solution reserved for special cases).