PI SQL Client ODBC Administrator Guide

PI SQL Client ODBC
2021
© 2015-2023 by AVEVA Group plc or its subsidiaries. All rights reserved.
No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means,
mechanical, photocopying, recording, or otherwise, without the prior written permission of AVEVA Group plc. No liability
is assumed with respect to the use of the information contained herein.
Although precaution has been taken in the preparation of this documentation, AVEVA assumes no responsibility for
errors or omissions. The information in this documentation is subject to change without notice and does not represent a
commitment on the part of AVEVA. The software described in this documentation is furnished under a license
agreement. This software may be used or copied only in accordance with the terms of such license agreement.
AVEVA, the AVEVA logo and logotype, OSIsoft, the OSIsoft logo and logotype, ArchestrA, Avantis, Citect, DYNSIM, eDNA,
EYESIM, InBatch, InduSoft, InStep, IntelaTrac, InTouch, Managed PI, OASyS, OSIsoft Advanced Services, OSIsoft Cloud
Services, OSIsoft Connected Services, OSIsoft EDS, PIPEPHASE, PI ACE, PI Advanced Computing Engine, PI AF SDK, PI API,
PI Asset Framework, PI Audit Viewer, PI Builder, PI Cloud Connect, PI Connectors, PI Data Archive, PI DataLink, PI DataLink
Server, PI Developers Club, PI Integrator for Business Analytics, PI Interfaces, PI JDBC Driver, PI Manual Logger, PI
Notifications, PI ODBC Driver, PI OLEDB Enterprise, PI OLEDB Provider, PI OPC DA Server, PI OPC HDA Server, PI
ProcessBook, PI SDK, PI Server, PI Square, PI System, PI System Access, PI Vision, PI Visualization Suite, PI Web API, PI
WebParts, PI Web Services, PRiSM, PRO/II, PROVISION, ROMeo, RLINK, RtReports, SIM4ME, SimCentral, SimSci, Skelta,
SmartGlance, Spiral Software, WindowMaker, WindowViewer, and Wonderware are trademarks of AVEVA and/or its
subsidiaries. All other brands may be trademarks of their respective owners.
U.S. GOVERNMENT RIGHTS
Use, duplication or disclosure by the U.S. Government is subject to restrictions set forth in the license agreement with
AVEVA Group plc or its subsidiaries and as provided in DFARS 227.7202, DFARS 252.227-7013, FAR 12-212, FAR
52.227-19, or their successors, as applicable.
PI SQL Client ODBC
Contents
Contents
PI SQL Client ODBC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
PI SQL Client ODBC Administrator Guide. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

Introduction to PI SQL Client ODBC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
About this guide. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
About Developer Technologies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Deployment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
PI SQL Client ODBC installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
System requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Install PI SQL Client. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Download the PI SQL Client setup kit. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Run the PI SQL Client setup kit. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
PI SQL Client silent installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
PI SQL Client installation results. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Verify the PI SQL Client installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Uninstall PI SQL Client ODBC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
PI SQL Client ODBC configuration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Connection to PI AF Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Data source name (DSN) configuration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Connection string format. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
PI SQL Client ODBC operation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Data types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Advanced PI SQL Client ODBC topics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Messaging features. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Turn on messaging features. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
PowerShell code example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
PI SQL Commander Lite. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Connect to. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Run sample SQL queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
PI SQL Client ODBC limitations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Troubleshooting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Connection messages and errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Third-party libraries PI SQL Client ODBC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
PI SQL Client ODBC and Oracle Database Gateway Configuration Guide. . . . . . . . . . . 30

Introduction to PI SQL Client ODBC and Oracle Database Gateway Configuration. . . . . . . . . . . . . . . . . . . . . 30
Deployment options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Install and configure PI SQL Client ODBC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
©2023 AVEVA Group plc and its subsidiaries. All rights reserved. Page 3
PI SQL Client ODBC
Contents
Configure Oracle. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Create a driver Init file (init<sid>.ora). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Configure Oracle Net Listener for the gateway (listener.ora). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Configure the Oracle database for gateway access (tnsnames.ora). . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Stop and start the Oracle Net Listener for the gateway. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Connection to the gateway. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Create database links. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Enable TO_NCHAR capability. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Installation and configuration checklist. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Query optimization and post-processing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Unicode data handling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Workarounds for TO_NCHAR capability. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Workaround 1 - use an explicit cast. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Workaround 2 - use pass-through query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Workaround 3 - change Unicode columns into ASCII. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Variant columns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Comparison with numeric values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Comparison with string literals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Workarounds for comparisons with string literals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Workaround 1 - use native Unicode column. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Workaround 2 - change Unicode columns into ASCII. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Recommendation for variant columns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Oracle specific issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Timestamp conditions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Workaround - specify very small subsecond part. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Oracle resources. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
Logging options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
PI SQL Client ODBC
PI SQL Client ODBC
PI SQL Client ODBC

PI SQL Client ODBC is an ODBC driver that provides robust data access to the PI System through the use of SQL
queries.
PI SQL Client ODBC supports connections and queries for PI AF server via PI SQL Data Access Server (RTQP
Engine). It is a part of PI SQL Client, which also includes PI SQL Client OLEDB and PI SQL Client JDBC.
For details about this release, see the PI SQL Client 2021 Release Notes available on our customer portal
(my.osisoft.com).
PI SQL Client ODBC
PI SQL Client ODBC Administrator Guide

Topics in this section describe information you need to install and administer PI SQL Client ODBC. The
information includes architecture, system requirements, installation instructions, and configuration details.
Introduction to PI SQL Client ODBC

PI SQL Client ODBC is a software component interface that offers data access to the PI System through the use of
SQL queries. PI SQL Client ODBC is part of PI SQL Client.
ODBC is a specification for a database application programming interface (API). This API is not dependent on any
one database management system (DBMS) or operating system. The ODBC API is based on the Common
Language Infrastructure (CLI) specifications from Open Group and ISO/IEC.
PI SQL Client ODBC is an ODBC driver that provides robust data access to the PI System through the use of SQL
queries. This version of PI SQL Client ODBC supports connections and queries for PI AF server.
PI SQL Client ODBC is a member of the Developer Technologies product suite. See About Developer Technologies
for more information.
About this guide

This guide provides procedures on how to install and configure PI SQL Client on Windows operating systems. It
also provides information about how to use and troubleshoot PI SQL Client ODBC.
Users of this guide should be familiar with ODBC technologies.
About Developer Technologies

Developer Technologies are designed to support implementation of custom applications on top of the PI System,
as well as integration of PI System data with other applications and business systems such as Microsoft Office or
SQL Server, Enterprise Resource Planning systems (ERPs), Web portals, and maintenance systems, to name just a
few.
Developer Technologies cover a wide range of use cases in various environments, programming languages,
operating systems and infrastructures. Products include:
• SQL-based data access (PI OLEDB Provider, PI OLEDB Enterprise, PI JDBC Driver, PI ODBC Driver, PI SQL Client
OLE DB, PI SQL Client ODBC, and PI SQL Client JDBC)
• Service-oriented architecture (PI Web API)
• Programmatic access (AF SDK)
Developer Technologies products are available for download from the OSIsoft Customer Portal Products page at
no charge. You can develop applications using the Developer Technologies tools and your PI Server. If you do not
have access to a PI Server, you can obtain development licenses for the PI Server through membership in the PI
Developers Club. For details, see PI Developers Club FAQ.
PI SQL Client ODBC
Architecture
PI SQL Client ODBC is an implementation of the ODBC standard based on the ODBC API.
Communication from PI SQL Client ODBC to the PI System requires PI SQL Data Access Server (RTQP Engine)
installed and running on the AF Server. It provides secure network communication through Net.Tcp or HTTPS to
PI SQL Client ODBC.
Note: For HTTPS connections, PI SQL Client ODBC supports Transport Layer Security (TLS), versions 1.0, 1.1, and
1.2. The TLS version is negotiated with PI SQL DAS and depends on its version and the operating systems
involved.
PI SQL Data Access Server (RTQP Engine) runs on Windows and PI SQL Client ODBC is supported on Windows
operating systems.
Deployment
PI SQL Client can be deployed in various combinations, but requires PI SQL Data Access Server (RTQP Engine) to
connect to the PI System. For details, see the PI SQL Data Access Server (RTQP Engine) Administrator Guide.
PI SQL Client can run on different architectures (32-bit or 64-bit).
Note: OSIsoft recommends using 64-bit operating systems whenever possible. Performance will be better on a
64-bit operating system than on a 32-bit operating system.
PI SQL Client ODBC
PI SQL Client ODBC installation

