Printed Icetips Article

Probably no single item causes more discussion in Clarion programming than the browse. With a desktop file system, browses are hard enough, but at least they are fast. This is because of the SET, NEXT, NEXT processing which is very efficient with such files.

But with SQL, there are more problems. The up side is that there are more opportunities.

Template generated code goes part of the way when doing an SQL browse. It will generate a VIEW and a QUEUE to hold the paged results. But this is still very rigid. There is no way to do a template generated browse to use SUM in a browse, for example.

Page loaded browses appeal to us because we are used to them and we get them by filling in a few prompts. With SQL, though, file loaded browses are quite often the best way to approach the problem. Also, in most cases, refreshing the browse box on return from an update procedure is a waste of resources and time.

In the demo baseball app that I uploaded to http://www.icetips.com/downloadfile.php?FileID=59 you can see a different approach, with some hand coding that is not too extensive. First, look at the BrowseTeams procedure. Notice that there is a queue declared to hold the result of a query and display it in a list control. (The TeamQueue.)

Now look at the SendQuery routine. It has the code to send SQL to the server and fill the browse queue. There is also some conditional filter handling there.

By the way, note the use I made of the iDynStr object. That's a built in string object that is declared in DYNSTR.INC in Clarion's libsrc directory. the iDynStr object makes building SQL statements much easier and more powerful.

In the SendQuery routine in the BrowseTeams procedure, note the use of the "sqlfile" file. That's a generic sql work file that is declared in the dictionary. Here is the generated declaration of that file structure:

sqlFile FILE,DRIVER('ODBC'),OWNER(glo:owner),|
             NAME('Teams'),PRE(sqlfile),BINDABLE,THREAD
Record                   RECORD,PRE()
f1                          CSTRING(256),NAME('TeamID')
f2                          CSTRING(256),NAME('TeamID')
f3                          CSTRING(256),NAME('TeamID')
f4                          CSTRING(256),NAME('TeamID')
f5                          CSTRING(256),NAME('TeamID')
f6                          CSTRING(256),NAME('TeamID')
f7                          CSTRING(256),NAME('TeamID')
f8                          CSTRING(256),NAME('TeamID')
f9                          CSTRING(256),NAME('TeamID')
f10                         CSTRING(256),NAME('TeamID')
                         END
                     END

Look carefully at that file structure, especially the NAME atrributes. Obviously, such a table cannot exist on the server. But that's not the point. The point is that this will serve as a truly generic mechanism to send SQL and receive the results. You can use joins, where, order by, sum, count, and in general any sql that you can send. And it can handle the results that are sent back, up to 10 columns, with the exception of large type fields like TEXT, and IMAGE.

When you OPEN that file structure in Clarion, Clarion will simply verify that there is a table named "Teams" and that it has a column--of any time--named "TeamID". Then you are ready to go.

You can use the structure to query ANY table that you have access to, to send no-result type queries, call stored procedures, and things like CREATE TABLE.

Here is a simple example, to fill a queue with all the balances for all your customers:

BalanceQueue        Queue
CustomerID            Long
CustomerName          String(60)
Balance               Decimal(20,2)
                    End

 code
 free(BalanceQueue)
 open(sqlfile)
 sqlfile{prop:select} = 'select customer.customerid,' &|
     ' customer.customername,' &|
     ' sum(account.balance) ' &|
     ' from customer,account ' &|
     ' where account.customerid = customer.customerid' &|
     ' group by customer.customerid,' &|
     ' customer.customername' &|
     ' order by customer.customername'
 if error()
        stop(error() & ' ' & fileerror())
 end
 loop
   next(sqlfile)
   if error() then break.
   BalanceQueue.CustomerID = sqlfile.f1
   BalanceQueue.CustomerName = sqlfile.f2
   BalanceQueue.Balance = sqlfile.f3
 end

Note that there is a relationship between the columns you SELECT in your SQL and the field that they are returned in. The first column goes into sqlfile.f1, the second into sqlfile.f2, etc.

Now that you have the results of your query, you have to handle updates, inserts, and deletes. You can see how I've done that in the BrowseTeams procedure.

That looks like a lot of code, doesn't it. Next, let's turn the browse into a one liner, where the queue structure itself defines part of the SELECT statement you will send.