Door 23 Dictionary

Welcome to door 23. We have already seen that we can turn an SQL result into JSON, XML, a matrix, CSV, or text. Now I would like to introduce you to another data structure: dictionaries.

These dictionaries are key-value pairs. Instead of addressing values via positions or sequences, you access the desired data directly via a meaningful key. This allows related information to be stored in a clear and structured manner. In addition, dictionaries are very fast at querying data, making them ideal for intermediate results. Today, we want to look at how we can fill such a dictionary with data using SQL. To do this, we have the functions Dictionary.AddSQLRecord and Dictionary.AddSQLRecords. Both functions are very similar in structure. First, both are passed the references to the dictionary. This is followed by a list of the names for the individual keys to be entered into the dictionary. This is followed by the SQL query we use to select the data for our dictionary. Next, we specify the file on which this SQL query is to be executed. If necessary, you can also pass any number of parameters to the function. We do not need these in our example. If both functions have the same parameter input and a similar description, what is the difference between the two? The Dictionary.AddSQLRecord function takes only the first entry from the SQL result set and stores it in the dictionary. Dictionary.AddSQLRecords, on the other hand, takes all records from the SQL result and enters the data into the dictionary.

But before we can use the two functions, we first have to create a reference to a new dictionary. The Dictionary.Create function helps us with this. We can then use the reference provided by this function in the corresponding function. Let's start with the Dictionary.AddSQLRecord function.

Set Variable [ $Dict ; Value: MBS("Dictionary.Create") ]
Set Variable [ $r ; Value: MBS( "Dictionary.AddSQLRecord"; $Dict; "Name¶Director¶Launch"; 
   "SELECT Name, Director, Launch FROM Movie WHERE Launch > 2000 AND Launch < 2005"; Get(FileName)) ]
Set Variable [ $text ; Value: MBS( "Dictionary.ToText"; $Dict  ) ]
Show Custom Dialog [ "Dictionary" ; $text ]

To output the dictionary as text, we then use the Dictionary.ToText function and pass on the reference. The result looks like this:

Now let's try this with the Dictionary.AddSQLRecords function.

Set Variable [ $Dict ; Value: MBS("Dictionary.Create") ]
Set Variable [ $r ; Value: MBS( "Dictionary.AddSQLRecords"; $Dict; "Name¶Director¶Launch";
   "SELECT Name, Director, Launch FROM Movie WHERE Launch > 2000 AND Launch < 2005"; Get(FileName)) ]
Set Variable [ $text ; Value: MBS( "Dictionary.ToText"; $Dict  ) ]
Show Custom Dialog [ "Dictionary" ; $text ]

We can see that this looks much more confusing for us, so what can we do? Instead of text, we can display the content as JSON, for example. We use the Dictionary.ToJSON function for this and pass the reference again.

Set Variable [ $Dict ; Value: MBS("Dictionary.Create") ]
Set Variable [ $r ; Value: MBS( "Dictionary.AddSQLRecords"; $Dict; "Name¶Director¶Launch";
   "SELECT Name, Director, Launch FROM Movie WHERE Launch > 2000 AND Launch < 2005"; Get(FileName)) ]
Set Variable [ $text ; Value: MBS( "Dictionary.ToJSON"; $Dict ) ]
Show Custom Dialog [ "Dictionary" ; $text ]

We see that the values belonging to a key are listed. But how do we now query the values for a single key? For this, we have the Dictionary.ValueForKey function. We again specify the reference as a parameter and then specify the name of the key.

…
Set Variable [ $text ; Value: MBS( "Dictionary.ValueForKey";$Dict; "Director" ) ]
…

We want to get the directors back. This function provides us with a list of all directors in the dictionary.

I hope you enjoyed this door as well, and we will see again tomorrow to open the last door together.