The PI SQL Client setup kit is distributed as a self-extracting executable file. The setup kit includes both the 32-bit
and 64-bit versions of PI SQL Client OLEDB, PI SQL Client ODBC, PI SQL Client JDBC, and PI SQL Commander Lite.
It also includes the iPiSql tool.
System requirements
Operating Systems
PI SQL Client ODBC is supported on the following operating systems:
• Windows 8.1
• Windows 10
• Windows 11
• Windows Server 2012
• Windows Server 2012 R2
Server Platforms
This release supports connections to PI Server 2018 and later when PI SQL Data Access Server (RTQP Engine) is
installed and running on the server.
For more details about the PI SQL Client release you are using, see the PI SQL Client Release Notes available at
OSIsoft Customer Portal Products page.
Install PI SQL Client

OSIsoft recommends installing PI SQL DAS (RTQP Engine) before PI SQL Client. See the PI SQL Data Access Server
(RTQP Engine) Administrator Guide for more information.
Install PI SQL Client by performing the following steps.
1. Download the PI SQL Client setup kit.

See Download the PI SQL Client setup kit.
2. Run the PI SQL Client setup kit.
See Run the PI SQL Client setup kit.
3. Validate the installation results by reviewing the files installed.
See PI SQL Client installation results.
4. Verify the installation using PI SQL Commander Lite or the iPiSql tool.
See Verify the PI SQL Client installation.
PI SQL Client ODBC
Note: Catalog files are separate downloadable files, and are used for application whitelisting. If you use
whitelisting, install the catalog files associated with your OSIsoft products to ensure that those products
function as intended. Windows catalog files that have been digitally signed by OSIsoft can be used as a digital
signature for 3rd party and other unsigned components installed by OSIsoft setup kits. This facilitates a
trusted way to verify these components and may be used for whitelisting purposes. For information on how
to use catalog files for whitelisting, refer to the OSIsoft Knowledge Base article: Whitelisting PI applications
based on catalog files.
Download the PI SQL Client setup kit

1. Log into the OSIsoft Customer Portal Products page with your OSIsoft credentials.
2. Navigate to Developer Technologies > PI SQL Client.
3. Find the PI SQL Client setup kit corresponding with the version of the software that you want to install and
click Download.
The File Download Details page appears.
4. Select the license type for the setup kit from the drop-down list, and agree to the OSIsoft, LLC. ("OSIsoft")
Software License and Services Agreement.
5. Click Download to start the download.
6. When prompted to run or save the executable (PI SQL Client_2021_.exe) file, click Save and click OK.
Run the PI SQL Client setup kit

The PI SQL Client setup kit includes the following:
• PI SQL Client OLE DB

• PI SQL Client ODBC
• PI SQL Client JDBC
• PI SQL Commander Lite
Procedure
1. Run PI SQL Client_2021_.exe to start the PI SQL Client installation wizard.

2. Click Install.
PI SQL Client silent installation

The PI SQL Client setup kit extracts several installation modules. The components of the installation process,
their order of installation, and the arguments used to start the components are provided in the configuration file
setup.ini. This configuration file is extracted to a sub-folder within the specified extraction path which is, by
default, a temporary folder.
By modifying this file, you can provide different command line arguments for different stages of the setup. This
might be useful within a well-controlled environment with options that are known in advance such as in the case
of an embedded installation.
PI SQL Client ODBC
The setup kit also contains a file named silent.ini that contains modifications to setup.ini that are typically
needed to run a silent installation. You can augment the modification arguments by adding any of the options
described in the table below.
Note: Individual arguments must contain no spaces unless they are surrounded by quotes.
Command-line installation arguments
Argument Description
/i Specifies an installation.
/qn Specifies the "quiet mode" and suppresses dialog

boxes and prompts. No UI is displayed.
/qb Specifies the "quiet mode" and suppresses dialog

boxes and prompts. Only a basic UI with a progress
bar is displayed.
PIPCFOLDER Specifies the PIHOME folder. If PIHOME

(HKEY_LOCAL_MACHINE\SOFTWARE\PISystem)
already exists, setting this argument does not have
any effect.
Use the following syntax for a silent installation of a single component:

msiexec.exe /i PI_SQL_Client_ODBC_x86.msi PIPCFOLDER=c:\MYPI /qn
msiexec.exe /i PI_SQL_Client_ODBC_x64.msi PIPCFOLDER=c:\MYPI64 /qb
Note: To run the complete package in silent mode, replace the setup.ini file with silent.ini and run Setup.exe or
run Setup -f silent.ini from a command prompt.
PI SQL Client installation results

Files are installed into the PIHOME\SQL\SQL Client directory, where PIHOME is replaced by the value of the
PIHOME directory.
Note: On a 64-bit Windows system, a 32-bit version of each PI SQL Client is also installed.
Verify the PI SQL Client installation

PI SQL Client includes the iPiSql command-line tool. You can use either tool to validate a successful installation.
For information about how to validate your installation with PI SQL Commander Lite, see PI SQL Commander
Lite.
The iPiSql command-line tool is located in the Tools directory of the PI SQL Client installation. iPiSql works with
raw connection strings. For more information, see Connection string format.
1. Open a Windows PowerShell command window as an administrator.

