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
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"]
.
.
.
SQL text from hgopars, id=2, len=57 ...
00: 53454C45 43542022 4944222C 224E616D [SELECT "ID","Nam]
10: 65222C22 456C656D 656E7449 44222046 [e","ElementID" F]
20: 524F4D20 22454C45 4D454E54 222E2241 [ROM "ELEMENT"."A]
30: 54545249 42555445 22 [TTRIBUTE"]
.
.
.
SQL text from hgopars, id=3, len=120 ...
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:

SQL text from hgopars, id=1, len=326 ...
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 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"]