AnsweredAssumed Answered

RDBMS with Parameters - ODBC Error

Question asked by fjmocke on May 15, 2017
Latest reply on May 15, 2017 by cramsey

Hi all,

 

I have a RDBMS setup running to pull data from a database and write the data to PI tags.  The current setup is inefficient as it mixes new and old data together, causing a big performance penalty. 

 

What I've done to increase performance is I created a PI tag to store the maximum ID, and then use that maximum ID in the next RDBMS call as a parameter.  The query looks as follows:

 

DECLARE @prevMaxID as BIGINT

SET @prevMaxID = ?

 

DECLARE @maxId as BIGINT

SET @maxId = (SELECT Max(ID) from [MineCare26].[dbo].[AggregatedOemParamValue])

 

SELECT

[ReadTime] as 'PI_TIMESTAMP',

EquipmentId + '.' + OemInterfaceId + '.' +  OemParamTypeId AS 'PI_TAGNAME',

[MeanValue] as 'PI_VALUE',

0 as 'PI_STATUS'

 

FROM

(

       SELECT

    [pv].[ReadTime],

    [pv].[MeanValue],

    [pv].[NumberOfSamples],

    convert(varchar(256),[it].[Name]) as 'OemInterfaceName',

       convert(varchar(256),[eu].[Name]) as 'EquipmentName',

       convert(varchar(256),[pt].[Name]) as 'OemParamTypeName',

    convert(varchar(256),[it].[Id]) as 'OemInterfaceId',

       convert(varchar(256),[eu].[Id]) as 'EquipmentId',

       convert(varchar(256),[pt].[Id]) as 'OemParamTypeId'

       FROM

       (

              SELECT * FROM [MineCare26].[dbo].[AggregatedOemParamValue] (nolock)

              WHERE Id >= @prevMaxID

       ) pv

 

INNER JOIN [MineCare26].[dbo].[EquipmentUnit] eu on [eu].[Id] = [pv].[EquipmentId]

INNER JOIN [MineCare26].[dbo].[OemParamType] pt on [pt].[Id] = [pv].[OemParamTypeId]

INNER JOIN [MineCare26].[dbo].[OemInterface] it on [pt].[OemInterfaceId] = [it].[Id]

 

) b

 

WHERE [b].[NumberOfSamples] < 10

 

UNION ALL

 

SELECT DATEADD(hour,-2,GETDATE()) AS PI_TIMESTAMP, 'RDBMS.MC_LastID' AS PI_TAGNAME, @maxId AS PI_VALUE, 0 as PI_STATUS

 

ORDER BY 'PI_TIMESTAMP' ASC;

 

The query runs perfectly if I replace the '?' with a value.  The path to the query is given in the Instrument Tag of the distributor tag.  The parameter is specified in the Exdesc of the distributor tag:

 

P1='RDBMS.MC_LastID'/VL

 

Although the setup looks fine to me I'm getting the following error in the log file:

 

Mon May 15 06:31:32 2017 [IFC-W]: SQLDescribeParam> Point - RDBMS.MC:DistributorTag : bindParam () : [S] [07009]: [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index

Mon May 15 06:31:32 2017 [IFC-W]: SQLDescribeParam> Point - RDBMS.MC:DistributorTag : bindParam () : [S] [07009]: [Microsoft][ODBC SQL Server Driver]Invalid parameter number

 

It seems like the problem is on SQL side.  Any idea? 

 

By the way, this thread has a similar problem.  But I don't think mine is related as my RDBMS is actually not working, and I have restarted the interface several times to make sure it is not a tag issue.  Reading about SQL issues, there are discussions that this might be a SQL bug

 

RDBMS PI Interface - Conditional Output

Outcomes