[solved] "database not open" - IBX - IBSQL - database not open

[Nicole]

I use an old database project and added a second database: I thought to do exactly the same as with my first database. Both databases have the same login-data. Both databases connect fine, if I check the checkbox "connected". Both databases have IBSQLs, BUT for the first database they work, for the second one they don't.

If I rightclick the non-working IBSQL at construction-time and open the IBSQL-Editor the connection works fine and e.g. SQL Code is generated.

However if I perform these lines below (a scheme which works fine for database 1!) I read at runtime: "database not open". This is true for ".open" as well as for ".execquery."

Code: Pascal  [Select][+][-]

1.   IBSQL_div.SQL.Text := 'SELECT COUNT(*) FROM TBAKTIEN WHERE ID_AKTIE = :ID_AKTIE';
2.   IBSQL_div.ParamByName('ID_AKTIE').AsInteger := ID_AKTIE;
3.   IBSQL_div.ExecQuery;

I attach you a screenshot of how my DBModul looks alike.
To me they are exactly the same.
To make sure, there is no file in the background, I deleted the files of my x86_64-win64.
Now I am clueless:
My old combination works fine every day and my new says "database not open".

There must be a difference, - but what and where?
Thanks.

[rvk]

I attach you a screenshot of how my DBModul looks alike.

You are not showing everything. What are the properties of the databases and transaction.
A transaction could be non active and the database set on not autostart transaction.

You can also copy the components (Query, Database and transactions) as text. Just CTRL+C them and paste them here.
We can select them and paste them back. Otherwise you would need to provide a sample project.

Code: Text  [Select][+][-]

1. object IBTransaction2: TIBTransaction
2.   TransactionName = '{4E778AA7-CD3D-457C-83A7-EB1E4E354697}'
3.   Active = False
4.   DefaultDatabase = IBDatabase1
5.   Left = 168
6.   Top = 72
7. end

[Nicole]

How do I set the database to "non autostart"?
This may be a hint.

Here are the copies, to me they look the same:
Working:

Code: Pascal  [Select][+][-]

1. object IBDatabase_24: TIBDatabase
2.   Connected = False
3.   LoginPrompt = False
4.   CreateIfNotExists = False
5.   AllowStreamedConnected = False
6.   DatabaseName = 'C:\Tiger\Firebird\TIGER_24.FDB'
7.   Params.Strings = (
8.     'user_name=SYSDBA'
9.     'lc_ctype=UTF8'
10.   )
11.   DefaultTransaction = IBTransaction_24
12.   IdleTimer = 0
13.   TraceFlags = []
14.   UseDefaultSystemCodePage = False
15.   Left = 48
16.   Top = 48
17. end
18.  
19.  
20. object IBTransaction_24: TIBTransaction
21.   TransactionName = '{0EB53507-52EE-419F-ADF7-F829C2305658}'
22.   Active = False
23.   DefaultDatabase = IBDatabase_24
24.   Left = 152
25.   Top = 48
26. end

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

not working:

Code: Pascal  [Select][+][-]

1. object IBDatabase_Aktien: TIBDatabase
2.   Connected = False
3.   LoginPrompt = False
4.   CreateIfNotExists = False
5.   AllowStreamedConnected = False
6.   DatabaseName = 'C:\Tiger\Firebird\AKTIEN_25.FDB'
7.   Params.Strings = (
8.     'user_name=SYSDBA'
9.     'lc_ctype=UTF8'
10.   )
11.   DefaultTransaction = IBTransaction_Aktien
12.   IdleTimer = 0
13.   TraceFlags = []
14.   UseDefaultSystemCodePage = False
15.   Left = 264
16.   Top = 136
17. end
18.  
19.  
20. object IBTransaction_Aktien: TIBTransaction
21.   TransactionName = '{0EB53507-52EE-419F-ADF7-F829C2305658}'
22.   Active = False
23.   DefaultDatabase = IBDatabase_Aktien
24.   Params.Strings = (
25.     ''
26.   )
27.   Left = 384
28.   Top = 136
29. end
30.  
31.  
32.  

[rvk]

Here are the copies, to me they look the same:

Could you also show the relevant SQL components.

And are you sure you set the Connected status of IBDatabase_Aktien to true somewhere?

[Nicole]

now, it works, but in a way, I am not happy.

I filled in before:

Code: Pascal  [Select][+][-]

1.   DataModule_24.IBDatabase_Aktien.connected:=true;
2.   DataModule_24.IBTransaction_Aktien.Active:=true;

I do not use this in the "working" version?
Contrary, when the database was "connected" on starting any sql query, this leads to troubles.

