MonkeybreadSoftware Posted December 5 Posted December 5 (edited) Today we start with the first door of this year's MBS Advent calendar. This year, we have come up with a themed Advent calendar for you. Over the next 24 doors, you will learn how to work with SQL within a FileMaker database using MBS functions. So let's start today with the question: What is SQL in general, and what can I use it for? SQL stands for Structured Query Language and refers to a language that can be used to query, modify, create, and delete data in a relational database. SQL can be used to work with a wide variety of databases, including MySQL, MariaDB, and PostgreSQL. SQL can also be used in FileMaker. On the one hand, it allows you to access other databases (e.g., with the Execute SQL script step or with the SQL component in MBS), thus providing a connection between FileMaker and the external database and making communication at all possible. But you can also use SQL internally in FileMaker, for example, to make queries to the database. To do this, you can use either FileMaker's own ExecuteSQL function or the MBS functions from the FMSQL component. We would like to focus on this part in this Advent calendar. Even though FileMaker already offers many built-in options for searching, evaluating, and linking data, SQL can be an extremely flexible addition to a FileMaker database. SQL allows you to access data in FileMaker directly and independently of the layout. This means that you can retrieve information spread across multiple tables without having to create additional relationships in the relationship graph or layouts. You can define complex queries that combine, filter, or group data from different tables, for example. For example, you can use a single SQL query to determine how many orders a customer has placed in the current year—something that would often only be possible in FileMaker itself with multiple scripts, loops, or auxiliary relationships. When working with FileMaker's own function, we can send SELECT queries to the database, which we will examine in more detail in the next section. SELECT queries allow you to retrieve information, similar to a search query. However, instead of a selection of records, you receive text that separates the individual pieces of information with predefined delimiters. The MBS plugin has a total of 65 functions in the FMSQL component to provide you with additional support when working with SQL, so that you can, for example, create, update, or delete records and handle errors even easily. In the next 23 days, I would like to dive into this topic with you so that you can then take full advantage of all the possibilities SQL has to offer. Before we take a look at what MBS can do with SQL, let's take a look at FileMaker's own functions and get started with the SQL language. In today's example, we are working with a table listing Christmas movies. It is called Movie and has the following fields: Name, Director, Year and Generated_Sales. The first queries will be run on this table in a moment. Important terms in the SQL language are SELECT FROM and WHERE. These are based on the English language, making it relatively easy to understand what a statement does. If we want to get the data from a specific field, we can write SELECT followed by the name of the field from which we want the data. SQL is layout-independent and does not automatically know from which table we want the data; we have to specify this in the FROM part. So we write FROM and then the table name. If we want to use SQL to query all movie names that are available in the Movie table, the query looks like this: SELECT Name FROM Movie We can use this query right away in the ExecuteSQL function in FileMaker. We should not mix up this function with the Execute SQL script step, because that one isn't meant for working on our FileMaker database, but for accessing an external database. Let's now turn to the ExecuteSQL function and take a look at its structure. ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } ) We see several parameters that need to be filled in. First, we have the parameter for the SQL statement, where we can enter the SQL expression we have already determined. Then we have the field separators. We can not only retrieve data from one field, as in this example, but also from several fields. These fields must then be separated from each other in the result text. We can choose this separator here; in our example, we use a space. However, there is not only a field separator, but also a separator between the individual data records. In our example, we choose a line break to separate the data records from each other. Optionally, you can also work with arguments in the function. We will come to these later. If we now insert the SQL query and other parameters into the function, it looks like this: Set Variable [ $Text ; Value: ExecuteSQL ( "SELECT Name FROM Movie" ; " " ; "¶" ) ] The result we get is text that we can then reuse, e.g., as shown here in a dialog box for output. We have already mentioned that we can return not just one field in the result, but several. If you do not want to select specific fields, you can also return all fields. To do this, you do not need to write all field names one after the other; instead, you can place an * after SELECT. Set Variable [ $Text ; Value: ExecuteSQL ( "SELECT * FROM Movie" ; ", " ; "¶" ) ] This time, we have used a comma as the field separator. As you can see, the output also displays fields that are not on the layout. This behavior once again highlights the independence from the layout. If we want data from multiple fields, we must separate them with a comma in the SELECT part. In this example, we have the name of the movie and the director. This time, the fields are separated by a hyphen. Set Variable [ $Text ; Value: ExecuteSQL( "SELECT Name, Director FROM Movie" ; "-" ; "¶" ) ] Let's try this with the name and year: Set Variable [ $Text ; Value: ExecuteSQL( "SELECT Name, Year FROM Movie" ; "-" ; "¶" ) ] Now it gets strange, because we get a question mark back instead of the movies with the corresponding years. The question mark is displayed when the function has not produced a reasonable result. There was an error. However, we cannot determine where the error occurred with this function. For this, we need the ExecuteSQLe function, which returns an error text in addition to the question mark in case of errors. The parameters of the function are the same. Set Variable [ $Text ; Value: ExecuteSQLe( "SELECT Name, Year FROM Movie" ; "-" ; "¶" ) ] Based on the error message, we can now see that the problem lies with the field name Year, as this is the same name as a FileMaker function and this causes a conflict. We can easily solve the problem by changing the field name and repeating the query with the changed field name. In SQL, we not only want to retrieve data on all records for different fields, but as we already indicated yesterday, data can also be filtered, and we can use this to formulate searches in our database in SQL style. An important key to this is the WHERE clause. Here, we can specify what our data must have in common in order for it to be included in the result set. For example, we can search for all films made by a specific director. To do this, we first specify the fields we want to display in the SELECT part, as we did before. In the FROM part, we then specify the table name from which the data comes. Now comes the new part, the WHERE part. Here, we query the director named Chris Columbus. Since this is text, we enclose the name in single quotation marks. The SQL statement then looks like this: SELECT Name, Launch FROM Movie WHERE Director = 'Chris Columbus' However, we can not only search for equal values, but also for values that are smaller or larger. For example, we can list all films made after 2015 by placing Launch>2015 in the WHERE section. Now, it may happen that we get a question mark as a return value in the output. Why is that? If we have defined our Launch field as text, the text cannot be compared with greater than or less than. For this reason, we must ensure that the field has the correct data type. Then our query will work. As in the IF statements in FileMaker, we can also specify conditions with AND, OR, and NOT here. This allows us to link multiple conditions for our data and define more specific search queries. In the next query, we want all movies directed by Chris Columbus, plus movies released in 2000 that were not directed by Greg Beeman. Such a query would look like this: Set Variable [ $Text ; Value: ExecuteSQL( „SELECT Name, Launch FROM Movie WHERE Director = 'Chris Columbus' OR Launch=2000 AND NOT Director='Greg Beeman'" ; "-" ; "¶" ) ] We already mentioned that FileMaker has arguments. These arguments are a bit like placeholders for values that we want to use in our SQL statement. This allows us to specify the name of the director as an argument. In the query text, we then insert a question mark instead of the value. It might look like this: Set Variable [ $Text ; Value: ExecuteSQL( "SELECT Name, Launch FROM Movie WHERE Director = ?" ; "-" ; "¶";"Chris Columbus" ) ] The output is exactly the same. But on the one hand, we have made the query more maintainable and can save ourselves the typing for concatenating values if the specified value is a variable. But probably the most important reason for using these arguments is that we have more security because we can avoid SQL injection. Let's assume we want to give the user free input and define our query as follows: Set Variable [ $Text ; Value: ExecuteSQL( "SELECT Name, Launch FROM Movie WHERE Director ='" & $Test&"'" ; "-" ; "¶" ) ] The variable Test contains what we got back from the user input. Ideally, this is the name of a director. But and this is the crux of the matter — theoretically, the user could also enter SQL commands and thus retrieve information that they should not have access to. For example, if the user enters the following: Chris Columbus' OR NOT Director='Chris Columbus Set Variable [ $Test ; Value: "Chris Columbus' OR NOT Director='Chris Columbus" ] Set Variable [ $Text ; Value: ExecuteSQL( "SELECT Name, Launch FROM Movie WHERE Director ='" & $Test & "'"; "-" ; "¶" ) ] Then this is an input that is accepted, since we have also set the appropriate quotation marks, and all data is then displayed because the query is correct for all data records. Of course, we want to avoid this, as it could cause much more potential damage. And for this purpose, we have our arguments that make the whole thing a prepared statement, which we read as formatted text and not as an instruction. Set Variable [ $Text ; Value: ExecuteSQL( "SELECT Name, Launch FROM Movie WHERE Director = ?" ; "-" ; "¶";"$Test" ) ] That brings us to the end of today's door, and I look forward to welcoming you back tomorrow. Edited December 5 by MonkeybreadSoftware
MonkeybreadSoftware Posted Saturday at 03:19 PM Author Posted Saturday at 03:19 PM (edited) Today is the third day. Yesterday, we learned what we can do with FileMaker's internal functions. Today, we want to look at an MBS function that is very similar in structure: the FM.ExecuteFileSQLfunction. Let's take a look at our query for all movies directed by Chris Columbus, plus movies released in 2000 that were not directed by Greg Beeman. We remember that this was the query: SELECT Name, Launch FROM Movie WHERE Director = 'Chris Columbus' OR Launch=2000 AND NOT Director='Greg Beeman' We now want to execute this query in the MBS function FM.ExecuteFileSQL. This function requires various parameters. First, we enter the file name, which this function should access. In our case, this is the same file in which we are executing the script, so we can specify Get(FileName) here. Then comes the SQL statement. These are the parameters that must always be specified for the MBS function. Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName); "SELECT Name, Launch FROM Movie WHERE Director = 'Chris Columbus' OR Launch=2000 AND NOT Director='Greg Beeman'" ) ] Optionally, we can also specify the field separators and line separators in this function. Prepared statements are also possible here, and at the end of the parameter chain, you specify the values to be inserted into the query instead of the question marks, separated by semicolons. Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName); "SELECT Name, Launch FROM Movie WHERE Director=? OR Launch=? AND NOT Director=?";"-" ; "¶"; "Chris Columbus"; 2000; "Greg Beeman" ) ] But if the function does the same thing as the FileMaker function, why do I need it at all and not just use the existing one? If you only want to run a few SELECT FROM WHERE SQL queries, the FileMaker function is perfectly sufficient. However, the MBS function can not only query values from the database, but also use SQL expressions that change the data structure. For example, we can insert new records into the database, update them or delete a specific record. We will learn about this and much more in the coming days. It is also possible to delete an entire table, meaning not just all entries but the entire structure. To do this, use DROP TABLE TableName Always exercise extreme caution when performing deletion operations before dismantling your database. With DROP Table, only the table is deleted, not the associated layouts. I hope to see you again tomorrow to continue exploring the possibilities of SQL Yesterday, we learned about an SQL function from MBS. Just like FileMaker's own function, it returned text that we could then work with. This time, we want to take a look at the FM.SQL.Executefunction. Instead of returning text output, this function creates a reference. A reference is a reference to the working memory in which the result of the function is temporarily stored. This allows you to use the result returned by the SQL query with other cool MBS functions, which you will learn about later in this calendar. The advantage of having a reference instead of text is that the text does not have to be reloaded into the working memory every time we want to work with the result. The result with the reference is simply stored in the working memory, which saves us a lot of time when dealing with large amounts of data. This also gives us the option of running through the result (in another door) or selecting a different output type. We will output the result as text as well as a matrix. The FM.SQL.Text function is available for output as text. Let's take a look at such a query on our database. Here, we want to search for all movies made before the year 2000. In the FM.SQL.Execute function, we first specify the database on which we want to perform our operations.If we leave this parameter blank, we do not limit the query to one database. Instead, we keep the option open for multiple databases. In our case, it is clear which one we want, and we specify it. Then the SQL statement follows. The result of this function, our reference, is then stored in our variable $SQLRes. We then use this reference as the first parameter in our next function, FM.SQL.Text. Then we can determine the range of data to be displayed. We have parameters for the first and last row and the first and last column. For now it is sufficient to leave the parameters empty, because then the entire result set will be displayed. Two further parameters then stand for the row separator and field separator. Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); "SELECT Name, Launch FROM Movie WHERE Launch<2000" ) ] Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes;"";"";"";"";"¶¶";"| - |") ] The result looks like this: There is also a special feature to note for line and field separators. If you want to have line and field separators that are longer than one character, you cannot use the FM.ExecuteFileSQL function from yesterday's door, because only one character is allowed here. If you enter multiple characters, only the first character will be used and the rest will be ignored. Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName); "SELECT Name, Launch FROM Movie WHERE Launch<2000"; "| - /";"¶¶" ) ] If you need multiple characters, you should also select the FM.SQL.Execute function in combination with the FM.SQL.Text function. As mentioned above, the result can be returned not only as text, but also as a matrix. This matrix can then be used with the matrix functions of the MBS FileMaker Plugin. With the FM.SQL.ToMatrixfunction, we convert an SQL reference into a matrix reference. This allows us to first determine the result as a matrix reference and then return an HTML to the result, which we can then display in a web viewer. Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); "SELECT Name, Launch FROM Movie WHERE Launch<2000" ) ] Set Variable [ $Matrix ; Value: MBS("FM.SQL.ToMatrix"; $SQLRes) ] Set Variable [ $HTML ; Value: MBS("Matrix.HTML"; $Matrix ) ] Go to Layout [ “WebViewer” (Christmas2025 Copy) ; Animation: None ] Set Web Viewer [ Object Name: "web" ; URL: "data:text/html;charset=utf-8," & $html ] There's one thing we shouldn't forget when working with references. When we don't need the reference anymore, we have to release it so we don't overfill our memory. In the SQL area, we have two suitable functions for this: FM.SQL.Release and FM.SQL.ReleaseAll. With the FM.SQL.Release function, we can release a specific reference, which we then specify as a parameter. FM.SQL.ReleaseAllreleases all SQL references in memory at the same time. Since you also have to work with references in the matrix, you must ensure that the references are released here as well. Set Variable [ $r ; Value: MBS("Matrix.ReleaseAll") ] Set Variable [ $r ; Value: MBS("FM.SQL.Release"; $SQLRes) ] That brings us to the end for today. See you tomorrow for the fifth door. Edited Saturday at 03:22 PM by MonkeybreadSoftware
MonkeybreadSoftware Posted Sunday at 10:23 AM Author Posted Sunday at 10:23 AM 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.
MonkeybreadSoftware Posted Monday at 02:36 PM Author Posted Monday at 02:36 PM Today, I would like to introduce you to an important topic in SQL: Joins. In FileMaker, we have relationships between the tables we work on, allowing us to relate data records to each other. We do not have these direct, fixed connections between two tables in SQL. Here, we have to create the relationship in the query, and we do that with Joins. There are different types of Joins, which we will now learn about using small examples so that we can use them later in your actual database. To do this, we will use a small section of our Movies table, which contains 5 movies for the example. We will simplify the primary key as a sequential number, as this is of course different in real life. Primarykey Name Director Launch Generated_Sale 1 Die Hard John McTiernan 1988 141,603,197 2 Christmas Vacation Jeremiah S. Chechik 1989 71,319,526 3 Love Actually Richard Curtis 2003 250,200,000 4 The Polar Express Robert Zemeckis 2004 315,249,768 5 Crown for Christmas Alex Zamm 2015 - We also have a table called Roles2, which contains the roles in the films. Name Actor Movie John McClane Bruce Willis 1 Hans Gruber Alan Rickman 1 Karl Alexander Godunov 1 Clark Griswold Chevy Chase 2 Ellen Griswold Beverly D'Angelo 2 Cousin Eddie Randy Quaid 2 David Hugh Grant 3 Karen Emma Thompson 3 Harry Alan Rickman 3 Allie Evans Danica McKellar 5 King Maximillian Rupert Penry-Jones 5 Princess Theodora Ellie Botterill 5 (INNER) JOIN First, we have the INNER JOIN, which is the join I use the most. The INNER JOIN connects two tables so that only records that match in both tables are displayed. It therefore only shows common hits – anything that has no connection is hidden. In our case, this means that only movies and actors with matching primary keys and names will be displayed. If we apply an INNER JOIN, often referred to as just JOIN, to these tables with primary key = Movie, it looks like this: SELECT Rols2.Movie, Movies2.Name, Movies2.Director, Movies2.Launch, Rols2.Actor FROM Movies2 JOIN Rols2 ON Movies2.PrimaryKey = Rols2.Movie First, we have our SELECT part again, in which we specify the fields. This time, we want fields from both tables, which is why we also put the table name in front of it, separated by a dot. We specify all the fields from which we want the data. Then we come to the FROM part. Here, we first specify table A, then the word JOIN, and then table B. After we have specified the tables, we then add the keyword ON. After this, we can specify the connection over which the result will be created. In our example, we now want to match the two tables using the Primary Key and Movie fields if the values in these fields are the same. FM.SQL.Execute" ; Get(FileName); "SELECT Rols2.Movie, Movies2.Name, Movies2.Director, Movies2.Launch, Rols2.Actor FROM Movies2 JOIN Rols2 ON Movies2.PrimaryKey = Rols2.Movie" ) ] Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ] Show Custom Dialog [ "Result" ; $r ] Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ] However, we not only have INNER JOIN, but also other types of JOIN. LEFT JOIN Let's move on to LEFT JOIN. A LEFT JOIN is a special form of joining two tables that guarantees that all records from the left table appear completely in the result, regardless of whether there are matching entries in the right table. You can think of it this way: The left table forms the fixed point, and for each of its rows, the database tries to find the corresponding information from the right table. If a matching record exists in the right table, both are combined and output. However, if there is no corresponding entry, the query still includes the row from the left table in the result. In this case, the fields in the right table are simply filled with NULL. Nice to know: In this context, NULL means that a specific field does not contain a value - not "0", not "empty", not "unknown", but truly no existing data entry. LEFT JOINs are useful when the left table is the focal point of the evaluation. A classic example would be a list of all available products (left table) that needs to be linked to the sales for the current day (right table). Even if a product has not been sold today, it still appears in the result because it is in the left table. In this way, missing information, anomalies, or gaps can be clearly identified. In our example, we now want the movies in the left table and the roles in the right table. These are also linked via the primary key of Movies2 to the Movies field in the Rols2 table. Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); "SELECT Movies2.Name, Movies2.Director, Movies2.Launch, Movies2.PrimaryKey, Rols2.Movie, Rols2.Name, Rols2.Actor FROM Movies2 LEFT JOIN Rols2 ON Movies2.PrimaryKey = Rols2.Movie" ) ] Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ] Show Custom Dialog [ "Result" ; $r ] Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ] This is what the result looks like: Unsupported joins in FileMaker The FileMaker engine for SQL, that the plugin also works with, cannot handle RIGHT JOIN and FULL OUTER JOIN. Therefore, you cannot use them in conjunction with FileMaker. However, in case you encounter them, we will explain both here. RIGHT JOIN is very similar to LEFT JOIN, except that instead of taking all records from the left table, we take them from the right - regardless of whether a corresponding record exists in the left table or not, the fields in the left table that correspond to a record that has no match in the left table are filled with NULL. Another join type is the FULL OUTER JOIN. This includes all records from the left and right table. If there is no match for a record in the opposing table, the records are still transferred to the result set and we also fill them with NULL. CROSS JOIN I would also like to mention CROSS JOIN. This join does not use a specific criterion or field, but connects all records in the right table with all records in the left table, regardless of whether the records are matching. So if table A has x entries and table B has y entries, we end up with a result set containing x*y records. We also talk about forming the Cartesian product of these two tables. If we then want to narrow down the records, we can use a WHERE clause. A CROSS JOIN can have the following structure: SELECT * FROM tabel 1 CROSS JOIN tabel 2 However, another notation is also possible, because we also form the Cartesian product when we combine two tables in the FROM section by listing them one after the other. SELECT * FROM tabel 1 ,tabel 2 Today, I would like to give you a little task, which we will solve tomorrow in the door. How would our query SELECT Rols2.Movie, Movies2.Name, Movies2.Director, Movies2.Launch, Rols2.Actor FROM Movies2 JOIN Rols2 ON Movies2.PrimaryKey = Rols2.Movie look like if we wanted to express it with a CROSS JOIN? Have fun trying it out, and I hope to see you again tomorrow.
MonkeybreadSoftware Posted Tuesday at 02:29 PM Author Posted Tuesday at 02:29 PM 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.
MonkeybreadSoftware Posted 47 minutes ago Author Posted 47 minutes ago In Door 2, we saw that FileMaker itself has a function called ExecuteSQLe that can output error messages when errors occur in the process. If we have an error in the SQL query with the MBS functions, then instead of a result or a reference number, we receive an error message from the function that went wrong. Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName); "SELECT Nme, Launch FROM Movie WHERE Director=? OR Launch=? AND NOT Director=?"; "-" ; "¶"; "Chris Columbus"; 2000; "Greg Beeman" ) ] In some cases, it would be nice to have a function that would allow you to simply query the error without having to look at the first characters of the output. The plugin provides two functions that can help you with this: FM.ExecuteSQL.LastError and FM.ExecuteSQL.LastErrorMessage. The FM.ExecuteSQL.LastError function gives us an error code for the last error in the SQL. The error code always refers to the last SQL query called. If everything went well with the SQL query, we get the error code 0 back. Otherwise, we receive the corresponding error code, and there are several of these. This error code can also be very useful if you need to make a case distinction, because if it is 0, you know that there was no SQL error in the function and can continue working with the result, or if an error occurred, you can terminate the script, for example. Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName); "SELECT Nme, Launch FROM Movie WHERE Director=? OR Launch=? AND NOT Director=?"; "-" ; "¶"; "Chris Columbus"; 2000; "Greg Beeman" ) ] If [ MBS("FM.ExecuteSQL.LastError")=0 ] # Do something Else Exit Script [ Text Result: ] End If The error code does not provide much assistance in resolving the error, or we need to know what this code is stand for. For this reason, there is the FM.ExecuteSQL.LastErrorMessage function, which displays the corresponding error message for the last SQL query. In this example, the text clearly indicates what is incorrect: Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName); "SELECT Nme, Launch FROM Movie WHERE Director=? OR Launch=? AND NOT Director=?"; "-" ; "¶"; "Chris Columbus"; 2000; "Greg Beeman" ) ] Set Variable [ $errorcode ; Value: MBS("FM.ExecuteSQL.LastError") ] Set Variable [ $errortext ; Value: MBS("FM.ExecuteSQL.LastErrorMessage") ] Show Custom Dialog [ "Error" ; $errorcode & "-" & $errortext ] We spelled the name of the field incorrectly. If we correct this, our SQL will be correct again and we will not get an error message. That's it for today on error handling. I hope you enjoyed it and we'll see each other again tomorrow.
Recommended Posts