November 19, 2005
To display a row of an SQL table in Pragma 6 is very easy. It sounds more forbidding than it is. A row of a table is what we used to call a record of a file. And like a record a table row is made up of fields.
A table can contain all your addresses, each row another customer, mistress or relative. Every row consists of name, street address, phone number etc., all the usual things that make up an address book . We want to display each row like this:
Not very creative, but to the point.
The buttons at the bottom will let you view the first, previous, next or last address.
The following explanation presumes that you created in the PostgreSQL Administrator a table called 'addresses' with 11 columns, like described in the page Create, Fill and View a Table of this tutorial.
In the tutorial vocab look at FORM SQL ADDRESSES DS1. It will show you how easy it is to get the various fields from the SQL addresses file and display them in the form.
The initialization event of the form looks like this:
The SQL query "SELECT number, name, company, street, city zipcode, country, phone, fax, mobile, e-mail FROM addresses" means that we want to fetch all (*) the columns of the table.
You really should not use "SELECT * FROM addresses" in an application, but the longer form shown above. The reason being that should you for any reason one day add a column to the table, if you use * your program might no longer work properly, while with the long syntax the program will still run ok.
We initialize the noun RECORD ROW to 0 so that whatever event we call next we are prepared.
The script SCRIPT ZERO ALL EDIT FIELDS is very straightforward and looks like this:
of course it goes on until all the rich edit fields display nothing.
The button clicked event in the Next button is called SCRIPT GET NEXT RECORD and looks like this:
etc.
Note how the FILE STATUS must be checked after every SQL GET FIELD. This is done with the utility verb .RETURN IF STATUS NOT OK. FILE STATUS will also tell you not only of potential problems, but also when you have reached the end of the rows. In this case the program will inform the user that the last row has been reached do nothing, but of course you can handle this situation the way you want.
In this program it is important that you always use the same query, contained in the noun SQL QUERY ADDRESS. If you use a different query you might end up with a different number of columns and you will have a mess. By all means experiment, but this is beyond the scope of this tutorial. The other reason to always use the same query is that this tells Pragma to look at the data in memory and only do one actual fetch from PostgreSQL, speeding things up enormously and reducing network traffic.
The actual event of the button Next is very simple and straightforward:
The event runs the script SCRIPT GET PREVIOUS RECORD. It could of course be written in the same way as SCRIPT GET NEXT RECORD, but we want to make things a bit more interesting. Instead of looking for the column by number, we use the column's name.
etc.
Using the column name instead of the number is not very interesting for this example, but you may find it useful for other programs. When using the column name it is even more important to check the FILE STATUS since you might misspell the name and then get a hard to trace bug. Also keep in mind that you are not allowed to use underlines in Pragma 6 while in PostgreSQL you are.
The button clicked event of the Last button looks like this:
Since you want to get the last row fetched, you must know how many rows were fetched from the server. You use the verb SQL GET NAME (SQLGTN) to accomplish this.
We used a GO TO in this example not because we could not do it in a different way, but because it adds a touch of color to the verb.
The remaining events are very similar and don't show anything new.
It is very easy, as you can see, to display data from PostgreSQL in Pragma 6. This because the strength of Pragma, especially of Pragma 4 and 5 has and is to program the user interface. Pragma 6 will give you the tool to program powerful clients in the usual, easy and maybe even idiosyncratic Pragma way.
To display an address record is not an earth shattering accomplishment, but, as you could see ion the example above Pragma lets you get data easily from PostgreSQL and then you can massage and interface it in the usual way.