SQL

Details: Category: Articles; Published on Monday, 31 December 2018 12:17; Written by AltonaLab; Hits: 1640

SQL Aliases:

Before use of any SQL block, one or more SQL Aliases have to be set. From the icon bar at the top of AltonaLab, select the Settings group, then click on the SQL Settings icon.

At the new opened interface should be added the databases that will be used. The list of aliases is valid to all the diagrams. For each alias must be entered:

Alias name;
SQL Server;
SQL User name;
SQL Password;
Name of the Database;

Only Microsoft SQL Server is supported!

When a new SQL block is added to the diagram, at its property SQLAlias, one of the existing aliases has to be selected.

Block SQLInsert

License: Hobby, Industrial

The SQLInsert block is used to add records to a SQL table.
For example, if we want to complete the following Insert:

insert into History (Date, Out_Temp, Out_Hum, Pr_Rel) values ('20180512 11:15:02', 12.5, 76, 1032)

We have to do the following:

At the SQLAlias parameter, we have to choose a working SQL alias;
At SQLTableName parameter - to set the name of the inserted table, in out case it is History;
to open the Inputs parameter and to add all the columns from the Insert command. In our case: Date with DateTime type, Out_Temp, Out_Hum, Pr_Rel with analogue type. The added SQL Insert columns will appear as block's inputs;

If the block's input Write is connected, then if it becomes to high value, a new SQL Insert command will be send to the SQL Server. The values of the SQL Insert columns will be gotten from the values of the inputs of the block. If the input Write is not connected, then in a certain number of seconds taken from the WritePerSec parameter, a new SQL Insert command will be send to the SQL server;

Other block's parameters:

UseMillisec - to DateTime values (for example '20180512 11:15:02') will be added millisec;
WithDateTimeCol - At the insert command will be added automatically Date column;

Block SQLSelect

License: Industrial

The block is very powerful and fast, allowing the execution of a random selection or a SQL stored function. It also allows the text of the SQL select to be formatted with information from the inputs of the block. The result of any SQL query with any columns appears at the block output as DateSet.

The work of the block will be explained with the SQL select:

select
top (<top>) data, Out_Temp, Out_Hum, pr_rel
from
history
where
date> = '20170101' and date <= '20170108'

For demonstration, the <top> string will be replaced with the value of the top input of the block, which is an integer.

We have to do the next steps with new added to the diagram block:

Choose a working alias in the SqlAlias parameter;
At the DateSet parameter to describe the result columns of the SQL select, in our case there are 4 columns: data, Out_Temp, Out_Hum, pr_rel, where Date is with type DateTime, the remaining Out_Temp, Out_Hum, pr_rel are analog. When the SQL select is executed, the returned columns from the SQL server will be matched by name with columns described at parameter DataSet. If there is a data type difference, the data will be atomaticaly converted from SQL column data type to data type of the column of DataSet;

At parameter Inputs to add the needful inputs to the block whose values will be used to format the text of the SQL select. In the current example, we will set the number of records returned from the SQL select using a <top> tag. For example, select top(10) returns 10 records. In the diagram, we will use a scroll bar control to set the number of records returned. The value of the scrollbar will be given at the input top of the block that we have added through the Inputs parameter. In the text of the SQL select there is a tag <top>, which indicates that the value of the top input will be converted to its integer value in the SQL select text.

How to format text with values of the inputs of the block can be read here:

https://scada.altonalab.com/index.php/en/scada-design-mnu/text-formatting-strategy

Just for idea, if we add two block's inputs at parameter Inputs with names FromDate, ToDate, with DateTime data type, we can use them at SQL select where clause:

select
top (<top>) data, Out_Temp, Out_Hum, pr_rel
from
history
where
date> = '<FromDate;yyyyMMdd hh:mm:ss>' and date <= '<ToDate;yyyyMMdd hh:mm:ss>'

At parameter SqlSelect to enter the SQL select text with needful tags inside the text;

Additional block's parameters:

Disable - if the parameters is checked, the block will not work when run the diagram;

Block's inputs:

Select - when the input becomes to high level, SQL select will be executed;

Block's outputs:

OutDataSet - contains the received records from executed SQL select;
OnSqlReady - become to high level, when SQL select is executed, data from SQL Server is received and OutDataSet is updated;
NumRecords - number of records at OutDataSet;

OnError - becomes at high level in case of error at SQL select execution;
Error - this is a string output, contains the received from SQL Server error message;

Example: Please load SQLSelect.nsm from Demo diagrams;

Block SQLExecute

License: Hobby, Industrial

The block is suitable for executing SQL Update commands or to execute stored procedures. Block work is also based on the idea of formatting text based on values of block's inputs.

We have to do the next steps with new added to the diagram block:

Choose a working alias in the SqlAlias parameter;
At parameter Inputs to add the needful inputs to the block whose values will be used to format the text of the SQL text;

At parameter SQL to set the text of the executed SQL command. The text can contains TAGs, which to be replaced with input's values at run time of the block;

How to format text with values of the inputs of the block can be read here:

https://scada.altonalab.com/index.php/en/scada-design-mnu/text-formatting-strategy

For example, if we add at parameter Inputs X, Y, Z with analog data type, we can execute the next SQL query:

update TestTable set X=<X;2>, Y=<Y;2>, Z=<Z;2>

The block will replace every TAG as <X;2> with its value gotten from input X. The result formatted text will be with 2 digits after the decimal separator.

Other block's parameters:

Disable - stop the work of the block when diagram runs;
WithCommit - commit the transaction after execution of the SQL query;

Block's inputs:

Execute - execute the query;

Block's outputs:

OnSqlReady - becomes at high level when SQL query is executed;

OnError - becomes at high level in case of error at SQL query execution;
Error - this is a string output, contains the received from SQL Server error message;