Reconnect to database whenever a connection can be made
Hi,
This question is an addition to:
https://www.experts-exchange.com/questions/26502495/SQL-Server-2008-Connection-error-EOleException.html#33776495
I'm using a SQL Server 2008 database (windows authentification). The connectionstring is extracted from a UDL-file.
Currently my application makes a connection to the database at startup. If the connection is lost during program execution my users get a connectionerror.
I need a way to open my Delphi application even without any available connection to the SQL Server database.
Whenever a connection can be made (over time), it should automatically (re-)connect to the database.
Also when the connection is lost during program execution, it will try reconnecting again.
If no connection can be made the user will be informed (showing some connect panel) and then has the option to either wait for a valid connection or close the application.
Any queries/stored procedures used in the application that are executed/opened will need to handle connectionloss and basically wait for the connection to become available again or when the user closes the application the query/stored procedures will be canceled.
What I'm looking for is a working concept, preferably a small sample application, that handles the above.
I prefer to use standard (maybe enhanced) Delphi ADO-components as my current application is using these a lot :-)
This question is an addition to:
https://www.experts-exchange.com/questions/26502495/SQL-Server-2008-Connection-error-EOleException.html#33776495
I'm using a SQL Server 2008 database (windows authentification). The connectionstring is extracted from a UDL-file.
Currently my application makes a connection to the database at startup. If the connection is lost during program execution my users get a connectionerror.
I need a way to open my Delphi application even without any available connection to the SQL Server database.
Whenever a connection can be made (over time), it should automatically (re-)connect to the database.
Also when the connection is lost during program execution, it will try reconnecting again.
If no connection can be made the user will be informed (showing some connect panel) and then has the option to either wait for a valid connection or close the application.
Any queries/stored procedures used in the application that are executed/opened will need to handle connectionloss and basically wait for the connection to become available again or when the user closes the application the query/stored procedures will be canceled.
What I'm looking for is a working concept, preferably a small sample application, that handles the above.
I prefer to use standard (maybe enhanced) Delphi ADO-components as my current application is using these a lot :-)
LelikInside
Just before running EVERY query you should check if your DB in open state (Active=true) to avoid errors and make the connection dialog....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Even when I know why the connection to the database is lost, it still would be a good thing to handle it appropiately.
There are some thirdparty components that can handle connectionerrors (like: SDAC from DevArt and AnyDAC).
I even integrated DevArt into my application, but needed to change a lot of code to get it working as they use different names for certain commands, and do not support all features (like connecting through a UDL file in the connectionstring.
Therefore I prefer to handle it with the standard ADO-components in Delphi.
Let's see where our collegs come up with. I gues many developers have the same issue.
There are some thirdparty components that can handle connectionerrors (like: SDAC from DevArt and AnyDAC).
I even integrated DevArt into my application, but needed to change a lot of code to get it working as they use different names for certain commands, and do not support all features (like connecting through a UDL file in the connectionstring.
Therefore I prefer to handle it with the standard ADO-components in Delphi.
Let's see where our collegs come up with. I gues many developers have the same issue.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I am curious about the idea of "[a]ny queries/stored procedures used in the application that are executed/opened will need to handle connectionloss and basically wait for the connection to become available again."
How do you plan to "handle" queries/stored procs in a disconnected mode?
You may want to consider doing a bit of research on the "Briefcase Model" applications. These are designed to operate either with a database connection or in a local mode (either with a local database, which is the approach I prefer or just with local XML files). If your users are suffering chronic disconnection issues, then using the Briefcase Model will allow them to pull a set of data from the Master Database to their local world, disconnect from the Master Database, work on it, and then apply any changes to the Master Database upon command (and assuming that there is a connection at that moment).
As a general comment, if the users are getting disconnected due to time-outs, you may want to look at extending the command time-outs for the queries, stored procs, and database connections. (The default is something like 30 seconds, if I remember correctly.)
How do you plan to "handle" queries/stored procs in a disconnected mode?
You may want to consider doing a bit of research on the "Briefcase Model" applications. These are designed to operate either with a database connection or in a local mode (either with a local database, which is the approach I prefer or just with local XML files). If your users are suffering chronic disconnection issues, then using the Briefcase Model will allow them to pull a set of data from the Master Database to their local world, disconnect from the Master Database, work on it, and then apply any changes to the Master Database upon command (and assuming that there is a connection at that moment).
As a general comment, if the users are getting disconnected due to time-outs, you may want to look at extending the command time-outs for the queries, stored procs, and database connections. (The default is something like 30 seconds, if I remember correctly.)
i always smile when people look amazed at a desktop computer loosing connection to a oracle database
consider a laptop computer on a forklift
and the forklift driver gets it's instructions on the display of the laptop
and now ... just imagine him driving with his pallet from 1 building to the next
1 with wireless available and nothing in between and then again in the building next door
if you can write a app with that in mind (periodically loosing (or having) connection to the database)
you have solved your problem
as mdspencer states ... it's a lot of work
from my experience ... 6 months to build the basic framework
and another 6 or so to filter out all the bugs
i have given you the complete library which can do that
it's up to you to do all the testing and setting up and programming ... etc
consider a laptop computer on a forklift
and the forklift driver gets it's instructions on the display of the laptop
and now ... just imagine him driving with his pallet from 1 building to the next
1 with wireless available and nothing in between and then again in the building next door
if you can write a app with that in mind (periodically loosing (or having) connection to the database)
you have solved your problem
as mdspencer states ... it's a lot of work
from my experience ... 6 months to build the basic framework
and another 6 or so to filter out all the bugs
i have given you the complete library which can do that
it's up to you to do all the testing and setting up and programming ... etc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you all for participating in this question.