23.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 package 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

23.3.1.2 Modification

Modifications to the database can be done using the following functions; insert-records, delete-records and update-records. The functions commit, rollback and the macro with-transaction are used to control transactions. Although commit or rollback may be used in isolation it is advisable to do any updates inside a with-transaction form instead. This provides consistency across different database transaction models. For example, some database systems do not provide an explicit "start-transaction" command while others do. with-transaction allows user code to ignore database-specific transaction models.

The function insert-records creates records in a specified table. The values can be either specified directly with the argument values or in the argument av-pairs, or they can be the result of a query specified in the query argument. The attributes can be specified with the argument attributes or in the argument av-pairs.

If attributes is supplied then values must be a corresponding list of values for each of the listed attribute names. For example, both:

(insert-records :into [person]

    :attributes '(person_id income surname occupation)

    :values '(115 11000 "Johnson" "plumber"))

and:

(insert-records :into [person]

               :av-pairs `((person_id 115) 

                           (income 11000) 

                           (surname "Johnson")

                           (occupation "plumber")))

are equivalent to the following SQL:

INSERT INTO PERSON

  (PERSON_ID,INCOME,SURNAME,OCCUPATION)

  VALUES (115,11000,'Johnson','plumber')

If query is provided, then neither values nor av-pairs should be. In this case the attribute names in the query expression must also exist in the insertion table. For example:

(insert-records :into [person]

     :query [select [id] [firstname] [surname] 

               :from [manager]] 

     :attributes '(person_id firstname surname))

To delete or alter those records in a table which match some condition, use delete-records or update-records.

23.3.1.3 Caching of table queries

Operations which add or modify records sometimes need to perform an internal query to obtain type information for the relevant attributes. In principle it is possible for the database schema to change between update operations, and hence this query is run for each update operation. This can be a significant overhead.

For tables which are guaranteed to have a constant schema, you can optimize performance by adding a cache of these internal query results, using the function cache-table-queries. This can also be used to reset the cache if the table schema is actually altered. To control the default caching behavior throughout every database connection, you can set the variable *cache-table-queries-default*.

23.3.1.4 Transaction handling

A transaction in SQL is defined as starting from the connect, or from a commit, rollback or data-dictionary update and lasting until a commit, rollback, data-dictionary update or a disconnect command.

The macro with-transaction executes a body of code and then does a commit, unless the body failed in which case it does a rollback. Using this macro allows your code to cope with the fact that transactions may be handled differently in the different vendor implementations. Any differences are transparent if the update is done within a with-transaction form.

Note: Common SQL opens an ODBC database in manual commit mode, so that with-transaction and rollback take effect.

Applications should perform all database update operations in a with-transaction form (or follow them with commit or rollback) in order to safely commit or discard their changes. This applies to operations that modify either the data or the schema.

The following example shows a series of updates to an employee table within a transaction. This example would commit the changes to the database on exit from with-transaction. This example inserts a new record into the emp table, then changes those employees whose department number is 40 to 50 and finally removes those employees whose salary is more than 300,000.

(connect "personnel")

(with-transaction

 (insert-records :into [emp]

                 :attributes '(empno ename job deptno)

                 :values '(7100 "ANDERSON" "SALESMAN" 30))

 (update-records [emp]

                :attributes [deptno]

                :values 50

                :where [= [deptno] 40])

 (delete-records :from [emp]

                 :where [> [sal] 300000]))

To commit or roll back all changes made since the last commit, use the functions commit or rollback.

23.3.1.5 Iteration

Common SQL has three iteration constructs: a do loop, a mapping function, and an extension to the Common Lisp loop macro.

The macros do-query and simple-do-query repeatedly execute a piece of code within the scope of variables bound to the attributes of each record resulting from a query.

The function map-query maps a function across the results of a query and returns its result in a sequence of a specified type, like the Common Lisp map function.

Common SQL provides an extension to the ANSI Common Lisp macro loop which is a clause for iterating over query results. The syntax of the clause is:

{for|as} var
 [type-spec] being

             {the|each}{tuples|tuple}

             {in|of} query-expression

query-expression can be a string, a SQL expression (a result of the [..] syntax) or a prepared statement (a result of prepare-statement).

The more general word tuple is used so that it can also be applied to the object-oriented case. In the functional case, tuple is synonymous with record.

Each iteration of the loop assigns the next record of the table to the variable var. The record is represented in Lisp as a list. Destructuring can be used in var to bind variables to specific attributes of the records resulting from query-expression. In conjunction with the panoply of existing clauses available from the loop macro, the new iteration clause provides an integrated report generation facility.

Suppose the name of everyone in an employee table is required. This simple query is shown below using the different iteration method. The function map-query requires flatp to be specified; otherwise each name would be wrapped in a list.

(do-query ((name)[select [ename] :from [emp]])

          (print name))

(map-query

     nil

     #'(lambda (name) (print name))

     [select [ename] :from [emp] :flatp t])

(loop for (name) 

          being each tuple in 

             [select [ename] :from [emp]] 

          do

(print name))

The following extended loop example binds, on each record returned as a result of the query, name and salary, accumulates the salary, and for salaries greater than 2750 increments a count, and prints the details. Finally, the average salary is printed.

(loop for (name salary) being each record in

   [select [ename] [sal] :from [emp]]

   initially (format t "~&~20A~10D" 'name 'salary)

   when (and salary (> salary 2750))

     count salary into salaries

     and sum salary into total

     and do (format t "~&~20A~10D" name salary)

   else

     do (format t "~&~20A~10D" name "N/A")

   finally

   (format t "~2&Av Salary:  ~10D" (/ total salaries)))

23.3.1.6 Specifying SQL directly

Sometimes it is necessary to execute vendor-specific SQL statements and queries. For these occasions Common SQL provides the functions query and execute-command. They can also be used when the exact SQL is known in advance and thus the square bracket syntax is not needed. The query expression can be a string, a SQL expression (a result of the [..] syntax) or a prepared statement (a result of prepare-statement).

The function query runs a SQL query on a database and returns a list of values like select (see Querying). It also returns a list of the field names selected.

execute-command is the basic function which executes any SQL statement other than a query. It can run a stored procedure, as described in execute-command.

23.3.1.7 Building vendor-specific SQL

Common SQL does not provide a general interface to vendor-specific syntax.

There are two approaches you can take with SQL such as this:

SELECT B.PARTY_CODE_ALIAS, A.VALUE FROM CODES A, CODE_ALIASES B

     WHERE A.DOMAIN=B.CODE_DOMAIN(+) AND A.VALUE=B.CODE_VALUE(+)

           AND B.PARTY_ID(+)=<party_id>

1. Construct the string as above and then call query as described in Specifying SQL directly.
2. Use sql-expression to construct the vendor-specific pieces of the SQL. The above expression can be written like this:

(sql:select [b party_code_alias] [a value]

            :from '([codes a] [codes_aliases b])

            :where [and [= [a domain] 

                           (sql:sql-expression 

                            :string "B.CODE_DOMAIN(+)")]

                        [= (sql:sql-expression 

                            :string "B.PARTY_ID(+)") PARTY-ID]])

23.3.2.1 Querying the schema

The functions list-tables, list-attributes, attribute-type and list-attribute-types return information about the structure of a database.

23.3.2.2 FDDL Querying example

This example shows you how to query the type of the ename attribute of the emp table.

(attribute-type [ename] [emp]) -> :char

23.3.2.3 Modification

You may create or drop (delete) tables using the functions create-table and drop-table.

Create or drop indexes using the functions create-index and drop-index.

To create or drop a view (that is, a derived table based on a query) use the functions create-view and drop-view.