|
Allegro CL version 11.0 | ||||||||
Function, dbi package
Arguments: param-num obj type width &key db hstmt direction
This function creates a binding for a parameter for a parameterized sql statement.
A binding is the assigning of a block of memory to hold the value of a given parameter. Whenever a parameterized sql statement is executed, ODBC looks in the bindings and uses the current value found there. param-num is the index of the parameter to be bound. The first parameter is number 1 (not 0). obj is the object to bind to the parameter. If obj is nil then an object will be allocated.
type is the type of the value stored in the object. It can be any of the following values:
:long or :int (treated the same)
:float or :double (treated the same)
:char
:varchar
:null
If the type is :char or :varchar then the width specifies the maximum number of characters in the object. If type is :null, then the width must be specified 0. A null bound parameter will always insert a NULL into a column.
The binding is associated with a particular statement handle for a particular database connection. If hstmt is true then it is that statement handle. Otherwise if db is true then its default statement handle is used. If db is nil then the value of *default-database* is the database connection whose statement handle is used.
direction specifies the direction of the binding being created in this call. Acceptable values are :input, :output, or :inout. Defaults to :input.
Normally in a call to bind-parameter the value of obj is nil which calls for bind-parameter to allocate an object of the appropriate type and return it as the result of the call. If obj is true then it should be an object allocated by a previous call to bind-parameter for the same type and width. The objects allocated by bind-parameter are foreign objects of allocation type :foreign-static-gc. It is the program's responsibility to keep pointers to these bound objects so that they aren't garbage collected before they are used for the last time.
There is an example of using bind-parameter in Binding input parameters in aodbc.html. That example shows what these bound objects look like from the Lisp perspective.
Here is an example where a parameter is bound to :int and to :null:
;; We create a table snort and prepare-sql to insert
;; some value into it:
cl-user(39): (dbi:sql "create table snort(i int, j int)")
nil
cl-user(40): (dbi:prepare-sql "insert into snort(i,j) values(?,?)")
t
;; We create two bound int parameters p1 and p2
cl-user(41): (setq p1 (dbi:bind-parameter 1 nil :int nil))
#<foreign object of class dbi::odbc-int-param>
cl-user(42): (setq p2 (dbi:bind-parameter 2 nil :int nil))
#<foreign object of class dbi::odbc-int-param>
;; We will insert 111,222 first
cl-user(43): (setf (ff:fslot-value p1 :data) 111)
111
cl-user(44): (setf (ff:fslot-value p2 :data) 222)
222
cl-user(45): (dbi:run-prepared-sql)
nil
;; Now we want to insert NULL,444 into the table. In order
;; to accomplish this we rebind paramater 1 to a :null typed
;; object (note that you must specify the width to be 0):
cl-user(46): (setq nullp1 (dbi:bind-parameter 1 nil :null 0))
#<foreign object of class dbi::odbc-char129-param>
;; We don't have to set the :data slot of the :null parameter.
;; It only has one value anyway.
;;
;; We set the value of the second parameter to 444
cl-user(47): (setf (ff:fslot-value p2 :data) 444)
444
cl-user(48): (dbi:run-prepared-sql)
nil
;; Now we want to insert 666,888 into the table. Parameter 2 is
;; still bound to p2. We rebind parameter 1 to our p1 object which
;; still exists.
cl-user(49): (dbi:bind-parameter 1 p1 :int nil)
#<foreign object of class dbi::odbc-int-param>
;; Then we use p1 and p2 to set the vaules and insert the rows
cl-user(50): (setf (ff:fslot-value p1 :data) 666)
666
cl-user(51): (setf (ff:fslot-value p2 :data) 888)
888
cl-user(52): (dbi:run-prepared-sql)
nil
;; And we verify that it worked out ok:
cl-user(53): (dbi:sql "select * from snort")
(("111" "222") (nil "444") ("666" "888"))
("i" "j")
cl-user(54): See aodbc.html for more information on Allegro ODBC.
Function, dbi package
Arguments: query
If the query object passed as the argument is not closed then close it and free up the resources it holds. If the query object is already closed then nothing is done. fetch-row will call close-query when it finds that there is no more data to retrieve, however it is a good idea to explicitly call close-query in your code as well.
See aodbc.html for more information on Allegro ODBC.
Function, dbi package
Arguments: &key data-source-name user password prompt hwnd width external-format connect-string non-blocking
This function establishes a connection to the database denoted by data-source-name (a string) and returns a database object.
See aodbc.html for a description of how data source names are defined. ODBC has two functions to establish a connection - one expects all the information to make the connection to be passed to the function and the other is willing to look for that information and possibly prompt for it. If the value of prompt is true (which it is by default) then the connect function will use the ODBC function that prompts for data (on Windows) or looks in the
The kind of information that ODBC will often need to make a connection is the user name and password. On Windows if a prompting dialog box must be put up, it will be made the child window of hwnd, if it is true. If hwnd is nil then the prompt windows will be at top level. If the connection cannot be made, an error is signaled. width is the default size for character buffers used in retrieving character-valued fields returned by an sql statement. If width is not specified it defaults to 1024.
The external-format keyword argument specifies the external format to be used when storing characters in the database. If not specified the value of *default-odbc-external-format* is used.
The connect-string keyword argument allows specifying the precise connection string to be passed to the ODBC library function to connect to a database. If a value is specified for connect-string, then the values of data-source-name, user and password are ignored.
The format of a connection string is described in ODBC documentation. At the time of writing, a concise description was available at http://msdn.microsoft.com/en-us/library/ms715433(VS.85).aspx.
The non-blocking keyword argument, if true, causes Lisp to never block other threads from running when making a call to the odbc libraries. The default is true. Note that non-blocking mode can slow down the operation of the interface. The non-blocking argument sets the non-blocking flag inside the database object returned by connect. You can change the non-blocking behavior with the function db-non-blocking, with the form (setf (db-non-blocking db-object) new-value)
See aodbc.html for more information on Allegro ODBC.
Function, dbi package
Arguments: db
This function returns the external format used with the argument database. setf can be used with this function to change the external format associated with the database.
See aodbc.html for more information on Allegro ODBC.
Generic Function, dbi package
Arguments: db
The default method of this generic function returns the default statement handle for the database connection specified by db. db should be a database object as returned by connect.
A statement handle is used to describe a conversation with the database. There can in principle be multiple statement handles for a given database connection (though this feature is not implemented). When the database connection is made a statement handle is allocated and becomes the default statement handle for conversations with that database connection. Normally an application won't need to be concerned about statement handles.
See aodbc.html for more information on Allegro ODBC.
Generic Function, dbi package
Arguments: db
This function returns, or sets with setf, the value of the non-blocking flag of the database db. The function connect, by default, creates non-blocking databases. These can, however, be slow. If it is necessary to improve performance, the non-blocking behavior can be changed by reseting the flag. The following form, for example, sets the flag to nil:
(setf (db-non-blocking db-object) nil)See aodbc.html for more information on Allegro ODBC.
Generic Function, dbi package
Arguments: db
The default method for this generic function returns the default size for character buffers used to retrieve the results of sql statements. It can be set with (setf db-width).
See aodbc.html for more information on Allegro ODBC.
Generic Function, dbi package
Arguments: db
Close down the database connection specified by db. Connections are opened with connect.
See aodbc.html for more information on Allegro ODBC.
Function, dbi package
Arguments: action &key db
end-transaction only has an effect if autocommit mode has been turned off with set-autocommit. Autocommit mode is per database.
action must be either :commit or :rollback. :commit will make the changes in the current transaction visible to all other users of the database. :rollback will undo all changes made in this transaction. Only the caller of the function will notice the change as other users of the database never saw the changes.
After either commiting or rolling back, end-transaction then starts a new transaction.
db identifies that database. It defaults to *default-database*.
See aodbc.html for more information on Allegro ODBC.
Function, dbi package
Arguments: query &optional (eof-errorp t) eof-value
Returns the next row of data from the given query. If there are no more rows to return then signal an error if eof-errorp is true. If eof-errorp is nil then return eof-value instead of signalling an error.
fetch-row will call close-query when it finds that there is no more data to retrieve. However it is a good idea to explicitly call close-query in your code as well. (It is not an error to call close-query on an already closed query object.)
See section Queries in aodbc.html.
See aodbc.html for more information on Allegro ODBC.
Function, dbi package
Arguments: &key db hstmt
This function returns two values: a list of all the tables in the database specified by db and a list of the column headers that describe the data returned as the first value. If statement handle hstmt is true (the default is nil) then it is used for the conversation with its associated database. Otherwise if db is true then it is the database whose tables are computed. If db is also nil then the database queried is the one that is the value of *default-database*.
See aodbc.html for more information on Allegro ODBC.
Function, dbi package
Arguments: table &key db hstmt
This function returns two values: a list of all the columns in the table specified by the table argument and a list of the column headers that describe the data returned as the first value. If statement handle hstmt is true then it is used for the conversation with its associated database. Otherwise if db is true then it is the database whose table information is computed. If db is also nil then the database queried is the one that is the value of *default-database*.
See aodbc.html for more information on Allegro ODBC.
Macro, dbi package
Arguments: (&key types column-names do-column-names return hstmt width db) &body body
After executing a sql statement with rr-sql, this form loops once for each result row, evaluating the body. types specifies how the values for each column should be returned (as it does for sql, see below for possible values of types). If you wish to have a list of the result column names computed, then pass a symbol as the value of column-names and pass a true value for do-column-names. If this is done then the symbol that is the value of column-names will be bound to the list of column names before the first time that body is evaluated. After body is evaluated for the last time the expression return is evaluated and the value of it is the value of the loop-over-results form. The statement handle of the results that are being scanned by this form is hstmt.
Within the body there are two macros defined: column-count and column-value. The macro column-count takes no arguments and its value is the number of columns of results available. The column-value macro takes one argument, the column number, and returns the value in that column for the current row. Note: column numbers are one-based, thus the value passed to column-value should be between 1 and the number of columns returned. The type of value returned by column-value will be a string unless some other type of value is specified in the types argument.
width is the size for character buffers used in retrieving character-valued fields. If not given, then the width for the database, db-width, is used.
types should be either the keyword :auto or a list of type specifiers from the following table. The value :auto means that the types will be determined automatically from the query results.
:int: return as a 4 byte signed value:long: return as a 4 byte signed value:double: return as an 8 byte floating point valuet: return as a string or nil for the null value (the default)db should be specified given so that the external format value stored in the database can be used to compute the value.
See aodbc.html for more information on Allegro ODBC.
Function, dbi package
Arguments:
This is an internal function and should not be called by users. The symbol naming it is exported because it also names the odbc-error condition class. (See Conditions in aodbc.html for information on the condition.)
See aodbc.html for more information on Allegro ODBC.
Function, dbi package
Arguments: sql-statement &key db hstmt
Check the sql-statement for errors and prepare for its execution, but don't execute the statement.
If statement handle hstmt is true then it is used for the conversation with its associated database. Otherwise if db is true then it is the database on which the sql statement will be run. If db is also nil then the database used is the one that is the value of *default-database*.
See aodbc.html for more information on Allegro ODBC.
Function, dbi package
Arguments: hstmt sql-statement &key db
This function is intended for use in programs which want to get involved in the processing of results from an sql statement. This function runs the sql-statement on the database for which hstmt is a statement handle. The results of the sql-statement are not processed. loop-over-results should be run to receive the results.
The db keyword argument should be specified so that the correct external format will be used when extracting values.
See aodbc.html for more information on Allegro ODBC.
Function, dbi package
Arguments: &key db hstmt row-count width types
Execute the sql statement that was last prepared via prepare-sql. The prepared sql statement is associated with the statement handle.
If statement handle hstmt is true then it is used for the conversation with its associated database. Otherwise if db is true then it is the database on which the sql statement will be run. If db is also nil then the database used is the one that is the value of *default-database*.
width is the size for character buffers used in retrieving character-valued fields. If not given, then the width for the database, db-width, is used.
types should be either the keyword :auto or a list of type specifiers from the following table. The value :auto means that the types will be determined automatically from the query results.
:int: return as a 4 byte signed value:long: return as a 4 byte signed value:double: return as an 8 byte floating point valuet return as a string or nil for the null value (the default)If the sql statement is parameterized then values for all of the parameters must be bound before the statement is executed.
The returned values are the same as for the sql function and are documented in sql's description.
See aodbc.html for more information on Allegro ODBC.
Function, dbi package
Arguments: value &key db
value can be true or nil. This sets the autocommit mode for db to value.
Autocommit mode is per database. db identifies that database. It defaults to *default-database*. All database connections start with autocommit mode true.
If the autocommit mode is true, then future sql statements will commit immediately. If the autocommit option is nil then future sql statements will be part of a transaction which must be commmitted or rolled back with end-transaction.
Note: for backward compatibility, a value of 0 will be treated as false.
See aodbc.html for more information on Allegro ODBC.
Function, dbi package
Arguments: sql-statement &key db hstmt types column-names row-count width query
This is the primary function for doing database queries and modification. The sql-statement is executed on the database and the results are returned.
If row-count is nil, the data is returned as one or two values. The first value is a list of rows. Each row is a list of column values (or nil for null values). If column-names is true (which is the default) then a second value is returned which is the list of column names for each row value. Some column names may be empty strings (especially if this column is a computed column).
If row-count is true then a single integer is returned which is the number of rows affected by the operation. It only makes sense to ask for the number of rows after an sql update, insert, or delete statement.
The query keyword argument is a boolean which if true causes a query object to be returned as the first value (instead of a list of the results of the sql statement). The query object then can be passed to the fetch-row function to retrieve the next row of data. Query objects are closed with close-query.
If statement handle hstmt is non-nil then it must be a valid statement handle value for the database to be accessed. Otherwise, if hstmt is nil then the value of db is checked. If it is non-nil then it must be a database object and the sql statement is run on that database. If db is nil then the database object that is the value of *default-database* is used.
Normally the column values in each row are returned as strings (or nil for the null value). You can specify in types the kinds of values you want returned for each column. types can either be the keyword :auto or a list of type specifiers from the table below. The value :auto means use the best Lisp type for the given database type. In practice, only integers and floating-point numbers are converted; all other types are returned as strings, as with the t specifier.
:int: return as a 4 byte signed value:long: return as a 4 byte signed value:double: return as an 8 byte floating point valuet return as a string or nil for the null value (the default)Note: an earlier version of this document stated that :auto was permissible as a value in the list of type specifiers when types is a list. That was incorrect. types can be :auto or a list consisting of the specifiers in the table above, which does not contain :auto.
The first type specifier in the types list describes the type of the first column, the second column, and so on. If there are too few type specifiers given then the t specifier is assumed for those unlabeled. If there are too many type specifiers given then the extras are ignored.
width is the size for character buffers used in retrieving character-valued fields. If not given, then the width for the database, db-width, is used. Data wider than width will still be retrieved correctly.
The sql-statement must be a valid sql statement for the given database connection. Each database vendor has a different version of sql although they all support a very minimal subset of expressions and data types.
The sql-statement may contain parameters. A parameter is denoted by a ? where a value would normally be found. Before calling sql on a parameterized sql-statement it is necessary to set values for each of the parameters in the statement. This is done with bind-parameter.
If the same sql statement is going to be repeatedly executed you may want to use prepare-sql and run-prepared-sql.
Errors are signaled with the dbi:odbc-error condition or its subclass dbi:odbc-error-width-too-small. See Conditions in aodbc.html for more information on these conditions.
See aodbc.html for more information on Allegro ODBC.
Copyright (c) 2023, Franz Inc. Lafayette, CA., USA. All rights reserved.
|
Allegro CL version 11.0 | ||||||||