Function

Summary

Generate a SQL expression from the supplied keywords.

Package

sql

Signature

sql-expression &key string table attribute owner alias type n-qualified parameter-index => sql-expression

Arguments

string

A string.

table

String, symbol or sql-expression-object.

attribute

String, symbol or sql-expression-object.

owner

String, symbol or sql-expression-object.

alias

A string.

type

A keyword.

n-qualified

A string.

parameter-index

Integer.

Values

sql-expression

A sql-expression-object .

Description

The function sql-expression generates a SQL expression from the supplied keywords. The result sql-expression is of type sql-expression-object, which specifies some SQL that can be used inside other calls to sql-expression, a "[...]" syntax expression or as an argument to the Common SQL functions. sql-expression matches what the read-time "[...]" syntax generates for identifiers (see Enclosing database identifiers), but can be used at run time.

If string is non-nil, it must be the only keyword and specifies the entire SQL, directly without any further processing.

If attribute is supplied, it specifies an attribute (column) name, and the resulting SQL is an attribute named by attribute. It can optionally be qualified by table and owner (the schema that owns the table), so the attribute name becomes table.attribute if owner is nil or owner.table.attribute if owner is non-nil. If table is nil the attribute name is not qualified.

If table is supplied, it specifies a table name. If attribute is supplied too, it qualifies the attribute as in the previous paragraph, otherwise the resulting SQL is a table name. It can be optionally qualified by owner, which specifies owner.table as the table name.

owner can be non-nil only when table is supplied, and qualifies table as described above. It specifies the schema to which the table belongs.

If alias is non-nil it specifies an alias, which added to the SQL after the attribute name if attribute is supplied or after the table name otherwise.

type can be non-nil only if attribute is supplied, and specifies the expected type of the attribute. This does not affect the SQL, but tells the querying interface (select, query) what type the value should be. It is useful when the attribute is used in the selection list of a query (see Specifying the type of retrieved values.).

If parameter-index is non-nil, it must be the only keyword, and sql-expression generates a bind-variable in the SQL. This can be used in an expression that is passed to prepare-statement and then later bound by set-prepared-statement-variables.

If n-qualified is non-nil, it must be the only keyword, and the value is used as-is as an N syntax string. This is required for passing non-ASCII string to Microsoft SQL Server (via ODBC), but does not work on SQLite or Microsoft Access.

(sql-expression :n-qualified "aa")

=>

#<SQL: "N'aa'">

(sql-expression :string      "aa")

=>

#<SQL: "aa">

See SQL string literals for discussion of N syntax strings.

Examples

Define a function that queries for the value of a supplied attribute in a supplied table using a supplied type:

(defun query-attribute (table-name attribute-name type)

  (let* ((table-arg

          (sql-expression

           :table table-name))

         (selection-arg

          (sql-expression

           :attribute attribute-name 

           :type type)))

    (select selection-arg :from table-arg)))

See also

sql
sql-operation
sql-operator
sql-expression-object
Symbolic SQL syntax