Professional OPC
Development Tools

logos

Online Forums

Technical support is provided through Support Forums below. Anybody can view them; you need to Register/Login to our site (see links in upper right corner) in order to Post questions. You do not have to be a licensed user of our product.

Please read Rules for forum posts before reporting your issue or asking a question. OPC Labs team is actively monitoring the forums, and replies as soon as possible. Various technical information can also be found in our Knowledge Base. For your convenience, we have also assembled a Frequently Asked Questions page.

Do not use the Contact page for technical issues.

Accessing ReadMultipleItems return values

More
31 Jan 2013 08:57 #1215 by admin
The disadvantage of using the QuickOPC-COM over Interop is that you do not get all the benefits of .NET type system, and you end up doing lots of typecasting and other "adaptations" to make it work; but currently we do not have any other way to do it from inside SQL Server.
You basically need to refer to QuickOPC-COM reference documentation instead, and read what the types of returned objects are, and then typecast them accordingly. The situation is made worse that in order to support all possible usage scenarious from various COM controllers, we had to use a plain VARIANT as the type almost everywhere (becomes Object in .NET), and just document what's inside it. In the particular case of ReadMultipleItems, the result is an array (SAFEARRAY in VARIANT) of IDAVtqResult, so you need to cast it to .NET Array type first. There is an example under QuickOPC-COM (named ReadMultipleSqlCom, installed with the product) with similar stuff for SQL stored procedure, and looks like this - I have highligted the interesting parts:
// ReadCurrentDataCom: Reads data of multiple OPC items and returns them in a recordset.
// Instructions:
// 1. Create the QuickOPCExamplesdatabase using \Examples\MSSQL\QuickOPCExamples.sql.
// 2. Create the assymetric key and login by running \Examples\MSSQL\CreateExamplesKey.sql.
// 3. Create the interop assembly in the database by \Examples\MSSQL\CreateInteropAssembly.sql.
// 4. Build and deploy this project from Visual Studio.
// 5. Start the project (runs Test Scripts\Test.sql)
using System;
using System.Data;
using System.Data.SqlTypes;
using System.Diagnostics;
using Microsoft.SqlServer.Server;
using OpcLabs.Interop.EasyOpcLib;


// ReSharper disable CheckNamespace
public class StoredProcedures
// ReSharper restore CheckNamespace
{
[SqlProcedure]
public static void ReadCurrentDataCom()
{
Trace.Assert(SqlContext.Pipe != null);

// Create EasyOPC-DA component.
// If you get "Access is denied" error:
// Start DCOMCNFG, and for applications "EasyOPC-COM 5.2" and "OPCKitServer", configure their security for the SQL
// Server process identity. Typically, this means adding Network Service or Local Service (depending on the account
// the SQL Server uses) to "Launch and Activation Permissions -> Local Launch, Local Activation", and to "Access
// Permissions -> Local Access".
IEasyDAClient easyDAClient = new EasyDAClient();

// Read the OPC items.
var itemIDs = new[] { "Simulation.Random", "Trends.Ramp (1 min)", "Trends.Sine (1 min)", "Simulation.Register_I4" };
var results = (Array)easyDAClient.ReadMultipleItems("", "OPCLabs.KitServer.2", itemIDs, null, 0, "");

// Create a record object that represents an individual row, including it's metadata.
var record = new SqlDataRecord(
new SqlMetaData("ItemID", SqlDbType.NVarChar, 50),
new SqlMetaData("Value", SqlDbType.Variant),
new SqlMetaData("Timestamp", SqlDbType.DateTime),
new SqlMetaData("Quality", SqlDbType.Int));

// Mark the beginning of a result set.
SqlContext.Pipe.SendResultsStart(record);

for (int i = 0; i < results.Length; i++)
{
var vtqResult = (IDAVtqResult)results.GetValue(i);

// In this example, we only return valid data. Production code would also handle errors.
var vtq = (IDAVtq)vtqResult.Vtq;
if (vtq != null)
{
// Populate the record.
record.SetString(0, itemIDs);
if (vtq.Value == null)
record.SetDBNull(1);
else
record.SetValue(1, vtq.Value);
SqlDateTime sqlTimestamp = (vtq.Timestamp < (DateTime) SqlDateTime.MinValue)
? SqlDateTime.MinValue
: vtq.Timestamp;
record.SetSqlDateTime(2, sqlTimestamp);
record.SetInt32(3, vtq.Quality);

// Send the record to the client.
SqlContext.Pipe.SendResultsRow(record);
}
}

// Mark the end of a result set.
SqlContext.Pipe.SendResultsEnd();
}
};

Please Log in or Create an account to join the conversation.

More
30 Jan 2013 17:22 #1204 by rdprecure

How do I access the results of the ReadMultipleItems call below? The Visual Studio object browser shows the ReadMultipleItems parameters and return value to be objects. I tried using a return type of DAVtqresult but I get a type-casting error. Is there another way to do this?
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports OpcLabs.Interop.EasyOpcLib
Partial Public Class UserDefinedFunctions
_
Public Shared Function ReadTrends() As Single
Dim opc As New EasyDAClient
Dim machines as String() = {"", "", ""}
Dim opcservers as String() = {"OPCLabs.KitServer.2", "OPCLabs.KitServer.2", "OPCLabs.KitServer.2"}
Dim items as String() = {"Trends.Ramp (1 s)", "Trends.Ramp (1 min)", "Trends.Ramp (10 min)"}
Dim objresult as Object
objresult = opc.ReadMultipleItems(machines, opcservers, items)
'How do I access the returned values, timestamps and qualities from objresult?
Return 0
End Function
End Class
Thank you.

Please Log in or Create an account to join the conversation.

Moderators: support
Time to create page: 0.050 seconds