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:

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:

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:

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:

If connected successfully, it is possible to operate on the geodb database as well as its tables. So let's insert a row:

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:

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:

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:

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: