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

Table of Contents

Back to MySQL Verbs

mysql.getRow

Retrieve an entire row from an MySQL query.

Syntax

mysql.getRow(queryid)

Params

A query ID

Returns

0 if no more rows, or a Frontier list containing data

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

MySQL might also return a 0 if there’s an error. It’s important to check for an error if a zero value is returned to differentiate between end of dataset and last row.

The MySQL fetch_row function is interesting, in that it looks like each field returned is returned as a string. This is great for Frontier, since Frontier can’t handle some of MySQL’s bigger number types (like double, MySQL’s double, which is way bigger than our double), but it can handle strings. So everything that’s supported is returned as a string to Frontier. The Frontier scripter can then do whatever coercing is necessary.

Formats supported and returned as string:

  • MYSQL_TYPE_TINY
  • MYSQL_TYPE_SHORT
  • MYSQL_TYPE_LONG
  • MYSQL_TYPE_INT24
  • MYSQL_TYPE_LONGLONG
  • MYSQL_TYPE_DECIMAL
  • MYSQL_TYPE_NEWDECIMAL
  • MYSQL_TYPE_FLOAT
  • MYSQL_TYPE_DOUBLE
  • MYSQL_TYPE_TIMESTAMP
  • MYSQL_TYPE_DATE
  • MYSQL_TYPE_TIME
  • MYSQL_TYPE_DATETIME
  • MYSQL_TYPE_YEAR
  • MYSQL_TYPE_STRING
  • MYSQL_TYPE_VAR_STRING

The only formats we explicitly don’t support are:

  • MYSQL_TYPE_BIT
  • MYSQL_TYPE_BLOB
  • MYSQL_TYPE_SET
  • MYSQL_TYPE_ENUM
  • MYSQL_TYPE_GEOMETRY
  • MYSQL_TYPE_NULL

More info: http://dev.mysql.com/doc/refman/5.1/en/c-api-datatypes.html

See Also


Personal Tools