23.3.1 Functional Data Manipulation Language (FDML)
The functions available for Data Manipulation and Data Definition are described below.
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"))
In this example,
[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")
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"))
: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])
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"))
Modifications to the database can be done using the following functions;
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.
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"))
(insert-records :into [person]
:av-pairs `((person_id 115)
are equivalent to the following SQL:
INSERT INTO PERSON
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]
:attributes '(person_id firstname surname))
To delete or alter those records in a table which match some condition, use
184.108.40.206 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
220.127.116.11 Transaction handling
A transaction in SQL is defined as
the connect, or from a commit,
rollback or data-dictionary update and
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.
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.
(insert-records :into [emp]
:attributes '(empno ename job deptno)
:values '(7100 "ANDERSON" "SALESMAN" 30))
: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
Common SQL has three iteration constructs: a
do loop, a mapping function, and an extension to the Common Lisp loop macro.
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
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:
query-expression can be a string, a sql-expression-object (a result of the "[...]" syntax) or a prepared-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
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]])
#'(lambda (name) (print name))
[select [ename] :from [emp] :flatp t])
(loop for (name)
being each tuple in
[select [ename] :from [emp]]
The following extended loop example binds, on each record returned as a result of the query,
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)
do (format t "~&~20A~10D" name "N/A")
(format t "~2&Av Salary: ~10D" (/ total salaries)))
18.104.22.168 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-object (a result of the "[...]" syntax) or a prepared-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.
22.214.171.124 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(+)
Construct the string as above and then call query as described in Specifying SQL directly.
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]
:string "B.PARTY_ID(+)") PARTY-ID]])