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

FreeBSD Manual Pages

  
 
  

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

______________________________________________________________________________

NAME
       tdbc::connection	- TDBC connection object

SYNOPSIS
       package require tdbc 1.0
       package require tdbc::driver version

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

       db configure ?-option value...?
       db close
       db foreignkeys ?-primary	tableName? ?-foreign tableName?
       db prepare sql-code
       db preparecall call
       db primarykeys tableName
       db statements
       db resultsets
       db tables ?pattern?
       db columns table	?pattern?
       db begintransaction
       db commit
       db rollback
       db transaction script
       db allrows ?-as lists|dicts? ?-columnsvariable name? ?--? sql-code
	      ?dictionary?
       db foreach ?-as lists|dicts? ?-columnsvariable name? ?--? varName sql-
	      code ?dictionary?	script
______________________________________________________________________________

DESCRIPTION
       Every database driver for TDBC (Tcl DataBase Connectivity) implements a
       connection object that represents a connection to a database.  By  con-
       vention,	 this  object is created by the	command, tdbc::driver::connec-
       tion create.  This command accepts the name of a	Tcl command that  will
       represent  the connection and a possible	set of options (see CONFIGURA-
       TION OPTIONS). It establishes a connection to the database and  returns
       the name	of the newly-created Tcl command.

       The  configure  object  command	on a database connection, if presented
       with no arguments, returns a list of alternating	 keywords  and	values
       representing  the connection's current configuration. If	presented with
       a single	argument -option, it returns the configured value of the given
       option.	Otherwise,  it must be given an	even number of arguments which
       are alternating options and values. The specified options  receive  the
       specified values, and nothing is	returned.

       The  close  object  command on a	database connection closes the connec-
       tion. All active	statements and	result	sets  on  the  connection  are
       closed.	Any uncommitted	transaction is rolled back. The	object command
       is deleted.

       The prepare object command on a	database  connection  prepares	a  SQL
       statement  for  execution.  The sql-code	argument must contain a	single
       SQL statement to	be executed. Bound variables may be included. The  re-
       turn  value  is	a newly-created	Tcl command that represents the	state-
       ment. See tdbc::statement for more detailed discussion of the  SQL  ac-
       cepted  by  the	prepare	object command and the interface accepted by a
       statement.

       On a database connection	where the underlying database and driver  sup-
       port  stored procedures,	the preparecall	object command prepares	a call
       to a stored procedure for execution.  The syntax	of the	stored	proce-
       dure call is:

       ?resultvar =? procname(?arg ?, arg...?)

       The  return  value  is  a newly-created Tcl command that	represents the
       statement. See tdbc::statement for the interface	accepted by  a	state-
       ment.

       The  statements	object	command	returns	a list of statements that have
       been created by prepare and preparecall statements  against  the	 given
       connection and have not yet been	closed.

       The  resultsets	object command returns a list of result	sets that have
       been obtained by	executing statements prepared using the	given  connec-
       tion and	not yet	closed.

       The  tables  object  command allows the program to query	the connection
       for the names of	tables that exist in the database.  The	optional  pat-
       tern  parameter	is a pattern to	match the name of a table. It may con-
       tain the	SQL wild-card characters '%' and and whose values are  subdic-
       tionaries. See the documentation	for the	individual database driver for
       the interpretation of the values.

       The columns object command allows the program to	query  the  connection
       for  the	 names	of  columns that exist in a given table.  The optional
       pattern parameter is a pattern to match the name	of a  column.  It  may
       contain	the SQL	wild-card characters '%' and and whose values are dic-
       tionaries. Each of the subdictionaries will contain at least  the  fol-
       lowing  keys  and  values (and may contain others whose usage is	deter-
       mined by	a specific database driver).

       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 column 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	other-
	      wise.

       The  primarykeys	object command allows the program to query the connec-
       tion for	the primary keys belonging to a	given table. The tableName pa-
       rameter	identifies  the	table being interrogated. The result is	a list
       of dictionaries enumerating the keys (in	a similar format to  the  list
       returned	 by $connection	allrows	-as dicts). The	keys of	the dictionary
       may include at least the	following. Values that are NULL	or meaningless
       in a given database are omitted.

       tableCatalog
	      Name of the catalog in which the table appears.

       tableSchema
	      Name of the schema in which the table  appears.

       tableName
	      Name of the table	owning the primary key.

       constraintCatalog
	      Name of the catalog in which the primary key constraint appears.
	      In some database systems,	this may not be	the same  as  the  ta-
	      ble's catalog.

       constraintSchema
	      Name  of the schema in which the primary key constraint appears.
	      In some database systems,	this may not be	the same  as  the  ta-
	      ble's schema.

       constraintName
	      Name of the primary key constraint,

       columnName
	      Name of a	column that is a member	of the primary key.

       ordinalPosition
	      Ordinal position of the column within the	primary	key.

       To  these  columns  may be added	additional ones	that are specific to a
       particular database system.

       The foreignkeys object command allows the program to query the  connec-
       tion  for  foreign  key relationships that apply	to a particular	table.
       The relationships may be	constrained to the keys	that appear in a  par-
       ticular table (-foreign tableName), the keys that refer to a particular
       table (-primary tableName), or both.  At	 least	one  of	 -primary  and
       -foreign	 should	be specified, although some drivers will enumerate all
       foreign keys in the current catalog if both options  are	 omitted.  The
       result  of  the	foreignkeys  object command is a list of dictionaries,
       with one	list element per key (in a similar format to the list returned
       by  $connection	allrows	-as dicts). The	keys of	the dictionary may in-
       clude at	least the following. Values that are NULL or meaningless in  a
       given database are omitted.

       foreignConstraintCatalog
	      Catalog in which the foreign key constraint appears.

       foreignConstraintSchema
	      Schema in	which the foreign key constraint appears.

       foreignConstraintName
	      Name of the foreign key constraint.

       primaryConstraintCatalog
	      Catalog  holding	the primary key	constraint (or unique key con-
	      straint) on the column to	which the foreign key refers.

       primaryConstraintSchema
	      Schema holding the primary key constraint	(or  unique  key  con-
	      straint) on the column to	which the foreign key refers.

       primaryConstraintName
	      Name of the primary key constraint (or unique key	constraint) on
	      the column to which the foreign key refers.

       updateAction
	      Action to	take when an UPDATE  statement	invalidates  the  con-
	      straint.	 The value will	be CASCADE, SET	DEFAULT, SET NULL, RE-
	      STRICT, or NO ACTION.

       deleteAction
	      Action to	take when a  DELETE  statement	invalidates  the  con-
	      straint.	 The value will	be CASCADE, SET	DEFAULT, SET NULL, RE-
	      STRICT, or NO ACTION.

       primaryCatalog
	      Catalog name in which the	primary	table (the one	to  which  the
	      foreign key refers) appears.

       primarySchema
	      Schema  name  in	which  the primary table (the one to which the
	      foreign key refers) appears.

       primaryTable
	      Table name of the	primary	table (the one to  which  the  foreign
	      key refers).

       primaryColumn
	      Name of the column to which the foreign key refers.

       foreignCatalog
	      Name  of	the  catalog in	which the table	containing the foreign
	      key appears.

       foreignSchema
	      Name of the schema in which the table containing the foreign key
	      appears.

       foreignTable
	      Name of the table	containing the foreign key.

       foreignColumn
	      Name of the column appearing in the foreign key.

       ordinalPosition
	      Position	of the column in the foreign key, if the key is	a com-
	      pound key.

       The begintransaction object command on a	database connection  begins  a
       transaction  on	the database. If the underlying	database does not sup-
       port atomic, consistent,	isolated,  durable  transactions,  the	begin-
       transaction  object  command returns an error reporting the fact. Simi-
       larly, if multiple begintransaction commands are	executed  withough  an
       intervening commit or rollback command, an error	is returned unless the
       underlying database supports nested transactions.

       The commit object command on a database connection ends the most	recent
       transaction  started  by	 begintransaction  and	commits	changes	to the
       database.

       The rollback object command on a	database  connection  rolls  back  the
       most  recent  transaction started by begintransaction. The state	of the
       database	is as if nothing happened during the transaction.

       The transaction object command on a database connection presents	a sim-
       ple  way	 of  bundling a	database transaction. It begins	a transaction,
       and evaluates the supplied script argument  as  a  Tcl  script  in  the
       caller's	 scope.	 If script terminates normally,	or by break, continue,
       or return, the transaction is committed (and any	 action	 requested  by
       break,  continue,  or  return takes place). If the commit fails for any
       reason, the error in the	commit is treated as an	error in  the  script.
       In  the case of an error	in script or in	the commit, the	transaction is
       rolled back and the error is rethrown. Any nonstandard return code from
       the  script  causes  the	 transaction  to  be  rolled  back and then is
       rethrown.

       The allrows object command prepares a SQL statement (given by the  sql-
       code  parameter)	 to execute against the	database.  It then executes it
       (see tdbc::statement for	details) with the optional dictionary  parame-
       ter  giving bind	variables. Finally, it uses the	allrows	object command
       on the result set (see tdbc::resultset) to construct a list of the  re-
       sults.  Finally,	 both  result set and statement	are closed. The	return
       value is	the list of results.

       The foreach object command prepares a SQL statement (given by the  sql-
       code  parameter)	 to execute against the	database.  It then executes it
       (see tdbc::statement for	details) with the optional dictionary  parame-
       ter  giving bind	variables. Finally, 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, both result set and statement are
       closed, even if the given script	results	in a return, an	error,	or  an
       unusual return code.

