|Allegro CL version 10.1|
Unrevised from 10.0 to 10.1.
Arguments: (columns &key table db where other first norows return convert blob float allrowsvar rowvar nobind showsql) &rest body
This macro creates an sql select statement to retrieve information from the database. It then executes this sql statement and processes the results as specified by the arguments to the macro.
It is very important to note that all the rows are retrieved before the processing of the rows started. This means two things:
We will use this sample table while we describe the meanings of the arguments to with-db-rows.
We create a table called "square" with 10 rows, each row holding a value and its square:
cl-user(19): (sql "create table square(val int, sq int)") cl-user(20): (dotimes (i 10) (sql (format nil "insert into square values(~d, ~d)" i (* i i))))
The columns argument to with-db-rows should be a list specifying which columns are to be returned. In the simplest case the columns are specified as Lisp symbols whose names correspond to the column names in the database.
cl-user(22): (with-db-rows ((val sq) :table "square") (format t ">> ~s~%" (list val sq))) >> (0 0) >> (1 1) >> (2 4) >> (3 9) >> (4 16) >> (5 25) >> (6 36) >> (7 49) >> (8 64) >> (9 81) nil
Listing a column name causes that column to be retrieved from the database. Then when the body of the with-db-rows is executed the Lisp symbols are bound to the successive values retrieved. The body is executed once for each row retrieved.
We have seen that a column specifier can be a symbol. It can also be a list of two or three items. The two item list is used when you want the lisp variable bound to have a different name than the column retrieved. This is usually done when the column retrieved is actually an expression and not a column name:
Here we ask the database to return a third value, the sum of the first two values. This value is bound to the lisp variable sum when the body is evaluated.
cl-user(24): (with-db-rows ((val sq (sum "val+sq")) :table "square") (format t ">> ~s~%" (list val sq sum))) >> (0 0 0) >> (1 1 2) >> (2 4 6) >> (3 9 12) >> (4 16 20) >> (5 25 30) >> (6 36 42) >> (7 49 56) >> (8 64 72) >> (9 81 90) nil
The column specifier can also contain a third item. This is useful when you want to give an alias for the column in the sql statement itself. This is rarely needed. Here's an example of getting an alias for a computed column so that it can be used in an 'order by' expression.
cl-user(29): (with-db-rows ((val sq (sum "val+sq" "ss")) :table "square" :other "order by ss desc") (format t ">> ~s~%" (list val sq sum))) >> (9 81 90) >> (8 64 72) >> (7 49 56) >> (6 36 42) >> (5 25 30) >> (4 16 20) >> (3 9 12) >> (2 4 6) >> (1 1 2) >> (0 0 0) nil
In order to help the programmer understand and debug uses of with-db-rows, the macro takes a showsql argument which if true causes the macro to print out (at compile or eval time) the sql generated.
For example we can see the sql generated in the previous example
cl-user(30): (with-db-rows ((val sq (sum "val+sq" "ss")) :table "square" :other "order by ss desc" :showsql t) (format t ">> ~s~%" (list val sq sum))) sql: "select val,sq,val+sq ss from square order by ss desc" >> (9 81 90) >> (8 64 72) >> (7 49 56) >> (6 36 42) >> (5 25 30) >> (4 16 20) >> (3 9 12) >> (2 4 6) >> (1 1 2) >> (0 0 0) nil
Now we'll examine the where argument to with-db-rows. This argument can either be a string to be used as the where of the generated select statement or it can be an expression that is converted by the macro into a where clause.
First we will see an example of the where argument being a string
cl-user(34): (with-db-rows ((val sq (sum "val+sq" "ss")) :table "square" :where "sq > 30" :showsql t) (format t ">> ~s~%" (list val sq sum))) sql: "select val,sq,val+sq ss from square where sq > 30 " >> (6 36 42) >> (7 49 56) >> (8 64 72) >> (9 81 90) nil cl-user(35):
Here is the same where clause written using the lisp expression syntax.
cl-user(37): (with-db-rows ((val sq (sum "val+sq" "ss")) :table "square" :where (> sq 30) :showsql t) (format t ">> ~s~%" (list val sq sum))) sql: "select val,sq,val+sq ss from square where sq > 30 " >> (6 36 42) >> (7 49 56) >> (8 64 72) >> (9 81 90) nil
One reason for using the lisp expression syntax is that it enhances readability to lisp programmers, especially for large expressions. Another reason is that the expression can use the value of lisp expressions and then the resulting sql expression is computed just before it is evaluated.
In this example instead of putting the value 30 in the expression we reference the value of the lisp variable limit. Note how the generated sql isn't a string but is instead an expression which will generate the sql statement based on the current value of the limit variable.
cl-user(38): (setq limit 30) 30 cl-user(39): (with-db-rows ((val sq (sum "val+sq" "ss")) :table "square" :where (> sq limit) :showsql t) (format t ">> ~s~%" (list val sq sum))) sql: (format nil "select val,sq,val+sq ss from square where sq > ~a " (dbi.mysql::stringify-float limit)) >> (6 36 42) >> (7 49 56) >> (8 64 72) >> (9 81 90) nil cl-user(40):
A where clause expression is not a general Lisp expression. Instead it is very restricted.
where-clause := boolean-clause | simple-expression boolean-clause := (and simple-expression simple-expression ...) | (or simple-expression simple-expression ...) simple-expression := (op column-spec lisp-expr) | (op column-spec string) op := = | < | > | <= | >= | != | =text column-spec := symbol | string lisp-expr := [any lisp expression]
There are a few things to note about simple-expressions:
(< val 3)if
valis a column name but you cannot use
(> 3 val)
(< val 3)and
(< "val" 3)are equivalent.
lisp-exprcan be any lisp expression. It can reference lisp variables and call lisp functions. It cannot reference the value of other columns. See the next item however.
:where (< val "sq")would generate the where clause: "where val < sq" which references the values of two columns of the table. This is also useful when comparing column values against expressions involving mysql built-in functions, such as
(< time "now()-1000")
(= colname :null)or
(!= colname :null).
Next we'll describe the meaning of the first and norows arguments to with-db-rows. When you ask for rows from the database the case of there being no rows returned can be a special case you wish to note.
Here we see a case where we want to put an error check in for the case where there's no data in the database to satisfy our query:
cl-user(42): (with-db-rows ((val sq) :table "square" :where (= val 200) :norows (error "the table isn't complete")) (print sq)) Error: the table isn't complete Restart actions (select using :continue): 0: Return to Top Level (an "abort" restart). 1: Abort entirely from this process.  cl-user(43):
The value of the norows argument is a Lisp expression which is evaluated in the case where the select returns no rows. The result of that evaluation is immediately returned from the with-db-rows macro. In the previous example the signaling of an error prevented the value from being returned
Here we do the same thing except we return the string "no rows found" when the database can't find a value to satisfy the where clause of our query:
cl-user(44): (with-db-rows ((sq) :table "square" :where (= val 200) :norows "no rows found") (print sq)) "no rows found" cl-user(45):
Another common case is where we expect to find just one row in the table and want to take action only on the first row. Here we want to return the value for the sq column when the val column is 3.
cl-user(46): (with-db-rows ((sq) :table "square" :where (= val 3) :first sq) ) 9 cl-user(47):
Normally the body of with-db-rows is executed once for every
row. Specifying a non-
nil value for
first causes the body to be executed only once
and then the first expression to be evaluated and
its value returned by with-db-rows. In the above example there
is no body so all you see is the result of returning the value of the
first expression. The first
expression is evaluated inside the binding of the column values to the
column variables (that's why sq has a value).
The expression above is a common idiom for returning a column value from the database. It is clearer than writing
(caar (sql "select sq from square where val = 3"))
Also if you cared what happened if there were no rows returned you could easily add a norows argument to with-db-rows macro call.
We've seen that both first and norows specify the value to be returned by with-db-rows. If neither first nor norows come into play, then the value of the argument return specifies the expression to evaluate and return. Note that the return expression cannot use the column variable names. It is evaluated after all the rows have been processed.
An example of using return:
cl-user(50): (with-db-rows ((val sq) :table "square" :where (> val 3) :return "all done") (format t ">> ~s~%" (list val sq))) >> (4 16) >> (5 25) >> (6 36) >> (7 49) >> (8 64) >> (9 81) "all done" cl-user(51):
Now we'll explain the use of the allrowsvar, rowvar and nobind arguments. When the select statement returns, the list of rows is stored in a variable and then processed row by row. You can choose the local variable used to store the list of rows and the local variable that takes on successive row values. By choosing the names of these variables you can then access their values in the first, return and body expressions. Note that the return expression only has access to one of the variables - the one containing all rows.
allrowsvar should have as a value a symbol which will name the local variable that will hold the list of all row values. rowvar should have as a value a symbol which will name the local variable holding each successive row.
In this example we return the list of results just as the select statement returned them:
cl-user(53): (with-db-rows ((val sq) :table "square" :where (> val 3) :allrowsvar allofthem :first allofthem)) ((4 16) (5 25) (6 36) (7 49) (8 64) (9 81)) cl-user(54):
This is slightly slower than but equivalent to
(sql "select val,sq from square where val > 3")
with-db-rows is slower because it bound the val and sq variables to the values from the first row returned. It bound them since it didn't know that their values were not going to be used.
You can tell with-db-rows that you don't want it to
bind to the column variables by adding
:nobind t to
the argument list:
cl-user(56): (with-db-rows ((val sq) :table "square" :where (> val 3) :allrowsvar allofthem :first allofthem :nobind t)) ((4 16) (5 25) (6 36) (7 49) (8 64) (9 81)) cl-user(57):
Now we have a with-db-rows expression that's as fast as the sql expression.
with-db-rows takes the same conversion arguments as the sql function: convert, blob, and float. You can find a description of those arguments in the documentation for sql.
You can also find the documentation for the db argument in the documentation for the sql function.
The table argument has been used in all of the examples but has not yet been described in detail. If the table argument is a string then that string is used verbatim in the from clause of the sql statement. It can list a single table (as above) or a group of tables as for example "foo, bar, baz". You can also use the table-list function to build a list of table names. This is especially useful when the table names aren't known until runtime.
(table-list "foo" "bar "baz") returns "foo, bar, baz".
You can alias tables in sql and a table name of ("foo" :as "f") turns into "foo as f". Thus
(table-list '("foo :as "f") "bar") returns "foo as f, bar".
The other argument to with-db-rows takes a string which is added verbatim at the end of the sql statement.
This does a join from two tables and returns the row results. Note that by using aliases for the tables we can write the column variables without knowing until runtime the exact names of the table we'll be reading.
(with-db-rows ((i.u_item i.prodnumber i.version v.brief_desc i.options i.quantity v.price i.totalcost) :table (table-list `(,(store-items store) :as "i") `(,(store-inventory store) :as "v")) :where (and (= i.cartnum cartid) (= i.prodnumber "v.prodnumber") (= i.version "v.version")) :float nil :db db :allrowsvar all-ret-vals :first all-ret-vals :nobind t))
In this example we compute the tax to charge on an order. Bound
outside this expression are two lisp variables:
city is bound to a lisp
string naming the city for which we want to compute the tax.
(with-db-rows (((tax "(t.rate*o.tsubtotal)/100")) :table (table-list '("tax" :as "t") `(,(store-orders store) :as "o")) :where (and (= o.u_order orderid) (=text t.city city)) :first tax))
In this example we want to retrieve a number of columns related to the processing of a credit card. We could just have the first argument to with-db-rows be the expression
(list u_processor ts result pnref authcode avsaddr avszip cvv2match)
but that would cause us to cons up a list that already existed and was
returned by the select statement. Why not use the list returned by
the select statement for this row? We do precisely that by naming the
rowvar (we call the variable
processor) and then we have
first return that variable.
(with-db-rows ((u_processor ts result pnref authcode avsaddr avszip cvv2match) :table (store-processor store) :db db :where (= u_processor processornum) :nobind t :float nil :rowvar processor :first processor))
See mysql.htm for information on the Allegro MySQL facility.
Copyright (c) 1998-2022, Franz Inc. Lafayette, CA., USA. All rights reserved.
This page was not revised from the 10.0 page.
|Allegro CL version 10.1|
Unrevised from 10.0 to 10.1.