Provided by: tcl8.6-tdbc_1.1.7-1build1_amd64 bug

NAME

       tdbc::statement - TDBC statement object

SYNOPSIS

       package require tdbc 1.0
       package require tdbc::driver version

       tdbc::driver::connection create db ?-option value...?

       set stmt [db prepare sql-code]
       set stmt [db preparecall call]

       $stmt params
       $stmt paramtype ?direction? type ?precision? ?scale?
       $stmt execute ?dict?
       $stmt resultsets
       $stmt allrows ?-as lists|dicts? ?-columnsvariable name? ?--? ?dict
       $stmt foreach ?-as lists|dicts? ?-columnsvariable name? ?--? varName ?dict? script
       $stmt close
_________________________________________________________________________________________________

DESCRIPTION

       Every  database  driver for TDBC (Tcl DataBase Connectivity) implements a statement object
       that represents a SQL statement in a database. Instances of this  object  are  created  by
       executing the prepare or preparecall object command on a database connection.

       The  prepare  object  command  against  the  connection  accepts  arbitrary SQL code to be
       executed against the database. The SQL code may contain bound variables, which are strings
       of  alphanumeric  characters  or underscores (the first character of the string may not be
       numeric), prefixed with a colon (:). If a bound variable appears in the SQL statement, and
       is  not in a string set off by single or double quotes, nor in a comment introduced by --,
       it becomes a value that is substituted when the statement is executed.  A  bound  variable
       becomes  a  single  value  (string  or  numeric)  in  the resulting statement. Drivers are
       responsible for ensuring that the mechanism for binding variables prevents SQL injection.

       The preparecall object command against the connection accepts a stylized statement in  the
       form:

              procname (?:varname? ?,:varname...?)

       or

              varname = procname (?:varname? ?,:varname...?)

       This  statement  represents  a  call  to  a stored procedure procname in the database. The
       variable name to the left of the equal sign (if present), and all variable names that  are
       parameters inside parentheses, become bound variables.

       The params method against a statement object enumerates the bound variables that appear in
       the statement. The result returned from the params method is a dictionary whose  keys  are
       the  names  of bound variables (listed in the order in which the variables first appear in
       the statement), and whose values are dictionaries. The subdictionaries  include  at  least
       the following keys (database drivers may add additional keys that are not in this list).

       direction
              Contains one of the keywords, in, out or inout according to whether the variable is
              an input to or output from the statement. Only stored procedure calls will have out
              or inout parameters.

       type   Contains  the  data  type of the column, and will generally be chosen from the set,
              bigint, binary, bit, char, date, decimal, double,  float,  integer,  longvarbinary,
              longvarchar,  numeric,  real,  time,  timestamp,  smallint, tinyint, varbinary, and
              varchar. (If the variable has a type that cannot  be  represented  as  one  of  the
              above, type will contain a driver-dependent description of the type.)

       precision
              Contains  the  precision  of  the  column  in bits, decimal digits, or the width in
              characters, according to the type.

       scale  Contains the scale of the column (the number of digits after the radix point),  for
              types that support the concept.

       nullable
              Contains 1 if the column can contain NULL values, and 0 otherwise.

       The  paramtype  object  command  allows  the  script  to specify the type and direction of
       parameter transmission of a variable in a statement. (Some databases provide no method  to
       determine this information automatically and place the burden on the caller to do so.) The
       direction, type, precision, scale, and nullable arguments have the  same  meaning  as  the
       corresponding dictionary values in the params object command.

       The  execute  object  command  executes  the  statement. Prior to executing the statement,
       values are provided for the bound variables that appear in it.  If the dict  parameter  is
       supplied,  it  is searched for a key whose name matches the name of the bound variable. If
       the key is present, its value becomes the substituted variable. If not, the value  of  the
       substituted  variable  becomes  a  SQL  NULL.  If  the dict parameter is not supplied, the
       execute object command searches for a variable in the caller's scope  whose  name  matches
       the  name  of  the bound variable. If one is found, its value becomes the bound variable's
       value. If none is found, the bound variable is assigned a SQL NULL  as  its  value.   Once
       substitution  is  finished,  the  resulting  statement  is executed. The return value is a
       result set object (see tdbc::resultset for details).

       The resultsets method returns a list of all the result sets that  have  been  returned  by
       executing the statement and have not yet been closed.

       The  allrows  object  command  executes  the statement as with the execute object command,
       accepting an optional dict parameter giving bind variables. After executing the statement,
       it  uses the allrows object command on the result set (see tdbc::resultset) to construct a
       list of the results. Finally, the result set is closed. The return value is  the  list  of
       results.

       The  foreach  object  command  executes  the statement as with the execute object command,
       accepting an optional dict parameter giving bind variables. After executing the statement,
       it uses the foreach object command on the result set (see tdbc::resultset) to evaluate the
       given script for each row of the results. Finally, the result set is closed, even  if  the
       given script results in a return, an error, or an unusual return code.

       The  close object command removes a statement and any result sets that it has created. All
       system resources associated with the objects are freed.

EXAMPLES

       The following code  would  look  up  a  telephone  number  in  a  directory,  assuming  an
       appropriate SQL schema:

              package require tdbc::sqlite3
              tdbc::sqlite3::connection create db phonebook.sqlite3
              set statement [db prepare {
                  select phone_num from directory
                  where first_name = :firstname and last_name = :lastname
              }]
              set firstname Fred
              set lastname Flintstone
              $statement foreach row {
                  puts [dict get $row phone_num]
              }
              $statement close
              db close

SEE ALSO

       encoding(3tcl),       tdbc(3tcl),      tdbc::connection(3tcl),      tdbc::resultset(3tcl),
       tdbc::tokenize(3tcl)

KEYWORDS

       TDBC, SQL, database,  connectivity,  connection,  resultset,  statement,  bound  variable,
       stored procedure, call

COPYRIGHT

       Copyright (c) 2008 by Kevin B. Kenny.