SQL interface

SQL queries from Storga programs

The first step is to connect the Storga program to the SQL server:

sql_connect "nom_de_la_base_de_données"

The Storga server uses 'unixodbc' to access the SQL server.
Les librairies spécifiques au modèle de moteur SQL à accéder doivent avoir été préalablement installées sur le serveur Storga, et déclarées via le fichier Unix /etc/odbcinst.ini. Ces opérations sont effectuées par la société Copliant.

Then, in the parameters of the Storga site, in the 'SQL databases' section, the database must be declared, specifying the SQL user and his password, the character encoding used by the SQL server, as well as and above all, the rights necessary for a Storga user to sign a program containing SQL requests to this database.

Then, we mainly use the 'sql_query' instruction:

var Str id := ...
sql_query "SELECT NOMUTI FROM TEST WHERE IDUTIL='" id "'"
  field Str NOMUTI
  ...

You can use variables to configure the SQL query. In this example we have used 'idutil'. However, for security reasons, these variables must correspond to a part between quotation marks of the SQL request, i.e. the supply of a value, and the content of the supplied value will be automatically encoded to prohibit the classic code injection type security.

In addition, in the SQL model, any request provides a statement row, and returns a result table. A Storga 'sql_query' instruction is therefore followed by a block, indented to the right, which will be executed once for each row of the table returned by the query. Columns in the table must be declared via the 'field' statement before they can be used.

If a query does not return a result, typically an 'UPDATE' SQL statement, then we will use a 'void' block:

var Str id := ...
var Str name := ...
sql_query "UPDATE TEST SET NOMUTI='" name "' WHERE IDUTIL='" id "'"
  void

Finally, when we have a doubt concerning the list of fields (columns) returned by an SQL request, we can use the 'sql_fields' instruction which returns this list in the form of a string of multiline characters:

var Str fields := sql_fields "SELECT * FROM TEST"

Nested queries

It is explicitly allowed (Storga does what it takes to make it work) to execute a second SQL request inside the block of a first.

Transactions

By default, Storga works in non-transactional mode: each SQL request is executed immediately and definitively by the server. This has an advantage and a disadvantage. The advantage is that the SQL server is loaded less; the downside is that if the Storga program ends in an error, then the changes at the Storga level will not be applied, but will be applied at the SQL server level.

It is therefore preferable to operate in transactional mode.
To do this, we add the keyword 'transactions' to the level of the 'Additional options' field on the database definition line in the 'SQL database' section of the Storga site declaration.
In transactional mode, any Storga program executes its requests in an SQL transaction, which will be applied (committed) if the Storga program terminates normally, and rolledback if the Storga program terminates in error.

You can also control the transactional mode more finely, and specifically for a Storga program, via the following three instructions:

sql_transactions true

activates or deactivates transactional mode for future requests in the running Storga program.

sql_commit

immediately applies the modifications linked to the SQL queries already executed, regardless of the rest of the execution of the Storga program.

sql_rollback

discards the modifications related to the SQL queries already executed.

Mapping Storga reports to SQL tables

One-way only bridges can be established between Storga and SQL databases by mapping Storga states to SQL tables.

To do this, in the parameters of a Storga report, in the 'Spec. collect 'we fill in the section' Connection to an SQL server '. In particular, the 'Exchange mode' parameter specifies how changes occurring on one side will be applied on the other.

In Storga, report lines are identified by the code of the form that was used to generate the line. If we collect several lines per form, that is to say that we collect the lines of a sub table of a form, then the code of the line in the sub table is added.
When you associate a Storga report with an SQL table, we recommend that you store the row code in the Storga report at one of the fields in the SQL table. The field name is specified via the 'Name of the field storing the Storga line number' parameter.

If you cannot use a field from the SQL table to store the Storga row number, you can indicate to Storga, via the 'Key SQL fields' parameter, the list of fields from the SQL table that form the primary key of the table used to identify each row. If the primary key consists of multiple fields, they will be separated by a plus symbol.
Warning: this mode is currently experimental.