Door 7 Precise entries and their types

Welcome to door 7. We still have some homework from yesterday to discuss. The task was to solve this query with a cross join:

SELECT Rols2.Movie, Movies2.Name, Movies2.Director, Movies2.Launch, Rols2.Actor 
FROM Movies2 
JOIN Rols2 ON Movies2.PrimaryKey = Rols2.Movie

Such a cross join may look like this:

# CROSS JOIN WITH WHERE
Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Rols2.Movie, Movies2.Name, Movies2.Director, Movies2.Launch, Rols2.Actor 
   FROM Rols2 CROSS JOIN Movies2 WHERE Rols2.Movie=Movies2.PrimaryKey" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ]
Show Custom Dialog [ "Result" ; $r ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

First, we create the cross join across both tables and then limit the result set using the WHERE clause so that only records are included in the result, where the Movie field from the Rols2 table matches the PrimaryKey field from the Movies2 table. This gives us the correct result:

Now let's move on to today's topic. Until now, we have always queried the database and had the results output as complete text. We want to change that today, because we can also go through and evaluate our results field by field. To do this, we use the FM.SQL.Field function. This function returns the value for a specific field. In the parameters of the function, we first specify the reference for the result, then the row number we want, then the column, and finally, we can optionally set a flag. If we set the flag to 0, which is also the default value, we get the value from the field; if we specify 1, we get the file name for a container field. If we specify 2, we get the container value back in base64-encrypted form.

Let's look at an example: we want to query the data from the results and then use it to create new data records. In this example, we want to write all movies with their name, launch year, and generated sales that were made before the year 2000 to a separate table. So that we don't have to deal with converting lists, we use the FM.SQL.Field function to run through the result. And this is what it might look like:

Go to Layout [ “Door7” (Door7) ; Animation: None ]
Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Launch, Generated_Sales FROM Movie WHERE Launch<2000" ) ]
Set Variable [ $CountRow ; Value: MBS("FM.SQL.RowCount"; $SQLRes) ]
Set Variable [ $i ; Value: 0 ]
Loop [ Flush: Always ]
	New Record/Request
	Set Field [ Door7::Name ; MBS( "FM.SQL.Field"; $SQLRes; $i; 0 ) ]
	Set Field [ Door7::Launch ; MBS( "FM.SQL.Field"; $SQLRes; $i; 1 ) ]
	Set Field [ Door7::Generated_Sales ; MBS( "FM.SQL.Field"; $SQLRes; $i; 2 ) ]
	Set Variable [ $i ; Value: $i+1 ]
	Exit Loop If [ $i ≥ $CountRow ]
End Loop

First, we make sure that we are in the correct layout in which we want to add our data records. In our case, this is the Door7 table that we have just created. Then we execute the appropriate query. Here we retrieve the fields we need: Name, Launch, and Generated_Sales from the Movie table, and we only want the records where Launch was earlier than 2000. Once we have the result, we determine how many records were found. To do this, we use the FM.SQL.RowCount function, which we already know from Door 5. We now go through the loop with this information and run through the individual fields. We retrieve the individual fields with the FM.SQL.Field function and use it to set the fields. When addressing the individual values, we must make sure that we do not start counting at 1 and instead start at 0.

Another helpful function is the FM.SQL.FieldType function. With this function, we can determine the field type of a field, e.g., whether it is text or a number.

To do this, we first specify the reference for the function and then specify the desired field with row and column numbers. Let's determine this for our query:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Launch, Generated_Sales FROM Movie WHERE Launch<2000" ) ]
Set Variable [ $TypName ; Value: MBS( "FM.SQL.FieldType"; $SQLRes; 0; 0 ) ]
Set Variable [ $TypLaunch ; Value: MBS( "FM.SQL.FieldType"; $SQLRes; 0; 1 ) ]
Set Variable [ $TypSale ; Value: MBS( "FM.SQL.FieldType"; $SQLRes; 0; 2 ) ]
Show Custom Dialog [ "Type" ; "Name: "& $TypName  & "¶Launch: " & 
   $TypLaunch &"¶Generated_Sales: " & $TypSale ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

We will look at the fields in the first row.

But today I would like to show you something else. Until now, we have only searched for entire values, such as a specific name or a number. But what if we only want to search for fragments? We still know that the movie title had something to do with Miracle. But how do we search for something like that? For this purpose, we have something in SQL called a wildcard. These are characters that we can use as placeholders. First, we have the % character. This stands for any character string that can be of any length. It can represent no, one, or several characters. This is best illustrated with an example. We still want to search for movies that contain the word "Miracle" somewhere. We don't know if it's at the beginning, end, or in the middle. So we use the placeholder and put it before and after the word Miracle.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name FROM Movie WHERE Name LIKE '%Miracle%'" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes;"";"";"";"";"¶¶";" - ") ]
Show Custom Dialog [ $r ]

Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

Our results found 2 matching films:

In one film, the word "Miracle" appears right at the beginning, and in the other film, it appears at the end. But what if we know for sure that the film title does not begin with this term? That's where the second wildcard I want to introduce to you today comes in. The underscore stands for exactly one arbitrary character. For example, if we write h_t, it would find entries with hat and hot, but not hight. This is because we have an h at the beginning and a t at the end, but there is more than one character in between. So if we want to see the films that have at least one other character before the word 'Miracle', meaning that the title does not begin with it, we can combine the two wildcards: a single character, then a string of any length, the word 'Miracle', and then, for any characters that may follow, the wildcard for a string of any length again. The query then looks like this:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name FROM Movie WHERE Name LIKE '_%Miracle%'" ) ]

And we only get one movie back.

When using wildcards, we need to be aware that the terms are case sensitive. This means that a title in which the word "miracle" was written in lowercase would not have been found.

This brings us to the end of today's door, and we wish you a wonderful second Advent.