The manual






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 := ...
  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 "'"

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.


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.


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


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.

Exchange mode

Description of operation

No change

There is no interconnection.

Manual, SQL -\u003e Storga

Such a state is not a true Storga state, in the sense that its rows are not created from Storga forms. Such a report simply allows the use of data from the SQL table in Storga programs.

A 'Receive from SQL server' button allows you to download the data from the SQL table to Storga.
The button 'Collect' then 'Browse forms' has the same effect.
This data can be used by Storga's 'lookup' and 'report' instructions.

On the other hand, when the report is created in Storga, the 'Auto' button is used to define the fields of the Storga report from those of the SQL table.

Manual, Storga -\u003e SQL

This mode allows you to make a copy of the contents of the Storga report in an SQL table, at the push of a button.

A 'Send to SQL server' button allows you to manually overwrite the content of the SQL table with the content of the Storga report.
The 'Send SQL' button produces the same effect.

On the other hand, the 'Create SQL table' button allows Storga to create the table on the SQL server.

Automatique, SQL -> Storga

This mode is close to the 'Manual, SQL -\u003e Storga' mode, with the difference that the SQL server is queried each time the report is displayed, and each use by the 'lookup' and 'report' statements.

In other words, this is the mode that allows you to access your SQL data from Storga programs, using the 'lookup' and 'report' statements instead of using the 'sql_query' statement.

In this mode, only the 'Receive from SQL server' button is used to download the data from the SQL table to Storga.
If the SQL server is temporarily unavailable, Storga uses the last copy of the table it assigned following the press on the 'Receive from SQL server' or 'Collect' 'Browse forms' button.

Automatic, Storga -\u003e SQL

This mode is close to the 'Manual, Storga -\u003e SQL' mode, with the difference that the SQL server is updated in real time.If the SQL server is temporarily unavailable, Storga keeps the modifications to be made pending, until the server becomes accessible again.

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.