Getting field and table information from DataSet or SQL text
lazarus / 16-Databases

----------------------------------------------------------------------
patyit · 2021-08-11 21:51

Hi all !

I have a query e.g.:

SELECT A.id, A.field1 AS name, B.field2 AS address
FROM names A
LEFT JOIN addresses B ON B.id = A.id
ORDER BY A.field1

The DataSet shows data in the following field: id, name, address.
I would also like to take the following information from DataSet or
SQL.Text somewhere: the name of the field, any aliases, and which
table this field is in.
(A.field1 AS name in table names A and B.field2 AS address in table
adresses B)
I need some kind of SQL parser, but I can't find one to use. I was
looking at fpsqlparser but I can't apply it to get the right
information.

Does anyone have an idea what can be done?
(I have tried Zeos, SQLDB and IBX for Lazarus)

ps. I forgot to mention that the database is Firebird 3.

----------------------------------------------------------------------
patyit · 2021-08-12 11:59

I have temporarily solved what I need to do now !
Not well tested ! If anyone wants to improve further, I enclose the
source code.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i type 
 TSQLSelectRec = record    select,    from,    join,    where,   
group,    having,    plan,    order,    rows : string;  end;
... function ParseSelectSQL(const sqs: string): TSQLSelectRec;var  i,
j, n : integer;begin  Result.select := '';  Result.from   := ''; 
Result.join   := '';  Result.where  := '';  Result.group  := ''; 
Result.having := '';  Result.plan   := '';  Result.order  := ''; 
Result.rows   := '';  if sqs > '' then begin    // SELECT    if
 Utf8Pos('SELECT ', sqs) > 0 then begin      i := Utf8Pos('SELECT ',
sqs)+6;      j := Utf8Pos('FROM ', sqs)-1;      Result.select :=
Trim(Utf8Copy(sqs, i, j-i));    end else begin      MessageDlg('SQL
error, no "SELECT" clause ...', mtError, [mbOk], 0);      Exit;   
end;     // FROM    if Utf8Pos('FROM ', sqs) > 0 then begin      i :=
Utf8Pos('FROM ', sqs)+4;      j := Utf8Pos('WHERE ', sqs)-1;     
Result.from := Trim(Utf8Copy(sqs, i, j-i)); // joins are in content ! 
  end else begin      MessageDlg('SQL error, no "FROM" clause ...',
mtError, [mbOk], 0);      Exit;    end;     // WHERE    if
Utf8Pos('WHERE ', sqs) > 0 then begin      if Utf8Pos('GROUP BY ',
sqs) > 0 then begin        i := Utf8Pos('WHERE ', sqs)+5;        j :=
Utf8Pos('GROUP BY ', sqs)-1;        Result.where := Trim(Utf8Copy(sqs,
i, j-i));      end else if Utf8Pos('PLAN ', sqs) > 0 then begin      
i := Utf8Pos('WHERE ', sqs)+5;        j := Utf8Pos('PLAN ', sqs)-1;  
    Result.where := Trim(Utf8Copy(sqs, i, j-i));      end else if
Utf8Pos('ORDER BY ', sqs) > 0 then begin        i := Utf8Pos('WHERE ',
sqs)+5;        j := Utf8Pos('ORDER BY ', sqs)-1;        Result.where
:= Trim(Utf8Copy(sqs, i, j-i));      end else if Utf8Pos('ROWS ', sqs)
> 0 then begin        i := Utf8Pos('WHERE ', sqs)+5;        j :=
Utf8Pos('ROWS ', sqs)-1;        Result.where := Trim(Utf8Copy(sqs, i,
j-i));      end else begin        i := Utf8Pos('WHERE ', sqs)+5;     
 j := Utf8Length(sqs);        Result.where := Trim(Utf8Copy(sqs, i,
j-i));       end;     end;     // GROUP BY    if Utf8Pos('GROUP BY ',
sqs) > 0 then begin      if Utf8Pos('HAVING ', sqs) > 0 then begin   
   i := Utf8Pos('GROUP BY ', sqs)+8;        j := Utf8Pos('HAVING ',
sqs)-1;        Result.group := Trim(Utf8Copy(sqs, i, j-i));        //
HAVING        if Utf8Pos('PLAN ', sqs) > 0 then begin          i :=
Utf8Pos('HAVING ', sqs)+6;          j := Utf8Pos('PLAN ', sqs)-1;    
    Result.having := Trim(Utf8Copy(sqs, i, j-i));        end else if
