Skip to content
TheChatArchiveThe Chat Archive
o1-preview-2024-09-12· OpenAIAi2 WildChat-4.8M (AI2)

unit SQLParserEnhanced; interface uses System.Classes, System.SysUtils,…

  1. User

    unit SQLParserEnhanced;

    interface

    uses System.Classes, System.SysUtils, System.Generics.Collections, System.RegularExpressions, Uni, System.StrUtils;

    type /// /// Represents information about a column in the SQL query. /// TColumnInfo = class private FFullName: string; FRealSchemaName: string; FRealTableName: string; FReferencedTableColumn: string; FQueryColumnAlias: string; FIsPKColumn: Boolean; FDataType: string; public property FullName: string read FFullName write FFullName; property RealSchemaName: string read FRealSchemaName write FRealSchemaName; property RealTableName: string read FRealTableName write FRealTableName; property ReferencedTableColumn: string read FReferencedTableColumn write FReferencedTableColumn; property QueryColumnAlias: string read FQueryColumnAlias write FQueryColumnAlias; property IsPKColumn: Boolean read FIsPKColumn write FIsPKColumn; property DataType: string read FDataType write FDataType; end;

    /// /// Represents information about a table used in the SQL query. /// TTableInfo = class private FSchemaName: string; FTableName: string; FAlias: string; public /// /// Schema name of the table. /// property SchemaName: string read FSchemaName write FSchemaName;

    /// <summary>
    /// Name of the table.
    /// </summary>
    property TableName: string read FTableName write FTableName;
    
    /// <summary>
    /// Alias of the table as defined in the query.
    /// </summary>
    property Alias: string read FAlias write FAlias;
    

    end;

    /// /// Parses SQL SELECT statements to extract table and column information. /// TSQLParser = class private FSQLText: string; FTables: TObjectList; FColumns: TObjectList; FConnection: TUniConnection; FPrimaryKeys: TDictionary<string, TList>; FTableColumns: TDictionary<string, TList>; // Cache for table columns

    procedure Parse;
    procedure ParseFromClause;
    procedure ParseSelectClause;
    function SplitSelectColumns(const SelectClause: string): TArray<string>;
    function IsExpression(const S: string): Boolean;
    function IsClauseKeyword(const Token: string): Boolean;
    function GetTableInfoByAliasOrName(const AliasOrName: string): TTableInfo;
    function IsJoinType(const Token: string): Boolean;
    procedure CheckPrimaryKeys; // Checks if columns are primary keys
    procedure FillPrimaryKeyDictionary; // Fills the primary key dictionary
    function GetTableColumnNames(const SchemaName, TableName: string): TList<string>; // Get column names for a table
    function IsReservedWord(const Token: string; const ReservedWords: array of string): Boolean;
    

    public procedure AssignDataTypesToColumns; /// /// Initializes a new instance of the TSQLParser class. /// /// The SQL query text to parse. /// The database connection used to retrieve schema information. constructor Create(const SQLText: string; AConnection: TUniConnection); /// /// Frees all resources used by the TSQLParser instance. /// destructor Destroy; override;

    /// <summary>
    /// Gets the list of tables parsed from the SQL query.
    /// </summary>
    property Tables: TObjectList<TTableInfo> read FTables;
    
    /// <summary>
    /// Gets the list of columns parsed from the SQL query.
    /// </summary>
    property Columns: TObjectList<TColumnInfo> read FColumns;
    

    end;

    implementation

    { TSQLParser }

    constructor TSQLParser.Create(const SQLText: string; AConnection: TUniConnection); begin inherited Create; FSQLText := SQLText; FConnection := AConnection; // Store the provided database connection FTables := TObjectList.Create(True); // Owns objects FColumns := TObjectList.Create(True); // Owns objects FPrimaryKeys := TDictionary<string, TList>.Create; FTableColumns := TDictionary<string, TList>.Create; Parse; FillPrimaryKeyDictionary; // After parsing, fill the primary key dictionary CheckPrimaryKeys; // Then check for primary key columns using the cache AssignDataTypesToColumns; // Assign data types to columns end;

    destructor TSQLParser.Destroy; var ColumnsList: TList; begin // Free the lists inside the dictionaries for ColumnsList in FPrimaryKeys.Values do ColumnsList.Free; FPrimaryKeys.Free;

    for ColumnsList in FTableColumns.Values do ColumnsList.Free; FTableColumns.Free;

    FTables.Free; FColumns.Free; inherited; end;

    procedure TSQLParser.Parse; begin ParseFromClause; ParseSelectClause; end;

    procedure TSQLParser.AssignDataTypesToColumns; var ColumnInfo: TColumnInfo; Query: TUniQuery; SchemaName, TableName, ColumnName, DataType: string; begin for ColumnInfo in FColumns do begin if (ColumnInfo.RealTableName <> '') and not (ColumnInfo.RealTableName.StartsWith('[') and ColumnInfo.RealTableName.EndsWith(']')) and (ColumnInfo.ReferencedTableColumn <> '') and not (ColumnInfo.ReferencedTableColumn.StartsWith('[') and ColumnInfo.ReferencedTableColumn.EndsWith(']')) then begin SchemaName := ColumnInfo.RealSchemaName; TableName := ColumnInfo.RealTableName; ColumnName := ColumnInfo.ReferencedTableColumn;

      // Query the information_schema.COLUMNS to get DATA_TYPE
      Query := TUniQuery.Create(nil);
      try
        Query.Connection := FConnection;
        Query.SQL.Text := 'SELECT DATA_TYPE, COLUMN_TYPE FROM information_schema.COLUMNS ' +
                          'WHERE TABLE_SCHEMA = :SchemaName AND TABLE_NAME = :TableName AND COLUMN_NAME = :ColumnName';
        Query.ParamByName('SchemaName').AsString := SchemaName;
        Query.ParamByName('TableName').AsString := TableName;
        Query.ParamByName('ColumnName').AsString := ColumnName;
        Query.Open;
        if not Query.Eof then
        begin
          // Use COLUMN_TYPE to get full type info (e.g., 'int(11)')
          DataType := Query.FieldByName('COLUMN_TYPE').AsString;
          ColumnInfo.DataType := DataType;
        end
        else
        begin
          ColumnInfo.DataType := 'unknown';
        end;
        Query.Close;
      finally
        Query.Free;
      end;
    end
    else
    begin
      ColumnInfo.DataType := 'unknown';
    end;
    

    end; end;

    function TSQLParser.IsClauseKeyword(const Token: string): Boolean; const ClauseKeywords: array[0..10] of string = ( 'WHERE', 'GROUP BY', 'HAVING', 'ORDER BY', 'LIMIT', 'OFFSET', 'UNION', 'INTERSECT', 'EXCEPT', 'EXISTS', ';' ); var Keyword: string; begin Result := False; for Keyword in ClauseKeywords do begin if SameText(Token, Keyword) then Exit(True); end; end;

    procedure TSQLParser.ParseFromClause; var FromMatch, NextClauseMatch: TMatch; FromPos, NextClausePos: Integer; FromClause: string; Tokens: TArray; i: Integer; Token: string; TableInfo: TTableInfo; NextToken: string; RemainingText: string; begin // Find position of FROM FromMatch := TRegEx.Match(FSQLText, '\bFROM\b', [roIgnoreCase]); if not FromMatch.Success then Exit; // No FROM clause found FromPos := FromMatch.Index + FromMatch.Length;

    // Extract the text after FROM RemainingText := Copy(FSQLText, FromPos + 1, MaxInt).Trim;

    // Find position of the next clause (e.g., WHERE, GROUP BY, etc.) in RemainingText NextClauseMatch := TRegEx.Match(RemainingText, '\b(WHERE|GROUP BY|HAVING|ORDER BY|LIMIT|OFFSET|UNION|INTERSECT|EXCEPT|EXISTS|;)\b', [roIgnoreCase]); if NextClauseMatch.Success then NextClausePos := NextClauseMatch.Index else NextClausePos := Length(RemainingText) + 1; // No next clause found

    // Extract FROM clause FromClause := Copy(RemainingText, 1, NextClausePos - 1).Trim;

    // Split the FROM clause into tokens using spaces and commas, considering possible quoted identifiers Tokens := TRegEx.Split(FromClause, '\s+|,(?![^()]*))'); // Split on spaces or commas not within parentheses

    i := 0; while i < Length(Tokens) do begin Token := Trim(Tokens[i]); if Token = '' then begin Inc(i); Continue; end;

    if IsJoinType(Token) or SameText(Token, 'JOIN') then
    begin
      // Skip join type and 'JOIN' keyword
      Inc(i);
      Continue;
    end
    else if SameText(Token, 'ON') then
    begin
      // Skip tokens until the next 'JOIN' or clause keyword
      Inc(i);
      while (i < Length(Tokens)) and not (IsJoinType(Tokens[i]) or SameText(Tokens[i], 'JOIN') or IsClauseKeyword(Tokens[i])) do
        Inc(i);
      Continue;
    end
    else if SameText(Token, 'USING') then
    begin
      // Skip tokens within 'USING (...)'
      Inc(i);
      while (i < Length(Tokens)) and not (Tokens[i].Contains(')')) do
        Inc(i);
      Inc(i); // Skip the token with ')'
      Continue;
    end
    else
    begin
      // Process table name (may include schema)
      TableInfo := TTableInfo.Create;
    
      // Check for schema.table format, considering possible quoted identifiers
      if Pos('.', Token) > 0 then
      begin
        var DotPos := LastDelimiter('.', Token);
        TableInfo.SchemaName := Copy(Token, 1, DotPos - 1).Trim(['"', '`']);
        TableInfo.TableName := Copy(Token, DotPos + 1, Length(Token)).Trim(['"', '`']);
      end
      else
      begin
        TableInfo.SchemaName := FConnection.Database; // Assume default schema
        TableInfo.TableName := Token.Trim(['"', '`']);
      end;
    
      // Check for alias
      if (i + 1) < Length(Tokens) then
      begin
        NextToken := Tokens[i + 1];
        if SameText(NextToken, 'AS') then
        begin
          // Alias after 'AS' keyword
          if (i + 2) < Length(Tokens) then
          begin
            TableInfo.Alias := Tokens[i + 2].Trim(['"', '`']);
            i := i + 2; // skip 'AS' and the alias
          end
          else
          begin
            // 'AS' without alias - skip 'AS'
            i := i + 1;
          end;
        end
        else if not (IsJoinType(NextToken) or SameText(NextToken, 'JOIN') or SameText(NextToken, 'ON')
          or SameText(NextToken, 'USING') or IsClauseKeyword(NextToken)) then
        begin
          // Alias without 'AS'
          TableInfo.Alias := NextToken.Trim(['"', '`']);
          Inc(i); // move to the alias
        end;
      end;
    
      FTables.Add(TableInfo);
    
      Inc(i); // move to the next token after processing
    end;
    

    end; end;

    function TSQLParser.SplitSelectColumns(const SelectClause: string): TArray; var i: Integer; c: Char; InSingleQuote, InDoubleQuote: Boolean; ParenthesisDepth: Integer; CurrentColumn: string; ColumnsList: TList; begin InSingleQuote := False; InDoubleQuote := False; ParenthesisDepth := 0; CurrentColumn := ''; ColumnsList := TList.Create; try i := 1; while i <= Length(SelectClause) do begin c := SelectClause[i]; if (c = '''') and not InDoubleQuote then begin InSingleQuote := not InSingleQuote; CurrentColumn := CurrentColumn + c; end else if (c = '"') and not InSingleQuote then begin InDoubleQuote := not InDoubleQuote; CurrentColumn := CurrentColumn + c; end else if not InSingleQuote and not InDoubleQuote then begin if c = '(' then begin Inc(ParenthesisDepth); CurrentColumn := CurrentColumn + c; end else if c = ')' then begin Dec(ParenthesisDepth); CurrentColumn := CurrentColumn + c; end else if (c = ',') and (ParenthesisDepth = 0) then begin // Column separator ColumnsList.Add(Trim(CurrentColumn)); CurrentColumn := ''; end else begin CurrentColumn := CurrentColumn + c; end end else begin // Inside quotes CurrentColumn := CurrentColumn + c; end; Inc(i); end; // After the loop, add the last column if CurrentColumn.Trim <> '' then ColumnsList.Add(Trim(CurrentColumn)); Result := ColumnsList.ToArray; finally ColumnsList.Free; end; end;

    procedure TSQLParser.ParseSelectClause; var SelectMatch, FromMatch: TMatch; SelectClause: string; ColumnsStr: string; ColumnList: TArray; ColumnStr: string; ColumnInfo: TColumnInfo; Regex: TRegEx; Match: TMatch; TableAliasOrName: string; ColumnName: string; TableInfo: TTableInfo; ReservedWords: array of string; Tokens: TArray; i: Integer; SelectPos, FromPos: Integer; SelectLength: Integer; begin // Find position of SELECT and FROM SelectMatch := TRegEx.Match(FSQLText, '\bSELECT\b', [roIgnoreCase]); FromMatch := TRegEx.Match(FSQLText, '\bFROM\b', [roIgnoreCase]);

    if (not SelectMatch.Success) or (not FromMatch.Success) then Exit;

    SelectPos := SelectMatch.Index + SelectMatch.Length; FromPos := FromMatch.Index;

    // Extract the SELECT clause content SelectLength := FromPos - SelectPos; if SelectLength <= 0 then Exit; SelectClause := Copy(FSQLText, SelectPos + 1, SelectLength - 1).Trim;

    // List of reserved words that may appear after SELECT ReservedWords := ['ALL', 'DISTINCT', 'DISTINCTROW', 'HIGH_PRIORITY', 'STRAIGHT_JOIN', 'SQL_SMALL_RESULT', 'SQL_BIG_RESULT', 'SQL_BUFFER_RESULT', 'SQL_CACHE', 'SQL_NO_CACHE', 'SQL_CALC_FOUND_ROWS'];

    // Split the SelectClause into tokens to check for reserved words at the beginning Tokens := TRegEx.Split(SelectClause, '\s+'); i := 0; // Skip reserved words at the beginning while (i < Length(Tokens)) and IsReservedWord(Tokens[i], ReservedWords) do begin Inc(i); end;

    // Reconstruct ColumnsStr without leading reserved words if i >= Length(Tokens) then ColumnsStr := '' else ColumnsStr := String.Join(' ', Copy(Tokens, i, Length(Tokens) - i));

    if ColumnsStr.IsEmpty then Exit;

    // Use the new SplitSelectColumns function to split columns ColumnList := SplitSelectColumns(ColumnsStr);

    for i := 0 to Length(ColumnList) - 1 do begin ColumnStr := Trim(ColumnList[i]);

    // Regular expression to extract column expressions and aliases
    // Handles cases like "expression AS alias", "expression alias", or just "expression"
    Regex := TRegEx.Create('^(.*?)(?:\s+(?:AS\s+)?(\w+))?$', [roIgnoreCase]);
    Match := Regex.Match(ColumnStr);
    
    ColumnInfo := TColumnInfo.Create; // Create the ColumnInfo object
    
    if Match.Success then
    begin
      ColumnInfo.FullName := Match.Groups[1].Value.Trim;
      if Match.Groups.Count > 2 then
        ColumnInfo.QueryColumnAlias := Match.Groups[2].Value.Trim
      else
        ColumnInfo.QueryColumnAlias := '';
    end
    else
    begin
      ColumnInfo.FullName := ColumnStr;
      ColumnInfo.QueryColumnAlias := '';
    end;
    
    // Determine if the column is an expression
    if IsExpression(ColumnInfo.FullName) then
    begin
      // It's an expression
      ColumnInfo.RealSchemaName := '[none]';
      ColumnInfo.RealTableName := '[none]';
      ColumnInfo.ReferencedTableColumn := '[none]';
    end
    else
    begin
      // It's a simple column reference
      if ColumnInfo.FullName.Contains('.') then
      begin
        // Parse table alias/name and column name
        var DotPos := LastDelimiter('.', ColumnInfo.FullName);
        TableAliasOrName := Copy(ColumnInfo.FullName, 1, DotPos - 1).Trim(['"', '`']);
        ColumnName := Copy(ColumnInfo.FullName, DotPos + 1, Length(ColumnInfo.FullName)).Trim(['"', '`']);
    
        // Get the table info for this alias or name
        TableInfo := GetTableInfoByAliasOrName(TableAliasOrName);
    
        if Assigned(TableInfo) then
        begin
          ColumnInfo.RealSchemaName := TableInfo.SchemaName;
          ColumnInfo.RealTableName := TableInfo.TableName;
        end
        else
        begin
          // Table alias/name not found; mark as unknown
          ColumnInfo.RealSchemaName := '[unknown schema]';
          ColumnInfo.RealTableName := '[unknown table]';
        end;
        ColumnInfo.ReferencedTableColumn := ColumnName;
      end
      else
      begin
        // Column without table alias/name prefix
        ColumnInfo.ReferencedTableColumn := ColumnInfo.FullName.Trim(['"', '`']);
        ColumnInfo.RealTableName := '';
        ColumnInfo.RealSchemaName := '';
    
        if FTables.Count = 1 then
        begin
          // Assign the only table's name
          ColumnInfo.RealTableName := FTables[0].TableName;
          ColumnInfo.RealSchemaName := FTables[0].SchemaName;
        end
        else if FTables.Count > 1 then
        begin
          // More than one table: try to find the table(s) that contain this column
          var PossibleTables := TList<TTableInfo>.Create;
          try
            for var TInfo in FTables do
            begin
              var ColumnsList := GetTableColumnNames(TInfo.SchemaName, TInfo.TableName);
              if ColumnsList.Contains(LowerCase(ColumnInfo.ReferencedTableColumn)) then
                PossibleTables.Add(TInfo);
            end;
    
            if PossibleTables.Count = 1 then
            begin
              // Only one table contains the column, assign it
              ColumnInfo.RealTableName := PossibleTables[0].TableName;
              ColumnInfo.RealSchemaName := PossibleTables[0].SchemaName;
            end
            else if PossibleTables.Count > 1 then
            begin
              // Column is ambiguous - exists in multiple tables
              ColumnInfo.RealTableName := '[ambiguous]';
              ColumnInfo.RealSchemaName := '[ambiguous schema]';
            end
            else
            begin
              // Column doesn't exist in any known table
              ColumnInfo.RealTableName := '[unknown]';
              ColumnInfo.RealSchemaName := '[unknown schema]';
            end;
          finally
            PossibleTables.Free;
          end;
        end
        else
        begin
          // No table information available
          ColumnInfo.RealTableName := '[no tables parsed]';
          ColumnInfo.RealSchemaName := '[no tables parsed]';
        end;
      end;
    end;
    
    FColumns.Add(ColumnInfo);
    

    end; end;

    function TSQLParser.IsReservedWord(const Token: string; const ReservedWords: array of string): Boolean; var Word: string; begin for Word in ReservedWords do begin if SameText(Token, Word) then Exit(True); end; Result := False; end;

    function TSQLParser.IsExpression(const S: string): Boolean; begin // Simple heuristic to determine if the string is an expression Result := S.Contains('(') or S.Contains('+') or S.Contains('-') or S.Contains('*') or S.Contains('/') or (S.Contains(' ') and not S.Contains('.')); end;

    function TSQLParser.GetTableInfoByAliasOrName(const AliasOrName: string): TTableInfo; var TableInfo: TTableInfo; begin Result := nil; for TableInfo in FTables do begin if SameText(TableInfo.Alias, AliasOrName) then Exit(TableInfo) else if (TableInfo.Alias.IsEmpty) and SameText(TableInfo.TableName, AliasOrName) then Exit(TableInfo); end; end;

    function TSQLParser.GetTableColumnNames(const SchemaName, TableName: string): TList; var ColumnsList: TList; Query: TUniQuery; ColumnName: string; Key: string; begin Key := LowerCase(SchemaName + '.' + TableName); if FTableColumns.TryGetValue(Key, ColumnsList) then begin Result := ColumnsList; Exit; end;

    // Load column names from the database schema ColumnsList := TList.Create; try Query := TUniQuery.Create(nil); try Query.Connection := FConnection; Query.SQL.Text := 'SELECT COLUMN_NAME FROM information_schema.COLUMNS ' + 'WHERE TABLE_SCHEMA = :SchemaName AND TABLE_NAME = :TableName'; Query.ParamByName('SchemaName').AsString := SchemaName; Query.ParamByName('TableName').AsString := TableName; Query.Open;

      while not Query.Eof do
      begin
        ColumnName := LowerCase(Query.FieldByName('COLUMN_NAME').AsString);
        ColumnsList.Add(ColumnName);
        Query.Next;
      end;
      Query.Close;
    finally
      Query.Free;
    end;
    FTableColumns.Add(Key, ColumnsList);
    Result := ColumnsList;
    

    except ColumnsList.Free; raise; end; end;

    function TSQLParser.IsJoinType(const Token: string): Boolean; const JoinTypes: array[0..7] of string = ('JOIN', 'INNER', 'LEFT', 'RIGHT', 'FULL', 'CROSS', 'NATURAL', 'OUTER'); var JoinType: string; begin Result := False; if Token.IsEmpty then Exit; for JoinType in JoinTypes do begin if SameText(Token, JoinType) then Exit(True); end; end;

    procedure TSQLParser.FillPrimaryKeyDictionary; var Query: TUniQuery; TableName, ColumnName, SchemaName: string; ColumnsList: TList; begin if not Assigned(FConnection) then Exit; // Cannot fill without a database connection

    Query := TUniQuery.Create(nil); try Query.Connection := FConnection; // Use the schema name from the connection if not specified SchemaName := FConnection.Database;

    Query.SQL.Text :=
      'SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE ' +
      'WHERE TABLE_SCHEMA = :SchemaName AND CONSTRAINT_NAME = ''PRIMARY'' ' +
      'ORDER BY TABLE_NAME, ORDINAL_POSITION';
    
    Query.ParamByName('SchemaName').AsString := SchemaName;
    Query.Open;
    
    while not Query.Eof do
    begin
      TableName := LowerCase(Query.FieldByName('TABLE_NAME').AsString);
      ColumnName := LowerCase(Query.FieldByName('COLUMN_NAME').AsString);
    
      if not FPrimaryKeys.TryGetValue(TableName, ColumnsList) then
      begin
        ColumnsList := TList<string>.Create;
        FPrimaryKeys.Add(TableName, ColumnsList);
      end;
    
      ColumnsList.Add(ColumnName);
    
      Query.Next;
    end;
    
    Query.Close;
    

    finally Query.Free; end; end;

    procedure TSQLParser.CheckPrimaryKeys; var ColumnInfo: TColumnInfo; TableName: string; ColumnName: string; ColumnsList: TList; begin for ColumnInfo in FColumns do begin if (ColumnInfo.RealTableName <> '') and not (ColumnInfo.RealTableName.StartsWith('[') and ColumnInfo.RealTableName.EndsWith(']')) and (ColumnInfo.ReferencedTableColumn <> '') and not (ColumnInfo.ReferencedTableColumn.StartsWith('[') and ColumnInfo.ReferencedTableColumn.EndsWith(']')) then begin TableName := LowerCase(ColumnInfo.RealTableName); ColumnName := LowerCase(ColumnInfo.ReferencedTableColumn);

      if FPrimaryKeys.TryGetValue(TableName, ColumnsList) then
      begin
        ColumnInfo.IsPKColumn := ColumnsList.Contains(ColumnName);
      end
      else
      begin
        // Table not found in primary keys, so column is not a primary key
        ColumnInfo.IsPKColumn := False;
      end;
    end
    else
    begin
      // For expressions or columns without table reference
      ColumnInfo.IsPKColumn := False;
    end;
    

    end; end;

    end.

    ====================

    Change the Delphi unit above, so the AssignDataTypesToColumns procedure populate the datatypes from running the own query being parsed, using Query.Fields. Field.DataType is a TFieldType, like the code snippet below for reference, so it can handle CAST for example. FDataType must be a TFieldType

    TFieldType = (ftUnknown, ftString, ftSmallint, ftInteger, ftWord, ftBoolean, ftFloat, ftCurrency, ftBCD, ftDate, ftTime, ftDateTime, ftBytes, ftVarBytes, ftAutoInc, ftBlob, ftMemo, ftGraphic, ftFmtMemo, ftParadoxOle, ftDBaseOle, ftTypedBinary, ftCursor, ftFixedChar, ftWideString, ftLargeint, ftADT, ftArray, ftReference, ftDataSet, ftOraBlob, ftOraClob, ftVariant, ftInterface, ftIDispatch, ftGuid, ftTimeStamp, ftFMTBcd, ftFixedWideChar, ftWideMemo, ftOraTimeStamp, ftOraInterval, ftLongWord, ftShortint, ftByte, ftExtended, ftConnection, ftParams, ftStream, ftTimeStampOffset, ftObject, ftSingle);

    // Iterate over fields to get field types: for i := 0 to Query.Fields.Count - 1 do begin Field := Query.Fields[i]; FieldName := Field.FieldName;

    case Field.DataType of
      ftString, ftWideString:
        DelphiFieldType := 'string';
      ftInteger, ftSmallint, ftShortInt, ftWord, ftByte:
        DelphiFieldType := 'Integer';
      ftLargeint:
        DelphiFieldType := 'Int64';
      ftFloat, ftCurrency:
        DelphiFieldType := 'Double';
      ftDate, ftTime, ftDateTime, ftTimeStamp:
        DelphiFieldType := 'TDateTime';
      ftBoolean:
        DelphiFieldType := 'Boolean';
      else
        DelphiFieldType := 'Variant'; // Default to Variant for unsupported types
    end;
    

    end;

  2. Assistant

    Here is the modified Delphi unit with the AssignDataTypesToColumns procedure updated to run the query being parsed and populate the data types using Query.Fields. The FDataType property in TColumnInfo has been changed to TFieldType.

    unit SQLParserEnhanced;
    
    interface
    
    uses
      System.Classes, System.SysUtils, System.Generics.Collections, System.RegularExpressions, Uni, System.StrUtils, Data.DB;
    
    type
      /// <summary>
      /// Represents information about a column in the SQL query.
      /// </summary>
      TColumnInfo = class
      private
        FFullName: string;
        FRealSchemaName: string;
        FRealTableName: string;
        FReferencedTableColumn: string;
        FQueryColumnAlias: string;
        FIsPKColumn: Boolean;
        FDataType: TFieldType;
      public
        property FullName: string read FFullName write FFullName;
        property RealSchemaName: string read FRealSchemaName write FRealSchemaName;
        property RealTableName: string read FRealTableName write FRealTableName;
        property ReferencedTableColumn: string read FReferencedTableColumn write FReferencedTableColumn;
        property QueryColumnAlias: string read FQueryColumnAlias write FQueryColumnAlias;
        property IsPKColumn: Boolean read FIsPKColumn write FIsPKColumn;
        property DataType: TFieldType read FDataType write FDataType;
      end;
    
      /// <summary>
      /// Represents information about a table used in the SQL query.
      /// </summary>
      TTableInfo = class
      private
        FSchemaName: string;
        FTableName: string;
        FAlias: string;
      public
        /// <summary>
        /// Schema name of the table.
        /// </summary>
        property SchemaName: string read FSchemaName write FSchemaName;
    
        /// <summary>
        /// Name of the table.
        /// </summary>
        property TableName: string read FTableName write FTableName;
    
        /// <summary>
        /// Alias of the table as defined in the query.
        /// </summary>
        property Alias: string read FAlias write FAlias;
      end;
    
      /// <summary>
      /// Parses SQL SELECT statements to extract table and column information.
      /// </summary>
      TSQLParser = class
      private
        FSQLText: string;
        FTables: TObjectList<TTableInfo>;
        FColumns: TObjectList<TColumnInfo>;
        FConnection: TUniConnection;
        FPrimaryKeys: TDictionary<string, TList<string>>;
        FTableColumns: TDictionary<string, TList<string>>; // Cache for table columns
    
        procedure Parse;
        procedure ParseFromClause;
        procedure ParseSelectClause;
        function SplitSelectColumns(const SelectClause: string): TArray<string>;
        function IsExpression(const S: string): Boolean;
        function IsClauseKeyword(const Token: string): Boolean;
        function GetTableInfoByAliasOrName(const AliasOrName: string): TTableInfo;
        function IsJoinType(const Token: string): Boolean;
        procedure CheckPrimaryKeys; // Checks if columns are primary keys
        procedure FillPrimaryKeyDictionary; // Fills the primary key dictionary
        function GetTableColumnNames(const SchemaName, TableName: string): TList<string>; // Get column names for a table
        function IsReservedWord(const Token: string; const ReservedWords: array of string): Boolean;
      public
        procedure AssignDataTypesToColumns;
        /// <summary>
        /// Initializes a new instance of the TSQLParser class.
        /// </summary>
        /// <param name="SQLText">The SQL query text to parse.</param>
        /// <param name="AConnection">The database connection used to retrieve schema information.</param>
        constructor Create(const SQLText: string; AConnection: TUniConnection);
        /// <summary>
        /// Frees all resources used by the TSQLParser instance.
        /// </summary>
        destructor Destroy; override;
    
        /// <summary>
        /// Gets the list of tables parsed from the SQL query.
        /// </summary>
        property Tables: TObjectList<TTableInfo> read FTables;
    
        /// <summary>
        /// Gets the list of columns parsed from the SQL query.
        /// </summary>
        property Columns: TObjectList<TColumnInfo> read FColumns;
      end;
    
    implementation
    
    { TSQLParser }
    
    constructor TSQLParser.Create(const SQLText: string; AConnection: TUniConnection);
    begin
      inherited Create;
      FSQLText := SQLText;
      FConnection := AConnection; // Store the provided database connection
      FTables := TObjectList<TTableInfo>.Create(True); // Owns objects
      FColumns := TObjectList<TColumnInfo>.Create(True); // Owns objects
      FPrimaryKeys := TDictionary<string, TList<string>>.Create;
      FTableColumns := TDictionary<string, TList<string>>.Create;
      Parse;
      FillPrimaryKeyDictionary; // After parsing, fill the primary key dictionary
      CheckPrimaryKeys; // Then check for primary key columns using the cache
      AssignDataTypesToColumns; // Assign data types to columns
    end;
    
    destructor TSQLParser.Destroy;
    var
      ColumnsList: TList<string>;
    begin
      // Free the lists inside the dictionaries
      for ColumnsList in FPrimaryKeys.Values do
        ColumnsList.Free;
      FPrimaryKeys.Free;
    
      for ColumnsList in FTableColumns.Values do
        ColumnsList.Free;
      FTableColumns.Free;
    
      FTables.Free;
      FColumns.Free;
      inherited;
    end;
    
    procedure TSQLParser.Parse;
    begin
      ParseFromClause;
      ParseSelectClause;
    end;
    
    procedure TSQLParser.AssignDataTypesToColumns;
    var
      Query: TUniQuery;
      Field: TField;
      i: Integer;
    begin
      Query := TUniQuery.Create(nil);
      try
        Query.Connection := FConnection;
        Query.SQL.Text := FSQLText;
        try
          Query.Open;
          // Iterate over fields to get field types
          for i := 0 to Query.Fields.Count - 1 do
          begin
            if i < FColumns.Count then
            begin
              Field := Query.Fields[i];
              FColumns[i].DataType := Field.DataType;
            end
            else
            begin
              // Field count greater than column count, handle as needed
              Break;
            end;
          end;
          Query.Close;
        except
          on E: Exception do
          begin
            // Handle exception, e.g., log error message
            // Optionally set FDataType to ftUnknown for all columns
            for i := 0 to FColumns.Count - 1 do
              FColumns[i].DataType := ftUnknown;
          end;
        end;
      finally
        Query.Free;
      end;
    end;
    
    function TSQLParser.IsClauseKeyword(const Token: string): Boolean;
    const
      ClauseKeywords: array[0..10] of string = (
        'WHERE', 'GROUP BY', 'HAVING', 'ORDER BY', 'LIMIT',
        'OFFSET', 'UNION', 'INTERSECT', 'EXCEPT', 'EXISTS', ';'
      );
    var
      Keyword: string;
    begin
      Result := False;
      for Keyword in ClauseKeywords do
      begin
        if SameText(Token, Keyword) then
          Exit(True);
      end;
    end;
    
    procedure TSQLParser.ParseFromClause;
    var
      FromMatch, NextClauseMatch: TMatch;
      FromPos, NextClausePos: Integer;
      FromClause: string;
      Tokens: TArray<string>;
      i: Integer;
      Token: string;
      TableInfo: TTableInfo;
      NextToken: string;
      RemainingText: string;
    begin
      // Find position of FROM
      FromMatch := TRegEx.Match(FSQLText, '\bFROM\b', [roIgnoreCase]);
      if not FromMatch.Success then Exit; // No FROM clause found
      FromPos := FromMatch.Index + FromMatch.Length;
    
      // Extract the text after FROM
      RemainingText := Copy(FSQLText, FromPos + 1, MaxInt).Trim;
    
      // Find position of the next clause (e.g., WHERE, GROUP BY, etc.) in RemainingText
      NextClauseMatch := TRegEx.Match(RemainingText, '\b(WHERE|GROUP BY|HAVING|ORDER BY|LIMIT|OFFSET|UNION|INTERSECT|EXCEPT|EXISTS|;)\b', [roIgnoreCase]);
      if NextClauseMatch.Success then
        NextClausePos := NextClauseMatch.Index
      else
        NextClausePos := Length(RemainingText) + 1; // No next clause found
    
      // Extract FROM clause
      FromClause := Copy(RemainingText, 1, NextClausePos - 1).Trim;
    
      // Split the FROM clause into tokens using spaces and commas, considering possible quoted identifiers
      Tokens := TRegEx.Split(FromClause, '\s+|,(?![^()]*\))'); // Split on spaces or commas not within parentheses
    
      i := 0;
      while i < Length(Tokens) do
      begin
        Token := Trim(Tokens[i]);
        if Token = '' then
        begin
          Inc(i);
          Continue;
        end;
    
        if IsJoinType(Token) or SameText(Token, 'JOIN') then
        begin
          // Skip join type and 'JOIN' keyword
          Inc(i);
          Continue;
        end
        else if SameText(Token, 'ON') then
        begin
          // Skip tokens until the next 'JOIN' or clause keyword
          Inc(i);
          while (i < Length(Tokens)) and not (IsJoinType(Tokens[i]) or SameText(Tokens[i], 'JOIN') or IsClauseKeyword(Tokens[i])) do
            Inc(i);
          Continue;
        end
        else if SameText(Token, 'USING') then
        begin
          // Skip tokens within 'USING (...)'
          Inc(i);
          while (i < Length(Tokens)) and not (Tokens[i].Contains(')')) do
            Inc(i);
          Inc(i); // Skip the token with ')'
          Continue;
        end
        else
        begin
          // Process table name (may include schema)
          TableInfo := TTableInfo.Create;
    
          // Check for schema.table format, considering possible quoted identifiers
          if Pos('.', Token) > 0 then
          begin
            var DotPos := LastDelimiter('.', Token);
            TableInfo.SchemaName := Copy(Token, 1, DotPos - 1).Trim(['"', '`']);
            TableInfo.TableName := Copy(Token, DotPos + 1, Length(Token)).Trim(['"', '`']);
          end
          else
          begin
            TableInfo.SchemaName := FConnection.Database; // Assume default schema
            TableInfo.TableName := Token.Trim(['"', '`']);
          end;
    
          // Check for alias
          if (i + 1) < Length(Tokens) then
          begin
            NextToken := Tokens[i + 1];
            if SameText(NextToken, 'AS') then
            begin
              // Alias after 'AS' keyword
              if (i + 2) < Length(Tokens) then
              begin
                TableInfo.Alias := Tokens[i + 2].Trim(['"', '`']);
                i := i + 2; // skip 'AS' and the alias
              end
              else
              begin
                // 'AS' without alias - skip 'AS'
                i := i + 1;
              end;
            end
            else if not (IsJoinType(NextToken) or SameText(NextToken, 'JOIN') or SameText(NextToken, 'ON')
              or SameText(NextToken, 'USING') or IsClauseKeyword(NextToken)) then
            begin
              // Alias without 'AS'
              TableInfo.Alias := NextToken.Trim(['"', '`']);
              Inc(i); // move to the alias
            end;
          end;
    
          FTables.Add(TableInfo);
    
          Inc(i); // move to the next token after processing
        end;
      end;
    end;
    
    function TSQLParser.SplitSelectColumns(const SelectClause: string): TArray<string>;
    var
      i: Integer;
      c: Char;
      InSingleQuote, InDoubleQuote: Boolean;
      ParenthesisDepth: Integer;
      CurrentColumn: string;
      ColumnsList: TList<string>;
    begin
      InSingleQuote := False;
      InDoubleQuote := False;
      ParenthesisDepth := 0;
      CurrentColumn := '';
      ColumnsList := TList<string>.Create;
      try
        i := 1;
        while i <= Length(SelectClause) do
        begin
          c := SelectClause[i];
          if (c = '''') and not InDoubleQuote then
          begin
            InSingleQuote := not InSingleQuote;
            CurrentColumn := CurrentColumn + c;
          end
          else if (c = '"') and not InSingleQuote then
          begin
            InDoubleQuote := not InDoubleQuote;
            CurrentColumn := CurrentColumn + c;
          end
          else if not InSingleQuote and not InDoubleQuote then
          begin
            if c = '(' then
            begin
              Inc(ParenthesisDepth);
              CurrentColumn := CurrentColumn + c;
            end
            else if c = ')' then
            begin
              Dec(ParenthesisDepth);
              CurrentColumn := CurrentColumn + c;
            end
            else if (c = ',') and (ParenthesisDepth = 0) then
            begin
              // Column separator
              ColumnsList.Add(Trim(CurrentColumn));
              CurrentColumn := '';
            end
            else
            begin
              CurrentColumn := CurrentColumn + c;
            end
          end
          else
          begin
            // Inside quotes
            CurrentColumn := CurrentColumn + c;
          end;
          Inc(i);
        end;
        // After the loop, add the last column
        if CurrentColumn.Trim <> '' then
          ColumnsList.Add(Trim(CurrentColumn));
        Result := ColumnsList.ToArray;
      finally
        ColumnsList.Free;
      end;
    end;
    
    procedure TSQLParser.ParseSelectClause;
    var
      SelectMatch, FromMatch: TMatch;
      SelectClause: string;
      ColumnsStr: string;
      ColumnList: TArray<string>;
      ColumnStr: string;
      ColumnInfo: TColumnInfo;
      Regex: TRegEx;
      Match: TMatch;
      TableAliasOrName: string;
      ColumnName: string;
      TableInfo: TTableInfo;
      ReservedWords: array of string;
      Tokens: TArray<string>;
      i: Integer;
      SelectPos, FromPos: Integer;
      SelectLength: Integer;
    begin
      // Find position of SELECT and FROM
      SelectMatch := TRegEx.Match(FSQLText, '\bSELECT\b', [roIgnoreCase]);
      FromMatch := TRegEx.Match(FSQLText, '\bFROM\b', [roIgnoreCase]);
    
      if (not SelectMatch.Success) or (not FromMatch.Success) then Exit;
    
      SelectPos := SelectMatch.Index + SelectMatch.Length;
      FromPos := FromMatch.Index;
    
      // Extract the SELECT clause content
      SelectLength := FromPos - SelectPos;
      if SelectLength <= 0 then Exit;
      SelectClause := Copy(FSQLText, SelectPos + 1, SelectLength - 1).Trim;
    
      // List of reserved words that may appear after SELECT
      ReservedWords := ['ALL', 'DISTINCT', 'DISTINCTROW', 'HIGH_PRIORITY', 'STRAIGHT_JOIN',
                        'SQL_SMALL_RESULT', 'SQL_BIG_RESULT', 'SQL_BUFFER_RESULT', 'SQL_CACHE',
                        'SQL_NO_CACHE', 'SQL_CALC_FOUND_ROWS'];
    
      // Split the SelectClause into tokens to check for reserved words at the beginning
      Tokens := TRegEx.Split(SelectClause, '\s+');
      i := 0;
      // Skip reserved words at the beginning
      while (i < Length(Tokens)) and IsReservedWord(Tokens[i], ReservedWords) do
      begin
        Inc(i);
      end;
    
      // Reconstruct ColumnsStr without leading reserved words
      if i >= Length(Tokens) then
        ColumnsStr := ''
      else
        ColumnsStr := String.Join(' ', Copy(Tokens, i, Length(Tokens) - i));
    
      if ColumnsStr.IsEmpty then Exit;
    
      // Use the new SplitSelectColumns function to split columns
      ColumnList := SplitSelectColumns(ColumnsStr);
    
      for i := 0 to Length(ColumnList) - 1 do
      begin
        ColumnStr := Trim(ColumnList[i]);
    
        // Regular expression to extract column expressions and aliases
        // Handles cases like "expression AS alias", "expression alias", or just "expression"
        Regex := TRegEx.Create('^(.*?)(?:\s+(?:AS\s+)?(\w+))?$', [roIgnoreCase]);
        Match := Regex.Match(ColumnStr);
    
        ColumnInfo := TColumnInfo.Create; // Create the ColumnInfo object
    
        if Match.Success then
        begin
          ColumnInfo.FullName := Match.Groups[1].Value.Trim;
          if Match.Groups.Count > 2 then
            ColumnInfo.QueryColumnAlias := Match.Groups[2].Value.Trim
          else
            ColumnInfo.QueryColumnAlias := '';
        end
        else
        begin
          ColumnInfo.FullName := ColumnStr;
          ColumnInfo.QueryColumnAlias := '';
        end;
    
        // Determine if the column is an expression
        if IsExpression(ColumnInfo.FullName) then
        begin
          // It's an expression
          ColumnInfo.RealSchemaName := '[none]';
          ColumnInfo.RealTableName := '[none]';
          ColumnInfo.ReferencedTableColumn := '[none]';
        end
        else
        begin
          // It's a simple column reference
          if ColumnInfo.FullName.Contains('.') then
          begin
            // Parse table alias/name and column name
            var DotPos := LastDelimiter('.', ColumnInfo.FullName);
            TableAliasOrName := Copy(ColumnInfo.FullName, 1, DotPos - 1).Trim(['"', '`']);
            ColumnName := Copy(ColumnInfo.FullName, DotPos + 1, Length(ColumnInfo.FullName)).Trim(['"', '`']);
    
            // Get the table info for this alias or name
            TableInfo := GetTableInfoByAliasOrName(TableAliasOrName);
    
            if Assigned(TableInfo) then
            begin
              ColumnInfo.RealSchemaName := TableInfo.SchemaName;
              ColumnInfo.RealTableName := TableInfo.TableName;
            end
            else
            begin
              // Table alias/name not found; mark as unknown
              ColumnInfo.RealSchemaName := '[unknown schema]';
              ColumnInfo.RealTableName := '[unknown table]';
            end;
            ColumnInfo.ReferencedTableColumn := ColumnName;
          end
          else
          begin
            // Column without table alias/name prefix
            ColumnInfo.ReferencedTableColumn := ColumnInfo.FullName.Trim(['"', '`']);
            ColumnInfo.RealTableName := '';
            ColumnInfo.RealSchemaName := '';
    
            if FTables.Count = 1 then
            begin
              // Assign the only table's name
              ColumnInfo.RealTableName := FTables[0].TableName;
              ColumnInfo.RealSchemaName := FTables[0].SchemaName;
            end
            else if FTables.Count > 1 then
            begin
              // More than one table: try to find the table(s) that contain this column
              var PossibleTables := TList<TTableInfo>.Create;
              try
                for var TInfo in FTables do
                begin
                  var ColumnsList := GetTableColumnNames(TInfo.SchemaName, TInfo.TableName);
                  if ColumnsList.Contains(LowerCase(ColumnInfo.ReferencedTableColumn)) then
                    PossibleTables.Add(TInfo);
                end;
    
                if PossibleTables.Count = 1 then
                begin
                  // Only one table contains the column, assign it
                  ColumnInfo.RealTableName := PossibleTables[0].TableName;
                  ColumnInfo.RealSchemaName := PossibleTables[0].SchemaName;
                end
                else if PossibleTables.Count > 1 then
                begin
                  // Column is ambiguous - exists in multiple tables
                  ColumnInfo.RealTableName := '[ambiguous]';
                  ColumnInfo.RealSchemaName := '[ambiguous schema]';
                end
                else
                begin
                  // Column doesn't exist in any known table
                  ColumnInfo.RealTableName := '[unknown]';
                  ColumnInfo.RealSchemaName := '[unknown schema]';
                end;
              finally
                PossibleTables.Free;
              end;
            end
            else
            begin
              // No table information available
              ColumnInfo.RealTableName := '[no tables parsed]';
              ColumnInfo.RealSchemaName := '[no tables parsed]';
            end;
          end;
        end;
    
        FColumns.Add(ColumnInfo);
      end;
    end;
    
    function TSQLParser.IsReservedWord(const Token: string; const ReservedWords: array of string): Boolean;
    var
      Word: string;
    begin
      for Word in ReservedWords do
      begin
        if SameText(Token, Word) then
          Exit(True);
      end;
      Result := False;
    end;
    
    function TSQLParser.IsExpression(const S: string): Boolean;
    begin
      // Simple heuristic to determine if the string is an expression
      Result := S.Contains('(') or
                S.Contains('+') or
                S.Contains('-') or
                S.Contains('*') or
                S.Contains('/') or
                (S.Contains(' ') and not S.Contains('.'));
    end;
    
    function TSQLParser.GetTableInfoByAliasOrName(const AliasOrName: string): TTableInfo;
    var
      TableInfo: TTableInfo;
    begin
      Result := nil;
      for TableInfo in FTables do
      begin
        if SameText(TableInfo.Alias, AliasOrName) then
          Exit(TableInfo)
        else if (TableInfo.Alias.IsEmpty) and SameText(TableInfo.TableName, AliasOrName) then
          Exit(TableInfo);
      end;
    end;
    
    function TSQLParser.GetTableColumnNames(const SchemaName, TableName: string): TList<string>;
    var
      ColumnsList: TList<string>;
      Query: TUniQuery;
      ColumnName: string;
      Key: string;
    begin
      Key := LowerCase(SchemaName + '.' + TableName);
      if FTableColumns.TryGetValue(Key, ColumnsList) then
      begin
        Result := ColumnsList;
        Exit;
      end;
    
      // Load column names from the database schema
      ColumnsList := TList<string>.Create;
      try
        Query := TUniQuery.Create(nil);
        try
          Query.Connection := FConnection;
          Query.SQL.Text := 'SELECT COLUMN_NAME FROM information_schema.COLUMNS ' +
                            'WHERE TABLE_SCHEMA = :SchemaName AND TABLE_NAME = :TableName';
          Query.ParamByName('SchemaName').AsString := SchemaName;
          Query.ParamByName('TableName').AsString := TableName;
          Query.Open;
    
          while not Query.Eof do
          begin
            ColumnName := LowerCase(Query.FieldByName('COLUMN_NAME').AsString);
            ColumnsList.Add(ColumnName);
            Query.Next;
          end;
          Query.Close;
        finally
          Query.Free;
        end;
        FTableColumns.Add(Key, ColumnsList);
        Result := ColumnsList;
      except
        ColumnsList.Free;
        raise;
      end;
    end;
    
    function TSQLParser.IsJoinType(const Token: string): Boolean;
    const
      JoinTypes: array[0..7] of string = ('JOIN', 'INNER', 'LEFT', 'RIGHT', 'FULL', 'CROSS', 'NATURAL', 'OUTER');
    var
      JoinType: string;
    begin
      Result := False;
      if Token.IsEmpty then Exit;
      for JoinType in JoinTypes do
      begin
        if SameText(Token, JoinType) then
          Exit(True);
      end;
    end;
    
    procedure TSQLParser.FillPrimaryKeyDictionary;
    var
      Query: TUniQuery;
      TableName, ColumnName, SchemaName: string;
      ColumnsList: TList<string>;
    begin
      if not Assigned(FConnection) then
        Exit; // Cannot fill without a database connection
    
      Query := TUniQuery.Create(nil);
      try
        Query.Connection := FConnection;
        // Use the schema name from the connection if not specified
        SchemaName := FConnection.Database;
    
        Query.SQL.Text :=
          'SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE ' +
          'WHERE TABLE_SCHEMA = :SchemaName AND CONSTRAINT_NAME = ''PRIMARY'' ' +
          'ORDER BY TABLE_NAME, ORDINAL_POSITION';
    
        Query.ParamByName('SchemaName').AsString := SchemaName;
        Query.Open;
    
        while not Query.Eof do
        begin
          TableName := LowerCase(Query.FieldByName('TABLE_NAME').AsString);
          ColumnName := LowerCase(Query.FieldByName('COLUMN_NAME').AsString);
    
          if not FPrimaryKeys.TryGetValue(TableName, ColumnsList) then
          begin
            ColumnsList := TList<string>.Create;
            FPrimaryKeys.Add(TableName, ColumnsList);
          end;
    
          ColumnsList.Add(ColumnName);
    
          Query.Next;
        end;
    
        Query.Close;
      finally
        Query.Free;
      end;
    end;
    
    procedure TSQLParser.CheckPrimaryKeys;
    var
      ColumnInfo: TColumnInfo;
      TableName: string;
      ColumnName: string;
      ColumnsList: TList<string>;
    begin
      for ColumnInfo in FColumns do
      begin
        if (ColumnInfo.RealTableName <> '') and
           not (ColumnInfo.RealTableName.StartsWith('[') and ColumnInfo.RealTableName.EndsWith(']')) and
           (ColumnInfo.ReferencedTableColumn <> '') and
           not (ColumnInfo.ReferencedTableColumn.StartsWith('[') and ColumnInfo.ReferencedTableColumn.EndsWith(']')) then
        begin
          TableName := LowerCase(ColumnInfo.RealTableName);
          ColumnName := LowerCase(ColumnInfo.ReferencedTableColumn);
    
          if FPrimaryKeys.TryGetValue(TableName, ColumnsList) then
          begin
            ColumnInfo.IsPKColumn := ColumnsList.Contains(ColumnName);
          end
          else
          begin
            // Table not found in primary keys, so column is not a primary key
            ColumnInfo.IsPKColumn := False;
          end;
        end
        else
        begin
          // For expressions or columns without table reference
          ColumnInfo.IsPKColumn := False;
        end;
      end;
    end;
    
    end.
    

    Explanation of Changes:

    1. Changed the FDataType Property:

      • In the TColumnInfo class, the FDataType property type has been changed from string to TFieldType to store the data type as a TFieldType, as required.
    2. Updated the AssignDataTypesToColumns Procedure:

      • The procedure now runs the query being parsed (FSQLText) using a TUniQuery object.
      • It opens the query and iterates over the Fields of the query.
      • For each field, it assigns the Field.DataType to the corresponding ColumnInfo.DataType.
      • It ensures that the number of fields in the query matches the number of parsed columns (FColumns). If there's a mismatch, it handles it appropriately.
      • Exception handling is added to catch any errors when running the query.
    3. Removed Previous Data Type Assignment Logic:

      • The previous logic that queried the information_schema.COLUMNS table to get data types has been removed, as it's no longer needed.
    4. Exception Handling:

      • If the query cannot be executed (e.g., due to an error), the code now assigns ftUnknown to each ColumnInfo.DataType to indicate that the data type could not be determined.

    Usage Notes:

    • Ensure that the SQL query (FSQLText) can be safely executed. If the query contains parameters or is not safe to run as-is, additional handling will be necessary.
    • This code assumes that the fields in the query result correspond to the columns in the parsed SELECT clause in the same order.
    • Be cautious when running dynamic SQL queries to prevent SQL injection or unintended side effects.

    I hope this modification meets your requirements.