SQL Related Articles: Using same file structure to retrieve multiple fields 2002-07-07 -- Dan Pressnell Newsgroups: TopSpeed.Products.Drivers
One of the inconveniences of using SQL in Clarion programs has been the file
or view structure that you have to use. The common advice is to create a
dummy table on the server, so that table's structure can hold results.
But look at this structure. As long as you have a table named "customer"
and it has a column named "customername", it will work fine for using
embedded SQL:
========================
tfile file, driver('odbc'), name('customer'), owner(glo:owner)
record record
f1 cstring(256), name('customername')
f2 cstring(256), name('customername')
f3 cstring(256), name('customername')
f4 cstring(256), name('customername')
f5 cstring(256), name('customername')
f6 cstring(256), name('customername')
f7 cstring(256), name('customername')
f8 cstring(256), name('customername')
f9 cstring(256), name('customername')
f10 cstring(256), name('customername')
end
end
=======================
When Clarion "opens" that file, it validates that there is a table named
"customer" with a columned named "customername". If you have such a table,
then you are ready to go.
Now you've got a generic file structure that you can use to return up to 10
columns with any embedded sql. And you haven't had to grovel and embarass
yourself by asking your DBA to add a blank dummy table to the database.
Now for some code to use it:
=============================
myqueue queue
customername string(60)
customerbalance decimal(20,2)
customerid decimal(20)
end
code
open(tfile)
if error()
stop(error())
end
tfile{prop:sql} = 'select customername,customerid,customerbalance' &|
' from customer order by customername'
if error()
stop(error() & fileerror())
end
loop
next(tfile)
if error() then break.
myqueue.customername = tfile.f1
myqueue.customerid = tfile.f2
myqueue.customerbalance = tfile.f3
add(myqueue)
end
========================
That code fills the queue with the result set form the SQL. The SQL can
contain joins, where, order by, sums, counts, etc. It's very easy to do
when you do it this way.
Oh, and yeah.... it's FAST FAST FAST FAST FAST.
And it works with MSSQL drivers, and I'm sure it works with other Clarion
file drivers, like Oracle, etc.
Later I'll show how to wrap all that up with something like this:
myqueue queue
Customername string(40), name('customer.customername')
Balance decimal(20,2), name('sum(account.balance)')
end
code
QueryFromQueue(MyQueue, 'from customer,account' &|
' where account.customerid = customer.customerid' &|
' group by customer.customername')
Now none of this is template based, so I know many people will consider it
utterly worthless. But for those Clarion programmers who actually program,
it can be very useful. For those of you who have to have a template to
write anything, grow up, stop being babies, getting all your nourishment
from the bottle, and become programmers. Clarion is only as successful as
the abilities and desire of its programming community. I've found that the
reason so many companies are turning away from Clarion is that the
PROGRAMMERS give Clarion a bad name. STOP IT! I'm tired of potential
customers thinking Clarion sucks because they've seen Clarion programs that
suck!
Dan
Printed November 21, 2024, 7:33 am This article has been viewed/printed 35219 times. Google search has resulted in 65 hits on this article since January 25, 2004. |