Mango crash when accessing H2 database from other client

[etantonio]

Hy,
I need to access H2 database with a generic SQL client like SQL Workbench to copy some data from POINTVALUES table while Mango is up and running, I've tried but the result is that Mango crashes when the H2 database is open in SQL Workbench.
How it is possible to avoid this? I'm using H2 database and Mango Version 2.8.4.
DB URL is
jdbc:h2:C:\MA_HOME\databases\mah2
username and password are empty

And also, is it possible to access this database from another PC on the same network?

Thanks
Antonio

[phildunlap]

Hi Antonio,

Have you considered converting the database over to MySQL? If you need to access it independently of Mango, our recommendation is generally to use MySQL. You can convert your database through the env.properties as described https://help.infiniteautomation.com/database-conversions/

[etantonio]

Hy, thanks for your answer,
if possible i prefer to use H2 due to portability considerations,
but if you tell me that it is not possible to access the H2 db from an SQL client while Mango is running, I will go for a mysql installation.

Otherwise I don't know if it is possible for Mango to export all the point values to a csv file or something similar.

Other question, the table POINTVALUEANNOTATIONS what is used for?

Thanks,
Antonio

[phildunlap]

It is not currently possible for H2. It is likely a very simple modification in the H2Proxy class to start the TCP server, but no settings for that are currently expose and we haven't tested it much.

It is possible to request a CSV of point values from Mango, or to have Mango export such a file.

The PointValueAnnotations table is used to store the message about the point value's 'source' if there is one and the point values are being stored in SQL (the NoSQL module is not installed).

[etantonio]

It will be very interesting if inside mango automation I could run automatically such kind of command during the night

call CSVWRITE ( 'h2export.csv', 'SELECT * FROM DATAPOINTS' )

is it possible?

I'm also trying with excel report but it is not exactly what is required actually.
Thanks
Antonio

[phildunlap]

What exactly are you trying to export? There is a blob column in the data points database table that will make a raw select somewhat difficult to read. Do you really mean to export points, or do you need to write a CSV of point values?

Here's a thread where a script reads from CSVs. One could use writers instead of readers to output a CSV instead: https://forum.infiniteautomation.com/topic/2970/is-it-possible-to-read-several-csv-documents-by-scripting

Or, if i get a better grasp on the export you're looking for, there's almost certainly a more direct way.