147

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 on Facebook Share on Twitter Share on Linkedin Share by Mail



Comments

Blog post currently doesn't have any comments.

Leave Comment

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