Pascal Scheffers 15Dec2004 - An introduction to Tcl database access with nstcl.
This example shows how to connect to a database, create tables, insert some data, how to select some data for reporting and shows how transactions work.
The code assumes you have a database server somewhere, to test it. sqlite will do just fine.
This code has been tested with several databases/drivers:
Note: The create table statements are somewhat sensitive to specific databases. You may need to alter the datatypes slightly.
# Choose a driver to run the demo
#set driver odbc
set driver sqlite
#set driver postgres
package require nstcl
namespace import nstcl::*
if { $driver eq "sqlite" } {
set loaded ""
foreach version {sqlite3 sqlite} {
if { [catch {
package require $version
set loaded $version
} E] } {
if { [file exists tcl$version[info sharedlibextension]] } {
load tcl$version[info sharedlibextension] tcl$version
set loaded $version
} else {
puts "$version not found."
}
}
if { $loaded ne "" } {break}
}
if { $loaded eq "" } {
puts "Sqlite not installed. Just drop the libsqlite[info sharedlibextension] \
or libsqlite3[info sharedlibextension] in the same directory as this demoscript."
exit 1
}
if { $loaded eq "sqlite3" } {
interp alias {} sqlite {} sqlite3
}
puts "Loaded $loaded."
} elseif { $driver eq "postgres" } {
if { [catch {
#The next generation Postgresql driver
package require Pgtcl
} E] } {
puts "Could not load Pgtcl (pgtcl-ng): $E"
puts "Trying libpgtcl..."
package require libpgtcl
}
}
# Before you can use any type of database, nstcl needs to load the appropriate
# database drivers. For this example, we use either odbc or sqlite.
#
# Load driver will automatically load the tclodbc package. sqlite needs to be
# preloaded, as no default package require exists for it.
#
# Doc: http://nstcl.sourceforge.net/docs/nstcl-database/load%5fdriver.html
nstcl::load_driver $driver
# nstcl works with database pools, later on, we will use the pool name for
# our statements.
#
# Doc: http://nstcl.sourceforge.net/docs/nstcl-database/configure%5fpool.html
# ::nstcl::configure_pool ?-immediately? ?-default? driver poolname connections ?datasource? ?username? ?password? ?verbose?
switch $driver {
odbc {
# driver pool #connections DSN user password
nstcl::configure_pool odbc bank 1 bank bankuser bankpass
catch {
db_dml bank:drop "drop table account;"
db_dml bank:drop "drop table orders;"
}
}
postgres {
# driver pool #connections DSN user password
nstcl::configure_pool postgres bank 1 localhost:5432:bank bankuser bankpass
catch {
db_dml bank:drop "drop table account;"
db_dml bank:drop "drop table orders;"
}
}
sqlite {
catch {
file delete [file join [file dirname [info script]] bank.dat]
file delete [file join [file dirname [info script]] bank.dat-journal]
}
# driver pool #connections sqlite-file
nstcl::configure_pool sqlite bank 1 [file join [file dirname [info script]] bank.dat]
}
}
# We now have a working database connection! db_dml bank:table_accounts {
create table account (
id integer,
name varchar(200),
balance numeric(10,2)
);
}
# There is no reason to have only one statement in a db_dml
# Putting in more than one does *NOT* make it a transaction!
db_dml bank:statement2 {
create table orders (
id integer,
account integer,
description varchar(200),
amount numeric(10,2)
);
}
db_dml bank:statement2 {
insert into account (id, name, balance) values (1, 'Pascal Scheffers', 100.00);
}
#So far so good. You see you can execute properly formatted sql statements.
#That is no big surprise.
# For data entry, there is something better, however. Some values may need
# quoting, and depending on the database type, quoting may differ between
# database types and drivers. nstcl takes care of that.
set accounts {
4 "Arjen Markus" 500.00
2 "Jean-Claude Wippler" 23.15
3 "Julian Scheffers" 56.87
}
foreach {accountno name amount} $accounts {
db_dml bank:new_accounts {
insert into account (id, name, balance) values (:accountno, :name, :amount);
}
}
# Okay, we have something in the database. Lets display the content:
# in a proc so we can do it again!
proc list_accounts {} {
db_foreach bank:accounts {
select id as a_id, name, balance from account order by id;
} {
# In the db_foreach loop, the column names are available as a normal
# tcl variable. Becareful they don't clash with local variables!
# rename them if you must!
puts [format " %4d %8.2f %s" $a_id $balance $name]
}
}
list_accounts
# A very convenient function is db_string, which will let you get a single
# value from the database:
set total [db_string bank:all_accounts_total "select sum(balance) from account"]
puts "The total in the bank is: [format %8.2f $total]\n"
# db_foreach is one of my personal favorites, but you may need something
# different.
#
# The important ones are:
#
# db_list
# Obtains a list of the first rows of the query:
set idList [db_list bank:ids "select id, name from account"]
puts "Account numbers: $idList\n"
# Note that the name column was dropped from the result!
#
# If you want the column names too, use db_list_of_lists:
set idNameList [db_list_of_lists bank:idAndNames "select id, name from account"]
puts "Account numbers and names: $idNameList\n"
#
# Now, db_foreach sets names variables for each row/column retrieved.
# I find that I frequently need the variables for a single row:
proc single_account { id } {
# Note the bind variable again!
db_1row bank:oneaccount "select name, balance from account where id=:id"
puts "Account : $id"
puts "Name : $name"
puts "Balance : [format %.2f $balance]"
puts ""
}
single_account 2
single_account 4
#
# db_1row will raise an error if the statement does not return exactly 1 row
# there is a companion function, db_0or1row which allows for checking the existence
# of a row.
# Both of these functions can also set an array, instead of the variables
# this is very convenient, as it won't clutter your local variables!
proc have_account? { id } {
if { [db_0or1row bank:oneaccount "select id, name, balance from account where id=:id" \
-column_array row] } {
puts "Account : $row(id)"
puts "Name : $row(name)"
puts "Balance : [format %.2f $row(balance)]"
} else {
puts "Account $id does not exist!"
}
puts ""
}
have_account? 1
have_account? 5
#
# Similarly, [db_string] has a -default option, so it does not throw an error
# but returns the default value:
puts "Account 6 is owned by [db_string bank:oneacct "select name from account where id=6" -default "nobody"]"
puts "Account 3 is owned by [db_string bank:oneacct "select name from account where id=3" -default "nobody"]"
#
#
# That covers most of the data access and modification functions.
# With a banking system, transactions are important: here is some code which
# demonstrates transactions:
proc transfer {fromAcct toAcct amount} {
catch {
db_transaction {
set initial_balance [db_string bank:total "select sum(balance) from account"]
db_dml bank:transferfrom "update account set balance=balance-:amount where id=:fromAcct"
db_dml bank:transferto "update account set balance=balance+:amount where id=:toAcct"
set final_balance [db_string bank:total "select sum(balance) from account"]
if { [format %.2f $initial_balance] ne [format %.2f $final_balance] } {
puts "Balance mismatch: [format %.2f $initial_balance] ne [format %.2f $final_balance] abort!"
db_abort_transaction
} else {
puts "Transfered 25.00 from account $fromAcct to account $toAcct"
}
}
}
}
puts "\nTransfer money (correctly):"
list_accounts
transfer 1 2 25.00
list_accounts
puts "\nTransfer money (incorrectly):"
transfer 4 5 105.22
list_accounts