Displaying the result of an SQL query in a list box

Home

4D v16.3

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 Dynamic variables of the array type. These dynamic arrays last as long as the form does. A dynamic variable is also created for each header. When the LISTBOX GET ARRAYS command is called, the arrColVars parameter contains pointers to the dynamic arrays and the arrHeaderVars parameter contains pointers to the dynamic 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