QUERY — Execute an SQL query and return the tuples as a list
Function
An sql expression that represents an SQL query which is expected to return a (possibly empty) result set.
A database object. This will default to the value of *default-database*.
A field type specifier. The default is NIL.
The purpose of this argument is cause CLSQL to import SQL numeric fields into numeric Lisp objects rather than strings. This reduces the cost of allocating a temporary string and the CLSQL users' inconvenience of converting number strings into number objects.
A value of :auto causes CLSQL to automatically convert SQL fields into a numeric format where applicable. The default value of NIL causes all fields to be returned as strings regardless of the SQL type. Otherwise a list is expected which has a element for each field that specifies the conversion. If the list is shorter than the number of fields, the a value of t is assumed for the field. If the list is longer than the number of fields, the extra elements are ignored.
| :int Field is imported as a signed integer, from 8-bits to 64-bits depending upon the field type. |
| :double Field is imported as a double-float number. |
| t Field is imported as a string. |
A list representing the result set obtained. For each tuple in the result set, there is an element in this list, which is itself a list of all the attribute values in the tuple.
This will execute the query given by query-expression in the database specified. If the execution succeeds it will return the result set returned by the database, otherwise an error of type clsql-sql-error will be signalled.
(execute-command "create table simple (name char(50), salary numeric(10,2))")
=> T
(execute-command "insert into simple values ('Mai, Pierre',10000)")
=> T
(execute-command "insert into simple values ('Hacker, Random J.',8000.50)")
=> T
(query "select * from simple")
=> (("Mai, Pierre" "10000.00") ("Hacker, Random J." "8000.50"))
(query "select salary from simple")
=> (("10000.00") ("8000.50"))
(query "select salary from simple where salary > 10000")
=> NIL
(query "select salary,name from simple where salary > 10000")
=> NIL
(query "select salary,name from simple where salary > 9000")
=> (("10000.00" "Mai, Pierre"))
(query "select salary,name from simple where salary > 8000")
=> (("10000.00" "Mai, Pierre") ("8000.50" "Hacker, Random J."))
;; MySQL-specific:
(query "show tables")
=> (("demo") ("log") ("newlog") ("simple") ("spacetrial"))