SQL

Home

4D v16

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

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.