<partsFromQuery> Element

This element specifies parts for a component type from a SQL query.

Parent Elements

Attributes

Name	Type	Description	Required
query	string	The SQL query that will be executed against the line database (by default) to retrieve the parts list.	Yes
componentAlternateColumn	string	The name of the query result column that contains the component alternate flag.	No (defaults to `componentAlternate`)
componentModelNumColumn	string	The name of the query result column that contains component model numbers.	No (defaults to `componentModelNum`)
componentSerialNumColumn	string	The name of the query result column that contains component serial numbers.	No (defaults to `componentSerialNum`)
connectionString	string	A connection string used to establish the connection before performing the query specified by `query`. If this is not specified, the default connection string will yield a connection to the line database. The connection string format is dependent on the data provider.	No
descriptionColumn	string	The name of the query result column that contains part descriptions.	No
ignoreBlankComponentRows	boolean	Whether blank component rows should be ignored. Component rows are considered blank if both the component model number and component serial number are blank.	No (defaults to `false`)
providerName	string	A name that specifies which ADO.NET data provider to use for SQL queries. If this is not specified, the default SQL Server provider is used.	No
refreshMinutes	integer	Specifies how often the query will be performed to refresh the parts list. If neither `refreshMinutes` nor `refreshWatchFile` are specified, then Acuit Pinpoint Server will query every fifteen minutes.	No
refreshWatchFile	string	Specifies the full path to a file that Acuit Pinpoint Server will watch for changes; whenever the file modification time changes, the query will be performed to refresh the parts list. If neither `refreshMinutes` nor `refreshWatchFile` are specified, then Acuit Pinpoint Server will query every fifteen minutes.	No
unitModelNumColumn	string	The name of the query result column that contains unit model numbers.	No (defaults to `unitModelNum`)
unitSerialNumColumn	string	The name of the query result column that contains unit serial numbers.	No (defaults to `unitSerialNum`)

Child Elements

None.

Remarks

For providerName, the following providers are available by default with ADO.NET (additional third-party data providers can also be installed):

Provider Name	Description
`System.Data.SqlClient`	The default .NET Framework Data Provider for SQL Server.
`System.Data.OleDb`	The .NET Framework Data Provider for OLE DB.
`System.Data.Odbc`	The .NET Framework Data Provider for ODBC.
`System.Data.OracleClient`	The .NET Framework Data Provider for Oracle.

query can specify any SQL query, as long as it results in a table with column names that match those specified by unitModelNumColumn, unitSerialNumColumn, componentModelNumColumn, componentSerialNumColumn, and componentAlternateColumn (case-insensitive). Refer to <part> Element for descriptions of these attributes. Any of these column names not included as columns in the query will act as if the corresponding part attributes were not specified.

The componentAlternateColumn data value is parsed as follows:

If componentAlternateColumn was not specified, false is used.
Otherwise, if the column is nullable and the field is NULL, false is used.
Otherwise, if the column data type is a boolean type (e.g., a bit SQL Server data type), its value is used directly.
Otherwise:
1. The value is converted to a string, and all leading and trailing whitespace is removed.
2. If the resulting string value is "True", "Yes", "Alternate", "Alt", or "A" (case-insensitive), then true is used.
3. Otherwise, if the value is "False" or "No" (case-insensitive), then false is used.
4. Otherwise, if the value can be parsed as an integer, then true is used if the integer value is non-zero; false is used if the integer value is zero.
5. Otherwise, true is used if the trimmed string value is not empty; false is used if it is empty.

The order of the returned set determines the order in which the parts are added to the parts list.

By default, Acuit Pinpoint Server executes the query every fifteen minutes, as needed, so changes made to the components parts list data while Acuit Pinpoint Server is running will take effect in fifteen minutes or less. refreshMinutes or refreshWatchFile can be used to change this default behavior. Note that both can be specified at the same time. To force Acuit Pinpoint Server to query the components parts list data immediately, do one of the following:

Restart the Acuit Pinpoint Server service, or
Edit the line configuration and save the changes.

Examples

Parts List in SQL Server Database Table

A custom table can be added to the line database to contain the parts list. For example, the following SQL command could be used to create a custom table to hold the compressor models parts list:

CREATE TABLE [CompressorModels] (
    [UnitModelNum] [varchar] (100) NOT NULL,
    [UnitSerialNum] [varchar] (100) NOT NULL,
    [ComponentModelNum] [varchar] (100) NOT NULL,
    [ComponentSerialNum] [varchar] (100) NOT NULL,
    [ComponentAlternate] [bit] NOT NULL
)

The parts list query for this table could be specified like this:

<partsList query="SELECT UnitModelNum, UnitSerialNum, ComponentModelNum, ComponentSerialNum, ComponentAlternate FROM CompressorModels" />

Since providerName and connectionString were not specified, the query acts against the line database. A different SQL Server database can be specified by providing a value for connectionString.

Parts List in Excel Spreadsheet

Warning

A better method of retrieving parts data from an Excel spreadsheet is to use a <table> Element configured with the Excel spreadsheet as the source file, and then retrieve the parts via <partsFromTable> Element.

The parts list can be retrieved from external files using the Microsoft Jet OLE DB data provider. To read the parts list from an Excel file, the parts list query could look like this:

<partsList
  providerName="System.Data.OleDb"
  connectionString='Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\PartsLists\Compressors.xls;Extended Properties="Excel 12.0 Xml;HDR=Yes"'
  query="SELECT UnitModelNum, UnitSerialNum, ComponentModelNum, ComponentAlternate FROM [Sheet1$]" />

The Excel spreadsheet is assumed to have a sheet named "Sheet1" which contains a table of values, with the first row containing headers above the columns named UnitModelNum, UnitSerialNum, ComponentModelNum, and ComponentAlternate.

This requires the 64-bit version of Microsoft Excel 2007 or later (or the 64-bit version of the Microsoft Access Database Engine 2010 Redistributable from http://www.microsoft.com/download/en/details.aspx?id=13255) to be installed on the computer hosting Acuit Pinpoint Server.

Parts List in Comma-separated Values File

The parts list can be retrieved from external files using the Microsoft Jet OLE DB data provider. To read the parts list from a comma-separated values file, the parts list query could look like this:

<partsList
  providerName="System.Data.OleDb"
  connectionString='Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\PartsLists\;Extended Properties="text;HDR=Yes;FMT=Delimited"'
  query="SELECT UnitModelNum, UnitSerialNum, ComponentModelNum, ComponentAlternate FROM Compressors.csv" />

The comma-separated values file is assumed to contain a table of values, with the first row containing headers above the columns named UnitModelNum, UnitSerialNum, ComponentModelNum, and ComponentAlternate.

Table of Contents