Video script

In this video, we're going to learn how to implement simple SQL commands that are integrated into 4D programming in two forms:

• several lines of SQL commands inserted into an SQL processing block
• or a single command line that executes a list of commands stored in a text variable.

In addition to the 4D language, we can integrate SQL commands into 4D as you do in other tools:

• mysql_query in PHP
• INCLUDE SQLCA in COBOL
• and so on.

These commands can also easily be integrated into your existing development projects.

We're going to take our Statistics form to calculate the same array based on SQL commands:

• We duplicate the button
• The beginning of the method stays the same
• Since we have not selected any records, the Technicians arrays will have 0 elements
• We add the Technicians IDs array into a local variable; we do not need to save it later
• Next we integrate our SQL code
  This selects the last name and the ID from the Technicians table and transfers them into the 2 arrays.

Note the syntax which indicates to 4D to transfer the result into the arrays. We use the variable name preceded by the “:” (colon).
The rest of the method (sizing the interventions array) remains generally structured in the same way:

• We just add the $Num value to know how many technicians we are going to process
• Then we remove the end part that we're going to replace with SQL commands
• We save the ID of the technician to be processed in a variable
• We execute the SQL commands that take the start year and end year date into account
• And then we assign values to the interventions array at the X element

When the method is executed:

• Here we pass the SQL code
• We do in fact have 7 Technicians
• We size the array of interventions
• Then for each value of the array, we'll do a SELECT COUNT
• Store it in a Result variable
• That will assign values to the array

and whether we choose one or the other of the options, we still obtain the same result.

Another way of programming, especially with this first part, would be to write it as follows:

• in other words, we write the query in a Text variable
• and then, between a Begin SQL and an End SQL, we use the EXECUTE IMMEDIATE command to which we pass the query in the form of text

which gives us the exact same result.