LispWorks User Guide and Reference Manual > 19 Common SQL > 19.3 Functional interface > 19.3.1 Functional Data Manipulation Language (FDML)

NextPrevUpTopContentsIndex 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"))


(insert-records :into [person]
               :av-pairs `((person_id 115) 
                           (income 11000) 
                           (surname "Johnson")
                           (occupation "plumber")))

are equivalent to the following SQL:

  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.

LispWorks User Guide and Reference Manual - 22 Dec 2009