Note: If you cannot open Windows PowerShell as an administrator, use PI SQL Commander Lite to validate
the installation. See PI SQL Commander Lite for details.
2. Use cd or set-location to change to the following directory: 'PIHOME\SQL\SQL Client\ODBC\Tools'.
PI SQL Client ODBC
Note: Use single quotes (') around the directory path to allow for spaces in the folder names.
3. Type .\iPiSql.ps1 and press Enter.
You can specify the connection string when you call the tool as follows:
PS PIHOME\SQL\SQL Client\ODBC\Tools> .\iPiSql.ps1 -connectionString "Driver=PI SQL
Client;
AF Server=myAfServer; AF Database=myAfDatabase; Integrated Security=SSPI;"
4. If you did not specify the connection string, enter the following parameters:
• PI Asset Framework Server Name
• PI Asset Framework Database Name
If the installation was successful, you should see results similar to the following:
Connected to PI SQL Data Access Server (RTQP Engine) 2018 SP3 Patch 3, 1.8.21196.1
Driver name: PI SQL Client, version: 4.2.22020.1.
Item: AF SDK Version: 2.10.9.593 FriendlyVersion: 2018 SP3 Patch 3

Item: AF Server Version: 2.10.9.593 FriendlyVersion: 2018 SP3 Patch 3
Item: Master Catalog Version: 2 FriendlyVersion: null
Item: Model Schema Version: 3 FriendlyVersion: null
Item: Operating System Version: 6.2.9200.0 FriendlyVersion: Microsoft Windows 10 Enterprise
Item: Query Processor Version: 1.8.21196.1 FriendlyVersion: 2018 SP3 Patch 3
Item: SQL Server Version: 15.0.2080.9 FriendlyVersion: Microsoft SQL Server 2019 (RTM-GDR)
(KB4583458) - 15.0.2080.9 (X64)
Note: Installation of PI SQL Data Access Server (RTQP Engine) requires you to connect to the PI SQL Client. For
more information, see the PI SQL Data Access Server (RTQP Engine) Administrator Guide.
If PI SQL Data Access Server (RTQP Engine) is not installed or the PI AF Server machine is not accessible, you
should see an error similar to the following:
C:\Program Files\PIPC\SQL\SQL Client\odbc\tools\iPiSql.ps1 : Exception calling "Open" with
"0" argument(s): "ERROR
[08001] None of the protocols could be used to establish a connection using Windows
Integrated Security.
[08004] Communication error with https://FRA2-AF210AC:5464/RTQP/Query occurred.
Could not connect to the server.
[08004] Could not connect to net.tcp://fra2-af210ac:5465/RTQP/Query. The connection attempt
lasted for a time span of
00:00:01.0224491. TCP error code 10061: No connection could be made because the target
machine actively refused it
10.65.3.234:5465.
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [08004] Communication error with https://FRA2-AF210AC:5464/RTQP/Query occurred.
Could not connect to the server.
ERROR [08004] Could not connect to net.tcp://fra2-af210ac:5465/RTQP/Query. The connection
attempt lasted for a time
span of 00:00:01.0224491. TCP error code 10061: No connection could be made because the
target machine actively
refused it 10.65.3.234:5465.
PI SQL Client ODBC
Uninstall PI SQL Client ODBC

The setup kit includes the 32-bit and 64-bit versions of PI SQL Client OLEDB, PI SQL Client ODBC, PI SQL Client
JDBC, and PI SQL Commander Lite. You need to remove each component separately.
1. In the Windows start menu, click Control Panel > Programs and Features, right-click on PI SQL Client ODBC
2021 (x64) and select Uninstall.
The selected component is removed.
2. In the Windows start menu, click Control Panel > Programs and Features, right-click on PI SQL Client ODBC
2021 (x86) and select Uninstall.
The selected component is removed.
PI SQL Client ODBC configuration

Configure PI SQL Client ODBC as described in the following topics.
Connection to PI AF Server
PI SQL Client ODBC allows applications to access the PI System through ODBC. Applications can be the iPiSql test
application provided with the PI SQL Client ODBC installation, a third-party application such as Microsoft Excel, or
an application you have developed.
You can define the ODBC connection using the following methods:
• Data Source Name (DSN)

• Connection string
The type of connection needed is determined by the application that you are using.
To test the connection do one of the following:
• Click Test Connection when you configure the DSN.

• From a third-party application, select the DSN. If you are not using integrated security, you will be prompted
to enter the user name and password for the connection.
• Use a connection string. For example, you can use a connection string with the iPiSql test application.
The TrustedServersOnly registry key, located at HKLM\SOFTWARE\PISystem\PI SQL Client, enables or disables
HTTPS connection to a server with an untrusted certificate. For backward compatibility, the TrustedServersOnly
key is set to 0 by default, which allows connections to servers that use a certificate that is not in the trusted
certificates list of the client machine. As a security best practice, OSIsoft recommends that you set it to 1 to
prevent these connections.
Data source name (DSN) configuration

A data source name (DSN) is a data structure that contains the information about a specific database that an
ODBC driver needs in order to connect to it. The DSN includes the name, directory, driver of the database, and,
PI SQL Client ODBC
depending on the type of DSN, the ID and password of the user. Once a DSN is configured, the ODBC client can
reference it to make the connection.
In contrast, a connection that does not use a DSN requires that all the connection information be specified
within the ODBC client using a connection string.
DSNs can be configured using the ODBC Data Source Administrator. On a 64-bit Windows system, there is a 32-
bit and a 64-bit ODBC Data Source Administrator so that DSNs for 32-bit connections and 64-bit connections can
be configured separately.
To configure a DSN for PI SQL Client ODBC, open the ODBC Data Source Administrator on your machine. Select
the tab for the type of DSN you want and click Add. Select PI SQL Client, click Finish, and then complete the
fields on the PI ODBC DSN Configuration window.
ODBC Data Source definition
The following table provides details about the fields in the ODBC Data Source area of the PI ODBC DSN
Configuration window for PI SQL Client.
Field Description
Name A unique name identifying the DSN. Once you save the
DSN, you cannot change the name.
Description A description to further identify the DSN.
Connection tab
The following table provides details about the fields on the Connection tab.
Field Description
AF Server PI AF server name or Data Archive name. Required.
AF Database Enter the AF database to connect to. Required.
Trusted Connection Check this box to specify that the driver will request a
secure (or trusted) connection to a PI SQL Data Access
Server (RTQP Engine) instance. When selected, PI SQL
Client ODBC uses integrated login security to establish
connections using this data source. Any login ID or
password supplied is ignored.
User The User ID to use for authentication when you log on

to the data source.
Password The password to use for authentication when you log

on to the data source.
Test Connection Click the button to test the connection information

you entered.
Advanced tab
The following table provides details about the fields on the Advanced tab.
PI SQL Client ODBC
Field Description
Initial Catalog Enter the 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
the PI SQL Commander Lite User Guide.
Time Zone Defaults to Local. You can select a specific time zone
used for parsing of timestamp literals and for
rendering of timestamp column values.
Query Date Defaults to Latest. You can enter any PI time

expression. See the PI Server topic, PI time.
Locale Enter 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. You can enter a specific locale. For a
list of locales available in Windows, see the
last table in the Microsoft article: Windows
Language Code Identifier Reference: Appendix
A.
PI SQL Client ODBC
Command Timeout Enter the command timeout in seconds. Enforces the

command timeout for all commands, even if ODBC
statement query timeout was specified.
Network Define the network protocols to use and set the order
in which to use them.
By default, there are two protocols defined: one for
HTTPS on port 5464 and one for Net.TCP on port 5465.
You can have one of each type defined. If you do not
want to use a protocol, delete it to remove it from the
list.
Click on a protocol to select it, then click the button
for the action you want to perform.
• Click Up to move the protocol up in the order.

• Click Down to move the protocol down in the
order.
• Click Edit to change the port number.
• Click Delete to remove the protocol.
• If you have deleted a protocol and want to add it
again, click Add.
Workarounds tab
The following table provides details about the fields on the Workarounds tab.
Field Description
Maximum String Specifies the maximum string size to be returned,

because some clients may not handle long string
correctly. Use 0 for unlimited size. Defaults to 4000.
Variant as String Check this box to expose original Variant columns as

String columns.
GUID as String Check this box to expose original GUID columns as

String columns.
Connection string format

ODBC can use a connection string to connect to a database without using a DSN. The connection string for PI SQL
Client ODBC includes:
• The driver name, for example, Driver=PI SQL Client;

• The AF Server machine name or Location, for example, AF Server=myAfServer; or Location=myAfServer;
PI SQL Client ODBC
• The AF Database name or Data Source, for example, AF Database=myAFfDatabase; or Data

Source=myAfDatabase;
• Optionally, PI SQL Client ODBC connection string keywords, for example, Integrated Security=SSPI; Command
Timeout=60;
The following examples show the syntax for the connection string:
Driver=PI SQL Client; AF Server=myAfServer; AF Database=myAfDatabase; Integrated
Security=SSPI;
Driver=PI SQL Client; Location=myAfServer; Data Source=myAfDatabase; Integrated
Security=SSPI;
Connection string keywords
The following table lists available keywords for the PI SQL Client ODBC connection string.
Connection string keyword Description Examples
Initial Catalog The initial catalog. Initial Catalog=Master; (default)

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 the PI SQL Commander Lite
User Guide.
Integrated Security Name of the authentication Integrated Security=SSPI; (default)
service.
Setting the property to SSPI
(Security Service Provider Interface)
invokes a trusted connection.
Protocol Order Order of the protocols used. Protocol Order=Https/

Soap:5464,NetTcp:5465; (default)
Query Date Defaults to Latest. Also supports Query Date='now';

now and PI time literals. See the PI Query Date='-1d';
Server topic, PI time.
Time Zone Defaults to Local. You can set a Time Zone=(UTC-08:00) Pacific
specific time zone used for parsing Time (US & Canada)
of timestamp literals and for Time Zone=Local
rendering of timestamp column
values.
User ID, User, or UID The User ID to use of User ID=piadmin;

authentication when you log on to
the data source.
PI SQL Client ODBC
Password or Pwd The password to use for Password=PI;

authentication when you log on to
the data source.
FileDSN Specify an ODBC File data source to FileDSN=MyFile.dsn;

use for the connection.
Variant as String True or False (default). Original Variant as String=True;

Variant columns will be exposed as
String columns.
GUID as String True or False (default). Original GUID as String=True;

GUID columns as String columns.
Maximum string size Specifies the maximum string size Maximum string size=2000;
to be returned, because some
clients may not handle long string
correctly. Use 0 for unlimited size.
Defaults to 4000.
Command Timeout Enter the command timeout in Command Timeout=20;

seconds. Enforces the command
timeout for all commands, even if
ODBC statement query timeout
was specified.
PI SQL Client ODBC operation

The topics in this section describe the use of PI SQL Client ODBC, including advanced topics such as
multithreading and multiple connections, messaging features, and a source code example. Short descriptions of
PI SQL Commander Lite and PI SQL Client ODBC limitations are also included.
The data model and SQL capabilities depend on the PI AF server version you are using. The latest version
provides the most capabilities and functionality. For more information, see the SQL for RTQP Engine Reference
Guide.
Data types
PI SQL Client ODBC supports the following data types:
Name Data Type Indicator
AnsiString SQL_C_CHAR
AnsiStringCs SQL_C_CHAR
Boolean SQL_C_BIT
PI SQL Client ODBC
DateTime SQL_C_TYPE_TIMESTAMP
Double SQL_C_DOUBLE
Guid SQL_C_GUID
Note: If Guid as String is used in the DSN or
connection string, this is SQl_C_WCHAR instead.
Int8 SQL_C_TINYINT
Int16 SQL_C_SHORT
Int32 SQL_C_LONG
Int64 SQL_C_BIGINT
Single SQL_C_FLOAT
String SQL_C_WCHAR
StringCs SQL_C_WCHAR
TimeSpan SQL_C_TIME
UInt8 SQL_C_UTINYINT
UInt16 SQL_C_USHORT
UInt32 SQL_C_ULONG
UInt64 SQL_C_UBIGINT
Variant SQL_SS_VARIANT
Note: If Variant as String is used in the DSN or
connection string, this is SQL_C_WCHAR instead.
Note: PI SQL Client ODBC supports columns that have DBTYPE_VARIANT as the underlying OLE DB data type. PI
SQL Client ODBC uses the same approach as Microsoft ODBC drivers and the underlying SQL Server data type
sql_variant.
Applications developed in .NET using namespace System.Data.ODBC automatically recognize an Object column.
You can use the GetType method to recognize the contained subtype.
For native code applications, details can be found in the technet article, Mapping Data Types (ODBC).
Applications that use binding usually bind the column to a string data type.
PI SQL Client ODBC
Advanced PI SQL Client ODBC topics

Multithreading
PI SQL Client ODBC supports query execution from multiple threads. OSIsoft recommends you reuse connection
objects as often as possible because connection open and close operations are expensive. For example,
connection objects can be passed as parameter to a new thread and when that thread is finished, it returns that
connection to a connection pool.
Multiple connections
Multiple connections from one PI SQL Client ODBC application to different PI SQL Data Access Server (RTQP
Engine) (installed on multiple nodes) are supported.
ODBC driver and variant data types

PI SQL Client ODBC supports columns that have DBTYPE_VARIANT as the underlying OLE DB data type. PI SQL
Client ODBC uses the same approach as is used for Microsoft ODBC drivers and the underlying SQL Server data
type sql_variant.
Applications developed in .NET, using namespace System.Data.ODBC automatically recognize such a column as
Object. To recognize the contained subtype, you can use the GetType method describe in the Microsoft article
GetType method.
For native code applications, see the Microsoft article Mapping Data Types (ODBC).
Applications that use binding usually bind the column to a string data type.
Messaging features
PI SQL Client ODBC uses Event Tracing for Windows (ETW) for function tracing and event logging.
Turn on messaging features

PI SQL Client ODBC offers Function Tracing using Event Tracing for Windows (ETW). Use the following procedure
to turn on messaging.
1. From Windows, start Event Viewer.

2. Click View > Show Analytic and Debug Logs.
PI SQL Client ODBC
3. In the left pane, expand Applications and Services Log > OSIsoft > SQL > PI SQL Client ODBC > Debug
Channel.
4. Right-click Debug and select Enable Log.
PI SQL Client ODBC
5. If a confirmation prompt appears, click OK.
Now all PI SQL Client ODBC activity gets logged. Use any Event Tracing for Windows (ETW) tool for analyzing the
trace events.
PowerShell code example

The source code for the iPiSql.ps1 test application is located at PIHOME\SQL\SQL Client\ODBC\Tools. You can
execute iPiSql.ps1 in the Windows PowerShell ISE (Integrated Scripting Environment) to step through the code as
shown below.
Source code example of iPiSql.ps1 test application
PI SQL Client ODBC
PI SQL Commander Lite

PI SQL Client includes PI SQL Commander Lite, an application you can use to validate your installation, get to
know the product, and develop test queries.
PI SQL Client ODBC
PI SQL Commander Lite includes a Query Compendium that you can use to learn about and test PI SQL
Commander Lite without affecting your real data.
For more information or to get started with the Query Compendium, see the PI SQL Commander Lite User Guide.
Connect to
The following procedure describes how to connect to PI AF server using PI SQL Client ODBC through PI SQL
Commander Lite. You can use a DSN to establish this connection. For more information, see the PI SQL
Commander Lite User Guide.
1. To open PI SQL Commander Lite, click Start > All Programs > PI System > PI SQL Commander Lite (64-bit).
2. In the Object Explorer pane, expand ODBC Data Sources, right-click PI SQL Client and click Connect.
3. In the PI SQL Client ODBC Login window, in the AF Server field, type the name of your PI AF server machine.
4. In the AF Database field, type the name of the PI AF database or select one from the drop-down list.
Note: The drop-down list in the AF Database field is available only if PI Web API Core Services is installed.
5. In the Authentication area, provide credentials for a Windows user account or select Trusted Connection to
use your Windows credentials to connect to PI AF server.
6. Optional: Click the Advanced tab to specify additional connection parameters such as the initial catalog,
command timeout, or protocol order.
PI SQL Client ODBC
Note: To access the various PI AF data sources, PI System data references, and so on, you must enter login
information for an account with valid permissions for the machine that runs PI AF server.
For more information, see the topic "Firewalls and PI AF Security" in the PI Asset Framework (PI AF)
Installation and Maintenance Guide available at the OSIsoft Customer Portal Products page.
7. Click OK.
A green arrow next to the data source icon indicates that the connection is successful.
Note: Installation of PI SQL Data Access Server (RTQP Engine) requires you to connect to the PI SQL Client.
For more information, see the PI SQL Data Access Server (RTQP Engine) Administrator Guide.
If PI SQL Data Access Server (RTQP Engine) is not installed or the PI AF Server machine is not accessible, you
should see an error similar to the following:
Run sample SQL queries

PI SQL Client ODBC includes one sample SQL query for each table in the catalog for PI AF server connections. Use
these queries to explore PI SQL Commander Lite functionality, or edit and save these queries to use as a custom
query.
1. In the Object Explorer pane, expand the schema under ODBC Data Sources > PI SQL Client.
PI SQL Client ODBC
2. In the Catalogs folder, right-click either a table or a view, and click Execute Predefined Query.
PI SQL Client ODBC
Note: You can edit a sample query and save it as a customized query. For information about two additional
collections of sample queries that you can load onto your system using PI SQL Commander Lite, see the PI
SQL Commander Lite Help.
3. View the query in the Query pane and its results in the Results pane.
PI SQL Client ODBC
PI SQL Client ODBC limitations

Data types
The following data types are not supported:
• .NET (AF) Object

• COM Object
• VARIANT containing GUID
• VARIANT containing timestamp
Transactions
Manual transactions are not supported. Queries should be executed in auto-commit mode. If an application
cannot use auto-commit, be aware that queries are executed immediately and commit has no effect. This also
means that rollback has no effect.
Batch processing
Batch processing is currently not supported with the RTQP Engine.
PI SQL Client ODBC
Troubleshooting
To identify solutions when PI SQL Client ODBC does not connect or does not get data, OSIsoft recommends that
you test components in the order they are used as described here:
1. Run PI SQL Data Access Server (RTQP Engine) interactively to see more information, debug output, or use the
PI SQL DAS (RTQP Engine) Event Log to check for error messages.
2. For more information, see the PI SQL Data Access Server (RTQP Engine) Administrator Guide.
Note: When PI SQL Data Access Server (RTQP Engine) is run interactively, Kerberos authentication cannot be
used.
3. For PI SQL Client ODBC, enable logging for the driver.
4. Install PI SQL Client ODBC on the same computer as PI SQL Data Access Server (RTQP Engine).
Connection messages and errors

Common connection messages, including error messages that indicate connection problems, are provided here.
The messages are visible in Windows PowerShell as output of the iPiSql.ps1 sample application. Different PI SQL
Client ODBC applications might display below messages in a message box or log file, depending on how those
applications handle exceptions.
Successful connection
The following message occurs when a successful connection is established:
Connected to PI SQL Data Access Server (RTQP Engine) 2018 SP3 Patch 3, 1.8.21196.1
Driver name: PI SQL Client, version: 4.2.22020.1.
Item: AF SDK Version: 2.10.9.593 FriendlyVersion: 2018 SP3 Patch 3

Item: AF Server Version: 2.10.9.593 FriendlyVersion: 2018 SP3 Patch 3
Item: Master Catalog Version: 2 FriendlyVersion: null
Item: Model Schema Version: 3 FriendlyVersion: null
Item: Operating System Version: 6.2.9200.0 FriendlyVersion: Microsoft Windows 10 Enterprise
Item: Query Processor Version: 1.8.21196.1 FriendlyVersion: 2018 SP3 Patch 3
Item: SQL Server Version: 15.0.2080.9 FriendlyVersion: Microsoft SQL Server 2019 (RTM-GDR)
(KB4583458) - 15.0.2080.9 (X64)
Bad protocol order

The following error message occurs if the specified protocol does not exist or if the spelling is wrong:
ERROR [HY000] [PI SQL Client] Bad protocol order.
No AF Server specified or misspelled

The following error message occurs if the AF Server was not specified or if it was misspelled:
ERROR [01S00] [PI SQL Client] The specified AF Server is invalid.
PI SQL Client ODBC
None of the protocols could be used to establish a connection using the provided user name
and password
The following error message occurs if the specified User ID, or Password, or both do not exist, or if the spelling is
wrong:
ERROR [08001] None of the protocols could be used to establish a connection using the
provided user name and password.
None of the protocols could be used to establish a connection using Windows Integrated
Security
The following error message occurs if Windows Integrated Security is not available for the specified data source:
ERROR [08001] None of the protocols could be used to establish a connection using Windows
Integrated Security.
Third-party libraries PI SQL Client ODBC

The list below provides information about third-party libraries that PI SQL Client ODBC includes.
Boost Libraries
PI SQL Client includes the Boost libraries.
gSOAP Library
PI SQL Client includes the gSOAP library.
Note: The license and copyright information documents are located in the PIHOME\SQL\SQL Client\ODBC\Doc
folder, where PIHOME is your PIPC installation directory.
PI SQL Client ODBC
PI SQL Client ODBC and Oracle Database Gateway Configuration Guide
PI SQL Client ODBC and Oracle Database

Gateway Configuration Guide
Topics in this section describe how to configure the Oracle Database Gateway for ODBC with PI SQL Client ODBC,
so that you can access your PI system data from Oracle.
This reference guide is supplemental to the
.
Introduction to PI SQL Client ODBC and Oracle Database Gateway

Configuration
PI System data can be accessed from Oracle via the Oracle Database Gateway for ODBC (DG4ODBC), which is
part of the Oracle Heterogeneous Connectivity. The Oracle Database Gateway is available with Oracle 11g, 12c,
18c, and 21c distributions and can also be installed on a different computer than the Oracle database server.
This document describes how to use PI SQL Client ODBC driver to access a PI System from Oracle 18c through
DG4ODBC for Windows. The document assumes that the Oracle database server is configured and the DG4ODBC
for Windows components are already installed on a Windows computer.
PI SQL Client ODBC
Deployment options
The gateway can be deployed with other components in various combinations. PI SQL Client ODBC and Oracle
Database Gateway for ODBC must be installed on the same Windows computer but other components can be
distributed on your network, as needed. These other components include:
• Oracle Database Server

• Oracle Database Gateway (included in Oracle Database Server or separate install)
• PI SQL Data Access Server (RTQP Engine)
• PI AF server (and Microsoft SQL Server)
• Data Archive
Installation
To connect Oracle to a non-Oracle system through DG4ODBC for Windows:
PI SQL Client ODBC
• Install and configure the PI SQL Client ODBC driver on the computer where DG4ODBC for Windows is
installed.
• Configure Oracle:
a. Configure the gateway init file (init*.ora).
b. Configure Oracle Net for the gateway (listener.ora).
c. Configure the Oracle database for gateway access (tnsnames.ora).
• Create a database link.
Note: DG4ODBC can be installed as 32-bit version or 64-bit version.
Install and configure PI SQL Client ODBC

For complete instructions about the PI SQL Client ODBC installation, see the PI SQL Client ODBC Administrator
Guide.
1. Install the PI SQL Client ODBC driver on the machine where DG4ODBC for Windows is installed.
2. In the Windows ODBC Administrator, add a System ODBC data source (DSN) that connects to the target
database.
On 64-bit Windows systems, both 32-bit and 64-bit versions of ODBC Administrator are available. Make sure
you use the platform that corresponds to the DG4ODBC platform.
3. On the Workarounds tab, select Variant as String and set Maximum String Size to 1333.
PI SQL Client ODBC
4. On the Connections tab, click Test Connection to test the connection to the data source.
Configure Oracle
To configure DG4ODBC for Windows, you must edit three files and restart the Oracle Listener. The files are:
PI SQL Client ODBC
• init<sid>.ora
• listener.ora
• tnsnames.ora
The init<sid>.ora file references the System ODBC data source that you added in the ODBC Administrator. You
manually add connection descriptors to tnsnames.ora, and then reference those in listener.ora.
Caution: An incorrect entry in these files can lead to connection failure.
Create a driver Init file (init<sid>.ora)

You must create an initialization file for your Oracle Database Gateway for ODBC. Oracle supplies a sample
initialization file named: initdg4odbc.ora. The sample file is stored in the ORACLE_HOME\hs\admin directory.
To create an initialization file for the ODBC gateway, copy the sample initialization file and rename it to:
initsid.ora, where sid is the system identifier (SID) you want to use for the instance of the non-Oracle system to
which the gateway connects.
The gateway system identifier (SID) is an alphanumeric character string that identifies a gateway instance. You
need one gateway instance, and therefore one gateway SID, for each ODBC source you are accessing.
If you want to access two ODBC sources, you need two gateway SIDs, one for each instance of the gateway. If you
have only one ODBC source but want to access it sometimes with one set of gateway parameter settings, and
other times with different gateway parameter settings, then you will need multiple gateway SIDs for the single
ODBC source. The SID is used as part of the file name for the initialization parameter file.
Examples
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=pisqlclient
HS_FDS_TRACE_LEVEL=DEBUG
HS_FDS_TIMESTAMP_MAPPING=TIMESTAMP
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
Where:
Parameter Description
HS_FDS_CONNECT_INFO pisqlclient is the name of the system DSN defined in

the Microsoft Windows ODBC Data Source
Administrator.
HS_FDS_TRACE_LEVEL Specifies the level of tracing. You should generally

leave this commented out, as tracing can degrade
performance.
PI SQL Client ODBC
In this example, it is enabled to be able to verify how

test queries are mapped to PI SQL Client ODBC.
This setting generates a log file for each SQL*Plus
connection. Gateway log files are stored in the
ORACLE_HOME\hs\trace folder. For example,
ORACLE_HOME\hs\trace\piodbc_agt_516.trc.
HS_FDS_TIMESTAMP_MAPPING By default, Oracle maps the SQL_TYPE_TIMESTAMP

ODBC data type into DATE, which does not support
fractional seconds. To get the full precision, this
setting changes the mapping into the TIMESTAMP
data type.
Configure Oracle Net Listener for the gateway (listener.ora)

The Oracle Listener monitors incoming requests from the Oracle database. For the Oracle Listener to monitor
DG4ODBC, information about DG4ODBC must be added to the Oracle Listener configuration file:
ORACLE_HOME\network\admin\listener.ora. Use the following steps:
1. Create a SID_NAME for DG4ODBC.

2. Specify the executable that the listener should start in response to DG4ODBC connection requests.
Examples
# listener.ora Network Configuration File:
C:\WINDOWS.X64_180000_db_home\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = dg4odbc)
(SID_NAME = pisqlclient)
(ORACLE_HOME = C:\WINDOWS.X64_180000_db_home)
)
…
Where:
Variable Description
PROGRAM Specifies the executable name of the Oracle Database

