TYL PROGRAMMING LANGUAGE
▶ DATABASE MODULE
Currently Tyl supports only limited number of database providers. One of the known relational database management systems is MS-SQL, referred to as mssql, and its management is integrated into Tyl System, in the mssqldb system module:
mssqldb
Typically to use a database it is needed to connect to it. Connection data is provided in a connection string that has properties that are separated by ';'.

Connecting to a database server:
≡ connect to MS SQL server
mssqldb 'data source=(LocalDb)\MSSQLLocalDB;integrated security=true;connection timeout=60;'
mssqldb.connect
print 'Connection string details' + newline
text string.replace mssqldb.connstr '=' ': '
lines string.split text ';'
lines line ~ line ? print '  ' + line
Connection string details

 data source: (LocalDb)\MSSQLLocalDB
 integrated security: true
 connection timeout: 60
Line mssqldb 'data source=... ', is the module declaration line, also can be described as the initialization line, where the module is declared and its variables are initialized. So mssqldb gets its connection string value, and assigns it to its connstr variable.

Variables and functions of system modules are accessed in the dot notation style, so in line mssqldb.connect, we called connect function of mssqldb, and the system will try to connect to the database according to connection data.

mssqldb has connected variable that indicates connection state. sqlerror is used to show the error that occurred on connection and also on all commands that are executed on the database:
connstr 'data source=(LocalDb)\MSSQLLocalDB;integrated security=true;connection timeout=60;'
mssqldb connstr
mssqldb.connect
mssqldb.connected ?
print 'Connected to MS SQL server successfully.'
\
print 'Connection failed!' + newline
print mssqldb.sqlerror
^
This type of connection is used mainly to verify database connectivity, and to execute maintenance commands.

If we want to connect to a specific database, we would write:
! connect to MS SQL database
dbname 'geodb'
connstr 'data source=(LocalDb)\MSSQLLocalDB;initial catalog=' + dbname + ';integrated security=true;connection timeout=60;'
mssqldb connstr
mssqldb.connect
If connected successfully, it is possible to operate on the geodb database as well as its tables. So let's insert a row:
mssqldb 'data source=(LocalDb)\MSSQLLocalDB;initial catalog=geodb;integrated security=true;connection timeout=60;'
mssqldb.connect
mssqldb.execute 'insert countries (name) values (`country1`)' ?
print 'Command executed successfully.'
\
print 'Command execution failed!' + newline
print mssqldb.sqlerror
^
We make use of execute function, that as the name suggests, executes commands on the database. If execution successful, execute returns TRUE, and it makes it easy for execution validity.

All sorts of DB executive operations such as insert, update, delete etc., can be done by execute function, including activating DB stored procedures. Also it is possible to connect to remote database servers and operate on them as well.

Full example of Tyl Database Module can be found in the examples. It includes various Tyl programs that do database scaffolding and view of a geodb database, that have countries and mountains tables. All the next code examples are taken from there.

Tyl Database Module supports various methods of getting data from the server, and serving it in Tyl components to the programmer. This enables you to seamlessly work with databases in a programmatic way.

Say we want the number of countries rows in geodb:
print mssqldb.value 'select count(*) from countries'
value function returns scalar value that is the result of executing the first parameter as SQL command. If the command fails, an empty string is returned.

To get a list of country names:
names mssqldb.list 'select name from countries'
names name ~ print name
list function gets SQL command that has one value in its selection clause, so names list gets all names from countries table.

Tyl has a neat fetch table rows all function, that returns a list of all rows, converted to Tyl records:
mssqldb 'data source=(LocalDb)\MSSQLLocalDB;initial catalog=geodb;integrated security=true;connection timeout=60;'
mssqldb.connect
countries mssqldb.all 'select * from countries'
crow { id 0 name '' }
cmax len countries
ci cmax ~
crow countries ci
print crow.id + ': ' + crow.name
all generated countries list. Looping by index over it, and assigning each countries item to a predefined crow record, populates crow with countries table row data. All table column names are the keys of crow. So if crow was declared with the appropriate mapping data, all values can be accessed as in line print crow.id + ': ' + crow.name.

As mentioned before, geodb has mountains table, that have a country column that holds the country id, and is referencing id column of countries table.

Here is an elaborate Show Data example that shows how to fetch and show countries and mountains data:
mssqldb 'data source=(LocalDb)\MSSQLLocalDB;initial catalog=geodb;integrated security=true;connection timeout=60;'
mssqldb.connect
! declare records that will be used for
! countries and mountains rows that are
! fetched from geodb database
! country record items will be accessed indirectly
c { }
! mountain record is declared with all expected keys,
! and items can be accessed directly (dot-notation).
! cname key is declared for use in method 3
m { id 0, name '', height 0, country 0, cname '' }
print 'countries' + newline + '--------------------'
countries mssqldb.all 'select * from countries'
cmax len countries
ci cmax ~
c countries ci
text c 'id'
text ++ ': ' + c 'name'
print text
≡ method 1 demonstrates how to search data in referenced table
≡ using the countries list.
≡ for direct approach use method 3
print
print 'mountains (method 1)' + newline + '--------------------'
mountains mssqldb.all 'select * from mountains'
mmax len mountains
mi mmax ~
m mountains mi
text m.id
text ++ ': ' + m.name
text ++ ' (' + m.height
text ++ '), '
 ≡ search country name by id
mcid m.country
cname ''
ci cmax ~
  c countries ci
  cid c 'id'
  cid mcid ? cname c 'name'
print text + cname
≡ method 2 demonstrates how to search data in referenced table
≡ using countries record that is built from countries list.
print
print 'mountains (method 2)' + newline + '--------------------'
countriesrecord { }
ci cmax ~
c countries ci
k c 'id'
v c 'name'
countriesrecord <- k v
mountains mssqldb.all 'select * from mountains'
mi mmax ~
m mountains mi
 ≡ get country name from countries record by
 ≡ mountain country id
cid m.country
cname countriesrecord cid
text m.id
text ++ ': ' + m.name
text ++ ' (' + m.height
text ++ 'm), ' + cname
print text
≡ method 3 uses SQL statement that gets the referenced data
≡ by joining the two tables
print
print 'mountains (method 3)' + newline + '--------------------'
cmd ''
cmd ++ 'select m.id, m.name, m.height, c.name cname '
cmd ++ 'from mountains m join countries c '
cmd ++ 'on m.country=c.id '
cmd ++ 'order by m.id'
mountains mssqldb.all cmd
mi mmax ~
m mountains mi
text m.id
text ++ ': ' + m.name
text ++ ' (' + m.height
text ++ '), ' + m.cname
print text