LispWorks User Guide and Reference Manual > 19 Common SQL > 19.3 Functional interface > 19.3.1 Functional Data Manipulation Language (FDML)

NextPrevUpTopContentsIndex

19.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.

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

LispWorks User Guide and Reference Manual - 22 Dec 2009

NextPrevUpTopContentsIndex