Door 5 Count your field and rows

Yesterday, we looked at the FM.SQL.Execute and FM.SQL.Text functions. In the parameters of the FM.SQL.Text function, we saw that we can limit our result set during output. But how large is our result set actually?

Let's find out together today. We have two functions that are essential for this: FM.SQL.RowCount and FM.SQL.FieldCount. With these two functions, we can count the rows or columns of our result.

Let's try it out right away. We have again used the query SELECT Name, Launch FROM Movie WHERE Launch<2000. We know that our result contains two fields, namely Name and Launch, and that the result contains a total of 11 movies.

This is what our script looks like:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Launch FROM Movie WHERE Launch<2000" ) ]
Set Variable [ $CountRow ; Value: MBS("FM.SQL.RowCount"; $SQLRes) ]
Set Variable [ $CountField ; Value: MBS("FM.SQL.FieldCount"; $SQLRes) ]
Show Custom Dialog [ "Count Rows and Fields" ; 
   "Row Count: " & $CountRow & 
   "¶Field Count: " & $CountField ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

First, we use our query again, just as before. Instead of outputting the result as text this time, we use the FM.SQL.RowCount and FM.SQL.FieldCount functions to determine the corresponding values. In both functions, the reference received from the FM.SQL.Execute function is used in the parameters. In the dialog, we then assemble our desired result accordingly. Since we are working with references here, we must of course release them again at the end.

When we run this script, our result looks like this:

So we get what we expected. Now we can use this knowledge in the FM.SQL.Text function, for example. Here we can limit our output to a specific range. We left these parameters blank in the last door, but now we want to fill them.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Launch FROM Movie WHERE Launch<2000" ) ]
Set Variable [ $CountRow ; Value: MBS("FM.SQL.RowCount"; $SQLRes) ]
Set Variable [ $CountField ; Value: MBS("FM.SQL.FieldCount"; $SQLRes) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; 
   $SQLRes;0;$CountRow-1;0;$CountField-1;"¶¶";"| - |") ]
Show Custom Dialog [ "SQL Result" ; $r ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

If we select these settings, we get the entire range specified because we display all rows from index range 0 to Row Count-1 and also the fields from index 0 to Field Count-1. If we want, we can also set the ranges differently. For example, if we want to output all data from the second row onwards and we don't want the last row, we can set the index to 1 for the rows to start at the second row and Row Count-2 to exclude the last row.

If you have already worked with SQL, you may know that the word COUNT is also a keyword in SQL, because here too we can determine how many rows the result set has. We set the COUNT in the SELECT part. The field names are replaced by an * in brackets. In this case, we do not need to select specific fields, because this example is only about the number.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT COUNT (*) FROM Movie WHERE Launch<2000" ) ]

This statement also returns 11.

But what is the difference, you may ask. This becomes clear when we apply the FM.SQL.RowCount and FM.SQL.FieldCount functions to this statement:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT COUNT (*) FROM Movie WHERE Launch<2000" ) ]
Set Variable [ $CountRow ; Value: MBS("FM.SQL.RowCount"; $SQLRes) ]
Set Variable [ $CountField ; Value: MBS("FM.SQL.FieldCount"; $SQLRes) ]
Show Custom Dialog [ "Count Rows and Fields" ; 
   "Row Count: " & $CountRow & 
   "¶Field Count: " & $CountField ]

Contrary to what you might expect, our result now only has one row and one column. This contains the number 11. This means that the SQL keyword COUNT works on the database and FM.SQL.RowCount and FM.SQL.FieldCount work on the result set.

Now I would like to show you a special use of Count. By adding another keyword, you can now find out the number of different years our films can have. This means that if two films were made in 1992, 1992 is only counted once. The keyword we need for this is DISTINCT. We put this in the brackets and specify the field from which we want to determine the number of different values. In our case, this is the Launch field. The query then looks like this:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT COUNT (DISTINCT Launch) FROM Movie WHERE Launch<2000" ) ]

Our answer is 10, because two of the films listed were made in the same year.

That brings us to the end of today's door. I hope we'll see again tomorrow.