LIST CONTROL ADD SQL TABLE (LCAT)
|
|
LIST CONTROL ADD SQL TABLE (LCAT) |
|
|
Event Handling Verb Form and Elements Operation |
This verb adds the contents of a PostgreSQL table to a List control.
Before the data is added to the list control, the list control is emptied, but the properties of the columns are remembered and will be used to display the SQL data.
With the SQL SELECT query you can select which fields of a table and in which sequence they will be added.
In the assumption that the first column of your table is a numeric primary key (highly recommended), the item data of each list control column will contain that number, facilitating your retrieval of that row with a subsequent query.
LIST CONTROL ADD SQL TABLE element ELEMENT NAME select query SQL QUERY
ELEMENT NAME is the name of the List Control element to which you want to add the SQL data.
SQL QUERY is a string or a noun containing the SQL query.
After using this verb check the FILE STATUS as if this were a standard SQL operation.
There are various ways to fill the List Control with SQL data. The control over what is filled into which column and how it is displayed (text, width, alignement) is done with the verb LIST CONTROL SET COLUMN, before you run this verb (LIST CONTROL ADD SQL TABLE).
Pragma 6 will display the number of columns specified in the definition of the List Control. By default the headings will be left aligned, the width 100 and the text will be COL 1, COL 2, COL 3 etc.
The SQL data will be put into these columns, left aligned and the width will not be changed. Column 1 will be filled with the data of column 1 of the SQL table, Column 2 will be filled with the data of column 2 and so on. The text in the heading will be exactly like the name of the SQL columns, underline included, if present.
If the List Control has less columns than the SQL table, only the available columns will be filled. If the List Control has more columns than the SQL table, the remaining columns will be left unfilled and without text in the heading.
This is a quick and dirty way to display SQL data in a program.
Before adding the SQL data with this verb (LIST CONTROL ADD SQL TABLE) set the heading text, alignment and width of each column with the verb LIST CONTROL SET COLUMN.
The width and alignement will be set like detailed in the reference of the verb LIST CONTROL SET COLUMN.
There are three possibilities for the text:
Text With a Number, like COL 3, COLUMN 4, HEADING 7 or simply 5. The program will extract the number from the text and display the equivalent SQL column number, with the text in the heading exactly like the name of the SQL columns, underline included, if present. If you don't do a LIST CONTROL SET COLUMN for each column and the defaults are the column numbers, the remaining columns will of course contain the SQL data of the default row number.
This option, Text With a Number, lets you display only the columns of your choice, but the headings will display the name of the SQL column. Which may or may not be suitable, depending mostly how creative and ingenious you were when naming the SQL columns. One problem remains. If you change the order of the SQL columns in the table or insert a new column, the program will no longer function as desired.
Text with SQL Column Name, like say ad name, Ad Title or AD COMPANY. The program will convert the text to lower case and insert an underline into the spaces and use this text to retrieve the column from SQL. The displayed headings will appear as you wrote them. If you misspell a column name, the program will display nothing in that column, giving you a gentle hint that your spelling or typing is not what it used to be.
This option, Text with SQL Column name, lets you display only the columns of your choice, but the headings will display the name of the SQL column, albeit in a little less 'computerish' way. Again, this option depends on how creative you were when naming the SQL columns. The advantage is that even if you change the order of the SQL columns in the table or insert a new column, the program will still function.
Text with Parenthesis, like say (ad name)Name, (AD COMPANY)COMPANY or (ad address)Street Address. The program will take the name that is in the parenthesis, convert the text to lower case and insert an underline into the spaces and use this text to retrieve the column from SQL. What is outside the parenthesis will be displayed in the heading as you wrote it.
This option, Text with Parenthesis, lets you display only the columns of your choice and the headings with whatever name you want, regardless of the nonsense names you gave to the SQL columns. Again, the advantage is that even if you change the order of the SQL columns in the table or insert a new column, the program will still function.
Of course you can mix these options, although we don't recommend it. Think of the poor guy that will look through your code and come away confused, to say the least.
Problems. The only possible problems could arise if you somehow manage to defy the SQL tradition and give columns names with uppercase characters and spaces.
One reason to list a table or part of an SQL table in a List Control is to then select a row and do something with the complete data of that row. You may for instance display the name and the company of an address table in two columns, select (click) on a particular name and then want to see all the data of that person, like for instance the telephone number and street address. The problem you may encounter is that the database may contain two John Smith, both working for the same company. Since you foresaw that possibility, you added a unique number to the first column of your table (first column a primary key, numeric, in SQL speak) to avoid a duplicate reference and encounter possible problems. You could list the unique number in front of the name and company, but that would not be estetically pleasing and useless for the end user.
To solve this problem the program will add to each row displayed the numeric contents of the first column of the table, assuming that it is a unique number, just like you would add that number with the verb LIST CONTROL SET ITEM DATA. It is therefore important that the first column of your table be a unique number. If it is not, the data will contain just zeros, making it useless as a reference.
You may want to display multiple queries of the same table or of a different table in the List Control. Do do this you must initialize the columns that you want to display each time with the verb LIST CONTROL SET COLUMN before running the verb LIST CONTROL ADD SQL TABLE.
If you don't do this the second time you do a query the List Control will display the columns of the previous query, which is not what you want.
To see how you use this verb look at FORM TEST LIST CONTROL SQL in the tutorial vocabulary.
LCAT standard # 427, msg # 455, 764