Printed Icetips Article

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.