CygNet ODBC and LEFT OUTER JOIN

June 19, 2015 / 0 comments / in General  Patch  / by CygNet Blog Admin

The CygNet ODBC driver has recently been patched such that it returns the correct rows for a query that processes the results of a LEFT OUTER JOIN with a subsequent LEFT OUTER JOIN. Previous to this patch, such a query would not return the correct rows and either generate an unknown exception in the associated log file or cause the application to crash.

For example, imagine you need a list of all Facility IDs in your Facility Service along with any notes from the Note Service that may be associated with the facilities in the list, knowing that most facilities do not have associated notes.

The following query uses a LEFT OUTER JOIN to list all “facility_id” values from the Facility Service as well as the “itemid” from the Notes Service’s Note Association Records that matches the “facility_id” or “NULL” if no association exists.  This query has always executed without error.

SELECT
F.facility_id,
NAR.itemid

FROM MYSITE_FAC.FAC_HEADER F

LEFT OUTER JOIN
MYSITE_NOTE.NOTE_ASSOCIATION_RECORD NAR
ON (F.facility_id=NAR.itemid);

CygNet ODBC and LEFT OUTER JOIN

The problem identified and fixed in this patch arises when retrieving the note text itself.  The following query applies a LEFT OUTER JOIN against the NOTEBODY table to the previous result set, also produced by a LEFT OUTER JOIN, in order to fetch the “notetext” column.

SELECT
F.facility_id,
NAR.itemid,
NB.notetext

FROM MYSITE_FAC.FAC_HEADER F

LEFT OUTER JOIN
MYSITE_NOTE.NOTE_ASSOCIATION_RECORD NAR
ON (F.facility_id=NAR.itemid)

LEFT OUTER JOIN
MYSITE_NOTE.NOTEBODY NB
ON (NB.level1_foreignkey = NAR.level1_foreignkey);

Prior to this patch, the ODBC driver would generate an unknown exception or crash due to improper handling of NULL entries in the result set of the first LEFT OUTER JOIN.  The patched version now properly retrieves the NOTEBODY.notetext column.

CygNet ODBC and LEFT OUTER JOIN

This CygNet ODBC patch is now available for versions 8.1.1, 8.1.2 and 8.1.3.  The patches will soon be available on the CygNet Support download site or you may contact your favorite CygNet Support cast member for direct distribution.

TAGS ODBC

Share this entry
Share by Mail



Comments

Blog post currently doesn't have any comments.
{{com.name}}           {{com.blogCommentDateFormatted}}

Leave Comment

Please correct the following error(s):

  • {{ error }}

Subscribe to this blog post

Enter your email address to subscribe to this post and receive notifications of new comments by email.


Search the CygNet Blog

Subscribe to the CygNet Blog

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Tags

.NET ‘Sparkline” “.NET “64-bit” “Allen “Azure “Canvas” “CygNet “Data “Database “DDS-based “Dispatch” “DNP3 “Facility “FWRD” “Geometric “HMI “IoT “Lufkin “MQTT “Note “OpenStreetMaps” “Relative “Replication” “SCADA” “Security” “SVG” “Telerik” “Thin “Totalflow “Weatherford 8.5.1 9.3 9.4 9.9” Ad hoc chart Alarms API API” ASR Authentication auto-failover automatic failover automatic service recovery beta program Bradley breakouts Bridge Bridge API broadcast browser-based Bytes Canvas CIP Client Client” Clients” Comm Config File Manager Configuration Context menu Controls controls” CRC custom database indexes Custom Events CygNet CygNet 9.0 CygNet 9.4 CygNet 9.6 CygNet 9.7 CygNet 9.8 CygNet messaging CygNet. Data Data Visualization DEIDs Depot Designer device mapping Diagnostics disaster recovery Dynagraph EAC EIE EIE” email Emerson emitter” Enhanced Enhanced Alarm Configuration Excel Facilities” failover feedback Files first look FMS Full FWRD Get Latest Get New GitHub gns Grid” group group grid Heat Map HMI HSS IoT iPhone Job Runner Link Link” Maps” Measurement Measurement” Messaging” Mobile Mode” Navigation OAuth ODBC On-demand chart OPC OPC UA Polling recovery redundancy reference facilities Reference Packages Release Remote replication Report Module SAM Samples SCADA screen performance Scripting Security Server” Shapes” slides Sniffer SQL Server survey Sync” Tabs Tech Bulletin Template Test” TFA Thin Web Client Token Tools transactions” Tree Map trend Troubleshooting Two-factor UA UIS video View Group Web web-based Well WESC 2016 WESC 2018 WESC 2020 WESC2019