ActiveX DLL Data Link

An ActiveX DLL data link retrieves data from a custom .NET Class DLL function or VB6 ActiveX DLL property, which may be developed either by you or by AssurX. The function or property must return data to CATSWeb in the following ways.

• For .NET Class DLLs:
  • Returned DataTable: An ADO.NET DataTable that CATSWeb can extract data from.
  • colHTML: An AssurX Collection that items can be added to. These items allow you to alter the properties and values of fields, controls and forms. Special fields can also be returned via colHTML, such as returning a validation error message.

The property parameter list and return value must comply with a specific format that is detailed elsewhere. Because .NET DataTables and ADO recordsets can be created "on the fly", it is possible to retrieve data from any source, even sources that are not databases. This makes the ActiveX DLL data link the most powerful and flexible type of data link. .NET Class DLLs can be easily created with .NET 2008 or above. ActiveX DLLs can be easily created in Visual Basic (v5.0 min) and other development languages.

When an ActiveX DLL data link is used in conjunction with a Drill Down Link, the ActiveX DLL function will also receive a complete copy of the current record (i.e. the record containing the drill down link that is clicked on). This enables the DLL function to make additional decisions about what data to provide back to CATSWeb for formatting and display.

ActiveX DLLs and their corresponding data links can also be used to perform custom validations and implement other application-specific logic when used in conjunction with Event Hooks.

An ActiveX DLL data link definition has the following parameters:

• Data Link Name - The name of the data link as it is referred to in CATSWeb, with a maximum length of 50 characters. User-defined data link names are enclosed in "[ ]" characters. CATSWeb will automatically add these characters to the name if not provided by you.

• Description - An optional description for the data link (255 characters max).

• Programmatic ID - The programmatic identifier of the .NET Class DLL or ActiveX DLL on the system. An example would be "MyDLL.MyDataClass". Visual Basic programmers will recognize this as the value that would be passed to the intrinsic CreateObject function.
  • Each DLL can have many properties, methods, and functions.

• Property Name - The name of the specific .NET Class DLL function or ActiveX DLL property that CATSWeb will read to retrieve data (such as an ADO.NET DataTable or VB6 ADO recordset).

• Data Field - The name of the field (column) in the DataTable or recordset returned from the DLL function or property that CATSWeb will read values from. If not specified, CATSWeb will read from the first field (column) in the DataTable or Recordset.

Back to Top

ActiveX DLL with Parameters Data Link

The ActiveX DLL with Parameters data link is the same as the ActiveX DLL data link, but also includes 10 optional parameters that may be used for any purpose. The parameters are passed to the DLL in the colHTML collection as items named "Parameter1" through "Parameter10". The parameters will only be passed to the DLL during a post-process call if the alternate method for registering for the call is used.

Back to Top

Calculated Parameter Data Link

A Calculated Parameter data link returns data calculated from internal variables in CATSWeb, based on the other input parameter values provided. Calculated parameters are useful for setting default values and for binding to a field to provide selection lists.

A Calculated Parameter data link definition has the following parameters:

• Data Link Name - The name of the data link as it is referred to in CATSWeb, with a maximum length of 50 characters. User-defined data link names are enclosed in "[ ]" characters. CATSWeb will automatically add these characters to the name if not provided by you.

• Description - An optional description for the data link (255 characters max).

• Parameter 1 - The type of data to calculate.

• Parameter 2-5 - Free-form entry fields where other input parameters may be specified.

These types of calculated parameters are available:

• Calculated Date, Calculated Weekday - These selections provides a date or weekday in the past or future relative to the current date. It is useful as a default for a date field. Enter the following values:

• Parameter 2 - Enter the number of days to add to the current date. For example, entering 7 produces a date 7 days in the future. Entering -34 produces a date 34 days in the past. Leaving this parameter blank or setting it to zero causes the current date to be returned. For a calculated weekday, the date will automatically be advanced forward in time to the next weekday if this parameter results in a weekend day (Saturday or Sunday) being returned.

• Sequential Dates, Sequential Weekdays - This selection provides a sequence of dates (or weekdays), and is useful for binding to a control to provide a selection list of dates. Enter the following values:

• Parameter 2 - Enter the number of dates to produce. For example, entering 12 produces a list of 12 dates.

• Parameter 3 - Enter the number of days to add to the current date to produce the first date in the list. For example, entering 7 causes the first date in the list to be 7 days in the future. Entering -34 causes the first date in the list to be 34 days in the past.

Calculated dates automatically correct for months of varying lengths, leap years, etc. and will only return valid dates. See this application note for an example of using this type of data link.

Back to Top

Session Parameter Data Link

