Database connection format

Save PDF

Last UpdatedFeb 24, 2023
3 minute read

To connect to a database, PI SQL Client OLEDB uses a connection string. The connection string for PI SQL Client OLEDB needs to include:

The name of the PI AF server machine and the database name, for example, Data Source=myAfServer\myAfDatabase;
Optional PI SQL Client OLEDB connection string keywords, for example, Integrated Security=SSPI; Command Timeout=60;

Connection examples

The syntax to use the PI SQL Client OLEDB tables and connect to the PI AF server is:

Provider=PiSqlClient; Data Source=AFServer\AFDatabase;

For example:

PS PIHOME\SQL\SQL Client\OLEDB\Tools> .\iPiSql.ps1 -connectionString
"Provider=PiSqlClient; Data Source=myAFServer\myAFDatabase; Integrated Security=SSPI;"

Standard OLEDB connection string keywords

Connection string keyword	Description	Examples
Data Source	Name of a PI AF server and AF database, separated by a backslash, to connect to.	Data Source=myAFServer\myAFDatabase
Extended Properties	Provider specific properties that can contain all other keywords.	Extended Properties = Command Timeout=60;
Initial Catalog	Initial catalog Note: By default, the data model for PI SQL Client consists of one catalog - Master. This catalog contains a set of tables and table-valued functions. For information about how to create other catalogs, see Generate views, TVFs and function tables for PI SQL Client in the PI SQL Commander Lite User Guide.	Initial Catalog=Master;
Integrated Security	Name of the authentication service. Setting the property to "SSPI" (Security Service Provider Interface) invokes a trusted connection.	Integrated Security=SSPI;
Password	Password to use for authentication when you log on to the PI SQL DAS.	Password=PI;
Prompt	The prompt mode designates whether the provider should ask for the missing information. Note: This property is not persisted in the connection string.
User ID	User ID to use for authentication when you log on to the PI SQL DAS.	User ID=piadmin;
Window Handle	Window handle from the calling application. It is used as a parent window of the dialog prompting for the missing information. Note: This property is not persisted in the connection string.

PI SQL Client-specific connection string keywords

Connection string keyword	Description	Examples
Command Timeout	Command timeout in seconds.	Command Timeout=0; (default; not set) Command Timeout=60;
Locale	The culture (locale) for the connection. The locale determines: Decimal point and group separator for conversion from string to float/double Decimal point for the conversion from float/double to string Decimal point and group separator for Format function used with double The following for Format function used with DateTime: Date separator Time separator Day names Abbreviated day names Month names Abbreviated month names Era names AM/PM designator names Defaults to Invariant, which is associated with the English language, but not with any country or region. For a list of locales available in Windows, see the last table in the Microsoft article: Windows Language Code Identifier Reference: Appendix A.	Locale=Invariant (default) Locale=fr-FR
Protocol Order	Order of the protocols used.	Protocol Order=NetTcp:5465,Https/Soap:5464; (default)
Query Date	Default is latest, but also supports now, and PI time literals.	Query Date=latest Query Date=now Query Date='1h'
Time Zone	Supports Windows time zone format, IANA time zone format, and Local.	Windows time zone format: Time Zone=UTC -08:00) Pacific Time (US & Canada) Time Zone=Local
Maximum String Size	Specifies the maximum string size to be returned, because some clients may not handle long strings correctly. Use 0 for unlimited size. Defaults to 8000.	Maximum String Size=8000 (default) Maximum String Size=0

PI SQL Client OLEDB