| Allegro CL version 8.2 Unrevised from 8.1 to 8.2. 8.1 version |
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.
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.htm. 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.htm for more information on Allegro ODBC.
Copyright (c) 1998-2016, Franz Inc. Oakland, CA., USA. All rights reserved.
This page was not revised from the 8.1 page.
Created 2010.1.21.
| Allegro CL version 8.2 Unrevised from 8.1 to 8.2. 8.1 version |