Clarion Handy Tools
HOME ABOUT NEWS BUY DOCS

CHT ListBoxBrowseExtender New, 2017 Features

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:

SQL DATABASE VIEW - NO ABC BROWSE

sqlnoabcbrowse01.png

Here is the HNDSCHOOL.APP procedure we've built to show you how it's done:

HNDSCHOOL.APP PROCEDURE TREE

sqlnoabcbrowse02.png

And here's the extension template list for that procedure. Note, no ABC Browse!

HNDSCHOOL.APP EXTENSION TEMPLATE LIST

sqlnoabcbrowse03.png

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.

LISBOXBROWSEEXTENDER (LBX)
MAIN DIALOG

sqlnoabcbrowse04.png

LBX FILL FROM VIEW DIALOG

sqlnoabcbrowse05.png

LBX PRIMARY TABLE DIALOG

sqlnoabcbrowse07.png

LBX TABLE FIELDS DIALOG

sqlnoabcbrowse06.png

ENROLLMENTVIEW
DICTIONARY DEFINITION

sqlnoabcbrowse08.png

ENROLLMENTVIEW CREATE-VIEW CODE
INSIDE HNDSCHOOL.SQLITE

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.

CREATE VIEW enrollmentview AS SELECT

Enrollment Table

Enrollment.ID AS ENRID,
Enrollment.StudentNumber AS STUDENTNUMBER,
Enrollment.ClaNumber AS CLANUMBER,
Enrollment.MidtermExam AS MIDTERMEXAM,
Enrollment.FinalExam AS FINALEXAM,
Enrollment.TermPaper AS TERMPAPER,

Classes Table

Classes.ClassNumber AS CLASSNUMBER,
Classes.CourseNumber AS COURSENUMBER,
Classes.ScheduledTime AS SCHEDULEDTIME,

Students Table

Students.StuNumber AS STUNUMBER,
Students.FirstName AS FIRSTNAME,
Students.LastName AS LASTNAME,
Students.Major AS MAJOR,
trim(Students.LastName) || ', ' ||
trim(Students.FirstName) AS STUDENTNAME

Majors Table

Majors.MajNumber AS MAJNUMBER,
Majors.MajDescription AS MAJDESCRIPTION,

Courses Table

Courses.CouNumber AS COUNUMBER,
Courses.Description AS DESCRIPTION,
trim(Courses.Description) || ' [' ||
trim(Classes.ScheduledTime) || ']' AS COURSEDESCRIPTION,

Table and Join Definition

FROM Enrollment, Classes, Students, Majors, Courses WHERE
Classes.ClassNumber = Enrollment.ClaNumber AND
Students.StuNumber = Enrollment.StudentNumber AND
Majors.MajNumber = Students.Major AND
Courses.CouNumber = Classes.CourseNumber

ENROLLMENTVIEW CREATE-VIEW CODE
From Inside HNDSCHOOL.APP

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!

SQLITESTUDIO.EXE

sqlnoabcbrowse05.png

SQL INJECTION - WITH CLARION "SEND()"

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.

!CREATE ENROLLMENTVIEW STRUCTURE IN THE DATA BASE
CLEAR(SQlCommandB)
SQLCommandB = |
'CREATE VIEW enrollmentview AS SELECT ' & |

Enrollment Table

'Enrollment.ID AS ENRID, ' & |
'Enrollment.StudentNumber AS STUDENTNUMBER, ' & |
'Enrollment.ClaNumber AS CLANUMBER, ' & |
'Enrollment.MidtermExam AS MIDTERMEXAM, ' & |
'Enrollment.FinalExam AS FINALEXAM, ' & |
'Enrollment.TermPaper AS TERMPAPER, ' & |

Classes Table

'Classes.ClassNumber AS CLASSNUMBER, ' & |
'Classes.CourseNumber AS COURSENUMBER, ' & |
'Classes.ScheduledTime AS SCHEDULEDTIME, ' & |

Students Table

'Students.StuNumber AS STUNUMBER, ' & |
'Students.FirstName AS FIRSTNAME, ' & |
'Students.LastName AS LASTNAME, ' & |
'Students.Major AS MAJOR, ' & |
'trim(Students.LastName) || <39>,<32><39> || ' & |
'trim(Students.FirstName) AS STUDENTNAME ' & |

Majors Table

'Majors.MajNumber AS MAJNUMBER, ' & |
'Majors.MajDescription AS MAJDESCRIPTION, ' & |

Courses Table

'Courses.CouNumber AS COUNUMBER, ' & |
'Courses.Description AS DESCRIPTION, ' & |
'trim(Courses.Description) || <39><32><32>[<39> || ' & |
'trim(Classes.ScheduledTime) || <39>]<39> AS COURSEDESCRIPTION, ' & |

Table and Join Definition

'FROM Enrollment, Classes, Students, Majors, Courses ' & |
'WHERE Classes.ClassNumber = Enrollment.ClaNumber AND ' & |
'Students.StuNumber = Enrollment.StudentNumber AND ' & |
'Majors.MajNumber = Students.Major AND ' & |
'Courses.CouNumber = Classes.CourseNumber '

Inject the VIEW into SQLLITE Data Base

SEND(SQLCommand,SQLCommandB)
IF ERRORCODE() THEN
  MESSAGE('ERROR CREATING EnrollmentVIEW ' & ERROR(,)|
  'Error...',ICON:Asterisk)
END

FUTURE LBX ARTICLES

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.