请求协助编写基于定时器的Gamma脚本：实现Cogent Datahub OPC DA域内数据的每秒数据库采集

Got it, here's a practical Gamma script built to meet your needs—it’ll pull every tag from a specified Cogent Datahub OPC DA domain every second, grab their current values and timestamps, and write the batch of data to your database table. I’ve added detailed comments so you can tweak it to match your exact setup:

-- --------------------------
-- Configuration Section - UPDATE THESE VALUES FOR YOUR ENVIRONMENT
-- --------------------------
OPC_SERVER_NAME = "Cogent.Datahub.1"  -- Name of your Cogent Datahub OPC DA server
TARGET_DOMAIN = "YourTargetDomain"    -- Name of the domain containing tags you want to collect
DB_CONNECTION_STRING = "Driver={SQL Server};Server=YourDBServer;Database=YourDBName;Uid=YourUser;Pwd=YourPassword;"  -- Adjust for your DB type (MySQL, Oracle, etc.)
DB_TABLE_NAME = "opc_tag_data"        -- Name of the database table to write data to
COLLECTION_INTERVAL = 1000            -- Collection interval in milliseconds (1000 = 1 second)

-- --------------------------
-- Global Variables
-- --------------------------
opc_client = nil
tag_list = {}
db_connection = nil

-- --------------------------
-- Initialize OPC Connection & Fetch Tag List
-- --------------------------
function init_opc()
    -- Connect to Cogent Datahub OPC DA server
    opc_client = OPC::Connect(OPC_SERVER_NAME)
    if opc_client == nil then
        print("Failed to connect to OPC server: " .. OPC::GetLastError())
        exit(1)
    end
    print("Successfully connected to OPC server: " .. OPC_SERVER_NAME)

    -- Browse all tags in the target domain
    tag_list = OPC::BrowseTags(opc_client, TARGET_DOMAIN .. ".*")  -- Wildcard to get all tags under the domain
    if #tag_list == 0 then
        print("No tags found in domain: " .. TARGET_DOMAIN)
        exit(1)
    end
    print("Found " .. #tag_list .. " tags in domain: " .. TARGET_DOMAIN)
end

-- --------------------------
-- Initialize Database Connection
-- --------------------------
function init_db()
    db_connection = DB::Connect(DB_CONNECTION_STRING)
    if db_connection == nil then
        print("Failed to connect to database: " .. DB::GetLastError())
        exit(1)
    end
    print("Successfully connected to database")
end

-- --------------------------
-- Collect & Write Tag Data to Database
-- --------------------------
function collect_and_write_data()
    -- Read all tags' current values and timestamps
    tag_data = OPC::ReadMultiple(opc_client, tag_list, OPC::VALUE + OPC::TIMESTAMP)
    
    -- Build batch insert query (adjust column names to match your table schema)
    insert_query = "INSERT INTO " .. DB_TABLE_NAME .. " (tag_name, tag_value, timestamp) VALUES "
    values_clauses = {}
    
    for i, tag in ipairs(tag_data) do
        -- Handle different data types (adjust based on your tag data types)
        local value_str
        if type(tag.Value) == "string" then
            value_str = "'" .. string.gsub(tag.Value, "'", "''") .. "'"  -- Escape single quotes for SQL
        else
            value_str = tostring(tag.Value)
        end
        
        -- Format timestamp (adjust format to match your DB's datetime type)
        local timestamp_str = "'" .. os.date("%Y-%m-%d %H:%M:%S", tag.Timestamp) .. "'"
        
        table.insert(values_clauses, "('" .. tag.Name .. "', " .. value_str .. ", " .. timestamp_str .. ")")
    end
    
    -- Combine all value clauses and execute the query
    insert_query = insert_query .. table.concat(values_clauses, ", ")
    local success = DB::Execute(db_connection, insert_query)
    
    if not success then
        print("Failed to write data to database: " .. DB::GetLastError())
    else
        print("Successfully wrote " .. #values_clauses .. " records to database at " .. os.date("%Y-%m-%d %H:%M:%S"))
    end
end

-- --------------------------
-- Main Execution Flow
-- --------------------------
-- Initialize connections
init_opc()
init_db()

-- Start continuous collection loop
print("Starting real-time tag collection...")
while true do
    collect_and_write_data()
    delay(COLLECTION_INTERVAL)  -- Wait for the specified interval before next collection
end

-- Cleanup (will only run if loop is interrupted)
OPC::Disconnect(opc_client)
DB::Disconnect(db_connection)
print("Collection stopped. Connections closed.")

Important Notes to Adjust for Your Setup: #

• Database Table Schema: Make sure your opc_tag_data table has columns matching the insert query:
  • tag_name (VARCHAR/TEXT) to store tag names
  • tag_value (VARCHAR/NUMERIC/TEXT) – adjust based on your tag data types (you might need to handle different types more granularly)
  • timestamp (DATETIME/TIMESTAMP) to store the capture time
• Database Driver: Update the DB_CONNECTION_STRING to match your database type (e.g., use {MySQL ODBC 8.0 Unicode Driver} for MySQL, {Oracle in OraClient19Home1} for Oracle)
• OPC Tag Browsing: The OPC::BrowseTags uses a wildcard (TARGET_DOMAIN .. ".*") to get all tags under the domain. If your domain uses a different hierarchy, adjust the browse path accordingly.
• Error Handling: The script includes basic error logging, but you might want to add more robust handling (e.g., retry logic for lost connections) depending on your production needs.
• Gamma Environment: Ensure your Gamma runtime has access to the Cogent Datahub OPC client libraries and the appropriate database ODBC drivers installed.

内容的提问来源于stack exchange，提问作者Joyal Joy