Data Source type SQL use pool connections

[Limraj]

Reason Data Source of SQL type does not use pool connections, it uses its own connection. Also, the connection handling is low-level, we don't use JdbcTemplate there from Spring. This makes it more difficult to manage connections in the system, and can lead to memory leaks or uncontrollable increase in the number of connections and resource blocking that are not used, inefficient use of resources.

[fabiodurao]

This improvement will be greatly appreciated by the community, datasource SQL is still one of the most used ways to perform integrations between different systems.

[Limraj]

This is something to think about and possibly reformulate. For Data Source SQL, one could add the ability to hook up a resource using jndi, where the new resource would be configured in the tomcat server. Then we can configure a pool or other connection method, depending on the needs and the database. However, in the implementation of this Data Source itself, I would add the use of JdbcTemplate.

[fabiodurao]

I don't know what this template would be, but I see two points of attention in this datasource, the need to add the drivers in the scadabr/scada-lts installation folder, originally it only supports mysql and I think postgreesql. Another point of attention is that in a row-based query, select needs to assemble the table with the first column being the name of the variable to be recognized in the datapoint search key, the second column needs to be the value and the third needs to be the timestamp, this is not always so clear to whoever is using it, even more so because most tables have an auto-incrementing id in the first column, so the select would need to be something like: SELECT m.name,m.value,m.timestamp FROM table m

[Limraj]

Okej:

1. Drivers are added to tomcat_home/lib. Generally one could configure a new Data Source (database) in Tomcat, and refer to the configured resource after jndi.

2. As for row-based, it is as you say, but you can easily adjust it in the select query, in the selet we decide which columns to retrieve, in what order and what we name them (select secondColumn as identifier, firstColumn as ts from table where ...) The fact is not very intuitive, especially since we enter a specific line id in Identifier and if select does not return this line first, it will generate an error event. Should search the entire result of the select query;

3. Reading the external row-based database: w select the first column is id, the second is value, the third is timestamp, if we choose column-name, it gets data by column names, on this basis a PointValueTime object is created and saved in our database for a given Data Point. In row-based mode, if the query result is for more than two columns, then it is forced to record the time as in the external database (assumes the third column is ts), if there is no third column it records the time from our system. In the Column-Name mode you can set whether to force the time recording from an external database.

4. Write to an external database -> at the point we can set upade query and apply '?' for the value to be set. For some reason, it doesn't include Identifier here. Can be inserted in an external database.

In my opinion, this significantly limits the use of this datasoruce. For example, I see a configuration that would make sense: selectDescId-insert and column-name, then we insert it into an external database as an update query, for example: insert into table (value, ts) values ​​(?, NOW ()) and we take the last row. If anything changes on this database, select will get the current value.