Firebird DB management tool - continuing the story

After a week of using my homegrown tool for managing Firebird databases, I have realised that it would help a great deal if I could edit live data - in other words,

• issue a query
• display the results of that query in a grid
• edit fields within that grid with immediate updating

I have already achieved the first two points above but editing the live data is something which I have never done before. There is a way of updating a database from a grid with TIB components but this would be extremely complicated to do with ad hoc queries. So with regret, I switched back to using the dbExpress components. As a result, I no longer have access to the query plan, but this was always a nice thing to have and not essential.

Unfortunately, dbExpress lacks the concept of a live query, because the components are not buffered. A query which references only one table and does not use parameters is a candidate for being live. I have searched high and low but have found no reference about how to check a query to see whether it references one or more tables (this management tool is not going to use parameters so I don't have to bother about this).

Just to make things clear, the following query would return a live query

select * from ranks

whereas the following query will not return a live query

select dockets.id
from dockets inner join statuses
on dockets.status = statuses.id
where statuses.future = 1

There is the canmodify property, but this is always false for SQLDataSets and always true for ClientDataSets, so basing any solution on this is doomed. It seems that the only way to solve this problem is to parse the query, at least to a certain extent.

I checked a few SQL parsers but they are far more complex than I need. Basically, all I need to do is find the 'from' keyword in the query; following that word will come the name of the first table in the query. I have to check what comes after the name of the first table - if nothing comes after the table name, or the next word is either 'where' or 'order', then there is only one table in the query and it can be regarded as being live. Anything else means that the query is not live. On the basis of this examination, I can set the editing flag of the grid.

After having written the above 'livequery' function, my program can now display and update simple recordsets or display complex recordsets.

Here is the very ad hoc parser from the program (global variable mem is a memo component which holds the user query):

function TForm1.LiveQuery: boolean;
// check what the second token after 'from' is
const
 EOI = #26;

var
 cmdlen, curpos: integer;
 ch: char;
 tmp: string;

 Procedure GetChar;
 begin
  inc (curpos);
  if curpos >= cmdlen
   then ch:= mem.text[curpos]
   else ch:= EOI;
 end;

 Function Token: string;
 begin
  result:= '';
  while ch = ' ' do getchar;
  while not (ch in [' ', ',', ';', EOI]) do
   begin
    result:= result + upcase (ch);
    getchar
   end
 end;

begin
 ch:= ' ';
 cmdlen:= length (mem.text);
 curpos:= 0;
 tmp:= token;
 while tmp <> 'FROM' do tmp:= token;
 tmp:= token; // this should be the first table name
 if ch = ',' then result:= false // select ... from table1, table2
 else if ch = EOI then result:= true // select * from table1
 else
  begin
   tmp:= token;
   result:= (tmp = 'WHERE') or (tmp = 'ORDER')
  end;
end;