FETCHQUERY

Solved a problem in an elegant manner and want to show off your code? Know a hard-to-find feature? Post it here for the benefit of others. Questions don't belong here.
Post Reply
steve
Posts: 375
Joined: Wed Nov 30, 2011 10:20 pm
Been thanked: 75 times

FETCHQUERY

Post by steve » Wed May 17, 2017 12:48 pm

FETCHQUERY
3.3.1.04
Allows you to cycle through FETCH results much like the current SQL / SQLFIRST/SQLNEXT etc.
One key difference is that you must perform a FETCHCLOSE after you have finished manipulating data, to release memory tied up by the query.

A maths wizard helps you construct these.
The commandset includes:
FETCHQUERY - defines the query parameters - which table etc - made by the wizard
FETCHFIRST - gets first record returned by the query
FETCHNEXT - gets next record in sequence
FETCHPREV - gets previous record in sequence
FETCHLAST - gets last record in set
FETCHRECORDAVAIL - checks whether the previous FETCHFIRST/FETCHNEXT/FETCHPREV /FETCHLAST command returned a record
FETCHDATA - gets the contents of a specific field on the record
FETCHCLOSE - releases memory allocated to result set
FETCHERRORMSG - returns any error messages generated by FETCH.... commands

Example:

Code: Select all

#Define a query to get records from the history table for this case
FETCHQUERY( "Qry-1" , "History" , "FOR EACH History NO-LOCK WHERE History.case_no = '" {case.key} "' AND (action_code = 'None' )" )
#
# If the FetchQuery did not work then show any error messages
v-msg = FETCHERRORMSG
IF v-msg <> "" THEN
MESSAGE( v-msg )
END
#
# Get records from the Diary table, sorted by Details
FETCHQUERY( "Qry-2" , "Diary" , "FOR EACH Diary NO-LOCK WHERE Diary.case_no = '" {case.key} "' AND (action_type = 'DE' ) BY details " )
#
# Work forward through the History records
#
FETCHFIRST( "Qry-1" )
v-avail = FETCHRECORDAVAIL( "Qry-1" )
WHILE v-avail = TRUE DO
v-case = FETCHDATA( "Qry-1" , "case_no" )
v-date = FETCHDATA( "Qry-1" , "created_date" )
v-details = FETCHDATA( "Qry-1" , "details" )
MESSAGE( "History: " v-case " " v-date " Details: " v-details )
FETCHNEXT( "Qry-1" )
v-avail = FETCHRECORDAVAIL( "Qry-1" )
END
#
# Work backwards through the Diary table, the BY clause in FetchQuery will cause the records to be sorted alphabetically by Details
#
FETCHLAST( "Qry-2" )
v-avail = FETCHRECORDAVAIL( "Qry-2" )
WHILE v-avail = TRUE DO
v-case2 = FETCHDATA( "Qry-2" , "case_no" )
v-date2 = FETCHDATA( "Qry-2" , "date" )
v-handler = FETCHDATA( "Qry-2" , "handler" )
v-details2 = FETCHDATA( "Qry-2" , "details" )
v-action = FETCHDATA( "Qry-2" , "modified-action" )
MESSAGE( "Diary: " v-case2 " " v-date2 " " v-handler " " v-details2 " " v-action )
FETCHPREV( "Qry-2" )
v-avail = FETCHRECORDAVAIL( "Qry-2" )
END
#
# Release system resources allocated to the queries
#
FETCHCLOSE( "Qry-1" )
FETCHCLOSE( "Qry-2" )

Post Reply