4D Doc Center : SQL

4D v13

SQL

In addition to the manipulations explained in the video, you should also note that:

We cannot use pointers or array elements in an SQL request because the interpreter does not take them into account.

In this case, it is often preferable to set up the query in the form of text and to execute it using the EXECUTE IMMEDIATE command.

  `VARIABLE TYPING IMPERATIVE, otherwise the query generates an error

 ARRAY TEXT(ArrTechniciansStats;0) `Creates an array without any elements that will be filled by the next query

 ARRAY TEXT($ArrTechniciansID;0) `Array where the IDs of the Technicians are stored

 

  `Do not forget the space character or carriage return at the end of each line

 C_TEXT($TextQuery)

 $TextQuery:="SELECT Last_name, ID "

 $TextQuery:=$TextQuery+"FROM TECHNICIANS "

 $TextQuery:=$TextQuery+"INTO :ArrTechnicians, :$ArrTechniciansID"

 

 Begin SQL `We only want to recover the last name of the technician and store it in an array

    EXECUTE IMMEDIATE :$TextQuery;

 End SQL

This selects the last name and ID in the Technicians table and transfer them into the 2 arrays.

Video script