This is a hint, but the solution is mess. How to clean up? How many times to clean? I will have lots of transactions and will write lots of queries the next days. The slime way of the "working" version would be fine for it. As there will be many visual items in the future, I will use IBQuery as well.

How to solve without mess?

and: Thank you for your ideas!

PS and to complete

object IBSQL_div: TIBSQL
  Database = DataModule_24.IBDatabase_Aktien
  CaseSensitiveParameterNames = False
  GenerateParamNames = False
  UniqueParamNames = False
  ParamCheck = True
  Scrollable = False
  Transaction = DataModule_24.IBTransaction_Aktien
  Left = 168
  Top = 280
end

[rvk]

now, it works, but in a way, I am not happy.

I filled in before:

Code: Pascal  [Select][+][-]

1.   DataModule_24.IBDatabase_Aktien.connected:=true;
2.   DataModule_24.IBTransaction_Aktien.Active:=true;

I do not use this in the "working" version?
Contrary, when the database was "connected" on starting any sql query, this leads to troubles.

I'm not sure what you have now and what makes you unhappy.

Setting TDatabase.Connected to true should be something that's central in your program.
You could do if not TDatabase.Connected then TDatabase.Connected := true;
But... if you do that... then you have no idea where in your program TDatabase is active and connected... and you should know.

You need to think out a flow in your program where you always know if the database is connected or not (and not just guessing at it).
BTW Setting the transaction to true might not always be needed because transactions can also be autostarted (there always has to be a transaction, even on a SELECT because it needs to access data and needs to know in what transactial context that needs to be).

[Nicole]

I did a full text search of my code and I cannot find such a line for the "working" database. If it MUST be connected, where can this happen? It is years ago, that I wrote the base elements of my software. So I cannot remember.

About this auto-transaction. I am aware somewhere in my mind, that if I added a query (I did not add one for very long time), I clicked "auto-transact" or similar and it just worked. This was a point in the object manager about top left up. - I could not find it any more in IBSQL. Is it there? Or is it just part of IBQuery?

How to disconnect my database? On destroy?

At the moment I have no workflow-idea. I have a rough Idea, what my program shall do, but not too many ideas how.

[rvk]

I did a full text search of my code and I cannot find such a line for the "working" database. If it MUST be connected, where can this happen?

If you have a databasename in the Object Inspector set, and have it as Active or Connected = true, then this could cause problems.

Your database is then connected during development and when you start your program outside the IDE, it gets connected automatically.

I normally advise against this. You would need to hardcode the databasename in the Object Inspector but don't have any chance to change it in code (because it autoconnects on startup). When you move the database to another location (or your program to another drive), it would still connect to the old database (resulting in an error).

Normally I advise against using 'live' databases during development, so you can set and change the databasename during initialization of your DataModule.

For instance:

Code: Pascal  [Select][+][-]

1. procedure TDataModule1.DataModuleCreate(Sender: TObject);
2. begin
3.   // read the database connection string (server and name) from an INI file
4.   Db := <read it here>
5.   IBDatabase1.DatabaseName := Db; // Connected should be set as false in Object Inspector
6.   IBDatabase1.Connected := true;
7. end;

You could also put this in a separate function if you don't want to connect on startup.

If you are using a 'live' database connection during development you need to be aware that you do.
That means a hardcoded databasename and no need (or even possibility) to do TDatabase.Connected := true;

Code: Pascal  [Select][+][-]

1. procedure TDataModule1.DataModuleCreate(Sender: TObject);
2. begin
3.   if IBDatabase1.Connected then IBDatabase1.Connected := false; // disconnect old database
4.  
5.   // now the connection is false, you can set and change TIBDatabase properties
6.  
7.   // read the database connection string (server and name) from an INI file
8.   Db := <read it here>
9.   IBDatabase1.DatabaseName := Db; // Connected should be set as false in Object Inspector
10.   IBDatabase1.Connected := true;
11.  
12. end;

But... if you do it like this... you need to be aware that when starting your program, the database in IBDatabase1.DatabaseName, needs to be available at all times, even when moving your program. Or you need to set the Connected to false before compiling your final program to move it to another location or computer.

TIBDatabase will always disconnect automatically on shutdown of your program. So there is no need to do IBDatabase.
But if you want to do it properly:

Code: Pascal  [Select][+][-]

1. procedure TDataModule1.DataModuleDestroy(Sender: TObject);
2. begin
3.   IBDatabase1.Connected := false; // will also check if connected and disconnect if needed
4. end;

[Nicole]

Thank you for taking time to explain!
This
"Db := <read it here>"
what is read it here? The full path to the database?

I have no passion for the one or other way. I work with a Win 7 VM and the things shall work without problems.
In the long run I dream from migrating to Linux (I dream this since long). At the moment there is a nested 3rd party software in my code, which refuses to use Linux. In the best case this software will be replaced by my own code at the end of the year.

