[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 [Microsoft Windows], too (with the driver from [http://gborg.postgresql.org/project/pgtclng/download/download.php]). Create a database named 'bank' and a user 'bankuser' with password 'bankpass', or change the variables in the demo to something else. ([BAS] Note that there is also a [Microsoft Windows] binary here [http://gborg.postgresql.org/project/pgtcl/download/download.php]) with libpq statically linked in as well.
   * '''[Sqlite]'''. Extremely capable embedded-[SQL] database. Download '''TCL'''sqlite from http://www.sqlite.org/download.html. That is '''tcl'''sqlite - not just 'sqlite'!. No extra config needed. The demo will try to create bank.dat in the current working directory.
   * '''[ODBC]'''. Most useful on [Microsoft 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. Create a user or system ODBC datasource to your favorite database. Call it 'bank' and make sure 'bankuser' with 'bankpass' may connect, or change the configuration variables near the top of the demo.


'''Note:''' The create table statements are somewhat sensitive to specific databases. You may need to alter the data types slightly if you decide to run the demo against another database type.

----
    # Choose a driver to run the demo    
    #set driver odbc
    set driver sqlite
    #set driver postgres
    
    # Alter the datasource to your needs if you use driver other than odbc, sqlite or postgres
    # See http://nstcl.sourceforge.net/docs/nstcl-database/configure%5fpool.html for the correct
    # content of this variable.
    set datasource localhost:5432:bank
    set username bankuser
    set password bankpass
    set odbc_dsn bank
    
    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            $odbc_dsn $username $password
            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        $datasource $username $password
            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]
        }
        default {
            #                     driver pool #connections DSN  user     password
            nstcl::configure_pool $driver   bank 1         $datasource $username $password
            catch {
                db_dml bank:drop "drop table account;"
                db_dml bank:drop "drop table orders;"
            }
        }
    }
    
    # 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

----
[LES] 13-08-2007: The above script didn't quite work for me. Although I have the tclsqlite extension installed with ActiveTcl, also in another directory in my PATH and also in the same directory where I ran the script, the line 59 '''nstcl::load_driver $driver''' gives me an error:

 couldn't load file "tclsqlite.so": tclsqlite.so: cannot open shared object file: No such file or directory
     while executing
 "load [::nstcl::find_shared_library tclsqlite]"
     (procedure "::nstcl::database::sqlite::load_driver" line 3)
     invoked from within
 "::nstcl::database::${driver}::load_driver $args"
     (procedure "::nstcl::database::load_driver" line 3)
     invoked from within
 "nstcl::load_driver $driver"
     (file "./nsdb.tcl" line 28)

Eventually, I edited '''/path/ActiveTcl/lib/nstcl-1.2/nstcl-database-sqlite.tcl''', changed 
 load [::nstcl::find_shared_library tclsqlite]
to
 load [file normalize [::nstcl::find_shared_library tclsqlite]]'''
...and it worked.

But then the '''transfer''' proc towards the end of the script gives me an error too:

 could not allocate 1 handle(s) from pool "bank"
     while executing
 "::nstcl::ns_db gethandle $pool"
     (procedure "::nstcl::database::api_get_dbhandle" line 37)
     invoked from within
 "::nstcl::database::api_get_dbhandle $statement_name"
     (procedure "db_foreach" line 29)
     invoked from within
 "db_foreach bank:accounts {
         select id as a_id, name, balance from account order by id;
     } {
         # In the db_foreach loop, the column nam..."
     (procedure "list_accounts" line 2)
     invoked from within
 "list_accounts"
     (file "./nsdb.tcl" line 244)

I haven't been able to fix that one. I'll try again later. cd zzz now.


----
[Category Database]