sqlite.compileQuery

Compile an SQLite query, preparing it for execution.

sqlite.compileQuery(dbID, query)

dbID is the database ID returned by sqlite.open. query is a string containing a full SQL query.

A value that corresponds to a query ID. You’ll use this ID in other query-related functions.

on createTable () {
	local (db, query, compiledQuery);
	db = sqlite.open("C:\\temp\\testdatabase.db3");
	bundle { // the create table query
		query = "CREATE TABLE [verbs] (" + cr;
		query = query + "  [ID] INTEGER," + cr;
		query = query + "  [category] TEXT COLLATE NOCASE," + cr;
		query = query + "  [verbname] TEXT COLLATE NOCASE)" + cr};
	compiledQuery = sqlite.compileQuery(db, query);
	result = sqlite.stepQuery(compiledQuery);
	result = sqlite.clearQuery(compiledQuery);
	sqlite.close(db)}

This next example shows how to check for an error. Notice the use of the try statement. If sqlite.compileQuery fails, tryError (a Frontier reserved word) will contain the sqlite.compileQuery error string, derived from the sqlite3_prepare function it calls. This routine also demonstrates how you can get back the queryID as a Frontier address and either true or the error string once executed.

on compileQueryToAddress (dbID, queryAddr, queryString) {
	local (queryID);
	try {
		queryID = sqlite.compileQuery(dbID, queryString)}
	else {
		return tryError};
	queryAddr^ = queryID;
	return true}

The following example shows a complete query sequence, with error handling, using a local version of the short routine written above:

on testQueryError () {
	local (db, result, compiledQuery, rowcount=0, i, columnType, columnCount);
	local (columnName, value, columnTypeString, queryOK);
	on compileQueryToAddress (dbID, queryAddr, queryString) {
		local (queryID);
		try {
			queryID = sqlite.compileQuery(dbID, queryString)}
		else {
			return tryError};
		queryAddr^ = queryID;
		return true};
	db = sqlite.open("c:\\temp\\world.db3");
	queryOK = compileQueryToAddress(db, @compiledQuery, "select count(*) from city");
	if queryOK == true {
		result = sqlite.stepQuery(compiledQuery);
		columnCount = sqlite.getColumnCount(compiledQuery);
		dialog.alert(columnCount);
		for i = 1 to columnCount {
			result = sqlite.getColumn(compiledQuery, i);
			columnName = sqlite.getColumnName(compiledQuery, i);
			dialog.alert(i + ": name=" + columnName + " value=" + result )};
		result = sqlite.clearQuery(compiledQuery)}
	else {
		dialog.alert(queryOK)};
	result = sqlite.close(db)}

Be sure to check SQL As Understood By SQLite and SQL Features That SQLite Does Not Implement for hints on developing your SQL queries.

• sqlite.clearQuery
• sqlite.easyQuery
• sqlite.escapeQuotes
• sqlite.getRow
• sqlite.getRowColumnNames
• sqlite.resetQuery
• sqlite.stepQuery
• sqlite3_prepare API