sql

Arguments: command &key db names full-names convert blob float

The (required) command should be a string giving a valid sql command for MySQL. It need not end in a semicolon. The keyword arguments are:

• db: db connection to use. The default is the value of *mysql*.
• names: if true (default is t) then return the list of column names from the query as the second value.
• full-names: if true and if names is true as well then return the full name for the columns. The full name is of the form tablename.columnname.
• convert: the value should be nil, t, or a list whose elements are nil or non-nil or the keyword :blob. If nil, do not convert any data.

  If t, convert floats if float is also true; convert text or blob columns if blob is also true; and convert all remaining columns appropriately.

  If a list, do not convert any column whose corresponding list value is nil. (If there are more columns than elements in the list, the extra columns are not converted.) For float columns, convert if the corresponding value is true and float is also true. For text or blob columns, convert if the corresponding value is :blob, or if the corresponding value is a non-nil value other than :blob and blob is also true. For other columns, convert if the corresponding value is true. See Conversions in mysql.htm for details on which conversions are done.

• blob: if true then text and blobs are converted and are returned as array's of (unsigned-byte 8) elements if convert is also true. If nil, text and blobs are converted if convert is :blob. Otherwise text and blobs are returned as strings. See Conversions in mysql.htm. blob argument defaults to nil.
• float: if true (default is t) and convert is also true and if a floating point number is being returned, then do the conversion of that number to an integer (if fractional part of the number is 0, like 12.0) or float (see Note on float conversions below for information on the type of float created). If float is nil then a string version of the floating point value will be returned regardless of the value of convert. See Conversions in mysql.htm.

The function runs command. If command returns row results, sql returns one or two values:

1. a list of row values. Each row value is a list of column values.
2. a list of column names (if the names argument is true). (See also mysql-fields, which when applied to the current database object returns a list of field descriptors.)

If command is something that affects rows in the table but doesn't return any values (like an insert or update command) then sql returns nil and (mysql-affected-rows db) will return how many rows were affected (see mysql-affected-rows).

Note on float conversions

Data is transmitted from the database as strings. Numbers are transmitted with strings like "-123.12" or "12.0". Converting "12.0" to the integer 12 is usually preferrable to converting it to the floating point number 12.0, so conversion is done to an integer when possible.

When the conversion is done to a float, as must be done for a number like "-123.12", the type of float (single or double) created is determined as follows:

1. If the column whose value is being converted has type float then the resulting value is a Lisp integer or a single-float value.
2. If the column whose value is being converted has type double then the resulting value is a Lisp integer or a double-float value.
3. If the column whose value is being converted has type decimal then the resulting value is a Lisp integer or a double-float value. A decimal value is a fixed point number (e.g. decimal(13,2) is a 13 digit number with two digits to the right of the decimal point.)

See mysql.htm for information on the Allegro MySQL facility.

Copyright (c) 1998-2022, Franz Inc. Lafayette, CA., USA. All rights reserved.
This page was not revised from the 10.0 page.
Created 2019.8.20.