Modifications to the database can be done using the following functions; insert-records
, delete-records
and update-records
. The functions commit
, rollback
and 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. The with-transaction
form allows user code to ignore database-specific transaction models.
insert-records &key into attributes values av-pairs query database
Inserts values for attributes (or av-pairs ) into the table into .
A list of values or a query expression.
A list of two-element lists of attributes and values.
(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"))
(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))
delete-records &key from where database
Deletes rows from table from where the where condition is true.
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.