Database interfaces

The Publisher is able to connect to several types of databases. First you must make sure you have a connection defined using datasources. Once you have defined your MySQl/Firebird/Interbase/MS SQL/Oracle connection, you can go to Actions > Database. Pushing that button will open the following Window:

Database dialog
Database dialog

Reading

The first input is the name of the data.

Select ‘Read’ or ‘Read once’. Because reading from a database can be slow, you can select ‘Read once’ if you are quite sure that the data in the database is not volatile. In that case, the model will read the data only once, and if the user passes the interface, the data will not be refreshed. If you are not sure, select ‘Read’.

Select the connection. This list will contain the definitions you defined in the datasources.

Once you have selected this, the next drop-down list, ‘Table’, will contain all the tables the Studio can find for that connection. It will show the data found on the right side of the window.

You can select a key, which is one of the fields of the table, which is only relevant if you are writing to the database.

Under ‘filter’ you add the ‘Where clause’ at the end of the SQL statement, as in:

SELECT age from tblkids where NAME='Mary'

This clause consists of free text, so it can be expanded, e.g. with an AND, OR , ORDER BY or GROUP BY clause.

A fully entered database interface can look like this:

Filled database dialog
Filled database dialog

Once you press [OK], the Studio will define a graph/dataset to make sure all the data can be stored. The graph will be called T<tablename>, so in this example tgem_overrides.

So the data will be a dataset you then can use in the model.

Writing

Writing to a database is done by selecting ‘Write’ or ‘Write on stop’. Select ‘Write on stop’ if you want the data be written to the database if the user ends the session. This is faster for the end-user, he will not notice the writing, but be aware that as long as the session is running, no changes are made to the database. If you are not sure, select ‘Write’.

If data is written to the database, a Key is needed to make sure the writing process succeeds. If you have read a dataset, and delete iterations, and then write that dataset to the database, the deleted iterations will be deleted from the database as well.

If you make new Iterations, make sure the Key is unique, or make sure that the database will generate a new key (autoinc).