Working with Time Tables

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;

Features of Working with Local Time Tables in Microsoft SQL Server DBMS

Microsoft SQL Server DBMS contains two types of time tables: local (the # character is added to name) and global (the ## characters are added to name). The specified above code is used to work with global tables. If the logic of developed application requires to use local time tables, take into account the following feature: on executing queries to Microsoft SQL Server DBMS the kernel of Foresight Analytics Platform always prepares a command for multiple use at the server. Microsoft SQL Server DBMS does not enable the user to create and use time objects in those commands. To solve this conflict, the following code in the Fore.NET language can be used:

Public Shared Sub Main(Params: StartParams);
Var
    MB: IMetabase;
    Connection: ISecurityConnection;
    Command: IDalCommand;
    SQL: String;
Begin
    MB := Params.Metabase;
    Connection := (MB.ItemById["DB"].Open(Null) As IDatabaseInstance).Connection;
    //Create a time table
    SQL := "CREATE TABLE #TEMP_TABLE ([KEY] INT NULL)";
    Command := Connection.CreateCommandRawO(DalCommandOption.dccoWithoutException, SQL);
    Command.Execute();
    //Fill time table with data
    SQL := "INSERT INTO #TEMP_TABLE VALUES (1)";
    Command.SQL := SQL;
    Command.Execute();
    //...
    //Further work with time table
    //...
End Sub;