Working with Time Tables: the Dal Assembly

If a developed application processes large amount of data, calculates and uses a large amount of secondary values, it may require to use DBMS time tables. Working with time tables is executed by means of SQL query runtime command. Working with time tables requires to take into account syntax of SQL queries that is different depending on the DBMS used.

Var
    MB: IMetabase;
    Connection: ISecurityConnection;
    Command: IDalCommand;
    SQL: String = "";
Begin
    MB := MetabaseClass.Active;
    Connection := (MB.ItemById("DB").Open(Null) As IDatabaseInstance).Connection;
    //Create a time table
    SQL := "CREATE GLOBAL TEMPORARY TABLE temp_table (key NUMBER(2,0)) ON COMMIT PRESERVE ROWS"; //Oracle
    SQL := "CREATE TABLE ##temp_table ([KEY] INT NULL)"; //Microsoft SQL Server
    Command := Connection.CreateCommand(SQL);
    Command.Execute;
    //Fill time table with data
    SQL := "INSERT INTO temp_table(key) VALUES(1)"; //Oracle
    SQL := "INSERT INTO ##temp_table VALUES (1)"; //Microsoft SQL Server
    Command.SQL := SQL;
    Command.Execute;
    //Extract data from time table
    SQL := "SELECT * FROM temp_table"; //Oracle
    SQL := "SELECT * FROM ##temp_table"; //Microsoft SQL Server
    Command.SQL := SQL;
    //...
    //Further work with data and time table
    //...
    //Remove time table
    SQL := "DROP TABLE temp_table"; //Oracle
    SQL := "DROP TABLE ##temp_table"; //Microsoft SQL Server
    Command.SQL := SQL;
    Command.Execute;