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

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 . See the LispWorks Reference Manual for a full specification.