Gateway for ODBC.
SID_NAME The name of the gateway init file without the init
prefix or ora extension. In the example, the init file is
named initpisqlclient, and so pisqlclient is used as
the SID_NAME.
PI SQL Client ODBC
ORACLE_HOME Specifies the Oracle home directory where the

gateway resides.
Configure the Oracle database for gateway access (tnsnames.ora)

Before you use the gateway to access an ODBC data source, you must configure the Oracle database to enable
communication with the gateway over Oracle Net.
To configure the Oracle database, add connection descriptors to the tnsnames.ora file.
By default, this file is in ORACLE_HOME\network\admin, where ORACLE_HOME is the directory in which the
Oracle database or Gateway is installed. You cannot use the Oracle Net Assistant or the Oracle Net Easy Config
tools to configure the tnsnames.ora file; you must edit the file manually.
Examples
# tnsnames.ora Network Configuration File:
C:\WINDOWS.X64_180000_db_home\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
pi =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora2gtw.osisoft.int)(PORT = 1521))
)
(CONNECT_DATA =
(SID = pisqlclient)
)
(HS = OK)
)
…
Where:
Variable Description
pi The connect descriptor for DG4ODBC. You include this

entry when creating the database link with SQL*Plus.
The SID_NAME is referenced from listener.ora.
PROTOCOL TCP is used as the protocol for TCP/IP connections.
HOST Specifies the machine where the gateway is running.
PORT Matches the port number used by the Oracle Net

