An article about ListBoxBrowseExtender from which this one carries on, was originally written early in 2016. With this writing, we expand and update that document to reflect new template features added since the original article.
While introducing SQL-VIEWs via the HNDSCHOOL.APP and SQLite feature study, we began once again - since we've spoken on this before - to notice how much of making ABC browse stuff work in new and more efficient ways -- especially using DATABASE VIEWS directly -- is an exercise in ABC Browse workarounds.
The Client Server paradigm as it exists already in the SQL world, is the simplest of all models for displaying and browsing data, yet with the ABC Browse Template we tend to make it into something way more complex than it actually is.
In short, the client server data paradigm is as follows: "Send a query to the data base and get back matching data". No query, no data. Simple.
There are a few corollaries to this, and all of them revolve around having the data base do as much of the work (eg: filtering, field formatting, field qualifying, and so on) as possible.
This SQL client server model sets you up to insert the internet, WAN or network between your app and your data tables and not see a lot of degredation in performance.
That bit of background re-introduces for 2017, our discussion of CHT ListBoxBrowseExtender.This template is a HandyMarkerBrowse look-and-act-alike, but it does not need an ABC browse template underneath it to fill the list box queue. It just needs a Clarion List Control and some files or data views to build the browse. Since our last version of this article, we've added a procedure to HNDSCHOOL.APP that builds a browse on an SQL view that we defy you to distinguish from any other ABC based browse like HandyMarkerBrowse.
The next image pictures just such a ListBoxBrowseExtender based browse:
Here is the HNDSCHOOL.APP procedure we've built to show you how it's done:
And here's the extension template list for that procedure. Note, no ABC Browse!
The next group of images are ListBoxBrowseExtender configuration dialogs. They assume that a vanilla Clarion List Control has been dropped on the window which has fields (columns) populated in it in the same way that you populate fields (columns) to an ABC browse template.
By examining the SQL view definition below, extracted from HNDSCHOOL.SQLITE using SQLITESTUDIO.EXE located in
\accessory\hnd\sqlite\sqlitestudio\, we are able to show that the table we see from Clarion and define in the HNDSCHOOL.DCT as "EnrollmentView" consists, in actuality, of fields from 5 different data tables.
Without any effort on our part in the way of coding inside our HNDSCHOOL.APP we are able to treat the fields in these 5 tables, enumerated in "EnrollmentView" as if they were located in a single table. The SQLITE data base handles all of the joining work for us.
The data base definition of EnrollmentView even handles several concatenations with the "STUDENTNAME" field providing a "LastName, FirstName" concatenation and the "COURSEDESCRIPTION" field providing a "Description [ScheduleTime]" concatenation.
All without us needing to write any Clarion code to maintain the joins and to perform the concatenations.
Look at the first image provided with this article depicting a Clarion-style browse entitled "SQL DATABASE VIEW - NO ABC BROWSE".
That is a browse from HNDSCHOOL.APP built with LBX, configured with its "FillFromView()" setting, and which requires NO EXTRA EMBED CODE.
How did the definition of "EnrollmentView" get into the HNDSCHOOL.SQLITE data base, we hear you ask?
How does any data table or data view definition get into an SQL data base? You write a "CREATE" SQL statement and execute it once, in the data base. A creation string of this sort can be typed in any editor and executed in the data base using a utility like SQLLITESTUDIO.EXE.
Some very clever developers at SQLITE STUDIO provide a donation-funded database table maintenance and browse utility which you can find packaged in your CHT installation in directory
\accessory\hnd\sqlite\sqlitestudio\. The executable is called SQLITESTUDIO.EXE.
We did not create this app but we use it and recommend that you do the same. Our inclusion of this utility will help to make your SQLITE experience as easy and rich as possible. Thank the developers of SQLITESTUDIO.EXE, and if possible, make your donation, for their hard work and for providing a very useful SQLITE support tool that easily rivals SV's TOPSCAN.EXE utility!
Another way is to create the string using the Clarion editor and then "injecting" it into the data base using the Clarion "SEND()" command as shown below.
SQL Injection with SEND() is how we did it in HNDSCHOOL.APP. And that code was executed a sum-total of once only in order to create the "EnrollmentView" definition as you see it here.
You can see this injected code in our HNDSCHOOL.APP example by opening a procedure called "CreateDB()".
Here is the code that creates the "EnrollmentView" via injection of our SQL CREATE VIEW code into the SQLLITE data base.
Once created, the view thus created handles all view-related back-end work for us, exactly as described in this article.
In an upcoming ListBoxBrowseExtender article we'll review further the use of DATA VIEWS as they apply in CHT Client Server applications providing data to clients located across the NETWORK, the WAN, and the INTERNET.
That discussion will revolve around another LBX example application already in your tool kits, called HNDPEOPLE_LBX.APP.