4D v13.4

Overview of SQL Commands

Home

 
4D v13.4
4D Language Reference
SQL
Overview of SQL Commands

Overview of SQL Commands  


 

 

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 ODBC driver).

The SQL documentation in 4D is built upon two main parts:

  • The 4D SQL Reference Guide (4D SQL Reference). This manual describes 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 (for example MissingRef or MissingRef). Please refer to this manual for any question regarding SQL language implementation in 4D.

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

  • Unless indicated otherwise, 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.
  • 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 via the IMPORT ODBC and EXPORT ODBC commands or "manually" 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 TypeSQL Type
C_STRINGSQL_C_CHAR
C_TEXTSQL_C_CHAR
C_REALSQL_C_DOUBLE
C_DATESQL_C_TYPE_DATE
C_TIMESQL_C_TYPE_TIME
C_BOOLEANSQL_C_BIT
C_INTEGERSQL_C_SHORT
C_LONGINTSQL_C_SLONG
C_BLOBSQL_C_BINARY
C_PICTURESQL_C_BINARY
C_GRAPHSQL_C_BINARY

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 ":".
 SQL EXECUTE("INSERT INTO emp (empnum,ename) VALUES (<<vEmpnum>>,<<vEname>>)")
 SQL EXECUTE("SELECT age FROM People WHERE name= :vName")

Note: In compiled mode, you cannot use references to local variables (beginning with the $ symbol).

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)

In compiled mode, you can use local variable references (beginning with the $ character) in SQL statements under certain conditions:

  • You can use local variables within a Begin SQL / End SQL sequence, except with the EXECUTE IMMEDIATE command;
  • You can use local variables with the SQL EXECUTE command when these variables are used directly in the parameter of the SQL request and not through references.
    For example, the following code works in compiled mode:
     SQL EXECUTE("select * from t1 into :$myvar") // works in compiled mode

    The following code generates an error in compiled mode:
     C_TEXT(tRequest)
     tRequest:="select * from t1 into :$myvar"
     SQL EXECUTE(tRequest) // error in compiled mode

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).

It is possible to place the results of an SQL query directly in an array type list box. This offers a rapid means for viewing the results of SQL queries. Only queries of the SELECT type can be used. This mechanism cannot be used with an external SQL database. 

It works according to the following principles:

  • Create the list box which will receive the query results. The data source of the list box must be Arrays.
  • Execute an SQL query of the SELECT type and assign the result to the variable associated with the list box. You can use the Begin SQL/End SQL keywords (see the 4D Language Reference manual).
  • List box columns can be sorted or modified by the user.
  • Each new execution of a SELECT query with the list box leads to the resetting of the columns (it is not possible to fill the same list box progressively using several SELECT queries).   
  • It is recommended to give the list box the same number of columns as there will be in the SQL query result. If the number of list box columns is less than that required by the SELECT query, columns are added automatically. If the number of columns is more than required by the SELECT query, the unnecessary columns are automatically hidden.
    Note: The columns added automatically are bound to variables of the temporary array type. These temporary arrays last as long as the form does. A temporary variable is also created for each header. When the LISTBOX GET ARRAYS command is called, the arrColVars parameter contains pointers to the temporary arrays and the arrHeaderVars parameter contains pointers to the temporary header variables. If the added column is, for example, the fifth column, its name is sql_column5 and its header name is sql_header5.
  • In interpreted mode, existing arrays that are used by the list box can be retyped automatically according to the data sent by the SQL query.

Example
We want to retrieve all the fields of the PEOPLE table and put their contents into the list box having the variable name vlistbox. In the object method of a button (for example), simply write:

 Begin SQL
    SELECT * FROM PEOPLE INTO <<vlistbox>>
 End SQL

 
PROPERTIES 

Product: 4D
Theme: SQL

 
SEE ALSO 

EXPORT ODBC
IMPORT ODBC