Listener that is listening for the gateway. The Oracle
Net Listener's port number can be found in the
listener.ora file used by the Oracle Net Listener.
PI SQL Client ODBC
SID Specifies the SID of the gateway and matches the SID
specified in the listener.ora file of the Oracle Net
Listener that is listening for the gateway.
HS = OK Specifies that this connect descriptor connects to a

non-Oracle system.
Stop and start the Oracle Net Listener for the gateway
You must stop and restart the Oracle Net Listener to initiate the new settings.
Note: For configuration of multiple listeners or multiple ODBC data sources, consult the Oracle Database
Gateway Installation and Configuration Guide.
1. From the Start menu, select Settings > Control Panel > Services.
2. Select the Oracle Net Listener service for the gateway.
3. If the service is already running, click Stop to stop it.
4. Click Start to start or restart the service.
Connection to the gateway

A connection to the gateway is established through a database link when it is first used in an Oracle session. In
this context, a connection refers to the connection between the Oracle database and the gateway. The
connection remains established until the Oracle session ends. Another session or user can access the same
database link and get a distinct connection to the gateway and ODBC data source. If you want to close a
database link during a session, you can do so with the ALTER SESSION statement.
If you want to check for existing database links you can use this query:
select * from all_db_links;
PI SQL Client ODBC
Create database links

Any Oracle client that is connected to the Oracle database can access an ODBC data source through the gateway.
The Oracle client and the Oracle database can reside on different machines. The gateway accepts connections
only from the Oracle database.
To access the ODBC data source, you must create a database link. A public database link is the most common of
database links.
Connect to SQL*Plus as if you are connecting to your regular database. At the SQL prompt, create a new
database link.
For example:
CREATE PUBLIC DATABASE LINK pi
CONNECT TO "Administrator" IDENTIFIED BY "password"
USING 'pi';
The command creates an Oracle database link named pi, referencing the pi entry in tnsnames.ora.
Note: It is also possible to create private database links. For details consult the Oracle Database SQL Language
Reference, CREATE DATABASE LINK statement.
Enable TO_NCHAR capability

