The result of a SQL command can be a set of 0 or more records, this is called a result set. SQL commands like select * from mytable generate a result set, a command like delete from mytable where id > 10 does not create a result set.
If a result set is created the query class can be used to iterate through the records in the result set. The current record in the result set is pointed to by a cursor, by moving the cursor a different record of the result set can be read. By moving the cursor to each record in a result set all the records in a result set are read.
A default cursor can only move forward to the next record and it always starts in front of the first record. If a cursor must be able to move up and down in a result set then the method putScrollable/2 must be called before a SQL statement is prepared and/or direct executed.
To determine if a SQL statement has created a result set the number of columns in the result set can be retrieved by calling the method numColumns/1. If a positive number is returned a result set was created, however the result set can still contain 0 records.
| Query<-numColumns(NumCol) |
The names of the columns in the result set can be retrieved by calling the method columns/2, a list is returned with the column names in it.
| Query<-columns(ColNames, true) |
It is then possible to adjust a grid control so that it contains the correct number of columns and column names of the result set. The following source code example (taken from the example program dbview) demonstrates this.
| Query<-numColumns(NumCol), %Adjust the number of rows and columns of the grid control Grid<-putFixedCols(1), Grid<-putFixedRows(1), Grid<-putRows(0), Grid<-putCols(NumCol), %If the result set contains columns then display the result set, else do nothing ((NumCol > 0) -> (Query<-columns(ColNames, true), displayColumnHeaders(Grid, 1, ColNames), !, displayRows(Query, Grid)) ; true). |
Displaying the column headers is a matter of iterating through the list of column names and assigning these to the cells of the first fixed row of the grid.
| browseFrame::displayColumnHeaders( Grid, _, [] ) :- Grid<-forceRedraw, %Reached end-of-list have grid immediately redrawn !. browseFrame::displayColumnHeaders( Grid, Pos, [X|List] ) :- Grid<-putCellText(Pos, 0, X), Pos2 is Pos+1, displayColumnHeaders( Grid, Pos2, List ). |
More meta information about each column can be retrieved by using the method columnInfo/8 and columnInfoName/8. It is not possible to exactly determine the number of rows in the result set.
After a SQL statement was executed the cursor is placed in front of the first record, so by calling the method fetchNextRow/3 the cursor is moved to the first record and the values of the columns of the record are put into a list. The second parameter of fetchNextRow/3 is a boolean output parameter which is assigned true if the end of the result set is reached. In the example below a recursive predicate is used to iterate from the top to the end of the result set .
| browseFrame::displayRows( Query, Grid ) :- %Go to the next row of the result set Query<-fetchNextRow(Values, Finished, true), (Finished==true -> (!) ; (displayValues(Values, Grid), displayRows(Query, Grid)) ). |
If not the complete record must be returned but only one or more columns the methods fetchCurColPos/3 and fetchCurColName/3 can be used, both these methods do not move the cursor but retrieve data from the current record pointed to by the cursor. The method fetchCurRow/2 can be used to retrieve all columns of the current record and nextRow/2 moves the cursor to the next record without retrieving the values of the next record.
If the cursor is not a forward-only cursor the following methods can be used for iterating through the result set; prevRow/2, firstRow/1, lastRow/1, absolute/2 and relative/2.
In the example above a recursive predicate is used to iterate through the entire result
set of query, the number of recursive calls is equal to the number of records in a result
set. This can be a disadvantage because it may consume a lot of memory if the result set
is very large.
There is another technique for iterating through a result set which uses only the memory
required for a single record. The idea is to use a fail driven loop, after the values of a
record have been displayed the built-in predicate fail/0 is
called. Failing of the predicate then starts and while Trinc-Prolog searches for a redo of
a predicate (to find another solution) all used memory is released. Instead of the method fetchNextRow/3 the method fetchNextRowRedo/3 must be used. This version can
be redone by Trinc-Prolog, so after redoing fetchNextRowRedo/3
the program execution continues again with the remaining predicates after fetchNextRowRedo/3. The same example as above but
with a fail driven loop is:
| browseFrame::displayRows( Query, Grid ) :- %Go to the next row of the result set of the query Query<-fetchNextRowRedo(Values, Finished, true), ((Finished == true) -> (!) ; (displayValues(Values, Grid)) ), %Start failing to create a fail driven loop, the method 'fetchNextRowRedo' %can be redone on a fail. fail. |
The following methods can also be used in a fail driven loop: nextRowRedo/2 and prevRowRedo/2.
If the values of a single record are in a list then these values are easily displayed by using the same kind of recursive predicate as in the previous example. In the example below a row is appended to a grid and the list of column values is iterated to fill the cells of the new row of the grid.
| browseFrame::displayValues(Values, Grid) :- %Append a new row to the grid Grid<-appendRow(RowNum), %Display the row number in the first column of the grid Grid<-putCellText(0, RowNum, RowNum), %Display the values in the list in the current row, start at column 1 displayVal(Values, Grid, RowNum, 1), !. browseFrame::displayVal([], _, _, _). browseFrame::displayVal([X|List], Grid, Row, Col) :- Grid<-putCellText(Col, Row, X), Col2 is Col+1, displayVal(List, Grid, Row, Col2). |