function TPDO.query_select (sql: PDO_selectRecord): IPDOStatement; overload;

function TPDO.query_select (sql: PDO_enhancedSelect): IPDOStatement; overload;

TPDO.query_select executes an SQL SELECT statement in a single function call, returning the result set (if any) returned by the statement as a PDOStatement object. The SELECT statement is actually assembled by the TPDO object, which produces the statement as a function of the database protocol that is being used. Currently, despite not yet having drivers for these servers, server-specific SQL can be produced for:

• MySQL
• SQLite
• PostgreSQL
• MSSQL/Sybase
• Oracle
• Firebird
• DB2/ODBC

For a query that you need to issue multiple times, you will realize better performance if you prepare an IPDOStatement object using TPDO.prepare_as_is or TPDO.prepare_select and issue the statement with multiple calls to IPDOStatement.execute after binding.

If you do not fetch all of the data in a result set before issuing your next call to TPDO::query_as_is or TPDO::query_select, there should not be any problem. Any remaining result is freed before another query is sent to the database server.

Parameters

sql: The components of the select statement must be placed in the PDO_selectRecord record without any syntax. It is best to always use the "sqlOrderBy" field. For some databases like MySQL and PostgreSQL this field isn't necessary for any select operations, but for other databases, selects involving limits and offsets require the "sqlOrderBy" field is used. It is best to explicitly use "ASC" or "DESC" keywords, especially for column lists. The "sqlLimit" field defaults to zero, meaning there is no limitation on rows retrieved.

    PDO_selectRecord = record
        sqlColumns:     AnsiString;
        sqlTables:      AnsiString;
        sqlConditions:  AnsiString;
        sqlOrderBy:     AnsiString;
        sqlLimit:       Int64; 
        sqlOffset:      Int64;
    end;

sql: A new structure now supports GROUP and HAVING by. Additionally, there is a little function called "full select" that can provide an initialized blank structure, or equally can be used to populate it in one statement.

    TDistinct       = (ALL_ROWS, DISTINCT);
    
    PDO_enhancedSelect = record
        DISTINCT    : TDistinct;
        COLUMNS     : Ansistring;
        FROM        : Ansistring;
        WHERE       : Ansistring;
        GROUP       : Ansistring;
        HAVING      : Ansistring;
        ORDER       : Ansistring;
        LIMIT       : int64;
        OFFSET      : int64;
    end;

    function full_select (const distinct: TDistinct = ALL_ROWS;
        const columns:     ansistring = '';
        const table_joins: ansistring = '';
        const condition:   ansistring = '';
        const orderby:     ansistring = '';
        const limit:       int64 = 0;
        const offset:      int64 = 0;
        const groupby:     ansistring = '';
        const having:      ansistring = ''): PDO_enhancedSelect;
    begin
        Result.DISTINCT := distinct;
        Result.COLUMNS  := columns;
        Result.FROM     := table_joins;
        Result.WHERE    := condition;
        Result.GROUP    := groupby;
        Result.HAVING   := having;
        Result.ORDER    := orderby;
        Result.LIMIT    := limit;
        Result.OFFSET   := offset;
    end;

Return Values

TPDO.query returns a IPDOStatement object.

Example

Example 1. Return a 64-element array.

Type
   TMapArray = array[0..63] of byte;

function load_team_mapping (season: Word): TMapArray;
var
    loop: Byte;
    index, seasonal_id: Byte;
    selectSQL: PDO_selectRecord;
begin
    for loop := 0 to 63 do
         Result[loop] := 0;

    with selectSQL do begin
        sqlColumns    := 'fixed_id, mapped_to_id';
        sqlTables     := 'schedule_mapping';
        sqlConditions := 'season = ' + IntToStr(season) ;
        sqlOrderBy    := 'fixed_id ASC';
        sqlLimit      := 0;
        sqlOffset     := 0;
    end;

    stmt := db.query_select(selectSQL);
    while (stmt.fetch(row)) do begin
        index := row.hash('fixed_id').asByte - 1;  {0 index}
        seasonal_id := row.hash('mapped_to_id').asByte;
        Result[index] := seasonal_id;
    end;
end;

The difference between TPDO.query_as_is and TPDO.query_select is that the former accepts the query as a string, which can be anything, and the later accepts 6 components so that it can assembly a SELECT statement as a function of which the connected database server.

Example 2. Perform a grouped query, then modify the PDO_enhancedSelect slightly for reuse.

procedure TFormPlayerBrowser.create_statistic_groups (PID: integer);
var
    rowIndex: integer;
    statIndex: word;
    GroupSel:  PDO_enhancedSelect;
begin
    groupSel := full_select(ALL_ROWS);

    groupSel.COLUMNS  := 'yyyswww, count(statid) as numberStats';
    groupSel.FROM     := MDB_PLAYER_STATS_POSTGAME;
    groupSel.WHERE    := format('player_id = %d and yyyswww mod 10000 between 5001 and 5200', [PID]);
    groupSel.GROUP    := 'yyyswww';
    groupSel.ORDER    := 'yyyswww DESC';

    stmt := db.query_select(groupSel);


    rowIndex := 0;
    setlength(self.statGroups, stmt.GetNumberRows);
    while stmt.fetch(row) do
    begin
        self.statGroups[rowIndex].yyyswww := row.hash('yyyswww').asInteger;
        self.statGroups[rowIndex].fantasy_points := 0;
        self.statGroups[rowIndex].numberStats := 0;
        setlength(self.statGroups[rowIndex].statistics, row.hash('numberStats').asWord);
        rowIndex := rowIndex + 1;
    end;

    groupSel.COLUMNS        := 'yyyswww, statid, amount';
    groupSel.GROUP          := '';

    stmt := db.query_select(groupSel);
    while stmt.fetch(row) do
    begin
        rowIndex := self.group_search(row.hash('yyyswww').asInteger);
        if (rowIndex >= 0) then
        begin
            statIndex := self.statGroups[rowIndex].numberStats;
            self.statGroups[rowIndex].statistics[statIndex].statid := row.hash('statid').asWord;
            self.statGroups[rowIndex].statistics[statIndex].amount := row.hash('amount').asDouble;
            self.statGroups[rowIndex].numberStats := statIndex + 1;
        end;
    end;

end;