A Session Parameter data link is the simplest type of data link, and it returns a single value from internal CATSWeb variables or constants. Most of the variables are specific to the logged in user, such as their Employee ID, Name, Department, etc. A session parameter data link would typically be used to supply the default value for a field.

A Session Parameter data link definition has the following parameters:

• Data Link Name - The name of the data link as it is referred to in CATSWeb, with a maximum length of 50 characters. User-defined data link names are enclosed in "[ ]" characters. CATSWeb will automatically add these characters to the name if not provided by you.

• Description - An optional description for the data link (255 characters max).

• Parameter - The parameter value to return.

Back to Top

Selected List Data Link

A Selected List data link allows values from a single list to be selected and filtered based on a session parameter. This enables a single list to present different values to different users, based on their department, group, employee ID, etc. See this application note for a detailed example of using a selected list.

A Selected List data link definition has the following parameters:

• Data Link Name - The name of the data link as it is referred to in CATSWeb, with a maximum length of 50 characters. User-defined data link names are enclosed in "[ ]" characters. CATSWeb will automatically add these characters to the name if not provided by you.

• Description - An optional description for the data link (255 characters max).

• List - The name of the List that selections will be taken from.

• Select By - The session parameter to use to select entries with. Whenever the value of this session parameter matches the Item Selector field for a List Item, the item will be included in the displayed list.

Back to Top

Stored Procedure Data Link

The intended use for Stored Procedure data links is to provide read-only data to the CATSWeb system. Stored Procedure data links retrieve data by executing a custom stored procedure in the CATSWeb database, and is expected to return a recordset. The stored procedure may be developed either by you or by AssurX. Stored procedures are very powerful, and can read data from the current database (CATSWeb), another database on the same server, or a database on a remote server. See this application note for a detailed example of using a stored procedure data link. If you plan to access stored procedures in external databases, the Stored Procedure (External) Data Link may be a better choice.

A Stored Procedure data link definition has the following parameters:

• Data Link Name - The name of the data link as it is referred to in CATSWeb, with a maximum length of 50 characters. User-defined data link names are enclosed in "[ ]" characters. CATSWeb will automatically add these characters to the name if not provided by you.

• Description - An optional description for the data link (255 characters max).

• Procedure Name - The name of the stored procedure in the CATSWeb database.

• Parameter 1-4 - Optional parameters that CATSWeb can pass to the stored procedure when it is executed. Parameters are passed in the order that they are defined, and there can be no missing parameters. For example, if you select parameters for Parameter 1 and Parameter 3, but leave Parameter 2 blank, CATSWeb will pass only Parameter 1. Since they are passed by order and not by name, the stored procedure variable that receives the parameter can be named anything you wish. The data type of the variable should be defined to match the data type of the parameter being passed, however. This list of common session parameters shows sample declarations.
  
  The parameters "Field Value" and "Field Name" are only usable when the stored procedure data link is used in conjunction with a Drill Down Link. They cause the current value of the field (or the name of the field) being clicked on to be passed to the stored procedure.
  
  When used with a drill down link, any field value from the current record may be passed to the stored procedure. Enter the names of the table fields in the parameter boxes. Note that the actual table field names must be used, not the captions or control names which may be different. As is the case with session parameters, the data type of the stored procedure variable should be defined to match the data type of the field whose value is being passed. The stored procedure variable does not need to be named the same as the table field, since the data is passed by order rather than by name.
  
  To select parameters from fixed lists (not recommended), check the Edit Using Fixed Parameter Lists box at the top of the form and click the iconic Reload button. If this box is checked when the data link is submitted, the form will open in this mode during future edits of the data link.
  

  Important

  If you enter field names or other parameters that are not present in the fixed parameter lists, then you check the Edit Using Fixed Parameter Lists box and reload the form, your entries will be lost.

• Data Field - The name of the field (column) in the recordset returned from the stored procedure that CATSWeb will read list or default values from. If not specified, CATSWeb will read from the first field (column) in the recordset.

Back to Top

Stored Procedure (External) Data Link

The the Stored Procedure (External) data link is similar to the Stored Procedure data link, but allows for execution of stored procedures in (and retrieval of data from) external databases via the optional Connection String setting. The DBMS (Database Management System) where the stored procedure is located must be of a type supported by the CATSWeb system. Currently, CATSWeb supports various versions and editions of Oracle and Microsoft SQL Server. Refer to CATSWeb System Requirements documents for the actual versions and editions supported. Successfully executing stored procedures in other DBMS types may be possible, but this usage is not supported by AssurX.

See these topics for additional information:

• Stored Procedure Data Links
• Connection String setting
• Using Credentials in Connection Strings
• Application Note - Using Stored Procedure Data Links

