14.3.1 Functional Data Manipulation Language (FDML)

14.3.1.2 Modification

Modifications to the database can be done using the following functions;insert-records, delete-records and update-records. The functionscommit, rollbackand 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. Thewith-transaction form allows user code to ignore database-specific transaction models.

insert-records

Function

insert-records &key into attributes values av-pairs query database

Inserts values for attributes (or av-pairs) into the table into.

values: A list of values or a query expression.
av-pairs: A list of two-element lists of attributes and values.

For example:

(insert-records :into [person]
                :values 
        '(114 "Joe" "Bloggs" 10000 3000 nil "plumber"))

: is equivalent to the following SQL:

INSERT INTO PERSON
  VALUES (114,'Joe','Bloggs',10000,3000,NULL,'plumber')

: 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 attributes 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))

delete-records

Function

delete-records &key from where database

: Deletes rows from table from where the where condition is true.

update-records

Function

update-records table &key attributes values av-pairs where database

: Changes the values of fields in table with columns specified by attributes and values (or by av-pairs) where the where condition is true.