SQL statements are translated and data types are mapped according to capabilities. Capabilities tell
Heterogeneous Services about the limitations of the non-Oracle system (such as what types of SQL statements
are supported) and how to map Oracle data types and SQL expressions to their non-Oracle system equivalents.
One capability that is disabled but is needed for PI SQL Client ODBC is the TO_NCHAR capability. When disabled,
comparison of strings with columns of type SQL_WCHAR or SQL_WVARCHAR will be handled by Oracle in the
post-processing part.
Note: See Unicode data handling for limitations and workarounds if you cannot or do not want to enable the
TO_NCHAR capability for DG4ODBC.
1. To enable the TO_NCHAR capability, connect to SQL*Plus as if connecting to your regular database, using the
sysdba account.
2. At the SQL prompt, load the stored procedure called enable_to_nchar.sql that is provided in the samples
directory, PIHOME\SQL\SQL Client\ODBC\Samples\Oracle.
Examples
The following example shows the enable_to_nchar.sql stored procedure:
SQL*Plus: Release 18.0.0.0.0 - Production on Tue May 7 12:26:40 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Enter user-name: system

Enter password:
Last Successful login time: Tue May 07 2019 12:22:30 +02:00
Connected to:
PI SQL Client ODBC
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production Version 18.3.0.0.0
SQL> @"c:/program files/pipc/sql/sql client/odbc/samples/oracle/enable_to_nchar.sql";

TO_NCHAR capability is enabled
PL/SQL procedure successfully completed.
SQL>
Installation and configuration checklist

The following checklist enumerates all items that are specific to the configuration for PI SQL Client ODBC. This list
is helpful if you are familiar with the standard configuration as provided in the Oracle document, Oracle
Database Gateway Installation and Configuration Guide.
• Configure an ODBC DSN, select the Variant as String workaround and set the Maximum String Size value to
1333.
• Create an init<sid>.ora file that includes the following specific setting:
HS_FDS_TIMESTAMP_MAPPING = TIMESTAMP
• Reference ODBC sid in listener.ora and tnsnames.ora (all standard, no specifics for PI SQL Client ODBC).
• Create the Database Link (all standard, no specifics for PI SQL Client ODBC).
• Enable the TO_NCHAR capability.
• Take special care when constraining string type columns.
Query optimization and post-processing

Oracle makes certain assumptions about the capabilities of the ODBC driver referenced by DG4ODBC. Not
everything can be influenced by configuration. As a result, queries that work well in other environments may
show low performance or even timeout in Oracle. This is usually due to the fact that Oracle performs post-
processing on certain query conditions, meaning it retrieves more data and applies remaining filter conditions
locally.
This section helps you to understand what is going on in the background and explains how to avoid post-
processing.
Unicode data handling

By default, the TO_NCHAR capability is disabled and comparison of strings with columns of type SQL_WCHAR or
SQL_WVARCHAR is handled by Oracle in post-processing.
Note: PI SQL Client ODBC exposes string columns in all tables as Unicode (SQL_WVARCHAR). In addition,
VARIANT columns can also be exposed as Unicode.
For example, let's look at the following query:
SELECT e."Name" Element, ea."Name" Attribute,
a."TimeStamp", a."Value", a."Value_Double"
FROM Element.Element@pi e
INNER JOIN Element.Attribute@pi ea ON ea."ElementID" = e.ID
PI SQL Client ODBC
INNER JOIN Element.Archive@pi a ON a."AttributeID" = ea.ID

