Till Database Module

Till Database Module is a system module, that is used for handling database (DB) activities.

Module:

mssqldb

Properties:

connstr – connection string
sqlerror – last SQL error

Functions:

connect – connect to database according to connection string
disconnect – close database connection
connected – state of connection
execute – execute query (command) and return state of execution
value – run query that return a single value
list – run query that return list of values
all – run query and return list of records that has all rows data

This module handles only MS-SQL databases. In the reference there is a directory “/system modules/database” with extensive code examples that demonstrate how to construct and work with databases. In the demonstration, we setup a database “geodb” and build sample tables (directory “setup”), then, multiple queries are run and the results are analyzed and shown.

Program from reference “/database/ms sql/setup/geodb.create.till” that demonstrate how to create database “geodb”:

! ===========    GEODB SETUP (MS SQL)    ===========
!
! To setup geodb database, first run geodb.create.till,
! then run geodb.build.till
! To check if geodb database and tables were created
! successfully, run geodb.show.till


! if geodb database exists, only countries and 
! mountains tables will be dropped

! if set to true, existing database will be dropped
FORCEDROP \f

! limit the time to check sql operations validity
timeout TIMEOUT 5

dbexistcmd 'select count(*) from sys.databases where name=`geodb`'

tablesexistcmd    'SELECT count(*) FROM geodb.INFORMATION_SCHEMA.TABLES '
tablesexistcmd ++ 'WHERE TABLE_TYPE = `BASE TABLE` '
tablesexistcmd ++ 'AND (TABLE_NAME = `countries` OR TABLE_NAME = `mountains`)'


! perform 'clean' connection, in order to handle databases
! set data source acc. to SQL server name
dbm mssqldb 'data source=JAGUAR\SQLEXPRESS;integrated security=true;connection timeout=60;'
dbm.connect

dbexist \f
dbm.value dbexistcmd ? dbexist \t

dbexist * FORCEDROP ?
	print 'dropping database `geodb`...'
	dropok \f
	timeout TIMEOUT

	dbm.execute 'ALTER DATABASE geodb SET AUTO_CLOSE OFF; DROP DATABASE geodb;' ?
		~
			pause 1
			timeout --
			dbm.value dbexistcmd ? % dropok \t
			not timeout ? %
		^
	^

	dropok ? print 'dropped ok.' + newline \ exiterror dbm.sqlerror
^


print 'creating database `geodb`...'
createok \f
timeout TIMEOUT

dbm.execute 'CREATE DATABASE geodb;' ?
	~
		pause 1
		dbm.value dbexistcmd ? % createok \t
		not timeout ? %
	^
^

createok ? print 'created ok.' + newline \ exiterror dbm.sqlerror


! connect to geodb database
! set data source acc. to SQL server name
geodb mssqldb 'data source=JAGUAR\SQLEXPRESS;initial catalog=geodb;integrated security=true;connection timeout=60;'
geodb.connect

not dropdb ?
	print 'dropping tables: `mountains`, `countries`...'
	dropok \f
	timeout TIMEOUT

	geodb.execute 'DROP TABLE mountains; DROP TABLE countries;' ?
		~
			pause 1
			timeout --
			geodb.value tablesexistcmd ? % dropok \t
			not timeout ? %
		^
	^

	dropok ? print 'dropped ok.' + newline \ exiterror dbm.sqlerror
^


!    ATTENTION: MS SQL accepts only apostrophe symbols ('), as
!               quotation marks. Do replace all apostrophe 
!               symbols with backtick symbols (`).

cmd    ''
cmd ++ 'CREATE TABLE [dbo].[countries] ('
cmd ++ '  [id]      INT           IDENTITY (1, 1) NOT NULL,'
cmd ++ '  [name]    NVARCHAR (20) DEFAULT (``) NOT NULL,'
cmd ++ '  PRIMARY KEY CLUSTERED ([id] ASC)'
cmd ++ ');'

cmd ++ 'CREATE TABLE [dbo].[mountains] ('
cmd ++ '  [id]      INT           IDENTITY (1, 1) NOT NULL,'
cmd ++ '  [name]    NVARCHAR (20) DEFAULT (``) NOT NULL,'
cmd ++ '  [country] INT           NOT NULL,'
cmd ++ '  [height]  INT           NOT NULL,'
cmd ++ '  PRIMARY KEY CLUSTERED ([id] ASC),'
cmd ++ '  CONSTRAINT [FK_mountains_countries] FOREIGN KEY ([country]) REFERENCES [dbo].[countries] ([id])'
cmd ++ ');'

geodb.execute cmd
pause 1

num geodb.value tablesexistcmd

num 2 ? 
	print 'tables created successfully.'
	\
	print 'tables creation failed!' + newline
	print geodb.sqlerror
^


exiterror err:
	print 'operation ended unsuccessfully!'
	print 'sql error can be found in geodb.create.tilldebug file'
	@ print err
	pause 5
	exit

Program from reference “/database/ms sql/single/database.connect.single.till” that demonstrate how to construct the module and connect to DB:

! connect to MS SQL server
connstr 'Data Source=JAGUAR\SQLEXPRESS;Initial Catalog=geodb;Integrated Security=True;connection timeout=60;'
mssqldb connstr
mssqldb.connect

! connect to MS SQL server
mssqldb.connected ?
	print 'Connected to MS SQL server successfully.'
	\
	print 'Connection failed!' + newline
	print mssqldb.sqlerror
^

mssqldb.disconnect

print '________________________________________' + newline

! connect to MS SQL database
dbname 'geodb'
connstr 'data source=JAGUAR\SQLEXPRESS;initial catalog=' + dbname + ';integrated security=true;connection timeout=60;'
mssqldb connstr
mssqldb.connect

mssqldb.connected ?
	print 'Connected to mssqldb `' + dbname + '` successfully.'
	\
	print 'Connection failed!' + newline
	print mssqldb.sqlerror
^

Connected to MS SQL server successfully.
________________________________________

Connected to database 'geodb' successfully.

Program from reference “/database/ms sql/single/database.value.single.till” that demonstrate how to get value from DB:

mssqldb 'data source=JAGUAR\SQLEXPRESS;initial catalog=geodb;integrated security=true;connection timeout=60;'
mssqldb.connect

print mssqldb.value 'select count(*) from countries'

Function “value” gets the query and returns one value.

Program from reference “/database/ms sql/single/database.list.single.till” that demonstrate how to get list of values from DB:

mssqldb 'data source=JAGUAR\SQLEXPRESS;initial catalog=geodb;integrated security=true;connection timeout=60;'
mssqldb.connect

names mssqldb.list 'select name from countries'
names name ~ print name

List variable “names” get result of querying the DB for list of names.

Program from reference “/database/ms sql/single/database.all.single.till” that demonstrate how to get list of rows from DB and analyze the result:

mssqldb 'data source=JAGUAR\SQLEXPRESS;initial catalog=geodb;integrated security=true;connection timeout=60;'
mssqldb.connect

countries mssqldb.all 'select * from countries'

! access countries data by looping
cmax len countries
i cmax ~
	c { id 0 name ''}
	c countries i
	print c.id + ': ' + c.name
^

List “countries” will have records that hold query result data. Each record will hold result row items where the keys are the column names and the values are the respective result values. For analyzing the results, we implement a looping, where each item (row) of “countries” is relayed to a row record “c” that have the expected column names as keys.