CONFIGURATION OPTIONS
       The  configuration  options accepted when the connection	is created and
       on the connection's configure object command include the	following, and
       may include others specific to a	database driver.

       -encoding name
	      Specifies	the encoding to	be used	in connecting to the database.
	      The name should be one of	the names  accepted  by	 the  encoding
	      command. This option is usually unnecessary; most	database driv-
	      ers can figure out the encoding in use by	themselves.

       -isolation level
	      Specifies	the transaction	isolation level	 needed	 for  transac-
	      tions on the database. The acceptable values for level are shown
	      under TRANSACTION	ISOLATION LEVELS.

       -timeout	ms
	      Specifies	the maximum time to wait for a an  operation  database
	      engine  before reporting an error	to the caller. The ms argument
	      gives the	maximum	time in	milliseconds. A	value of zero (the de-
	      fault)  specifies	 that  the  calling process is to wait indefi-
	      nitely for database operations.

       -readonly flag
	      Specifies	that the connection will not modify the	 database  (if
	      the  Boolean  parameter flag is true), or	that it	may modify the
	      database (if flag	is false). If flag is true,  this  option  may
	      have  the	 effect	 of raising the	transaction isolation level to
	      readonly.

   TRANSACTION ISOLATION LEVELS
       The acceptable values for the -isolation	configuration  option  are  as
       follows:

       readuncommitted
	      Allows  the  transaction	to  read "dirty", that is, uncommitted
	      data. This isolation level may compromise	data  integrity,  does
	      not  guarantee  that  foreign keys or uniqueness constraints are
	      satisfied, and in	general	does not guarantee data	consistency.

       readcommitted
	      Forbids the transaction from reading "dirty" data, but does  not
	      guarantee	 repeatable  reads;  if	a transaction reads a row of a
	      database at a given time,	there is no guarantee  that  the  same
	      row will be available at a later time in the same	transaction.

       repeatableread
	      Guarantees  that	any  row of the	database, once read, will have
	      the same values for the life of  a  transaction.	Still  permits
	      "phantom	reads" (that is, newly-added rows appearing if a table
	      is queried a second time).

       serializable
	      The most restrictive (and	most expensive)	level  of  transaction
	      isolation.  Any  query to	the database, if repeated, will	return
	      precisely	the same results for the life of the transaction,  ex-
	      actly as if the transaction is the only user of the database.

       readonly
	      Behaves  like  serializable  in that the only results visible to
	      the transaction are those	that were committed prior to the start
	      of  the  transaction, but	forbids	the transaction	from modifying
	      the database.

       A database that does not	implement one of these isolation  levels  will
       instead	use  the  next	more restrictive isolation level. If the given
       level of	isolation cannot be obtained, the database interface throws an
       error reporting the fact. The default isolation level is	readcommitted.

       A  script  should  not  the  isolation  level  when a transaction is in
       progress.

SEE ALSO
       encoding(n), tdbc(n), tdbc::resultset(n), tdbc::statement(n), tdbc::to-
       kenize(n)

KEYWORDS
       TDBC, SQL, database, connectivity, connection, resultset, statement

COPYRIGHT
       Copyright (c) 2008 by Kevin B. Kenny.

Tcl				      8.6		   tdbc::connection(n)

NAME | SYNOPSIS | DESCRIPTION | CONFIGURATION OPTIONS | SEE ALSO | KEYWORDS | COPYRIGHT

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=tdbc_connection&manpath=FreeBSD+12.0-RELEASE+and+Ports>

home | help