# 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