Back to Top

SQL Data Link

Like Stored Procedure data links, the intended use for SQL data links is to provide read-only data to the CATSWeb system via SQL Select statements. The SQL statement is defined directly in the data link record. SQL Data Links retrieve data from the CATSWeb database by default, and can retrieve data from other databases and information systems if the optional External Data Connections feature is installed.

A SQL data link has the following parameters:

• Data Link Name - The name of the data link as it is referred to in CATSWeb, with a maximum length of 50 characters. User-defined data link names are enclosed in "[ ]" characters. CATSWeb will automatically add these characters to the name if not provided by you.

• Description - An optional description for the data link (255 characters max).

• SQL - The SQL can contain standard tokens (variables) that allow characteristics of the current user (name, ID, etc.) to be substituted at runtime. Date tokens may also be used to replace single dates (but not entire ranges) in the SQL. Field values from records may also be substituted into the SQL at runtime by specifying the field name between tilde characters like this: ~YourFieldName~. Be sure to specify the field within the Replacement Fields list if you use this technique. The SQL can also use additional tokens that substitute the current user's Record Access Restrictions. For example, the {And My Subtask Record Access Restrictions} token can be used like this:
  
  Select * From SubTasks Where (AssignedTo={My Name}) {And My Subtask Record Access Restrictions}
  
  Issue, Action and Subform record access restrictions may be used in a similar way (replace "Subtask" with "Issue", "Action" or "Subform" in the token).
  
  When the techniques described above are used to substitute values into the SQL, the following rules apply regarding the need for single quotes around the values
  :
  • When using a standard text token such as {My Name} or {My Department}, no single quotes are needed. CATSWeb will automatically include these as needed during the substitution process.
    
  • When using a tilde-delimited field value replacement such as ~YourFieldName~, you should include single quotes around the value if appropriate for the type of value (required for text, not required for numbers).
    
  • When using date tokens, include single quotes as may be needed to make your expression valid. CATSWeb will perform the date replacements without including single quotes.
    
    
• Here are some examples of valid SQL statements:
  
  
  • Select * From All_Issues Where AssignedTo={My Name}
    
    
  • Select SubtaskID From Subtasks Where Title=N'~StandardText001~' And Status=~ShortInteger002~
    
    
  • Select * From SubTasks Where AssignedTo={My Name} And Title=N'~MyTitleParameter~' And ShortInteger001 > ~ShortInteger001~ And (UDDateEdited Between '{Today-20}' And '{Today} 11:59:59 PM') Order By SubtaskID
    
    
• Replacement Fields - If the SQL includes tilde-delimited field replacements such as "~YourFieldName~", enter a comma-delimited list of the field names without the tilde characters (ex: "YourFieldName1, YourFieldName2, YourFieldName3"). This information is required for CATSWeb to successfully process these replacements at runtime.
  
  
• Data Field - The name of the field (column) in the output recordset that CATSWeb will read list or default values from. If not specified, CATSWeb will read from the first field (column) in the recordset.
  
  
• Connection String - This setting will be present if the optional External Data Connections feature is installed. It allows an optional ADO.NET (ActiveX Data Objects) connection string to be specified. If specified, CATSWeb will connect to the database or information system that the connection string points to, and retrieve the data. If not specified, the data will be retrieved from the CATSWeb database. The ConnectionStrings.com Web site provides examples of ADO.NET connection strings for many different databases and information systems.
  
  CATSWeb initiates the ADO.NET connection from the Web server, using the OLE DB provider(s) that are installed and configured on it. If the connection string points at a database of the same type as the CATSWeb database (i.e. Oracle if running CATSWeb on Oracle or SQL Server if running CATSWeb on SQL Server), no additional software will be required to make the connection. If connecting to a different type of database, you may need to install and configure the appropriate OLE DB provider on the Web servers. For example, if CATSWeb is running with SQL Server 2008, but the connection string points to an Oracle 10g database, the Oracle Provider for OLE DB would be required.
  
  The connection string may contain all literal data, or may include variable Credentials via this technique. Note that the technique uses a bracketed CATSWeb fill specification. Other types of fill specifications may be substituted for the Credential Reference fill specification to perform alternative types of lookups and substitutions. For example, a lookup performed by a stored procedure can be implemented with a fill specification like this: "sp|sp_DoMyLookup|{My ID}". The fill specification would need to be enclosed in square brackets before placing it into the connection string, like this: "[sp|sp_DoMyLookup|{My ID}]".
  

Back to Top

URL Data Link