So this is how I work:
I start VMWare, I start my software environment. It holds 3 databases: one for my original software, one from the third party (I do not touch it, just using the data from a provided interface) and since some days the third database, which shall replace the third party db in the long run.

I do not think, that my software once will be used by a different person or on a computer different from mine.

So if I understood you corretly, I shall do on create:
db:= <set the path> ?

And the create and the destroy you were so kind to suggest.
This would mean, that I must not use FlameRobin while my software is running?

About one year ago, may be you remember, my database was sadly damaged by some keys. I had to make a new one and it took me weeks to rescue the data. I wondered how and why. One suggested, that I grabbed FlameRobin while I worked with Lazarus by mistake. In the best case the new version of FlameRobin forbids it. Not sure. It is very new.

[rvk]

Thank you for taking time to explain!
This
"Db := <read it here>"
what is read it here? The full path to the database?

Yes. But I would normally not hardcode this into the code.
I read my connectionstring from an INI file.

So something like this:

Code: Pascal  [Select][+][-]

1. uses
2.   IniFiles;
3.  
4. procedure TDataModule1.DataModuleCreate(Sender: TObject);
5. var
6.   IniFile: TIniFile;
7.   IniFilePath: string;
8.   Db: string;
9. begin
10.   IniFilePath := ChangeFileExt(ParamStr(0), '.ini'); // from projectname.ini
11.   IniFile := TIniFile.Create(IniFilePath);
12.   try
13.     Db := IniFile.ReadString('server', 'server', '');
14.     IBDatabase1.DatabaseName := Db;
15.     IBDatabase1.Connected := true;
16.   finally
17.     IniFile.Free;
18.   end;
19. end;

Project1.ini (or other program.ini name)

Code: INI  [Select][+][-]

1. [server]
2. server=192.168.2.21:C:\Pad\To\My\Databasename.db
3.  

But you can play with this. 192.168.2.21 is my IP. You can also use localhost if it's always local.
The upside is you can move the database to something else without touching your program.

This would mean, that I must not use FlameRobin while my software is running?

If you use a server-based Firebird instance on your computer (so connect to IP:/path/databasename instead of direct connection) you can connect multiple programs to the database at the same time. Only with embedded instances connecting multiple programs is a problem.

[Nicole]

This will do to my code snipet directory.
Perhaps I need the server thing one day.

[Nicole]

In the meanwhile I wrote create and destroy in my dbmodule.
There is one issue, I do not understand.
If I write into my code:
DataModule_24.IBTransaction_Aktien.Active:=true;
it works. Sure.
If I remove the line, it will not work, sure.

What I do not understand - I hope the question is answerable at all - why does it work WITHOUT this line in my other database? IBQuery has a orioperty silimar to "autoopen transaction" or so. I always check it. IBSQL does NOT have such a property, - or is it hidden? Neverthelesss it works, without I care about transaction starting and stopping in my first database. Or have I set "starting transaction automatically" anywhere in the database? I read all the properties and could not find any.

The error can be in my person, in Lauzarus, in some permanent files or not to be guessed.

[rvk]

IBQuery has a orioperty silimar to "autoopen transaction" or so. I always check it. IBSQL does NOT have such a property, - or is it hidden? Neverthelesss it works, without I care about transaction starting and stopping in my first database. Or have I set "starting transaction automatically" anywhere in the database? I read all the properties and could not find any.

For TIBSQL you indeed need to set the Transaction to active. This is and was always normal behavior for IBX. The AllowAutoActiveTransaction in TIBQuery is a addition which was added later on. But yes, for normal transactional database programming, you normally need to handle all transactions, it's just part of transaction database handling.

[Nicole]

thank you!

Do I have to set it explicitly to acitve = false every time as well?

[rvk]

Do I have to set it explicitly to acitve = false every time as well?

No, during the disconnect or cleanup of the transaction, the default transaction action will be done (rollback or commit, but usually a rollback).

I usually create a separate transaction per query, unless certain queries need to share a transaction, and clean them up when the query is finished (resulting in short lived transactions).

An update screen also has its own transaction. In a large program you also really need to think about those transaction because if you do a SELECT and UPDATE in a transaction, it normally won't be seen in another SELECT with another transaction (although you have different levels of isolation for that).
READ COMMITTED, RECORD_VERSION etc.

Some light reading material 
https://ib-aid.com/en/transactions-in-firebird-acid-isolation-levels-deadlocks-and-update-conflicts-resolution/
http://ibexpert.com/docu/doku.php?id=01-documentation:01-05-database-technology:database-technology-articles:firebird-interbase-server:transaction-options-explained
https://www.ibphoenix.com/articles/art-00000400