Data export through ODBC to other database

[FrankCZ]

Hi all,

We use SCX – Geo Scada Expert 2021 – build 84. We use SQL export to TXT file. We have set up ODBC connection as well and if we use SQL command, we can export some variables to MS Excel. There are only time, date, name of station and value of minimum flow per day.

Our vision is, that would like to use / present time series with „Graphana „ – but Graphana is tool for view some values – these values are saved in database, but Graphana has not ODBC connector.

So, we would like to ask YOU, if somebody has ever solved some „data pump“ from SCX database to other database help with ODBC ? Could you, please, share some idea ?

Our idea is like some „data pump“ connect through ODBC driver/connection to SCX database and with SQL command (we have already) save data from SQL command to some other database, which Graphana is able to use as a source of data. Graphana is open source dashboard for analytics and visualization.

We have set up ODBC connection - Clear SCADA driver.

Thank you for any comments

Frank

[tkswathik · Accepted solution]

Here is one way this can be done, using SQL Server

1. Install the ClearSCADA/GeoSCADA data components on the SQL server you are planning to extract data

2. The above would install the "Configure Connections" utility. Open that and set up a connection to the GeoSCADA server and also set up a system name

3. Set up ODBC source (64 bit preferable) and use the System Name created in Step 2

4. Set up a linked server in SQL, using the ODBC source. In the security provide user credentials that can run queries on GeoSCADA. This user MUST exist in GeoSCADA and have the necessary access permissions. If you are able to create the linked server, without any errors, then you can try to "test" it using the "test connection" option from SQL Sever

5. Once the connection is good, you should be able to use openquery to retrieve from data from GeoSCADA:

Select * from openquery(<name of linked server>, 'Select FullName, Name from CDBPoint where Id = 1234567')

[FrankCZ]

Thank you for advice, we try it !

BR

F