Write values (row) in Mysql externally LTS

[nex2000]

I am trying to integrate ScadaLTS with my application, to do this I need to read and write the data in the poinValues.pointvalue table.

I installed the ODBC drivers and with a select, respecting the relationship between the tables I can read any value of the datapoint.

But unfortunately I can't write anything, I modify the data on the pointValues ​​table but there is no effect on the watchlist, even if I delete all the data the watchlist continues to read but I don't understand where from.

There is documentation on how to update a value externally. I just want to update Datapoint

I used this query.

Thanks regards

INSERT INTO pointvalues(pointvalue,datapointId,dataType,ts) VALUES (1,7,1,UNIX_TIMESTAMP());

[Limraj]

Hi @nex2000,

1. You need to set an update query, e.g.: (we assume that in the external database there is a point called vdp_1, with type numeric): INSERT INTO pointValues (dataPointId, dataType, pointValue, ts) VALUES ((SELECT id FROM dataPoints WHERE pointName='vdp_1'), 3, ?, UNIX_TIMESTAMP() * 1000) (? will be replaced with the setting value;)

or by xid: INSERT INTO pointValues (dataPointId, dataType, pointValue, ts) VALUES ((SELECT id FROM dataPoints WHERE xid='DP_34234'), 3, ?, UNIX_TIMESTAMP() * 1000)

Now if you set value on point 'sdp_1', this value will be set at point 'vdp_1' in the external database.

2. However, I recommend configuring connection pools in Tomcat and then configuring SQL Data Source after the jndi resource name, and set limit:

Let me know if this solved the problem.

Specific questions should be posted here: https://github.com/SCADA-LTS/Scada-LTS/discussions

Regards, Kamil Jarmusik

[nex2000]

Thanks for your reply but maybe I formulated the question badly.

I don't need to update an external database but I wanted to update a pointvalues ​​in the scadalts database, this point will be updated by an application of mine that connects in ODBC.

I have already inserted some records in the point values ​​table with my updated data but in the watchlist they are not updated they remain the same.

As you can see in the figures the record 11295 has been set to 1 while in the watchlist it remains at 0.

Even deleting all the point values ​​records the watchlist continues to show a value that does not correspond to the start value, I don't understand where you continue reading from.

Thank a lot for your help

[Limraj]

Hi @nex2000, In the configuration I presented, first of all, iam have corrected ts (the key is *1000 because in our system we support time measured in ms, not seconds), and it is worth not relying on the id for the point from an external database, because the id may change etc., and after all, on the watchlist, when you set this point from SQL Data Source, the values ​​are updated. So I guess you didn't carefully analyze what I presented to you here.

If you set a value on point sdp_1, this value will be saved local database and the external database, and it will be updated in the watchlist.

You need to configure it in the same way as I showed.

However, if you enter something outside the application on a local database, of course it will not be visible. You must use the mechanisms available in the application. If you restart the application, you will see these manually added values.

You can create a SQL Data Source that will run on the externa database with Scada-LTS, then you will have an update in the points of this ds according to the update parameter.

database_1 with Scada-LTS1 change value then save in external database and Scada-LTS2 with SQL Data Point watching pointValues from local database for Scada-LTS2 then, depending on the period update, you will have a visible current value on watchlist from Scada-LTS2.

The next time you report something and want to be clearly understood, you put yourself in the shoes of a person who does not know your thoughts and provide the information you would need to recreate this configuration and your activities.

Regards, Kamil Jarmusik

[Limraj]

Scada-LTS2 created SQL Data Source with Data Point sdp_2 with watching pointValue from table pointValues from local database, with change update period:

Scada-LTS1 created SQL Data Source with Data Point sdp_1 with watching and update sdp_2 from external database;

[nex2000]

Hello

Unfortunately I did not understand what was explained because I do not have the version with SQL data source to be able to do internal manipulations.

I only have the SQL window that does not allow me to write to the DB.

Which is the version of LTS that has sql properties?

Thanks

[nex2000]

I found the correct version

thanks

[Limraj]

Hi @nex2000, this is just setting the appropriate parameters of this database, if you set it to a local database, SQL Data Source will work on the local database.

Not all versions include configuration using JNDI resources (from v2.7.5.2), but in these versions you can also set up a local database by providing its url and username/password. However, we recommend using either the stable version v2.6.18 or the latest version v2.7+.

Did this solve the problem?

Regards, Kamil Jarmusik

[nex2000]

Yes now all work fine

Thanks a lot

Paul