|
Allegro CL version 11.0 | ||||||||
Function, dbi.mysql package
Arguments: &key host port user file password database external-format client-flags
The arguments are:
host: a string naming the machine to connect to, or a list with the first element being a string naming the host to connect to followed any number of keyword arguments accepted by socket:make-ssl-client-stream These arguments will be passed to socket:make-ssl-client-stream when negotiating an SSL connection if CLIENT_SSL is included in the client-flag argument documented below, and are otherwise ignored. host defaults to "localhost".
port: an integer specifying the port to connect to. Defaults to 3306.
user: a string providing a user name to send to authenticate. Defaults to the current user's login name.
file: the value can be either a pathname designator which can be used in place of host and port to specify the Unix-domain socket on which mysql is listening for a connection; or any other non-nil value. If a non-nil value which is not a pathname designator, the system connects to "/var/lib/mysql/mysql.sock" which is the default Unix-domain socket used by mysql on Linux (and perhaps other architectures as well). This argument can only be used if both the client and server are running the same Unix machine.
password: if given, then password to send.
database: if given, then initial database to use.
external-format: the value can be nil, a symbol or string naming an external format, or an external format object. This argument sets the external-format used to process strings for this connection. If external-format is nil then no external format processing is done (nil is equivalent to the :octets external format except that nil is faster since the external-format processing code is bypassed). External formats are described in iacl.html.
client-flags: if supplied, should be a bitmask of flags indicating the capabilities of the mysql client. Any specified flags are logior'd with the default capabilities of the mysql client. See the MySQL documentation for the server version you are using for the available capability flags. Many of the common flags have been exported from the dbi.mysql package for ease of use (e.g. dbi.mysql:CLIENT_FOUND_ROWS).
Connects to the mysql database, authenticates the user and optionally sets the current database.
The instance of mysql to which to connect is specified by either the file argument or the host and port arguments. If both the client and server are running the same Unix machine then the file argument can be used to specify the Unix-domain socket on which mysql is listening for a connection. Typically that socket is named "/var/lib/mysql/mysql.sock".
If a file argument isn't given to connect then the host and port arguments specify the location of the mysql server. By default the host argument has value "localhost" (meaning the same machine as the client) and the port argument has the value 3306 (which is the normal port on which mysql listens).
Once connected to the mysql server the connect function will then pass the user and password to the mysql server so that it can authenticate the client. The rules for authentication are described in great detail in the MySQL reference manual (available on the http://www.mysql.com website, whose main page refers to it as the MySQL Manual). The default value for the user argument is the user's login name (the value returned by user-name). The password for an account is given in the mysql.user table of the mysql database. This password is unrelated to the password used to log onto the machine. If the database argument is given then once authentication succeeds, the connect makes the given database the current database.
connect returns a mysql object which can be used as the value of the db argument in calls to other functions in this library. connect also sets the value of the symbol *mysql* to the mysql object being returned. Since *mysql* is the default value for the db argument in other functions in this library, this means that if you're just experimenting with mysql interactively, you can call connect and then other functions in this library without saving the mysql object returned by connect and then passing it back in subsequent calls.
See mysql.html for information on the Allegro MySQL facility.
Function, dbi.mysql package
Arguments: &key db
The db, which defaults to the value of *mysql*, must be a mysql object such as returned by connect.
This function disconnects the connection to the database specified by db.
See mysql.html for information on the Allegro MySQL facility.
Function, dbi.mysql package
Arguments: &key db ignore
The db, which defaults to the value of *mysql* must be a mysql object such as returned by connect.
This function returns a list of items in the next row. It returns nil if there are no rows left.
If ignore is specified true (it defaults to nil), then t is returned rather than a list of items if there is an unread row. If you have gotten all you wanted out of a query but haven't reached the end of the result set (i.e get-next-row hasn't returned nil yet) then you still must continue to call get-next-row until get-next-row returns nil (this keeps your client code in sync with the MySQL server). If you are just calling get-next-row until it returns nil then passing the argument :ignore t will tell get-next-row to not bother decoding the items in this row, which will save you some time and reduce consing.
See mysql.html for information on the Allegro MySQL facility.
Function, dbi.mysql package
Arguments: cols-and-vals &key table db showsql on-dup-key
insert-db is a macro that creates an sql statement to insert a row into a table.
db should be a mysql object returned by connect. The value of *mysql* is a suitable value (and the default).
table specifies the table in the database into which the new row will be inserted. There is no meaningful default: you must specify a string naming a table as the value.
If showsql is specified true, the generated sql statement will be printed.
cols-and-vals specifies the columns and values. It looks like a let binding list:
The value of on-dup-key has the same form as cols-and-vals and specifies which columns should be set to which values if the insert tries to insert a row with the same values in key columns as an existing row. Using this argument will save you the trouble of checking if a row exists and then either doing an insert or an update based on what you found.
cl-user(62): (insert-db ((val 20)
(sq 400))
:table "square"
:showsql t)
sql: (format nil "insert into square(val,sq) values(~a,~a)" 20 400)
nil
cl-user(63): If you are inserting a string or blob value you should specify a third value of :text in the cols-and-vals list.
cl-user(63): (sql "create table atext(ttt text)")
nil
cl-user(64): (insert-db ((ttt "foo" :text)) :table "atext")
nilIf you do not specify :text and and the value to be stored in the column is a string then the string is put in the sql statement verbatim. This is useful to store the value of mysql built-in functions.
cl-user(67): (insert-db ((val "now()")
(sq "now()*now()"))
:table "square")
nil
cl-user(68): See mysql.html for information on the Allegro MySQL facility.
Function, dbi.mysql package
Arguments:
This function generates and executes an sql "lock tables" statement. The read and write arguments describe which tables are to be locked for reading and writing respectively.
The db, which defaults to the value of *mysql*, must be a mysql object such as returned by connect.
The values of the read and write arguments can be either:
a string naming a single table
a list (tablename :as alias) where tablename and alias are strings.
a list of items from 1 and 2.
You can see the sql generated by specifying t as the value of showsql.
cl-user(13): (lock-tables :read "aaa"
:write '("bigtab" ("ccc" :as "c"))
:showsql t)
sql: "lock tables aaa read , bigtab write , ccc as c write"
nil
cl-user(14): If the code that uses the tables while they are locked uses a table alias then you must lock the tables under that alias or MySQL will complain. So if you have this code:
(sql "select f.item, b.item from foo f, bar b")then you must have locked the table "foo" under the alias "f" and the table "bar" under the alias "b". You may end up specifying that the same table be locked multiple times under different aliases and under no alias:
(lock-tables :read '("foo" ("foo" :as "f") ("foo" :as "ff")))After locking tables you'll likely want to use the with-locked-tables around the code that processes the locked tables. This ensures that the locks get released when control leaves the with-locked-tables body.
See mysql.html for information on the Allegro MySQL facility.
Function, dbi.mysql package
Arguments: &key db
This function is an accessor to a field of a mysql structure. db should be a mysql object returned by connect. The value of *mysql* is a suitable value. This function returns the number of rows affected by the last update or delete sql command.
See mysql.html for information on the Allegro MySQL facility.
Function, dbi.mysql package
Arguments: &key db
This function is an accessor to a field of a mysql structure. Its value is true if the db object represents an active connection to the MySQL server. The connect function creates an active connection. The disconnect function closes the connection and setf's mysql-connected to nil. Transmission errors will cause the connect to be disconnected automatically.
See mysql.html for information on the Allegro MySQL facility.
Function, dbi.mysql package
Arguments: &key seq
seq should be a string or a vector of type (unsigned-byte 8). This function takes seq and returns a string with all of the special characters that mysql's parsers cares about escaped.
If you want to insert a random string or a binary array in a field you'll want to use this function, as in the following example:
(sql (format nil "insert into footab values('~a')"
(mysql-escape-sequence object)))See mysql.html for information on the Allegro MySQL facility.
Function, dbi.mysql package
Arguments:
This function returns the current external format for the connection. setf can be used with this function to change the external-format of the connection. External formats are described in iacl.html. The external format can also be specified with the external-format keyword argument to connect.
See mysql.html for information on the Allegro MySQL facility.
Function, dbi.mysql package
Arguments: &key db
This function is an accessor to a field of a mysql structure. The field holds a list of column descriptions for the most recently executed sql operation.
See mysql.html for information on the Allegro MySQL facility.
Function, dbi.mysql package
Arguments: &key db
This function is an accessor to a field of a mysql structure. db should be a mysql object returned by connect. The value of *mysql* is a suitable value. This function returns the value of the single autoincrement column in the last row inserted. This is a very powerful feature. See MySQL Examples in mysql.html for more details.
See mysql.html for information on the Allegro MySQL facility.
Function, dbi.mysql package
Arguments: &key db
This function is an accessor to a field of a mysql structure. This value is an integer where the bits of the integer represent capabilities of the MySQL server.
See mysql.html for information on the Allegro MySQL facility.
Function, dbi.mysql package
Arguments: &key db
This function is an accessor to a field of a mysql structure. Its value is a string sent by the MySQL server to identify the particular version of the server.
See mysql.html for information on the Allegro MySQL facility.
Function, dbi.mysql package
Arguments: command &key db names full-names convert blob float
The (required) command should be a string giving a valid sql command for MySQL. It need not end in a semicolon. The keyword arguments are:
db: db connection to use. The default is the value of *mysql*.
names: if true (default is t) then return the list of column names from the query as the second value.
full-names: if true and if names is true as well then return the full name for the columns. The full name is of the form tablename.columnname.
convert: the value should be nil, t, or a list whose elements are nil or non-nil or the keyword :blob. If nil, do not convert any data.
If t, convert floats if float is also true; convert text or blob columns if blob is also true; and convert all remaining columns appropriately.
If a list, do not convert any column whose corresponding list value is nil. (If there are more columns than elements in the list, the extra columns are not converted.) For float columns, convert if the corresponding value is true and float is also true. For text or blob columns, convert if the corresponding value is :blob, or if the corresponding value is a non-nil value other than :blob and blob is also true. For other columns, convert if the corresponding value is true. See Conversions in mysql.html for details on which conversions are done.
blob: if true then text and blobs are converted and are returned as array's of (unsigned-byte 8) elements if convert is also true. If nil, text and blobs are converted if convert is :blob. Otherwise text and blobs are returned as strings. See Conversions in mysql.html. blob argument defaults to nil.
float: if true (default is t) and convert is also true and if a floating point number is being returned, then do the conversion of that number to an integer (if fractional part of the number is 0, like 12.0) or float (see Note on float conversions below for information on the type of float created). If float is nil then a string version of the floating point value will be returned regardless of the value of convert. See Conversions in mysql.html.
The function runs command. If command returns row results, sql returns one or two values:
a list of row values. Each row value is a list of column values.
a list of column names (if the names argument is true). (See also mysql-fields, which when applied to the current database object returns a list of field descriptors.)
If command is something that affects rows in the table but doesn't return any values (like an insert or update command) then sql returns nil and (mysql-affected-rows db) will return how many rows were affected (see mysql-affected-rows).
Data is transmitted from the database as strings. Numbers are transmitted with strings like "-123.12" or "12.0". Converting "12.0" to the integer 12 is usually preferrable to converting it to the floating point number 12.0, so conversion is done to an integer when possible.
When the conversion is done to a float, as must be done for a number like "-123.12", the type of float (single or double) created is determined as follows:
If the column whose value is being converted has type float then the resulting value is a Lisp integer or a single-float value.
If the column whose value is being converted has type double then the resulting value is a Lisp integer or a double-float value.
If the column whose value is being converted has type decimal then the resulting value is a Lisp integer or a double-float value. A decimal value is a fixed point number (e.g. decimal(13,2) is a 13 digit number with two digits to the right of the decimal point.)
See mysql.html for information on the Allegro MySQL facility.
Function, dbi.mysql package
Arguments: command &key db names full-names convert blob float
The function is just like sql except it just sends the command and reads the start of the response. If the response will not return rows of data sql-start returns nil. If there are rows of data to return, it returns an ordered list of the column names to be found in the results. Then you must call get-next-row to get each row until get-next-row returns nil.
See mysql.html for information on the Allegro MySQL facility.
Function, dbi.mysql package
Arguments: &rest tables
This function creates a string that contains a list of table names separated by commas. Each argument to table-list can be
a string naming a single table
a list (<i>tablename</i> :as <i>alias</i>) where tablename and alias are strings.
The result of table-list is often used as the value of the table argument to with-db-rows.
cl-user(16): (table-list "foo" "bar")
"foo, bar"
cl-user(17): (table-list "foo" '("bar" :as "b"))
"foo, bar as b"
cl-user(18): See mysql.html for information on the Allegro MySQL facility.
Macro, dbi.mysql package
Arguments: cols-and-vals &key table where db showsql
This macro creates an sql update statement. update can alter zero or more rows in a table or tables. The syntax of update-db combines the cols-and-vals from insert-db and the where argument from with-db-rows.
db should be a mysql object returned by connect. The value of *mysql* is a suitable value (and the default).
table specifies the table in the database to update. There is no meaningful default: you must specify a string naming a table as the value.
If showsql is specified true, the generated sql statement will be printed.
A simple example to zero the order id in the customer table for a given custid (customer id):
(update-db ((orderid 0))
:table (store-customer store)
:where (= u_customer custid)
:db db)A more complex example where we use the fact that a string value to store is inserted verbatim in the sql statement:
(update-db ((quantity quantity)
(totalcost
(format nil "~a*~a" quantity price))
(totalweight
(format nil "~a*~a" quantity weight)))
:table (store-items store)
:where (and (= u_item itemnum)
(= cartnum cartnum))
:db db)See mysql.html for information on the Allegro MySQL facility.
Macro, dbi.mysql package
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:
in the code that processes the row results you are free to execute other database commands over the same database connection.
if the desire is to short circuit the retrieval of data rows after looking at the first few rows returned then you should not use this macro. All rows are retrieved first before the first row is passed to user code.
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)
nilListing 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)
nilThe 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)
nilIn 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)
nilNow 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)
nilOne 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:
The expressions are always binary (compare two values, but not more than two): thus (< a b c) is not allowed.
The column name is always the first argument. You can use (< val 3) if val is a column name but you cannot use (> 3 val)
The column name can be a string or symbol: (< val 3) and (< "val" 3) are equivalent.
The lisp-expr can 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.
If the second argument to an operator (other than =text) is a string then that string is used verbatim in the where expression. so :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")
The =text operator is designed to do a test for string equality. If the second argument to =text is a lisp-expr then the lisp-expr is evaluated just before the sql statement is executed. The lisp-expr should return a string which is then compared for equality with the column that is the first argument to =text.
If the =text operator's second argument is a string then the column that is the first argument is compared against that string for equality.
You can check for the presence of a null in a column using (= 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.
[1] 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")
<b>returns</b> "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")
<b>returns</b> "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: orderid and city. 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.html for information on the Allegro MySQL facility.
macro, dbi.mysql package
Arguments:
The forms in the body are evaluated and when control leaves the body through any means all tables are unlocked. This is just a call to (sql "unlock tables") in the cleanup form of an unwind-protect. See sql.
db should be a mysql object returned by connect. The value of *mysql* is a suitable value (and the default).
See mysql.html for information on the Allegro MySQL facility.
Copyright (c) 2023, Franz Inc. Lafayette, CA., USA. All rights reserved.
|
Allegro CL version 11.0 | ||||||||