© 2004-2015 J. St. Jean
The SQL Database Object is a standalone MMF component that acts as a fast, powerful local data store for MMF application or game information. This LE edition is an initial release that is free for personal, professional, and commercial use. The SQL Database Object is substantially more useful and flexible than data files, INI files, or XML files because it supports powerful write, read, and query functions on diverse and potentially large data sets. A single database can store the contents of multiple data files or INI files and provides a much more powerful and flexible interface.
Unlike database connectors that rely on ODBC or other database APIs to connect to a separately installed database such as MS Access or mySQL, the SQL Database Object is itself a fully operational database and requires no other software. Adding powerful database capabilities to your application is as simple as loading the single sql.mfx file onto your machine and distributing the file with your applications.
A file on the file system acts as the persistent store (database) for the SQL Database Object. Different databases can be opened and used by different applications or even within the same application. Using a database is achieved by first opening the file, then issuing commands. All results are returned in a simple array result set structure and can be accessed by row and column index or by name.
SQL stands for Structured Query Language. SQL is a robust and easy to use command language to manipulate and search relational data (rows and columns of data). The SQL Database Object internally uses the open-source SQLite database engine (version 3.8.8), which supports a large subset of the full SQL command language. Some primary SQL functions supported by the SQL Database Object include INSERT, UPDATE, DELETE, COPY, CREATE, DROP, and of course SELECT. TRANSACTIONS are also supported. A detailed description of the SQL commands and query language supported by the SQL Database Object’s internal SQLite database engine can be found here.
This section covers the basics of using the SQL Database Object. Full details of the various operations you can perform using the SQL Database Object and their APIs are provided in a later section.
Using the SQL Database object is simple:
The SQL Database object retains the results of the last operation in an internal, two dimensional array. The contents of this array can be accessed using (x,y) numerical coordinates where x is the row, and y is the column. Columns in the current row can also be accessed by name. The object maintains the concept of a current row and current column, the contents on this cell in the result set can always be accessed without any additional parameters. The database can sort, compute, and compare data elements using types such as integers, floats, or string, however all result set data is always stored and returned as strings.
The object has been designed to support a common pattern of database usage where the database is opened once at the beginning of the application, then used multiple times throughout the application and in multiple different frames, and then the database is closed when the application closes.
Example
The following provides a detailed example of using the SQL Database Object and accessing its result set:
As an example, suppose we had a database opened, which contains a table People containing columns Name and Age, and the following five rows:
Name | Age
Alice | 43
Fred | 18
Bob | 28
Cindy | 21
Sandy | 56
We then submit a query against the database using the RunSQL() action, as follows:
RunSQL(“SELECT Name, Age FROM People WHERE Age>18 AND Age<50”)
The result data set from this query will be:
Name | Age
Alice | 43
Bob | 28
Cindy | 21
Following the execution of the query, we would have 3 rows and 3 columns of data in the result set (note: the column headings are not counted in the row count). The current row and column would each be a value of 1 upon completion of the query. We could then access this data using the following example expressions in our MMF Application:
Expression Returns
Data() “Alice” (value at location 1,1)
DataRowCol(2,2) “28” (value 2,2 – returned as a string)
DataRowColByName(3,“Name”) “Cindy” (value at location 3,1)
ColumnName() “Name” (column name of current column)
DataRowCol(0,2) “Age” (row 0 represents column names)
This exact example is also provided in the MMF sample application.
When multiple SQL Database Objects are used in a single frame, or in a single application, all such objects share a common database. This means that once a database is opened or closed on any SQL Database Object, all other object instances are equally effected. All SQL Database Objects share a single database file on the file system and this is handled safely.
This implementation decision was made because:
1) A single database can hold an arbitrary number of tables, therefore each database represents an open-ended data store. It is easier to open a single database once and use it throughout the application, then repeatedly open and close databases on each frame.
2) In cases where multiple databases are required, it is possible to either use the ATTACH command to link them, or just close one database and open another explicitly.
The practical effect of having one database shared by all object instances is that once you open the database the first time, early in your application, all later application code can use the database without any prior preparation. Any frame can execute SQL commands as required. This saves a significant amount of duplicate database opening and closing code thus simplifying your application.
Although each SQL Database Object shares a common database, each object instance retains its own result set. This means each object will hold an independent array of results from the previous SQL command that were issued through that object. This allows multiple SQL objects to be used to fetch and hold separate arrays in memory at the same time.
The basic architecture of multiple SQL Database Objects is shown in Figure 1.
Figure 1 – Architecture of Multiple SQL Database Objects
This section provides a full API reference for the SQL Database Object v1.1.
This action opens a new database. If the specified file does not exist, it is created. Note that all instances of the SQL Database Object in the application share the same database, so after this action is used, all SQL Database Objects will subsequently use this database. If a previous database is already open, that database is closed before opening the new one. Any active result set is cleared in the object that is the subject of the Open File call.
This action open a new database optionally using the MMF file selection box. Otherwise, this action is identical to OpenFile.
This action closes a currently open database. Note that all instances of the SQL Database Object in the application share the same database, so after this action is used, all SQL Database Objects will no longer have an active database.
This action executes the SQL Statement contained in the single string parameter. It is possible to execute multiple SQL statements by separating them with a semi-colon (though be aware MMF does not like to build large strings and may limit the string length). SQL Statements can be used to create, write, read, or update the database, or execute standalone expressions. A description of the SQL command language available for use with the SQL Database Object can be found here.
The output of the SQL statement execution is saved in memory in a result set held by the SQL Database Object. These results can be accessed using a number of different expressions that start with Get Data. The SQL Database Object maintains a current row and current column index, which is set to (1,1) following a new query. If there are no results, these indexes will be (0,0) and no data or column names can be obtained from the result set. Any cell in the result set can be obtained based on the current row and column, by specific row or column index, or by using the text column name within the result set.
If the SQL statement cannot be executed for some reason, the error code and error message will be saved and can be obtained using the Err and ErrMsg expressions. Also, following an error, the DBError condition will return true. This provides a convenient way to capture SQL statement errors.
This action sets the current row in the result set. If the requested row index is not available in the current result set because the index is out of range, the row index will be set to 1 instead. A value of 1 represents the first row of the result set, meaning the array is 1-based. If there is no result set, the column index will be set to zero.
This action sets the current column in the result set. If the requested column index is not available in the current result set because the index is out of range, the column index will be set to 1. A value of 1 represents the first column of the current row in the result set, meaning the array is 1-based. If there is no result set, the column index will be set to zero.
This action clears a previous error. An error is set by a failed SQL statement execution. If there is no error, this function performs no function.
This condition is set to true when the previous RunSQL statement failed. This is typically due to invalid syntax in the SQL string or if a database has not been opened. By trapping this true condition (and displaying the ErrMsg value in a dialog box) you can capture failed SQL statement executions effectively.
This condition is set to true when this object has an open database to operate on. If this condition is true for one SQL Database Object, it should be true for all objects because all instances in the same application share a common database.
This expression returns the current number of rows in the result set. If there is no result set, a value of 0 is returned.
This expression returns the current number of columns in the result set. If there is no result set, a value of 0 is returned.
This expression returns the row index in the result set. If there is no result set, a value of 0 is returned.
This expression returns the current column index in the result set. If there is no result set, a value of 0 is returned.
This expression returns the element of the result set at location indicated by the current row and column. The result data is always returned as a string. If the object does not currently have a result set available, the value of "SQLDB: No Data Present" will be returned. If the requested location in the result set is NULL, then this function will return no data (note, it will not return an empty string in this case. An empty string is only returned if the requested location in the result set actually contains an empty string).
This expression returns the data element of the result set at the specified row and column index. These indexes have a base of 1. Use of this function will not change the current row and current column indexes being held by the object. If one or more of the indexes exceed the valid index range, the highest available row or column will be returned instead. If the object does not currently have a result set available, the value of "SQLDB: No Data Present" will be returned. If a row index of zero is used, this function will return column name information.
This expression returns the column name of the current column. If the current column index is not valid (if it is zero or out of range of the current result set for some reason), no value will be returned. The column name comes from the column in the table that sourced the column in the result set. This column name can be overridden in the SQL statement to provide user-friendly column names.
This expression returns the data element of the result set at the current row index and specified column name. The appropriate column index will be looked up using the supplied column name, and result data at that location returned. This function is otherwise identical to DataRowCol.
This expression returns the data element of the result set at the specified row index and specified column name. The appropriate column index will be looked up using the supplied column name, the result data at that location returned. This function is otherwise identical to DataRowCol.
This function is a special processing function used to build SQL statements for later submission to the SQL Database Object. When a string contains a single quote character, this needs to be specially processed (“escaped”) prior to submission in an SQL statement. This expression will return a properly processed string that can be later submitted within an SQL statement. For example, if the string “Can’t” is to be submitted within an SQL statement, this string should be processed through this function first. The result of submitting the string “Can’t” to this function would be “Can’’t”. Note that the single quote has been repeated a second time, which is the required escape sequence.
This function returns the value of the previous database engine error, typically as a result of a failed SQL statement execution or when a database has not been opened. The value of Err and ErrMsg are cleared using the ClearErr action.
This function returns the text string of the previous database engine error, typically as a result of a failed SQL statement execution or when a database has not been opened. The value of Err and ErrMsg are cleared using the ClearErr action.
This section contains some usage info and guidelines for using the SQL Database Object in your MMF applications:
Single quotes which are intended to be stored in the database must be escaped. The escape sequence consists of a two single quotes in a row. Use the SQL Database Object Quote() function to convert a string that has single quotes (such as containing the word “can’t” to a properly escaped string prior to use in an SQL statement.
Single quotes should not be escaped when they form the bounds of a single string value in an SQL statement. This is done very frequently in SQL. Here are some examples:
Single quotes should not be escaped in this statement, and the single quote serves as a boundary marker for the Name field value:
“SELECT Name, Age FROM People WHERE Name='Joe Smith' AND Age<40”
Single quotes must be escaped in name “O'Henry” in this statement, as the intent is to actually insert a single quote into the database as a character as part of the name. However, the quotes around the name should not be escaped:
“INSERT INTO People (Name, Age) VALUES ('Brian O''Henry', 27)”
You must always be careful to use single quotes around your string values when you use them with the SQL Database Object. When storing or using user-entered strings, the Quote() function of the SQL Database Object can be used to accomplish proper escaping as in this example code:
SQLDB.RunSQL(“INSERT INTO People (Name, Age) VALUES ('” +
SQLDB.Quote(UserName)+ “', 21)”
Note how the single quotes appear before and after the insertion of the UserName string variable, and the UserName string variable is processed using the Quote() function prior to use in the SQL statement. This example will properly handle the case where user includes single quotes in their name. Think of this with names, game names (“Jim’s Game”) and other cases where the user data may properly contain single quotes.
Every subsequent SQL statement you execute through an object will change the current result set of the object, even if the new statement does not return any results. Therefore if you use an instance of the SQL Database Object to store the results of a query during your frame, do not submit any further SQL statements to that SQL Database Object instance until you are done with the data.
If the SQL statement provided to RunSQL is bad, such as do to a syntax error, the condition DBError is set. You can trap this and fire a dialog box or similar. You can obtain an error code and error message by using the Err and ErrMsg expressions, and you will need to call ClearErr to reset the object.
Certain ‘non-serious’ issues like requesting a column but providing an invalid column name, requesting data when none is present, or executing a function when no database is open will return special output strings. These strings have the form “SQLDB: message” and will be returned as the expression output string.
If you call any functions and provide row or column indexes that are outside the valid range, the component will restrict indexes to the valid range and return the closest result.
SQL = “SELECT Name, Age, Town, Occupation FROM CitizensTable”
SQL = SQL + “ WHERE State='” + StateString + “'”
SQL = SQL + “ AND Country='” + CountryString + “'”
SQL = SQL + “ AND Age>=” + MinAgeValue
Note how the single quotes are used before and after each string, but not before after the number MinAgeValue. Also note how a space is inserted at the beginning of each successive line. If this is forgotten, a syntax error will usually result. As pointed out earlier, it would be safer to use the Quote() function to pre-process the StateString and CountryString in the above example.
The SQL COPY command can be used to load data from external text files with delimiters such as commas or tabs. This function is documented in the SQL language page here. The number of columns in each row of the input file must exactly match the number of columns in the destination table. Data is appended to the destination table. Note that the default delimiter is a tab, so change it to a comma if you use comma separated value (CSV) files.
The internal database engine, SQLite (version 2.8.15), limits the amount of data in one row to 1 megabyte. The database itself has no practical size limit (according to the SQLite documentation, maximum database size is in the terabyte region - though I think Windows would stop you long before then…).
If you need to load a large amount of data, try to use external files whenever possible and use the COPY command. Making many inserts in quick succession will be slow.
Unless already in a transaction, each SQL statement has a
new transaction started for it. This is very expensive, since it requires
reopening, writing to, and closing the journal file for each statement. This
can be avoided by wrapping sequences of SQL statements with BEGIN TRANSACTION;
and END TRANSACTION;
statements. This
speedup is also obtained for statements which don't alter the database.
A detailed description of the SQL commands and query language supported by the SQL Database Object’s internal SQLite database engine can be found here. A good understanding of this information will help you to write fast, reliable, and effective applications with the SQL Database Object.
You may also want to use certain 3rd party tools to define, test, introspect, or develop underlying databases that will be queried through the SQL Database Object. The following set of useful links represent information or tools that may aid your database design and development efforts.
Note that these 3rd party tools can create and modify existing databases, therefore it is in theory possible that an advanced user could obtain one of these tools, and use it to modify data that supports your application. The SQL Database Object v1.1 does not include encryption.
This link leads to a short course that is helpful to learn SQL basics
This link leads to a free GUI application that lets you operate on SQLite databases. You can test your SQL code or configure a database before using it with the SQL Database Object.
http://bobmanc.home.comcast.net/sqlitecc.html
A free tool that allows you to define relational databases in a visual tool, and export the scripts required to create them. These scripts can be loaded into your MMF application and executed, or you can pre-create the core database outside of MMF. This is a very helpful tool, just be aware that DB Designer allows some features not supported by the internal SQLite database engine used by the SQL Database Object.
http://www.fabforce.net/dbdesigner4/
A useful command line tool for accessing and modifying SQLite databases. Very helpful for executing scripts previously output from DBDesigner.
http://www.sqlite.org/sqlite-2_8_16.zip