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 11 2002-10-29 -- Dan Pressnell Sometimes you need to retrieve all the records of a table that matches a
certain condition, and you need to get all the fields in each record.
Clarion's normal SET/NEXT processing can work for you in this case. Let's say you want all the transactions for customerID 234, and you want all the fields that are in each record. It's an easy thing to do, like this:
myQueue queue(tra:record) ! this queue will exactly
! duplicate the dTransactions
! file structure
end
code
free(myQueue)
open(Transactions)
buffer(Transactions, 200) ! speed things up a lot
set(Transactions)
send(Transactions, '/where customerid = ' & 234)
loop
next(transactions)
if error() then break.
myQueue :=: tra:record
add(myQueue)
end
It's really as simple as that. Note the SEND procedure. This sets the SQL
WHERE clause for the data retrieval. It has to be send after the SET and
before the NEXT. With SEND, the WHERE clause is constructed like this:
... WHERE (customerid = 234)
This method is preferable to using prop:sql by sending 'SELECT * FROM
TRANSACTIONS', because it guarantees that your sent query will give you the
results back in the file buffer correctly, and DATE-TIME fields will be
converted for you if you have defined them correctly in your file structure.
If you SET(tra:mykey), your query will have an ORDER BY statement in it, but it's not REALLY necessary to use a key with SET/NEXT. But you do have to resort to a little trick, because the Clarion driver will surround the entire WHERE clause with ( ). If for some reason you do need an ORDER BY in your query, you can construct your SEND() like this, and avoid SQL syntax errors:
SEND(Transactions, '/where customerid = ' & 234 &|
') order by (transactiondate')
See the trick with the '(' and the ')'. If you need more than one column in
the ORDER BY clause, you can do it like this:
SEND(Transactions, '/where customerid = ' & 234 &|
') order by (transactiondate), (status')
Don't rely on this too much, though. You should do this only when you need
all the columns returned. Retrieving all the columns when you only need a
few will really slow things down.Today is November 21, 2024, 3:59 am This article has been viewed 35226 times. Google search has resulted in 15 hits on this article since January 25, 2004.
|
|