Lazarus

Programming => Databases => Topic started by: Nicole on March 21, 2025, 09:09:58 am

Title: [solved] "database not open" - IBX - IBSQL - database not open
Post by: Nicole on March 21, 2025, 09:09:58 am
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.


Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: rvk on March 21, 2025, 09:41:58 am
Quote from: Nicole on March 21, 2025, 09:09:58 am
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
Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: Nicole on March 21, 2025, 12:07:49 pm
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.  
Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: rvk on March 21, 2025, 12:30:54 pm
Quote from: Nicole on March 21, 2025, 12:07:49 pm
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?
Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: Nicole on March 21, 2025, 02:16:19 pm
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
Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: rvk on March 21, 2025, 02:39:12 pm
Quote from: Nicole on March 21, 2025, 02:16:19 pm
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).
Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: Nicole on March 21, 2025, 03:28:00 pm
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.
Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: rvk on March 21, 2025, 04:02:05 pm
Quote from: Nicole on March 21, 2025, 03:28:00 pm
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;
Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: Nicole on March 21, 2025, 04:32:16 pm
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.

Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: rvk on March 21, 2025, 04:51:02 pm
Quote from: Nicole on March 21, 2025, 04:32:16 pm
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.

Quote from: Nicole on March 21, 2025, 04:32:16 pm
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.
Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: Nicole on March 21, 2025, 08:25:05 pm
This will do to my code snipet directory.
Perhaps I need the server thing one day.
Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: Nicole on March 22, 2025, 10:27:43 am
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.
Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: rvk on March 24, 2025, 11:49:35 am
Quote from: Nicole on March 22, 2025, 10:27:43 am
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.

Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: Nicole on March 24, 2025, 04:32:44 pm
thank you!

Do I have to set it explicitly to acitve = false every time as well?
Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: rvk on March 24, 2025, 05:55:32 pm
Quote from: Nicole on March 24, 2025, 04:32:44 pm
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

Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: CharlyTango on March 25, 2025, 11:20:22 am
Here are some hints that might help you, based on experience of what works and what doesn't.

Use one data module for each database you want to use, i.e. one data module each for IBDatabase_24 and IBDatabase_Aktien.