Utf8Pos('ORDER BY ', sqs) > 0 then begin          i := Utf8Pos('HAVING
', sqs)+6;          j := Utf8Pos('ORDER BY ', sqs)-1;         
Result.having := Trim(Utf8Copy(sqs, i, j-i));        end else if
Utf8Pos('ROWS ', sqs) > 0 then begin          i := Utf8Pos('HAVING ',
sqs)+6;          j := Utf8Pos('ROWS ', sqs)-1;          Result.having
:= Trim(Utf8Copy(sqs, i, j-i));        end else begin          i :=
Utf8Pos('HAVING ', sqs)+6;          j := Utf8Length(sqs);         
Result.having := Trim(Utf8Copy(sqs, i, j-i));        end;      end
else if Utf8Pos('PLAN ', sqs) > 0 then begin        i :=
Utf8Pos('GROUP BY ', sqs)+8;        j := Utf8Pos('PLAN ', sqs)-1;    
  Result.group := Trim(Utf8Copy(sqs, i, j-i));      end else if
Utf8Pos('ORDER BY ', sqs) > 0 then begin        i := Utf8Pos('GROUP BY
', sqs)+8;        j := Utf8Pos('ORDER BY ', sqs)-1;       
Result.group := Trim(Utf8Copy(sqs, i, j-i));      end else if
Utf8Pos('ROWS ', sqs) > 0 then begin        i := Utf8Pos('GROUP BY ',
sqs)+8;        j := Utf8Pos('ROWS ', sqs)-1;        Result.group :=
Trim(Utf8Copy(sqs, i, j-i));      end else begin        i :=
Utf8Pos('GROUP BY ', sqs)+8;        j := Utf8Length(sqs);       
Result.group := Trim(Utf8Copy(sqs, i, j-i));      end;    end;     //
PLAN    if Utf8Pos('PLAN ', sqs) > 0 then begin      if Utf8Pos('ORDER
BY ', sqs) > 0 then begin        i := Utf8Pos('PLAN ', sqs)+4;       
j := Utf8Pos('ORDER BY ', sqs)-1;        Result.plan :=
Trim(Utf8Copy(sqs, i, j-i));      end else if Utf8Pos('ROWS ', sqs) >
0 then begin        i := Utf8Pos('PLAN ', sqs)+4;        j :=
Utf8Pos('ROWS ', sqs)-1;        Result.plan := Trim(Utf8Copy(sqs, i,
j-i));      end else begin        i := Utf8Pos('PLAN ', sqs)+4;      
j := Utf8Length(sqs);        Result.plan := Trim(Utf8Copy(sqs, i,
j-i));      end;    end;     // ORDER BY    if Utf8Pos('ORDER BY ',
sqs) > 0 then begin      if Utf8Pos('ROWS ', sqs) > 0 then begin     
 i := Utf8Pos('ORDER BY ', sqs)+8;        j := Utf8Pos('ROWS ',
sqs)-1;        Result.order := Trim(Utf8Copy(sqs, i, j-i));      end
else begin        i := Utf8Pos('ORDER BY ', sqs)+8;        j :=
Utf8Length(sqs);        Result.order := Trim(Utf8Copy(sqs, i, j-i)); 
   end;    end;    // ROWS    if Utf8Pos('ROWS ', sqs) > 0 then begin 
    i := Utf8Pos('ROWS ', sqs)+4;      j := Utf8Length(sqs);     
Result.rows := Trim(Utf8Copy(sqs, i, j-i));    end;  end;end;