Delphi DBGrid date format for Firebird timestamp field

Question

I display the content of a Firebird database into a TDBgrid. The database has a 'TIMESTAMP' data type field that I would like to display with date/time format: 'YYYY/MM/DD HH:mm:ss'. (Now its displayed as 'YYMMDD HHmmss')

How to achieve this?

I tried this:

procedure TDataModule1.IBQuery1AfterOpen(DataSet: TDataSet);
begin
  TDateTimeField(IBQuery1.FieldByName('timestamp_')).DisplayFormat := 'YYYY/MM/DD HH:mm:ss';
end;

But this causes some side effects at other parts of the program, so its not an alternative. For example at the 'IBQuery1.Open' statement I get the '...timestamp_ not found...' debugger message in the method that I clear the database with.

function TfrmLogger.db_events_clearall: integer;
begin
  result := -1;
  try
    with datamodule1.IBQuery1 do begin
      Close;
      With SQL do begin
        Clear;     
        Add('DELETE FROM MEVENTS')
      end;
      if not Prepared then 
        Prepare;      
      Open;          //Exception here          
      Close;
      Result := 1;
    end;
  except
    on E: Exception do begin
      ShowMessage(E.ClassName);
      ShowMessage(E.Message);          
      Datamodule1.IBQuery1.close;
    end;
  end;
end;

I get the same exception message when trying to open the query for writing into the database.

*EDIT >>

I have modified the database clear as the following:

function TfrmLogger.db_events_clearall: integer;
var
  IBQuery: TIBQuery;
  IBTransaction: TIBTransaction;
  DataSource: TDataSource;
begin
  result := -1;

  //Implicit local db objects creation
  IBQuery := TIBQuery.Create(nil);
  IBQuery.Database := datamodule1.IBdbCLEVENTS;

  DataSource := TDataSource.Create(nil);
  DataSource.DataSet := IBQuery; 

  IBTransaction := TIBTransaction.Create(nil);
  IBTransaction.DefaultDatabase := datamodule1.IBdbCLEVENTS;
  IBQuery.Transaction := IBTransaction;

  try
    with IBQuery do begin
      SQL.Text := DELETE FROM MSTEVENTS;
      ExecSQL;
      IBTransaction.Commit;
      result := 1;
    end;
  except
    on E : Exception do
    begin
      ShowMessage(E.ClassName + ^M^J + E.Message);
      IBTransaction.Rollback;
    end;
  end;

  freeandnil(IBQuery);
  freeandnil(DataSource);
  freeandnil(IBTransaction);
end;

After clearing the database yet i can load the records into the dbgrid, seems like the database has not been updated. After the program restart i can see all the records been deleted.

Answer 1

The whole function TfrmLogger.db_events_clearall seems very dubious.

1. You do not provide SQL_DELETE_ROW but by the answer this does not seem to be SELECT-request returning the "resultset". So most probably it should NOT be run by ".Open" but instead by ".Execute" or ".ExecSQL" or something like that.

UPD. it was added SQL_DELETE_ROW = 'DELETE FROM MEVENTS'; confirming my prior and further expectations. Almost. The constant name suggests you want to delete ONE ROW, and the query text says you delete ALL ROWS, which is correct I wonder?..

Additionally, since there is no "resultset" - there is nothing to .Close after .Exec.... - but you may check the .RowsAffected if there is such a property in DBX, to see how many rows were actually scheduled to be deleted.

Additionally, no, this function DOES NOT delete rows, it only schedules them to be deleted. When dealing with SQL you do have to invest time and effort into learning about TRANSACTIONS, otherwise you would soon get drown in side-effects.

In particular, here you have to COMMIT the deleting transaction. For that you either have to explicitly create, start and bind to the IBQuery a transaction, or to find out which transaction was implicitly used by IBQuery1 and .Commit; it. And .Rollback it on exceptions.

Yes, boring, and all that. And you may hope for IBX to be smart-enough to do commits for you once in a while. But without isolating data changes by transactions you would be bound to hardly reproducible "side effects" coming from all kinds of "race conditions".

Example

  FieldDefs.Clear;  // frankly, I do not quite recall if IBX has those, but probably it does.
  Fields.Clear;     // forget the customizations to the fields, and the fields as well
  Open;      // Make no Exception here          
  Close;
  Halt;  // << insert this line
  Result := 1;

Try this, and I bet your table would not get cleared despite the query was "opened" and "closed" without error.

2. The whole With SQL do begin monster can be replaced with the one-liner SQL.Text := SQL_DELETE_ROW;. Learn what TStrings class is in Delphi - it is used in very many places of Delphi libraries so it would save you much time to know this class services and features.

3. There is no point to Prepare a one-time query, that you execute and forget. Preparation is done to the queries where you DO NOT CHANGE the SQL.Text but only change PARAMETERS and then re-open the query with THE SAME TEXT but different values. Okay, sometimes I do use(misuse?) explicit preparation to make sure the library fetches parameters datatypes from the server. But in your example there is neither. Your code however does not use parameters and you do not use many opens with the same neverchanging SQL.text. Thus, it becomes a noise, making longer to type and harder to read.

4. Try ShowMessage(E.ClassName + ^M^J + E.Message) or just Application.ShowException(E) - no point to make TWO stopping modal windows instead of one.

5. Datamodule1.IBQuery1.close; - this is actually a place for rolling back the transaction, rather than merely closing queries, which were not open anyway.

6. Now, the very idea to make TWO (or more?) SQL requests going throw ONE Delphi query object is questionable per se. You make customization to the query, such as fixing DisplayFormat or setting fields' event handlers, then that query is quite worth to be left persistently customized. You may even set DisplayFormat in design-time, why not.

There is little point in jockeying on one single TIBQuery object - have as many as you need. As of now you have to pervasively and accurately reason WHICH text is inside the IBQuery1 in every function of you program.

That again creates the potential for future side effects. Imagine you have some place where you do function1; function2; and later you would decide you need to swap them and do function2; function1;. Can you do it? But what if function2 changes the IBQuery1.SQL.Text and function1 is depending on the prior text? What then?

So, basically, sort your queries. There should be those queries that do live across the function calls, and then they better to have a dedicated query object and not get overused with different queries. And there should be "one time" queries that only are used inside one function and never outside it, like the SQL_DELETE_ROW - those queries you may overuse, if done with care. But still better remake those functions to make their queries as local variables, invisible to no one but themselves.

PS. Seems you've got stuck with IBX library, then I suggest you to take a look at this extension http://www.loginovprojects.ru/download.php?getfilename=uploads/other/ibxfbutils.zip Among other things it provides for generic insert/delete functions, which would create and delete temporary query objects inside, so you would not have to think about it.

Transactions management is still on you to keep in mind and control.