Write or insert Value from Geo Scada tag to SQL table

[helmihanibah]

Can anyone help to guide on how I can write or insert value to SQL table from Geo Scada analog tag using SQL query in Geo Scada?

I manage to do select and update statement but how do we insert the existing tag in geo Scada to SQL Table?
How do we define the tag in the query within the Geo Scada?

Attach the sample of Insert query.
Please let me know if I need to provide any additional information on this.

Appreciate anyone can help me on this.

[AndrewScott]

The screenshot shows an ODBC Query database object. The "Query" property shown must be a SELECT as this defines the values which Geo SCADA gets from the external database and are then mapped onto the individual points associated with the query, see Configure the Query Properties in the help.

To update or insert into the external database you must enable controls on the ODBC Analog Point database object (on the "Query" tab of the form) and then enter the UPDATE or INSERT query in the "Query" property on this form, see Define the Query Properties and Use an SQL Query to Insert or Update Data in Another Database  in the help. This SQL is then executed when you control the point.

[helmihanibah]

Hello Andrew,

Thanks for the reply, still new to the Geo Scada and working on my Geo Scada project.
Previously I use Wonderware IDE or InTouch Scada, we can just define the tag in the script after create a tag in tag name dictionary.

So for Geo Scada we need to configure ODBC to link to SQL and I manage to work on select statement from test database and point to tag to display in trend.

Just to be clear, do we have anyway to point the Geo Scada tag by the tag path?
I already Configure ODBC and created the table in SQL.
The configuration is enabled with Min and Max.

The script that i use as below:
Working when static value and is updating the SQL table every 30 second.

Insert into Table
Values
('Text','Text','123','123','123')

Not Working if live analog tag reading value and the SQL table stop updating.

Insert into Table
Values
('Text','Text',Full Analog Geo Tag 1 Path,Full Analog Geo Tag 2 Path,Full Analog Geo Tag 3 Path)

so what you saying is that I can't direct point to the tag by the tag full path?

Do you have any example?

Thank you and Appreciate it.    

[AndrewScott]

The UPDATE or INSERT in the control query is designed to take the value the user enters when issuing the control and write this to the external database (along with the current time) as shown in the example in the help (see Use an SQL Query to Insert or Update Data in Another Database). This is analogous to controlling an analog output point using a traditional protocol like DNP3, IEC 60870-5 or Modbus.

This feature (and the ODBC driver) isn't designed for exporting data from Geo SCADA to an external database.

[helmihanibah]

Okay understood, but is there any features or way in Geo Scada that can write or insert the Geo Scada live tag reading Value to the SQL Database directly?

[sbeadle]

That is what Andrew explained. But maybe your question needs to be more clear. What do you mean directly? ODBC/SQL are direct ways that Geo SCADA uses.

Can you try again to explain what you want to do? Perhaps include a diagram?

[helmihanibah]

Sorry for my poor explanation, what I mean is can I use OPC data as below in my script directly.

[AndrewScott]

You ought to be able to do this using a combination of a linked ODBC table (see Linked ODBC Tables ) and a structured text program (see Inserting, Updating, and Deleting Values by Using SQL in an ST Program ) that reads the point values and then inserts the values into the linked table.

Alternatively, you could create a custom application which is an OPC DA client (to Geo SCADA) and a data access client (to the SQL server) which subscribes to the OPC DA items you're interested in and and then inserts the values into the SQL server. This will likely be more scalable than the first approach.

[sbeadle]

The SCADA ODBC Driver points are like any protocol points such as Modbus or DNP3, except that the data source is an ODBC SQL query. The points are full SCADA points with alarm, value, historic and other data processing.

If you want to read a value from ODBC and put it on screen, this is a simple way to do it, but it will need a licensed point per data value.

Alternatives are to use a Linked ODBC table or use an external script to transfer the data into a Geo SCADA Data Grid or Data Table. But these will make the data available to SQL in script, mimics and Logic. They won't make it available to OPC tags.