Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
tdbc::statement(n)	   Tcl Database	Connectivity	    tdbc::statement(n)


       tdbc::statement - TDBC statement	object

       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

       Every database driver for TDBC (Tcl DataBase Connectivity) implements a
       statement  object  that	represents  a SQL statement in a database. In-
       stances of this object are created by executing the prepare or prepare-
       call 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	under-
       scores (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 en-
       suring that the mechanism for binding variables prevents	SQL injection.

       The preparecall object command against the connection accepts  a	 styl-
       ized statement in the form:

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


	      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 vari-
       ables 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	state-
       ment), 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).

	      Contains one of the keywords, in,	 out  or  inout	 according  to
	      whether  the  variable  is an input to or	output from the	state-
	      ment. Only stored	procedure calls	will have out or inout parame-

       type   Contains the data	type of	the column, and	will generally be cho-
	      sen from the set,	bigint,	binary,	bit, char, date, decimal, dou-
	      ble,  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

	      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.

	      Contains	1  if the column can contain NULL values, and 0	other-

       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, pre-
       cision, scale, and nullable arguments have the same meaning as the cor-
       responding 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 re-
       turn 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	 vari-
       ables.  After  executing	the statement, it uses the allrows object com-
       mand 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 vari-
       ables. After executing the statement, it	uses the foreach  object  com-
       mand  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

       The following code would	look up	a telephone number in a	directory, as-
       suming 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

       encoding(n),    tdbc(n),	   tdbc::connection(n),	   tdbc::resultset(n),

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

       Copyright (c) 2008 by Kevin B. Kenny.

Tcl				      8.6		    tdbc::statement(n)


Want to link to this manual page? Use this URL:

home | help