These data modules contain everything you need to connect to the database, including transactions and, if necessary, logging. Also the method suggested by @rvk to read the parameters for the database settings from an INI file. (https://forum.lazarus.freepascal.org/index.php/topic,70586.msg550413.html#msg550413)

Both data modules will then look pretty much the same except for the name of the INI file.  Optimizations can be considered later. (Here's a tip: avoid inheriting data modules)

I recommend that you do not open a database connection directly in Lazarus to view data live in Lazarus. This simply causes problems sooner or later.

I also recommend that you do not let Lazarus create the data modules automatically, but create them yourself in the code. Then you can control the exact time of their creation and thus also the time of the connection to the database. This prevents forms from being created in the application without an existing database connection.
Essentially, only the main form is created automatically in my applications, everything else is done in the code.

It also makes sense to create a database development against test databases and not to use the original data. The INI files are then very helpful when switching over

Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: Nicole on March 26, 2025, 11:54:25 am
Thank you for taking time to search for the old link.
I saved the text carefully and put it into my code library. What I do not understand: What are the advantages?
The disadvantages are, that I have to mess around with the ASCII code in an ini-file.
I can set the values in the parameter-property of the DB-s much easier?

At the moment I have one datamodul for two databases. It just holds a Tdatabase and a TTransaction each.
The queries, datasources are organized in those frames, which work with them.
I care, not to use two queries, which can interact.
E.g. there is one query, which writes quotes and the other query has to search for the quotes' foreign key.

There is something you said, which keeps my thinking since days.
Is it enough to keep the queries apart from each other? - Or may I mess up my database by using the same transaction?
I care to work clean by shutting down and active-inactive.
But caring to work clean and succeed in it are two aspects.
Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: Zvoni on March 27, 2025, 09:03:27 am
Quote from: Nicole on March 26, 2025, 11:54:25 am
At the moment I have one datamodul for two databases. It just holds a Tdatabase and a TTransaction each.

Is it enough to keep the queries apart from each other? - Or may I mess up my database by using the same transaction?
I care to work clean by shutting down and active-inactive.
But caring to work clean and succeed in it are two aspects.
There is nothing wrong with having "only" one connection and one Transaction for 1 (or more) Query-Objects (per Database), as long as you play by the rules:
If you start a Transaction, you have to finish it (Commit or Rollback), BEFORE doing anything else, like switching to the second Query-Object, or executing a Statement directly from the connection.
Notabene: This is for ONE Database.
There is nothing wrong working with a second Database (and Connection, and Transaction) at the same time, again, as long as you play by the rules.
That said:
Per Database one Connection, one Transaction and one (or more) Query-Objects.
BUT: Do not mix them. Don't switch Transaction-Objects and Query-Objects from one connection to the other
Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: Nicole on March 27, 2025, 07:08:01 pm
I just wrote this code, and it does not look alike, but it initiates TWO queries:
The first one finds out, which id_sektoren (primary key and foreign key) has to be used.
The second query uses it.
ChatGPT just said me, that the transaction is fine without setting it to inactive.
Is this a case for commit? Or would this be just one line more?
(This is fresh written code, undebugged, e.g. the unlogic  zero setting)

Code: Pascal  [Select][+][-]
  1. Procedure TDataModule_Aktien.Trage_Branche_neu_ein(branche, sektor: string; Prozent: double = 0);
  2. Var sektor_id: integer;
  3.  
  4. begin
  5.   DataModule_24.IBTransaction_Aktien.Active:=true;
  6.   branche:=Trim(branche);
  7.   Sektor_id:= Finde_id_Sektor(sektor);
  8.   if Sektor_id = -1 then begin
  9.       ShowMessage('Sektor nicht gefunden: ' + sektor + '. Die Branche kann nicht angelegt werden: ' + branche);
  10.       exit;
  11.    end;
  12.  
  13.   //DataModule_24.IBTransaction_Aktien.Active:=false; Chat GPT sagt, das brauche ich nicht und erklärt es
  14.  
  15.   DataModule_24.IBTransaction_Aktien.Active:=true;
  16.   IBSQL_Branchen.SQL.Text := 'INSERT INTO TBBRANCHEN (BRANCHE, PROZENT, FK_SEKTOR) VALUES (:Branche, :Prozent, :FK_SEKTOR)';
  17.   IBSQL_Branchen.ParamByName('Branche').AsString := branche;
  18.   IBSQL_Branchen.ParamByName('Prozent').AsFloat := 0.0; // Beispielwert für Prozent
  19.   IBSQL_Branchen.ParamByName('FK_SEKTOR').AsInteger := Sektor_ID;
  20.   IBSQL_Branchen.ExecQuery;
  21.  
  22. end;
  23.  
Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: rvk on March 27, 2025, 07:41:24 pm
You have two lines (line 5 and 15) with
DataModule_24.IBTransaction_Aktien.Active:=true
Just one is needed.

And yes. You do need to commit the transaction somewhere.
You can also do commitretaining if you need the transaction to stay open.
Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: Nicole on March 27, 2025, 08:18:36 pm
I do a sidestep to go out of the shooting line: I never did commit.
In IBQuery there is a checkbox autocommit, this I check usually.
IBSQL just works, if I care to start the transaction.

The first code snipets of the new database work. The values are inserted as desired.
Without explicit commit. The next question is, if there can happen damage to the data?
(up to now I only used IBSQL to insert the first data non visual)
Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: rvk on March 27, 2025, 08:22:58 pm
Quote from: Nicole on March 27, 2025, 08:18:36 pm
In IBQuery there is a checkbox autocommit, this I check usually.
IBSQL just works, if I care to start the transaction.
It could be that the default action of the transaction is commit. I normally set that to rollback so I don't commit something by accident.

The default action gets executed during cleanup of the transaction. So if set to commit, it will commit automatically. But like I said... I like to handle committing and rollback manually. But you can do it automatically if you want.
Title: Re: "database not open" - IBX - IBSQL - database not open
Post by: incendio on March 29, 2025, 04:53:02 am
Here are what usually I do :


Code: Pascal  [Select][+][-]
  1. Dba.Close
  2. Dba.DatabaseName := mydb;
  3. Dba.Open;

TinyPortal © 2005-2018