SQL

4D v16

SQL

ビデオで説明されている操作に加え、以下の点についても留意してください:

SQLリクエストの中で配列要素を使用することはできません。なぜならSQLインタープリターがそれをサポートしないからです。

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.