This project has moved and is read-only. For the latest updates, please go here.

AdoOutputAdapter error

Apr 26, 2011 at 1:21 PM

Hi,

I'm getting the following error with AdoOutputAdapter:

[AdoOutputAdapter] The parameterized query '(@TAG nvarchar(4000),@TIME nvarchar(24),@VALUE float)INSERT INTO' expects the parameter '@TAG', which was not supplied.

 

These are my settings:

Adapter name: AdoOutputAdapter

Assembly name: AdoAdapters.dll

Type name: AdoAdapters.AdoOutputAdapter

Connection string: tableName=Measurement;dbconnectionString={server=localhost\SQLEXPRESS;database=Historian;uid=my_user_login;pwd=my_user_pwd};dataProviderString={AssemblyName={System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089}; ConnectionType=System.Data.SqlClient.SqlConnection;AdapterType=System.Data.SqlClient.SqlDataAdapter}

 

And I'm not sure if this is gonna help, but here is what I got using sql profiler:

exec sp_executesql N'INSERT INTO Measurement(TAG,TIME,VALUE) VALUES (@TAG,@TIME,@VALUE)',N'@TAG nvarchar(4000),@TIME nvarchar(24),@VALUE float',@TAG=default,@TIME=N'26-abr-2011 09:08:02.900',@VALUE=352304

 

Thanks,

Hugo.

Apr 29, 2011 at 8:15 PM

The goal of the ADO Output Adapter is to be able to use any database as an archive source (e.g., SQL Server, MySQL, Oracle or even OSI-PI via ODBC) - it is designed to be flexible, but has a few requirements for use:

  1. The connection string needs to define a table name in which to store the data.
  2. This table needs to have fields to hold measurement data - possible fields are:
    1. an integer based ID field
    2. a Guid based SignalID field
    3. a string based TagName field
    4. a string based data Source field
    5. a Timestamp field (can be a date/time or long integer)
    6. a boolean TimestampQualityIsGood field
    7. a boolean ValueQualityIsGood field
    8. a double-precision Value field
    9. Any other IMeasurement properties can be defined as well
  3. You are only required to define one field for identification (one of "ID", "SignalID" or "TagName"), then a timestamp field and a value field - other fields are optional
  4. The database field names can be any name, the connection string will map the property name to the field name, for example:
    1. tableName=DataValues; IDFieldName=PointID; TimestampFieldName=Time; ValueFieldName=Value; timestampFormat="dd-MMM-yyyy HH:mm:ss.fff"
  5. A database connection string must be provided in the connection string parameter called dbConnectionString, for example:
    1. dbConnectionString = { Data Source=localhost\SQLEXPRESS; Initial Catalog=openPDC; Integrated Security=SSPI };
  6. A database provider string must be provided to tell the system which ADO assemblies and types to use for the connection, for example:
    1. dataProviderString = { AssemblyName={System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089}; ConnectionType=System.Data.SqlClient.SqlConnection; AdapterType=System.Data.SqlClient.SqlDataAdapter };
  7. You can look in the openPDC.exe.config settings file under the <exampleConnectionSettings> section for examples of database connection and provider strings.
  8. You would then add a new historian output adapter from the openPDC Manager (accessible via Adapters / Historians menu) or the Configuration Setup Utility using:
    1. Type Name = AdoAdapters.AdoOutputAdapter
    2. Assembly Name = AdoAdapters.dll
  9. Once enabled, you should be able to start using your ADO data source as a historian for the openPDC :-)

Hope that helps!

Ritchie

 

May 2, 2011 at 5:09 PM

Hi Ritchie,
Thanks for the reply. It really helped a lot.

I was able to get AdoOutputAdapter running.
But now I'm facing the following problems:

1. When I put the TagName field in the connection string, I get the error message:

"[AdoOutputAdapter] The parameterized query... expects the parameter '@TAGNAME', which was not supplied."

What happens is that the TagName property is null when AdoOutputAdapter.ProcessMeasurements is executed.
Any ideas why?


2. The other problem is When I put the ID field in the connection string.

I get the error:

"The parameter data type of UInt32 is invalid."

I was able to workaround this issue by doing the following change in the AdoOutputAdapter.ProcessMeasurements method:

if (propertyName != "Timestamp")
   if (propertyName == "ID")
      parameter.Value = Convert.ToInt32(value);
   else
      parameter.Value = value;


This error is probably caused by some conversion made by the IdbCommand.

 

Thanks,

Hugo.

May 2, 2011 at 6:45 PM
  1. The specification for tag name in the connection string should look like "; tagNameFieldName=PointTag". However the IMeasurement's flowing through the openPDC do not always populate the TagName metadata since this is considered optional when and an integer based ID and Guid based SignalID already exist. For mapped syncrhophasor measurements from input devices (e.g., PMU's), the TagName is not populated (see: TVA.PhasorProtocols.PhasorMeasurementMapper::MapMeasurementAttributes) but this could be easily added. For mapped OutputMeasurements created from calculated measurements, the TagName is populated from from "PointTag" (see TimeSeriesFramework.Adapters.AdapterBase::ParseOutputMeasurements). This error likely occurs when the the TagName is null.
  2. Nice catch - your database column must be an integer type, not an unisgned integer. I have made the code correction you suggested and checked in the code.

Thanks!
Ritchie