The functional interface provides a full set of Data Manipulation and Data Definition functions. The interface provides a SQL-compatible means of querying and updating the database from Lisp. In particular, the values returned from the database are Lisp values — thus smoothly integrating user applications with database transactions. An embedded syntax is provided for dynamically constructing sophisticated queries through select. Iteration is also provided via a mapping function and an extension to the loop macro. If necessary, the basic functions query and execute-command can be called with SQL statements expressed as strings. It is also possible to update or query the data dictionary.
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 23.5 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 23.9.9 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
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.
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*.
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.
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|records|record}
{in|of} query-expression
[not-inside-transaction not-inside-transaction]
[get-all get-all]
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 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.
If type-spec is present, then var is declared to be of type type-spec.
The additional the clauses not-inside-transaction not-inside-transaction and get-all get-all may be useful when fetching many records through a connection with database-type :mysql. See the section 23.9.6 Special considerations for iteration functions and macros for details.
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)))
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 23.3.1.1 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.
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>
(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]])
Prepared statements are SQL statements (queries or other statements) that are prepared once and can then be used repeatedly. Prepared statements use the prepare API of the underlying DBMS. Using a prepared statement can be simpler in many cases. Also, because the preparation of a statement can be a significant overhead, it can improve performance for repeated query or execution with the same statement with optionally different values.
A prepared statement is an object of type prepared-statement. You create one by calling the function prepare-statement with a SQL statement. Optionally, if the SQL statement contains variables, then you set these variables by calling the function set-prepared-statement-variables. Then you can use the prepared-statement in any of the query or execution functions that take a SQL statement: query, do-query, simple-do-query, map-query, select, Loop Extensions in Common SQL and execute-command. Finally, once you have finished with the prepared-statement, it should be destroyed by calling the function destroy-prepared-statement to avoid memory leaks in the database server. The call to destroy-prepared-statement must be before the database is disconnected.
The same prepared-statement can be used repeatedly for querying or executing. Setting the variables can happen repeatedly, but it is not required for each query or execution.
For the common case when you want to set the variables and immediately query or execute the statement, the convenience functions prepared-statement-set-and-execute, prepared-statement-set-and-execute*, prepared-statement-set-and-query and prepared-statement-set-and-query* can be used to perform both operations in one call.
A prepared statement can be very long-lived, but can be also useful in a limited scope, in which case the macro with-prepared-statement is useful, mainly by ensuring destruction of the prepared statement on exit.
Functions in the FDDL may be used to change or query the structure of the database.
The functions list-tables, list-attributes, attribute-type and list-attribute-types return information about the structure of a database.
This example shows you how to query the type of the ename attribute of the emp table.
(attribute-type [ename] [emp]) -> :char
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.
LispWorks® User Guide and Reference Manual - 01 Dec 2021 19:30:23