Login
Templates, Tools and Utilities
for Clarion Developers
Templates, Tools and Utilities
|
||
|
|
|
Icetips Article
Back to article list
Search Articles
Add Comment
Printer friendly
Direct link
SQL Related Articles: Better SQL Part 2 2002-07-22 -- Dan Pressnell 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. Today is November 21, 2024, 3:31 am This article has been viewed 35242 times. Google search has resulted in 75 hits on this article since January 25, 2004.
|
|