DB Best Practice
lazarus / 16-Databases

----------------------------------------------------------------------
JZS · 2014-01-25 13:34

Hi Everyone,

I would like to ask about the best practice in using SQLdb components,
mainly TMySQLxxConnection & TIBConnection.

Is it better to keep the Connection & transaction always open and
ready for handling requested queries?
Or it's better to CRUD whenever need be, to execute or open queries?

And what are the Advantages and Disadvantages of both ways, if any.

What am doing currently is having DataModule holding one Connection
and one Transaction components and when I need to read or write I
create TSQLQuery, link it to the connection, use it, and then free it.

Thank you.

----------------------------------------------------------------------
Leledumbo · 2014-01-25 14:10

It's up to you, you decide based on your target environment. Does the
server exist on the same machine as your application? Are you prepared
for connection closed anytime?

The advantage of having it connected all the time is of course speed,
especially if you do bulk operation.

----------------------------------------------------------------------
BigChimp · 2014-01-25 14:19

Well, keeping the connection open is not so bad, but I'd think
starting and committing the transaction as quickly as possible (i.e.
don't keep it open) is good practice for multi-user environments.

Won't matter much in single user/single transaction environments of
course.

----------------------------------------------------------------------
exdatis · 2014-01-25 15:14

Quote from: BigChimp on January 25, 2014, 03:19:29 pmWell, keeping the
connection open is not so bad, but I'd think starting and committing
the transaction as quickly as possible (i.e. don't keep it open) is
good practice for multi-user environments.
I agree completely. I have experience working with about 200 computers
in the network and for several years of use I have not had any
problem. Of course there is many factors(database projection, number
of clients, broken network etc.)
Regards.

p.s. PostgreSql(openSuSe - server) SqlDB, mixed OS(win-linux)

----------------------------------------------------------------------
JZS · 2014-01-26 08:42

Many thanks.

Quote from: Leledumbo on January 25, 2014, 03:10:20 pmDoes the server
exist on the same machine as your application? Are you prepared for
connection closed anytime?
It's for network multi-user environment, where the connection is
stable (no disconnect experienced so far) but I must tackle lost
connections when happens, for instance power failure.
Is there a way of catching connections' errors when the server goes
off. Right now testing for the connectivity before stepping into data
fetching/modification is working well, although I used to use
components that offer events such as OnConnectionLost to keep the user
aware of the connection status ahead of trying to process anything.

Quote from: BigChimp on January 25, 2014, 03:19:29 pmWell, keeping the
connection open is not so bad, but I'd think starting and committing
the transaction as quickly as possible (i.e. don't keep it open) is
good practice for multi-user environments.
Although am not yet used to manage the SQLQuery's along with
transaction but wouldn't implementing an AutoOpen and
AutoCommit/Rollback make it possible to eliminate the transaction part
from accompanying the SQLQuery commands?
What I am thinking of is to do something like:
@SQLQueryBeforInsert/Edit/Post/Cancel... <- Start the transaction
and:
@SQLQueryAfterInsert/Edit/Post/Cancel <- Commit/Rollback
Could be doable but might not be preferable, not yet tried. But
anyways, this is not because Transaction is difficult to mange but for
not to worry about its status.

Quote from: exdatis on January 25, 2014, 04:14:18 pmI have not had any
problem. Of course there is many factors(database projection, number
of clients, broken network etc.)
I see your point, testing for connection availability before
committing transactions. Isn't it? Have you tried getting 'Server
Down' notification somehow to notify the users when the system is
idle?

----------------------------------------------------------------------
JD · 2014-01-26 11:23

Quote from: JZS on January 26, 2014, 09:42:40 amI see your point,
testing for connection availability before committing transactions.
Isn't it? Have you tried getting 'Server Down' notification somehow to
notify the users when the system is idle?

Try a system where the client "pings" the server at regular intervals.
If no reponse is received, the client can conclude that the server is
down.

----------------------------------------------------------------------
JZS · 2014-02-05 10:01

Thank you all...