select

select Function

Summary

Selects data from a database given a number of specified constraints.

Package

sql

Signature

select &rest selections &key all set-operation distinct from result-types flatp where group-by having database order-by refresh for-update => result-list

Arguments

`selections`⇩	A set of database identifiers or strings.
`all`⇩	A boolean.
`set-operation`⇩	A SQL operation.
`distinct`⇩	A boolean.
`from`⇩	A SQL table.
`result-types`⇩	A list of symbols.
`flatp`⇩	A boolean.
`where`⇩	A SQL condition.
`group-by`⇩	A SQL condition.
`having`⇩	A SQL condition.
`database`⇩	A database.
`order-by`⇩	A SQL condition.
`refresh`⇩	A boolean.
`for-update`⇩	`t`, `:nowait`, a string or a list.

Values

result-list

A list of selections.

Description

The function select selects data from database, which has a default value of *default-database*, given the constraints specified by the rest of the arguments. It returns a list of objects as specified by selections. By default, the objects will each be represented as lists of attribute values.

The argument selections consists either of database identifiers, type-modified database identifiers or literal strings.

A type-modified database identifier is an expression such as [foo :string] which means that the values in column foo are returned as Lisp strings. This syntax can be used to force values in time/date fields to be returned as strings (see below for an example). It can also be used to affect the value returned from MySQL, using the keywords mentioned in the section 23.9 Using MySQL. It can also be used to return lob-stream objects for queries on Oracle LOB columns, using an expression like [foo :input-stream] or [foo :output-stream].

result-types is used when selections is * or [*]. It should be a list of symbols such as :string and :integer, one for each field in the table being selected in order to specify the types to return. Note that, for specific selections, the result type can be specified by using a type-modified identifier as described above. However, you cannot use result-types to modify the type returned from a time/date field.

flatp, which has a default value of nil, specifies if full bracketed results should be returned for each matched entry. If flatp is nil, the results are returned as a list of lists. If flatp is t, the results are returned as elements of a list, only if there is only one result per row. See the examples section for an example of the use of flatp.

The arguments all, set-operation, distinct, from, where, group-by, having and order-by have the same function as the equivalent SQL expression.

for-update is used to specify the FOR UPDATE clause in a select statement which is used by Oracle to lock the selected records. If for-update is t then a plain "FOR UPDATE" clause is generated. This locks all retrieved records, waiting for the locks to become available. If for-update is :nowait then a "FOR UPDATE NOWAIT" clause is generated. This locks all the retrieved records, or otherwise returns with error ora-00054 which causes Lisp to signal a sql-temporary-error. If for-update is a string then it should specify a column to be locked and a clause "FOR UPDATE OF for-update" is generated. If for-update is a list then the elements of the list should be strings each specifying a column to be locked, except that the last element of the list may be :nowait. A clause locking multiple columns is generated, waiting for the locks according to whether :nowait was supplied. For an example see the section 23.11.3 Locking.

The function select is common across both the functional and object-oriented SQL interfaces. If selections refers to View Classes then the select operation becomes object-oriented. This means that select returns a list of View Class instances, and slot-value becomes a valid SQL operator for use within where.

In the View Class case, a second equivalent select call will return the same View Class instance objects. If refresh is true, then existing instances are updated if necessary, and in this case you might need to extend the hook instance-refreshed. Any join slots defined using retrieval :deferred will be recomputed the next time time they are accessed. The default value of refresh is nil.

SQL expressions used in the select function are specified using the square bracket syntax, once this syntax has been enabled using enable-sql-reader-syntax.

SQL expressions used in the select function are commonly specified using the 23.5 Symbolic SQL syntax. Note that you need to enable it by using enable-sql-reader-syntax or locally-enable-sql-reader-syntax so they can be read correctly. An expression can also be made dynamically by using sql-expression.

Examples

The following is a potential query and result:

(select [person_id] [surname] :from [person])

=> ((111 "Brown") (112 "Jones") (113 "Smith"))

In the next example, flatp is t, and the result is a simple list of surname values:

(select [surname] :from [person] :flatp t)

=> ("Brown" "Jones" "Smith")

In this example data in the attribute largenum, which is of a vendor-specific large numeric type, is returned to Lisp as strings:

(sql:select [largenum :string] :from [my-table])

In this example the second column of some_table is a date that we want to return as a string:

(sql:select [*]
            :from [some_table]
            :result-types '(nil :string))

In this example we see that a time/date field value is returned as an integer. We then use Common Lisp to decode that universal time, and finally query the database again, forcing the return value to be a string formatted by the database:

CL-USER 219 > (sql:select [MyDate] 
                          :from [MyTable] 
                          :flatp t)
(3313785600)
("MYDATE")
 
CL-USER 220 > (decode-universal-time (car *))
0
0
0
4
1
2005
1
NIL
0
 
CL-USER 221 > (sql:select [MyDate :string] 
                          :from [MyTable] 
                          :flatp t)
("2005-01-04 00:00:00")
("MYDATE")

Finally this code gets the first 1 KB of data from the first LOB returned by a query on an Oracle table containing a column of type LOB:

(let* ((array 
        (make-array 1024
                    :element-type '(unsigned-byte 8)))
       (lobs (sql:select [my-lob-column :input-stream] 
                         :from [mytable] :flatp t)))
  (read-sequence array (car lobs)))