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);
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.
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.
Share this entry
