The function
select
returns data from a database matching the constraints specified. The data is returned, by default, as a list of records in which each record is represented as a list of attribute values. See the
LispWorks Reference Manual
for a full specification.
Database identifiers used in
select
are conveniently specified using the symbolic SQL
[]
syntax. This syntax is enabled by calling
enable-sql-reader-syntax
.
The square bracket syntax assumes that sql symbols are visible. Therefore when using the
[]
syntax, ensure that the current package either is
sql
, or is a pacakge which has the
sql
package on its package-use-list.
For a description of the symbolic SQL syntax see Symbolic SQL syntax. For example, the following is a potential query and its result:
(select [person_id] [person surname] :from [person])
=>
((111 "Brown") (222 "Jones") (333 "Smith"))
("PERSON_ID" "SURNAME")
In this example,
[person_id]
,
[person surname
] and
[person
] are database-identifiers and evaluate to literal SQL. The result is a list of lists of attribute values. Conversely, consider
(select [surname] :from [person] :flatp t)
=>
("Brown" "Jones" "Smith")
("SURNAME")
In this case the result is a simple list of surname values because of the use of the flatp keyword. The flatp keyword only works when there is one column of data to return.
In this example we use * to match all fields in the table, and then we use the result-types keyword to specify the types to return:
(select [*] :from [person])
=>
((2 111 "Brown") (3 222 "Jones") (4 333 "Smith"))
("ID" "Person_ID" "Surname")
(select [*] :from [person] :result-types '(:integer :string :string))
=>
((2 "111" "Brown") (3 "222" "Jones") (4 "333" "Smith"))
("ID" "Person_ID" "Surname")
If you want to affect the result type for a specified field, use a type-modified database identifier. As an example:
(sql:select [Person_ID :string][Surname] :from [person])
=>
(("111" "Brown") ("222" "Jones") ("333" "Smith"))
("PERSON_ID" "SURNAME")
With
database-type
:mysql
, further control over the values returned from queries is possible as described in Types of values returned from queries.
In this final example the
:where
keyword is used to specify a condition for returning selected values from the database.
(select [surname] :from [person] :where [= [person_id] 222])
=>
(("Jones"))
("SURNAME")
To output the results of a query in a more easily readable tabulated way, use the function
print-query
. For example the following call prints two even columns of names and salaries:
(print-query [select [surname] [income] :from [employee]]
:titles '("NAME" "SALARY"))
NAME SALARY
Brown 22000
Jones 45000
Smith 35000