Interested in improving this site? Please check the To Do page.

Table of Contents

Back to Verbs

MySQL Verbs

MySQL implementation notes

As of 07/22/2007, MySQL support available in the main Frontier release Frontier v10.1a15 and is available to Mac and Windows users. Special thanks to the guys for making the Mac version possible. – David Gewirtz

This section describes the Frontier-specific implementation of the MySQL client library. The following are some notes you should be aware of as you prepare to use MySQL within Frontier:

Frontier and MySQL

Frontier already supports limited MySQL access through the ODBC DLL. These verbs are unrelated to that DLL and implement the MySQL client library natively within Frontier. Unfortunately, we don't have access to the old ODBC DLL source for this open source Frontier Kernel project. We've also noticed that a bunch of ODBC DLL loads and unloads seem to cause Frontier to crash over time.

It just didn't seem like a production solution that we could fix or debug. So, rather than tack on the old DLL, we decided to reimplement MySQL natively.

Frontier accesses the MySQL server. You'll need to have access to a MySQL server for full functionality

The version of MySQL client embedded within the Frontier Kernel is 5.0, which, theoretically, means we only support access to MySQL 5.0.x. In practice, we've tested access to MySQL 4.x databases and have had some success.

Scripting Notes

Here's a useful description of how Frontier differs from MySQL: MySQL Explained to Frontier Users.

Here are some more scripting tips:

  • Frontier implements a “Frontierified” version of the MySQL client, designed initially to provide large data storage capabilities natively within Frontier. Not all of the MySQL client API has been implemented.
  • Function names have been “Frontierified” as well, to make them clearer for a Frontier developer. For example, the mysql_field_count function has been named mysql.getColumnCount in Frontier. The mysql_free_result function has been named mysql.clearQuery in Frontier, and so forth. This has also been done to indicate that Frontier calls to MySQL are not direct one-to-one analogs to the MySQL API. Some adjustment to the data handling and operation has been done within Frontier.
  • MySQL operates on a base-0 structure, meaning that the first item in a given sequence is always considered 0. By contrast, Frontier operates on a base-1 structure, meaning that the first element in a Frontier sequence is always a 1. The Frontier implementation of the MySQL client implements access as a base-1 structure. If you want the first column, you'll specify column 1, not 0. Internally, Frontier converts from its own base-1 to base-0. This can be confusing if you're reading MySQL documentation, so simply be aware that whenever MySQL talks about an offset, MySQL begins with 0 and Frontier with 1.
  • Many of the MySQL numeric types are much bigger than represented in Frontier. However, when running the mysql_fetchrow API function, MySQL returns field values as strings. So rather than coercing those values to numeric in the function calls (and possibly mucking with the data in baaaaad ways), we return string values for everything retrieved from a MySQL query. It'll be up to the Frontier scripter to do any coercion necessary.
  • MySQL supports multiple SQL statements in SQL queries. The current Frontier implementation does not. For each query, Frontier's implementation of the MySQL client will simply query based on the first statement in the SQL provided.
  • MySQL provides different ways to perform queries. As you might imagine, MySQL has a lot of depth in how it manages queries. In particular, it has two query result approaches: return the entire results table at once or return one line of the results table at once. The first uses the mysql_store_result API call and is the mechanism used within Frontier. While there are advantages to both, we use the store result approach because it gives us some more flexibility in certain areas. Read the MySQL API for more details.
  • In the Frontier implementation, you'll need to first compile a query, then step through your query results row-by-row, and then, finally, clear the query from memory. Obviously for SQL calls that only return one line of data or no data at all, you'll only need to step through one row of query results to be sure the query executes. Frontier does implement mysql.easyQuery, a script-based interface to the MySQL query engine that does everything in one call.
  • MySQL (and all SQL, for that matter) is very fussy about characters and character formatting. One utility function, mysql.escapeString has been provided to help with this, but you'll need to be very careful about how you send strings into the MySQL interface and how you process them when they come back out. Pay particular attention to single and double quotes, and to the Frontier escape character, the backslash (\).
  • Although MySQL has a series of Unicode-smart functions, the Frontier MySQL client implementation does not. Frontier's MySQL client implementation implements simple, seven-bit character values. Anything more may screw things up, so, again, use care.
  • The Frontier implementation of the MySQL client implementation requires you to do more management than is typical in Frontier. In particular, you'll need to keep track of database IDs and be sure to close them when you exit Frontier and you'll need to keep track of query IDs and be sure to clear them when you're done or exit Frontier. Frontier will not implicitly do any MySQL garbage collection.

Resources


Personal Tools