Date not retained between sessions with FB embedded

[Delphic]

Hi

I am working on an application (Xe5, zeos 7.1.4, Firebird 2.5 Embedded) that is a translation of a MySQL system.

The first form we handle on startup shows a group of data-aware controls hooked up to a TZQuery with
"SELECT * FROM mast WHERE live = 1"
I enter the data and Post, then close that form and it is freed. If I reopen that it the data it still there. But if I close the application, that form comes up empty. The record has been lost.

The TZConnection involved has AutoCommit = true. I don't have any other ideas. It just seems like a transaction issue, but I am not starting any transactions, and with AutoCommit, each post should be commited immediately anyway, right?

Can anyone help?

TIA
Mark

[marsupilami]

Hello Mark,

this is uncommon. Which protocol do you use in your project? firebird-2.5? firebirdd-2.5? Did you try to hook up an SQL Monitor component to your connection to see what happens? Usually Transactions are logged there.

With best regards,

Jan

[Delphic]

Here is the section of dfm that codes for our TZConnection. In the code only the Database property gets changed, IIRC:

object dbConnect: TZConnection
ControlsCodePage = cCP_UTF16
Catalog = ''
HostName = ''
Port = 0
Database = ''
User = 'SYSDBA'
Password = 'masterkey'
Protocol = 'firebirdd-2.5'
Left = 24
Top = 16
end

[Delphic]

I found out that I could get the data to be retained between sessions by removing some of the code that I had added trying to fix this before. I removed calls to this procedure that I was calling after checking the database against a directory of CREATE TABLE statements.

Code: Select all

procedure TDbFunctions.RefreshDatabase;
begin
  database.AutoCommit := false;
  database.Commit;
  database.Disconnect;
  database.Connect;
  database.AutoCommit := true;
  database.StartTransaction;
end;

The only thing that was being called, after the first run when the tables are created - was the TableExists function.

Code: Select all

function TDbFunctions.TableExists(const tblName: string): boolean; /// Untested
const TableExistsSql =
      'select COUNT(0)'
  +#10'from rdb$relations'
  +#10'where rdb$view_blr is null'
  +#10'and (rdb$system_flag is null or rdb$system_flag = 0)'
  +#10'and lower(rdb$relation_name) = ';
begin
  result := ReadInt(TableExistsSql + SqlStr(LowerCase(tblName))) > 0;
end;

ReadInt should be obvious: returns an integer value from an SQL SELECT.

I don't know why my RefreshDatabase function is messing things up, but I'm glad I can progress now. Any explanations would be most welcome.

Regards
Mark Patterson

[marsupilami]

Hello Mark,

I am not sure, why your RefreshDatabase would mess up some things. But it does some pretty weird things. If you set AutoCommit to false you must have been in AutoCommit mode previously so every statement would have been followed by an implicit commit. So any work done before you set AutoCommit to false usually would have been committed already. So calling a commit after setting AutoCommit to false usualy would only commit a transaction without any work done in it.
After you reconnect, you explicitly enable AutoCommit mode and then start an explicit transaction immediately afterwards.

It seems like you should decide on how you want your application to work. Do you want Zeos to do an implicit commit after each write operation? In that case use AutoCommit = true. Usually one would do that kind of thing, if you display some data to your user and any change should be committed to the database immediately. If you need an transaction where you want to do multiple operations just in that transaction, then you can start an explicit transaction by calling StartTransaction. In that case StartTransaction temporarily disables AutoCommit until you call Rollback or Commit. Either one of them will bring you back to the AutoCommit.
If you always want to be in control when to commit, then AutoCommit mode probably isn't for you. In That case set AutoCommit to false. Zeos will start a transaction for you immediately after you connect to the database. Calling Commit or Rollback will end that transaction and immediately start a new transaction, but it will never do a commit or rollback on its own.