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

[CharlyTango]

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

[Nicole]

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.

[Zvoni]

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

[Nicole]

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.  

[rvk]

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.

[Nicole]

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)

[rvk]

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.

[incendio]

Here are what usually I do :

• Create a datamodule automatically (not by code)
• Put Database Object on datamodule and set connected properties to false
• On Main Project, create datamodule first before create other forms
• On DataModuleCreate event, use this code something like these

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

1. Dba.Close
2. Dba.DatabaseName := mydb;
3. Dba.Open;

• Create a Transaction for every forms created
• Set DefaultAction = TARollback
• Set DefaultDatabase = to database in datamodule
• Set every query and dataset of this form to this transaction
• On FormCreate event, set Transaction property Active to true