SQL Database Object v2.1 – MMF Extension

© 2004-2015 J. St. Jean

Overview

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.

Extension Usage

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.

Basic Operation

Using the SQL Database object is simple:

  1. Open a database file at the start of your application: SQLDB.OpenFile(“filename.db”)
  2. Read/write data or perform other operations using standard SQL statements: SQLDB.RunSQL(“SELECT * FROM myTable”)
  3. Access data in the result set: MyString = SQLDB.Data(1,1)
  4. Close the database file at the end of your application: SQLDB.CloseFile

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.

 

 

 

Use of Multiple SQL Database Objects

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

Extension Reference

This section provides a full API reference for the SQL Database Object v1.1.

Actions

Open File (filename)

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.

Open File from Selector (filename or select)

This action open a new database optionally using the MMF file selection box. Otherwise, this action is identical to OpenFile.

Close File

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.

Run SQL (SQLString)

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.

Set Current Row

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.

Set Current Column

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.

Clear Last Error

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.        

Conditions

OnSQLError

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.

DBOpen

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.

Expressions

Get Num Rows

This expression returns the current number of rows in the result set. If there is no result set, a value of 0 is returned.           

Get Num Cols

This expression returns the current number of columns in the result set. If there is no result set, a value of 0 is returned.          

Get Current Row Index

This expression returns the row index in the result set. If there is no result set, a value of 0 is returned.    

Get Current Col Index

This expression returns the current column index in the result set. If there is no result set, a value of 0 is returned.           

Get Data

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).

Get Data from Row, Col (RowIndex,ColIndex)

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.

Get ColumnName

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.

Get Data from Col Name (ColName)

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.

Get Data from Row, Col Name (RowIndex,ColName)

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.

Quote (inString)

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.

Err

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.

ErrMsg

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.


Using the SQL Database Object Effectively in MMF

This section contains some usage info and guidelines for using the SQL Database Object in your MMF applications:

Escaping of single quotes in SQL statements

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.

 

Using result sets

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.

Invalid Conditions

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.

Large SQL Statements

When you need to construct a large SQL statement, you must build it in parts. The MMF expression editor limits the size of the strings you can build in a single expression. This can be accomplished by using statements such as the following:

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.

Loading External Data Sets

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.

Database Size and Performance

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.


Working with SQLite Databases

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.

Useful links:

SQL Course

This link leads to a short course that is helpful to learn SQL basics

http://www.sqlcourse.com/

SQLite Control Center

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

DB Designer

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/

SQLite Tools

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