Interested in improving this site? Please check the To Do page.
mysql.compileQuery
Compile a MySQL query, preparing it for execution.
Syntax
mysql.compileQuery(dbid, query)
Params
A database connection ID and a query string consisting of an SQL statement.
Returns
A query id or 0 if no rows were returned.
Examples
on querysample () {
local (id, result, queryID, sql);
result = mysql.init();
id = mysql.connect("localhost", "root", "frontier", "frontier", 3306);
queryID = mysql.compileQuery(id, "select * from names");
dialog.alert("Selected row count: " + mysql.getSelectedRowCount(queryID));
dialog.alert("Column count: " + mysql.getColumnCount(id));
dialog.alert("Query warnings: " + mysql.getQueryWarningCount(id));
dialog.alert("Query info: " + mysql.getQueryInfo(id));
loop {
result = mysql.getRow(queryID);
if result == 0 {
break};
dialog.alert(result)};
mysql.clearQuery(queryID);
result = mysql.close(id);
result = mysql.end();
return result}
on easyQuery(dbID, queryString, maxRows=0) {
local (compiledQuery, dataset = {}, row, rowCount = 0);
on isNum(s) {
for i = 1 to string.length(s) {
ch = string.mid(s, i, 1);
if (ch < '0') or (ch > '9') {
return false}};
return true};
if not isNum(maxRows) {
scriptError("mysql.easyQuery requires a numeric value for maxRows.")};
compiledQuery = mysql.compileQuery(dbID, queryString); // this will scriptError on its own if it fails
if compiledQuery > 0 {
loop {
result = mysql.getRow(compiledQuery);
if result == 0 { // MySQL's code for no more rows
break};
++rowCount;
if (maxRows > 0) and (rowCount > maxRows) {
break};
dataset[rowCount] = result};
mysql.clearQuery(compiledQuery)}
else {
return {}};
return dataset}
Notes
Don't forget to put the mysql.compileQuery call inside a try statement. If you query with a bad SQL statement, Frontier will return a scriptError. It's also probably a good idea to check the various query information functions mysql.getSelectedRowCount, mysql.getColumnCount, mysql.getQueryWarningCount, and mysql.getQueryInfo.
It's important to test the results of mysql.compileQuery for a 0 value. There are a number of other functions that rely on a queryID and if the queryID is 0, they will not function correctly or may crash. In particular, if mysql.compileQuery returns a 0, you do not need to execute a mysql.clearQuery command.
There's a lot going on inside mysql.compileQuery. Within Frontier, we're calling two key MySQL API calls: mysql_query and mysql_store_result. 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. A lot of good information is at the bottom of this page.
It should also be noted that MySQL supports multiple SQL statements in SQL queries. The current Frontier implementation does not. For each query, Frontier's wrapper to SQL will simply query based on the first statement in the SQL provided.
