[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: * '''[Postgresql]'''. My favorite. Works on Windows too. (with the driver from [http://gborg.postgresql.org/project/pgtclng/download/download.php]) * '''[Sqlite]'''. Extremely capable embedded SQL database. Download '''TCL'''sqlite from http://www.sqlite.org/download.html. That is '''tcl'''sqlite - not just 'sqlite'!. * '''[ODBC]'''. Most useful on Windows, on Unix this is troublesome. It will allow you to run the demo with a Microsoft SQL server database, Access database file or any other ODBC database. '''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! # Lets create some tables: 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 ---- [Category Database]