Function | Package: dbi | ToC | DocOverview | CGDoc | RelNotes | FAQ | Index | PermutedIndex
| Allegro CL version 9.0 Object described on page has changed in 9.0. 8.2 version

## 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.

|  | ToC | DocOverview | CGDoc | RelNotes | FAQ | Index | PermutedIndex
| Allegro CL version 9.0 Object described on page has changed in 9.0. 8.2 version