bind-parameter

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.

Examples

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-2019, Franz Inc. Oakland, CA., USA. All rights reserved.
The object described on this page has been modified in the 9.0 release; see the Release Notes.
Created 2019.8.20.