WHERE a."TimeStamp" BETWEEN '25-APR-2019' AND '26-APR-2019'
AND e."Name" = 'Houston'
AND ea."Name" = 'Environment'
ORDER BY e."Name", ea."Name";
Using the NuGreen AF sample database, the query above will take much longer than expected when compared
with execution time in PI SQL Commander Lite. That is because Oracle removes e."Name" = 'Houston' AND
ea."Name" = 'Environment' from the remote query. In fact, Oracle splits the query into multiple remote queries
and performs the JOIN locally.
Note: You can verify the remote query generated by Oracle by monitoring the log or trace files created using
Oracle Database Gateway logging options.
Checking with the Oracle trace file shows that situation:
.
.
.
SQL text from hgopars, id=1, len=43 ...
00: 53454C45 43542022 4944222C 224E616D [SELECT "ID","Nam]
10: 65222046 524F4D20 22454C45 4D454E54 [e" FROM "ELEMENT]
20: 222E2245 4C454D45 4E5422 ["."ELEMENT"]
.
.
.
10: 65222C22 456C656D 656E7449 44222046 [e","ElementID" F]
20: 524F4D20 22454C45 4D454E54 222E2241 [ROM "ELEMENT"."A]
30: 54545249 42555445 22 [TTRIBUTE"]
.
.
.
00: 53454C45 43542022 41747472 69627574 [SELECT "Attribut]
10: 65494422 2C225469 6D655374 616D7022 [eID","TimeStamp"]
20: 2C225661 6C756522 2C225661 6C75655F [,"Value","Value_]
30: 446F7562 6C652220 46524F4D 2022454C [Double" FROM "EL]
40: 454D454E 54222E22 41524348 49564522 [EMENT"."ARCHIVE"]
50: 20574845 52452022 54696D65 5374616D [ WHERE "TimeStam]
60: 70223E3D 3F20414E 44202254 696D6553 [p">=? AND "TimeS]
70: 74616D70 223C3D3F [tamp"<=?]
.
.
.
If the TO_NCHAR capability has been enabled as shown in the previous section, then execution time is as
expected and the Oracle trace file shows that all constrains have been used in the remote query:
000: 53454C45 43542041 332E224E 616D6522 [SELECT A3."Name"]
010: 2C41322E 224E616D 65222C41 312E2254 [,A2."Name",A1."T]
020: 696D6553 74616D70 222C4131 2E225661 [imeStamp",A1."Va]
030: 6C756522 2C41312E 2256616C 75655F44 [lue",A1."Value_D]
PI SQL Client ODBC
040: 6F75626C 65222046 524F4D20 22454C45 [ouble" FROM "ELE]

050: 4D454E54 222E2245 4C454D45 4E542220 [MENT"."ELEMENT" ]
060: 41332C22 454C454D 454E5422 2E224154 [A3,"ELEMENT"."AT]
070: 54524942 55544522 2041322C 22454C45 [TRIBUTE" A2,"ELE]
080: 4D454E54 222E2241 52434849 56452220 [MENT"."ARCHIVE" ]
090: 41312057 48455245 2041312E 2254696D [A1 WHERE A1."Tim]
0A0: 65537461 6D70223E 3D273235 2D415052 [eStamp">='25-APR]
0B0: 2D323031 39272041 4E442041 312E2254 [-2019' AND A1."T]
0C0: 696D6553 74616D70 223C3D27 32362D41 [imeStamp"<='26-A]
0D0: 50522D32 30313927 20414E44 2041332E [PR-2019' AND A3.]
0E0: 224E616D 65223D4E 27486F75 73746F6E ["Name"=N'Houston]
0F0: 2720414E 44204132 2E224E61 6D65223D [' AND A2."Name"=]
100: 4E27456E 7669726F 6E6D656E 74272041 [N'Environment' A]
110: 4E442041 312E2241 74747269 62757465 [ND A1."Attribute]
120: 4944223D 41322E22 49442220 414E4420 [ID"=A2."ID" AND ]
130: 41322E22 456C656D 656E7449 44223D41 [A2."ElementID"=A]
140: 332E2249 4422 [3."ID"]
Workarounds for TO_NCHAR capability

The following are some workarounds for the problems you encounter when the TO_NCHAR capability is
disabled. If you cannot or do not want to enable the TO_NCHAR capability for DG4ODBC, then you should
consider these workarounds to improve query performance.
Workaround 1 - use an explicit cast

Cast the comparison strings explicitly to NVARCHAR2(n). This will force Oracle to parameterize the condition.
However, the remote query is still split into multiple remote queries and the JOIN is performed locally.
For example,
AND e."Name" = cast('Houston' as NVARCHAR2(40))
AND ea."Name" = cast('Environment' as NVARCHAR2(40))
The trace file contains:
.
.
.
10: 65222046 524F4D20 22454C45 4D454E54 [e" FROM "ELEMENT]
20: 222E2245 4C454D45 4E542220 57484552 ["."ELEMENT" WHER]
30: 4520224E 616D6522 3D3F [E "Name"=?]
.
.
.
PI SQL Client ODBC

10: 65222C22 456C656D 656E7449 44222046 [e","ElementID" F]
20: 524F4D20 22454C45 4D454E54 222E2241 [ROM "ELEMENT"."A]
30: 54545249 42555445 22205748 45524520 [TTRIBUTE" WHERE ]
40: 224E616D 65223D3F ["Name"=?]
.
.
.
00: 53454C45 43542022 41747472 69627574 [SELECT "Attribut]
10: 65494422 2C225469 6D655374 616D7022 [eID","TimeStamp"]
20: 2C225661 6C756522 2C225661 6C75655F [,"Value","Value_]
30: 446F7562 6C652220 46524F4D 2022454C [Double" FROM "EL]
40: 454D454E 54222E22 41524348 49564522 [EMENT"."ARCHIVE"]
50: 20574845 52452022 54696D65 5374616D [ WHERE "TimeStam]
60: 70223E3D 3F20414E 44202254 696D6553 [p">=? AND "TimeS]
70: 74616D70 223C3D3F [tamp"<=?]
.
.
.
Workaround 2 - use pass-through query

Use a pass-through query to avoid any manipulation of the remote query by Oracle.
For example:
set pagesize 50000
set serveroutput on size 30000;
--Using bind variables in a query
declare
v_cursor binary_integer;
v_ret binary_integer;
v_elementname nvarchar2(30);
v_attributename nvarchar2(30);
v_time timestamp;
v_value nvarchar2(30);
v_value2 float;
v_bind_elementname varchar2(30);
begin
v_cursor:=dbms_hs_passthrough.open_cursor@pi;
dbms_hs_passthrough.parse@pi(v_cursor,
'SELECT e.Name Element, ea.Name Attribute, a.TimeStamp, a.Value, a.Value_Double
FROM Element.Element e
INNER JOIN Element.Attribute ea ON ea.ElementID = e.ID
INNER JOIN Element.Archive a ON a.AttributeID = ea.ID
WHERE a.TimeStamp BETWEEN ''25-APR-2019'' AND ''26-APR-2019''
AND e.Name = ?
AND ea.Name = ''Environment''
ORDER BY e.Name, ea.Name;
');
v_bind_elementname := 'Houston';
dbms_hs_passthrough.bind_variable@pi(v_cursor,1,v_bind_elementname);
PI SQL Client ODBC
loop
v_ret:=dbms_hs_passthrough.fetch_row@pi(v_cursor,false);
EXIT WHEN v_ret = 0;
dbms_hs_passthrough.get_value@pi(v_cursor,1,v_elementname);
dbms_hs_passthrough.get_value@pi(v_cursor,2,v_attributename);
dbms_hs_passthrough.get_value@pi(v_cursor,3,v_time);
dbms_hs_passthrough.get_value@pi(v_cursor,4,v_value);
dbms_hs_passthrough.get_value@pi(v_cursor,5,v_value2);
dbms_output.put_line('Element '||v_elementname);
dbms_output.put_line('Attribute '||v_attributename);
dbms_output.put_line('TimeStamp '|| to_char(v_time,
'DD-MON-YYYY HH24:MI:SS.FF3'));
dbms_output.put_line('Value '|| v_value);
dbms_output.put_line('Value_Double '|| v_value2);
end loop;
dbms_hs_passthrough.close_cursor@pi(v_cursor);
end;
/
Workaround 3 - change Unicode columns into ASCII

Changing the column type of a table is possible when using PI SQL Views. A PI SQL View can be defined using PI
SQL Commander Lite. The following image shows the definition of two views named Element2 and Attribute2 in
PI SQL Commander Lite. Unicode string columns have been changed to ASCII string columns. These views can be
used in an Oracle query as shown below.
PI SQL Client ODBC

FROM Element.Element2@pi e
INNER JOIN Element.Attribute2@pi ea ON ea."ElementID" = e.ID
AND e."Name" = 'Houston'
AND ea."Name" = 'Environment'
Checking the Oracle trace file shows that the string comparison conditions are present in the remote query:
.
.
.
000: 53454C45 43542041 332E224E 616D6522 [SELECT A3."Name"]
010: 2C41322E 224E616D 65222C41 312E2254 [,A2."Name",A1."T]
020: 696D6553 74616D70 222C4131 2E225661 [imeStamp",A1."Va]
030: 6C756522 2C41312E 2256616C 75655F44 [lue",A1."Value_D]
040: 6F75626C 65222046 524F4D20 22454C45 [ouble" FROM "ELE]
050: 4D454E54 222E2245 4C454D45 4E543222 [MENT"."ELEMENT2"]
060: 2041332C 22454C45 4D454E54 222E2241 [ A3,"ELEMENT"."A]
070: 54545249 42555445 32222041 322C2245 [TTRIBUTE2" A2,"E]
080: 4C454D45 4E54222E 22415243 48495645 [LEMENT"."ARCHIVE]
PI SQL Client ODBC
090: 22204131 20574845 52452041 312E2254 [" A1 WHERE A1."T]

0A0: 696D6553 74616D70 223E3D27 32352D41 [imeStamp">='25-A]
0B0: 50522D32 30313927 20414E44 2041312E [PR-2019' AND A1.]
0C0: 2254696D 65537461 6D70223C 3D273236 ["TimeStamp"<='26]
0D0: 2D415052 2D323031 39272041 4E442041 [-APR-2019' AND A]
0E0: 332E224E 616D6522 3D27486F 7573746F [3."Name"='Housto]
0F0: 6E272041 4E442041 322E224E 616D6522 [n' AND A2."Name"]
100: 3D27456E 7669726F 6E6D656E 74272041 [='Environment' A]
110: 4E442041 312E2241 74747269 62757465 [ND A1."Attribute]
120: 4944223D 41322E22 49442220 414E4420 [ID"=A2."ID" AND ]
130: 41322E22 456C656D 656E7449 44223D41 [A2."ElementID"=A]
140: 332E2249 4422 [3."ID"]
.
.
.
Variant columns
Columns that hold data of different data types are exposed as SQL_VARIANT in PI SQL Client ODBC. For example,
this is true for most Value columns. The Oracle Database Gateway for ODBC (DG4ODBC) does not support this
data type. The Oracle behavior for unknown data types is such that these columns are treated as nonexistent,
meaning you can neither retrieve nor constrain such columns.
PI SQL Client ODBC offers a workaround for the problem and allows you to expose SQL_VARIANT as a Unicode
string instead. While this allows you to include these columns in the SELECT list, constraining these columns has
several issues, which are described in the following sections.
Comparison with numeric values

Comparison with numeric values causes post-processing, so the query execution is slowed down.
For example:
ea."Value", ea."Value_Double"
FROM Element.Element2@pi e
WHERE e."Name" = 'Houston'
AND (ea."Name" = 'Environment' OR ea."Name" Like 'Plant%')
AND ea."Value" > 50
As long as the intermediate result contains only numeric data for the value column, post-processing will generate
correct results. However, if there are non-numeric strings in the intermediate result, post-processing will
generate an ORA-01722: invalid number error message.
Comparison with string literals

When the TO_NCHAR capability is enabled, the string comparison for Unicode String columns is delegated to the
remote query. However, comparison for Variant as String columns with string literals will generate the following
or similar error message:
Here is an example query:
PI SQL Client ODBC

ea."Value"
WHERE ea."Name" = 'Plant'
AND ea."Value" Like N'H%'
Here is the resulting error message:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
No suitable function overload for 'Like' could be found. {42000,NativeErr = -1}
ORA-02063: preceding 2 lines from PI
28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
*Cause: The cause is explained in the forwarded message.
*Action: See the non-Oracle system's documentation of the forwarded
message.
This error is due to the fact that the underlying Real-Time Query Processing (RTQP) Engine requires an explicit
cast between the two data types. The fact that the ODBC driver exposes the Value column as String is not known
to the RTQP Engine. Unfortunately, an explicit cast in the query can only be done using Oracle syntax so that the
cast is performed locally and this will lead to post-processing.
Note: The error results from the Value column only. The Name column is a native Unicode String column and has
no such issue.
An explicit cast in the Oracle query is one solution but results in post-processing:
ea."Value"
AND cast(ea."Value" as nvarchar2(50)) Like N'H%'
Workarounds for comparisons with string literals

The following are some workarounds to performing comparisons using Variant as String columns.
Workaround 1 - use native Unicode column
When the TO_NCHAR capability is disabled, the string comparison for Unicode String columns is not delegated to
the remote query and the comparison is done in post-processing.
ea."Value"
AND ea."Value_String" Like N'H%'
Workaround 2 - change Unicode columns into ASCII

Use the PI SQL View for Attribute2 that was created earlier and make the comparison with the AnsiString
column.
PI SQL Client ODBC

ea."Value"
AND ea."Value_String" Like N'H%'
Recommendation for variant columns

As long as the converted Variant column does not need to be constrained, the workaround provided, Variant as
String, is sufficient. All data will be provided as strings, and visualization and reporting tools will have no
problems.
When constraints must be applied to such columns, it is recommended that you create or use PI SQL Views that
explicitly cast the Variant column to a native data type (such as String, Float, and so on.) When the TO_NCHAR
capability is enabled, for strings, the Unicode type (String) should be used. When the TO_NCHAR capability is
disabled, for strings, the ASCII type (AnsiString) should be used.
Note: The Attribute and data tables do already have native data type value columns (Value_Int, Value_Double,
Value_String (Unicode), and so on).
Oracle specific issues

This section covers issues that are specific to using Oracle.
Timestamp conditions
An issue exists when querying tables and using a timestep condition. In the following example, the timestamp is
specified without subseconds. Oracle parametrizes the timestep column using a Timestamp ODBC data type. This
Timestamp contains dirty subseconds as shown by the trace file, which leads to an unexpected result. In the
example query, the value timestamp returned is different than the one specified in the condition.
Examples:
-- The following PI SQL View is created using PI SQL Commander
CREATE VIEW [Master].[Element].[SampledValueView]
AS
SELECT "@time" time, sv."value", CAST("@time" AS "string") "time2", ea.Name "Name"
FROM [Master].[Element].[ft_GetSampledValue] sv
INNER JOIN [Master].[Element].[Attribute] ea ON ea.ID = sv.[@AttributeID]
WHERE ea.Element = 'B-210'
-- This query is executed using Oracle SQL Developer
SELECT "time" time1, "value" value, "time2" time2
FROM SampledValueView@pi
WHERE "Name" = 'Water Flow' AND "time" = '21-Apr-2019 12:30:00'
Result:
PI SQL Client ODBC
The trace file shows that subseconds are part of the timestamp parameter value being passed:
.
.
.
00: 53454C45 43542022 74696D65 222C2276 [SELECT "time","v]
10: 616C7565 222C2274 696D6532 222C224E [alue","time2","N]
20: 616D6522 2046524F 4D202253 414D504C [ame" FROM "SAMPL]
30: 45445641 4C554556 49455722 20574845 [EDVALUEVIEW" WHE]
40: 52452022 74696D65 223D3F [RE "time"=?]
Exiting hgopars, rc=0 at 2019/05/13-16:05:15
Entered hgoopen, cursor id 1 at 2019/05/13-16:05:15
hgoopen, line 89: Printing hoada @ 000000C7E2DBEFD0
MAX:1, ACTUAL:1, BRC:1, WHT=3 (BIND_LIST)
DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME
93 TIMESTAMP N 16 0 0/ 0 0 0 0 ?
Entered hgoprbv at 2019/05/13-16:05:16
hgoprbv, line 216: Printing hoada @ 000000C7E2DBEFD0
93 TIMESTAMP N 16 0 0/ 0 0 0 0 ?
Entered WP_SQLBindParameter at 2019/05/13-16:05:16
ParameterNumber 1, InputOutputType 1, ValueType 93, ParameterType 93,
ColumnSize 28, DecimalDigits 8, BufferLength 16,StrLen_or_IndPtr 0x000000C7E2DBEE90 (*0x10)
(Array size: 1)
16 bytes of data at 0x000000C7C9B14D50....
PI SQL Client ODBC
.
.
Workaround - specify very small subsecond part

A workaround is possible by specifying a small subsecond part like:
"time" = ''21-Apr-2019 12:30:00.0000001''
Now the corresponding trace file shows no subseconds for the timestamp parameter value being passed:
.
.
.
00: 53454C45 43542022 74696D65 222C2276 [SELECT "time","v]
10: 616C7565 222C2274 696D6532 222C224E [alue","time2","N]
20: 616D6522 2046524F 4D202253 414D504C [ame" FROM "SAMPL]
30: 45445641 4C554556 49455722 20574845 [EDVALUEVIEW" WHE]
40: 52452022 74696D65 223D3F [RE "time"=?]
Exiting hgopars, rc=0 at 2019/05/13-15:55:48
Entered hgoopen, cursor id 1 at 2019/05/13-15:55:48
hgoopen, line 89: Printing hoada @ 00000090F7320A58
93 TIMESTAMP N 16 0 0/ 0 0 0 0 ?
Entered hgoprbv at 2019/05/13-15:55:48
hgoprbv, line 216: Printing hoada @ 00000090F7320A58
PI SQL Client ODBC

93 TIMESTAMP N 16 0 0/ 0 0 0 0 ?
Entered WP_SQLBindParameter at 2019/05/13-15:55:48
ParameterNumber 1, InputOutputType 1, ValueType 93, ParameterType 93,
ColumnSize 19, DecimalDigits 0, BufferLength 16,StrLen_or_IndPtr 0x00000090F7317FF0 (*0x10)
(Array size: 1)
16 bytes of data at 0x00000090F7314CC0....
.
Oracle resources
The following table lists relevant Oracle documentation.
Document Note
Database Gateway Installation and Configuration Note especially the following sections:
Guide
• Installing Oracle Database Gateway for ODBC
• Configuring Oracle Database Gateway for ODBC
• Installation Parameters
Database Gateway for ODBC User's Guide Contains information about the Oracle Database
Gateway for ODBC.
Oracle Database Heterogeneous Connectivity User's Contains information about how to optimize
Guide heterogeneous queries.
Oracle Database SQL Language Reference Note especially the CREATE DATABASE LINK section.
Logging options
For performance reasons and to understand unexpected results, it is very important to recognize the difference
between the heterogeneous query that is executed in the Oracle client and the remote query that is executed by
the Oracle Database Gateway.
The remote query is visible at various levels by using logging options:
• Oracle Database Gateway tracing (see FDS_HS_TRACE_LEVEL)

• PI SQL Client ODBC logging
• PI SQL Data Access Server (RTQP Engine) logging
Note: Using logging options can degrade performance, so logging should normally be disabled.
AVEVA Group plc Tel +44 (0)1223 556655
High Cross
Madingley Road
Cambridge
CB3 0HB www.aveva.com
UK To find your local AVEVA office, visit www.aveva.com/offices
AVEVA believes the information in this publication is correct as of its publication date. As part of continued product development, such information is subject to
change without prior notice and is related to the current software release. AVEVA is not responsible for any inadvertent errors. All product names mentioned are
the trademarks of their respective holders.

PI SQL Client ODBC Administrator Guide

Uploaded by

Copyright:

Available Formats

PI SQL Client ODBC Administrator Guide

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

PI SQL Client ODBC Administrator Guide

Uploaded by

Copyright:

Available Formats

PI SQL Client ODBC

PI SQL Client ODBC Administrator Guide. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

PI SQL Client ODBC and Oracle Database Gateway Configuration Guide. . . . . . . . . . . 30

PI SQL Client ODBC

PI SQL Client ODBC Administrator Guide

Introduction to PI SQL Client ODBC

About this guide

About Developer Technologies

PI SQL Client ODBC installation

Install PI SQL Client

1. Download the PI SQL Client setup kit.

Download the PI SQL Client setup kit

Run the PI SQL Client setup kit

• PI SQL Client OLE DB

1. Run PI SQL Client_2021_.exe to start the PI SQL Client installation wizard.

PI SQL Client silent installation

/qn Specifies the "quiet mode" and suppresses dialog

/qb Specifies the "quiet mode" and suppresses dialog

PIPCFOLDER Specifies the PIHOME folder. If PIHOME

Use the following syntax for a silent installation of a single component:

PI SQL Client installation results

Verify the PI SQL Client installation

1. Open a Windows PowerShell command window as an administrator.

Item: AF SDK Version: 2.10.9.593 FriendlyVersion: 2018 SP3 Patch 3

Uninstall PI SQL Client ODBC

PI SQL Client ODBC configuration

• Data Source Name (DSN)

• Click Test Connection when you configure the DSN.

Data source name (DSN) configuration

Description A description to further identify the DSN.

AF Server PI AF server name or Data Archive name. Required.

AF Database Enter the AF database to connect to. Required.

User The User ID to use for authentication when you log on

Password The password to use for authentication when you log

Test Connection Click the button to test the connection information

Initial Catalog Enter the initial catalog.

Query Date Defaults to Latest. You can enter any PI time

Locale Enter the culture (locale) for the connection. The

• Decimal point and group separator for conversion

Command Timeout Enter the command timeout in seconds. Enforces the

• Click Up to move the protocol up in the order.

Maximum String Specifies the maximum string size to be returned,

Variant as String Check this box to expose original Variant columns as

GUID as String Check this box to expose original GUID columns as

Connection string format

• The driver name, for example, Driver=PI SQL Client;

• The AF Database name or Data Source, for example, AF Database=myAFfDatabase; or Data

Connection string keyword Description Examples

Initial Catalog The initial catalog. Initial Catalog=Master; (default)

Protocol Order Order of the protocols used. Protocol Order=Https/

Query Date Defaults to Latest. Also supports Query Date='now';

User ID, User, or UID The User ID to use of User ID=piadmin;

Password or Pwd The password to use for Password=PI;

FileDSN Specify an ODBC File data source to FileDSN=MyFile.dsn;

Variant as String True or False (default). Original Variant as String=True;

GUID as String True or False (default). Original GUID as String=True;

Command Timeout Enter the command timeout in Command Timeout=20;

PI SQL Client ODBC operation

Name Data Type Indicator

Advanced PI SQL Client ODBC topics