Where and when: TIBConnection, TSQLTransaction, TSQLQuery, TDatasource
lazarus / 16-Databases

----------------------------------------------------------------------
ramutau · 2016-09-30 13:17

Hi, Lazarus newbie here - trying hard! :-)

I am in the process of writing an app that connects to two Firebird
databases, and has various forms with db-aware grids and components,
lookupcombos etc.

Simply put, each dbgrid lists data from one of the databases, and then
has buttons for to ADD a new record, EDIT a current record or DELETE a
record from a table.

The NEW and EDIT functions all use a separate form with lookups from
table in the databases, then does some checks before returning to the
calling grid form.

I have a TIBConnection to each of the two databases on my Main Form. I
now have TSQLTransactions, TSQLQueries and TDatasource non-visual
components on almost every form. This is becoming a nightmare to
manage, as I don't seem to always be in control of which dataset is
active when, etc.

My question is this:

What is the best way to organise these visual components?
Do I need a TSQLTransaction for each TSQLQuery, or only one
TSQLTransaction for each TIBConnection?

Should I rather create and control all of these programatically
instead of using visual components?

Thank you in advance for your help :-)

----------------------------------------------------------------------
LacaK · 2016-10-01 18:34

You can use TDataModule instead of TForm.
And place database components on TDataModule and use this datamodule
in forms as you like.

According to TSQLTransaction you can use one or you can use multiple.
It depends on your application needs ;-)

----------------------------------------------------------------------
ramutau · 2016-10-02 11:54

Thank you for the response LacaK 

I have found so many contradicting answers Googling, that I'm quite
confused.

Are you saying:

1.  I can put ALL non-visual database related components for the
entire application on one TDatamodule?
2.  I can use ONE Transaction per connection, or one transaction for
each SQLQuery, both will work?

Thanks again for your time.
Stefan

----------------------------------------------------------------------
ramutau · 2016-10-02 12:41

I have another question if I may:

Do I connect to the database when the app starts, and close the
connection when the app loses. or to I connect and close the
connection as and when I need to access the database?

----------------------------------------------------------------------
molly · 2016-10-02 12:59

Quote from: ramutau on October 02, 2016, 01:54:25 pm1.  I can put ALL
non-visual database related components for the entire application on
one TDatamodule?
I don't know if that is what LacaK meant but, yes that is possible.

Quote from: ramutau on October 02, 2016, 02:41:31 pmDo I connect to
the database when the app starts, and close the connection when the
app loses. or to I connect and close the connection as and when I need
to access the database?
Preferably when you actually need the database.

I've seen too many examples out there that opens up a shitload of
databases with even so many sql queries that takes ages to finish. All
that time letting the user linger in limbo-land on what exactly is
happening.

In case your databases are small and direct to the point then there is
no problem doing so on program startup but still it is preferred to do
it after you have given the user a bit of feedback that you
application actually started correctly. The initialization of the
database(s) can be initiated directly after that.

----------------------------------------------------------------------
ramutau · 2016-10-02 16:07

Thank you molly for the advice.

I will plod along. Unfortunately the database, and specifically
Firebird, examples and tutorials are hard to find, and teaching
myself, I don't have colleagues I can ask these beginner questions!

Cheers
Stefan

----------------------------------------------------------------------
LacaK · 2016-10-03 07:09

Quote from: ramutau on October 02, 2016, 01:54:25 pm1.  I can put ALL
non-visual database related components for the entire application on
one TDatamodule?
Yes you can if you want.
But if you have many database components, which can be grouped into
logical groups you may also create multiple DataModules.
I have applications, where I use one TDataModule and also
applications, where I use multiple.

Quote from: ramutau on October 02, 2016, 01:54:25 pm2.  I can use ONE
Transaction per connection, or one transaction for each SQLQuery, both
will work?
Yes both will work. Again it depends on your application logic, what
operations should be isolated in separate transactions.

----------------------------------------------------------------------
ramutau · 2016-10-03 07:24

Thanks LacaK, much appreciated