NextPrevUpTopContentsIndex

18.3.1.1 Querying

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, note that the default return type for the DATE type is a Common Lisp univeral-time (that is, an integer).You can obtain a string formatted by the database from the MyDate field by modifiying the database identifier, adding :string like this:

(sql:select [MyDate :string] :from [MyTable] :where [= [id] 1])

See the manual page for select in the LispWorks Reference Manual for details.

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

LispWorks User Guide - 8 Apr 2005

NextPrevUpTopContentsIndex