A URL data link is used only in conjunction with Drill Down Links. This type of data link specifies where a user is redirected to when the drill down link is clicked. URL data links do not return data to CATSWeb, so they cannot be used to provide list selections or default values. URL data links may be used to redirect the user to any HTTP (web) resource on the Internet or intranet, including CATSWeb URLs.

A URL data link definition has the following parameters:

• Data Link Name - The name of the data link as it is referred to in CATSWeb, with a maximum length of 50 characters. User-defined data link names are enclosed in "[ ]" characters. CATSWeb will automatically add these characters to the name if not provided by you.

• Description - An optional description for the data link (255 characters max).

• URL - The URL that the user is redirected to, including optional replaceable parameters. URLs are typically fully-qualified, in that they begin with "http://" and contain the full path to the resource. When specifying a URL for a CATSWeb record or function, it is best to specify a relative URL instead of a fully-qualified URL. Examples of CATSWeb URLs are given below.

• URL Parameter Replacements - Optional parameter replacements that CATSWeb makes immediately prior to redirecting the user to the URL. Parameter replacements are in the form of "XYZ=Customer" where "XYZ" are the characters in the URL to replace, and "Customer" is the name of a CATSWeb table field in the underlying data record. In this example, all occurrences of "XYZ" in the URL would be replaced with the value of the Customer field in the underlying data record (e.g. "YourBigCustomerName").
  
  Note that the length of the characters to replace is not at all related to the length of the data that replaces them. The length of the characters to be replaced need only be long enough to guarantee their uniqueness in the URL. CATSWeb replaces those characters with the data specified, regardless of how long that data may be.
  
  Also, the replacement characters should not start or end with the letters A, B, C, D, E or F. These characters are used for URL encoding and may cause erroneous replacements when combined with form data.
  
  When specifying a replacement, the field name must be the actual name of the underlying CATSWeb table field, which is not necessarily the same as the caption that you or AssurX has arbitrarily assigned to the field for display. One means of getting the actual table field name is to run an ad-hoc query and check the Show SQL statement generated box. You may also use database management utilities, Microsoft Access, or another tool to view the actual table definition in the CATSWeb database. The replacements can use any field in the underlying record, not just the fields which are present on the CATSWeb form.
  
  Multiple parameter replacements may be listed and separated (delimited) by a pair of pipe characters ("||"). For example: "XYZ=Customer||ZXQ=PartNumber||QQQ=Process" defines 3 replacements in the URL.
  
  The following special values (tokens) may be used in place of literal field names:

• {Field Value} - This token causes CATSWeb to make the replacement using the value of the current field (i.e. the field that the corresponding Drill Down Link was defined for). For example, if the parameter replacement were specified as "XYZ={Field Value}||ZXQ=PartNumber" and the drill down link was set on the Customer field, the characters XYZ would be replaced with the value of the Customer field in the current record, and the characters ZXQ would be replaced with the part number in the current record, regardless of which field the drill down link was defined on.

• {Field Name} - This token causes CATSWeb to make the replacement using the name of the current field (i.e. the field that the corresponding Drill Down Link was defined for). In the above example, XYZ would be replaced with the literal string "Customer". If the drill down link were instead defined on the LotNumber field, XYZ would be replaced with "LotNumber".

• {Record ID} - This token causes CATSWeb to make the replacement using the record ID of the current record.

• {Record Type} - This token causes CATSWeb to make the replacement using the record type of the current record: Issues=1, Corrective Actions=2, Subtasks=5.

Several session parameters may also be specified as tokens.

When replacing the parameters, CATSWeb will automatically apply URL Encoding to the substituted data. URL Encoding is necessary because URLs cannot accept all characters directly. For example, spaces are not allowed in URLs, so CATSWeb will replace any spaces in the data with the "+" character. Other special characters will be replaced with their encoded values. The web application pointed to by the URL will automatically decode the data back to its original format. Note that the URL encoding performed by CATSWeb applies only to the data replacements you have specified, it does not apply to the entire URL. It is your responsibility to assure that the remainder of the URL is properly formatted.

The following table lists relative URLs which may be used to refer to CATSWeb records. In each case, the character string "ZZZZ" is a placeholder for the CATSWeb record number. The URL Parameter Replacements field should be configured to replace this character string when the URL is used from a drill down link. Or, replace "ZZZZ" with the literal record number if the intention is to always navigate to that specific record:

CATSWeb Record	URL (relative)
Issue	main.aspx?WCI=Main&WCE=ViewIssue&WCU=r%3dZZZZ
Corrective Action	main.aspx?WCI=Main&WCE=ViewCA&WCU=r%3dZZZZ
Subtask	main.aspx?WCI=Main&WCE=ViewSubtask&WCU=r%3dZZZZ