Sorry, If I replied you late, been busy these past few days adding rule base to our client Nokia Firewall and their VPN connections..Replies below...
It is not effecient to open and close connections the whole time. Hence the reason why we open a connection on app start and close it on app end.
Given that you are opening and closing connections on every update, insert or delete you are either not using a framework or else you are doing this opening and closing hundreds of times.
So Lets say you want to update 10 records in table A and 50 child records in table B . Invoice -> Invoicedetail tables. These HAVE to be done in a transaction.
Q1. How do you do this in a transaction when you will have to open and close the connection 60 (50+10) times? You cannot maintain a transaction once you close a connection. Closing the connection is an implicit Rollback or Commit depending on whether you have Auto or Manual Transactions on.
So if you open a connection,Insert into the parent, then Close that connection. Then open another connection and then insert into the child then close that coinnection. This is not a transaction. If Auto Transactions are on the parent will be committed and then your child update may fail. Then what?
Well, I haven't do automatic transactions, I prefer manual transactions, but if I need to update 10 header records and 50 detail records for every 1 header records , I have to use AUTOMATIC TRANSACTION PROCESS 1 header record and its detail records, after that commit, if succesful update, process next header records and its detailed records until I finish the 10 records, then thats the time I disconnect my connection.I can do also like this, fetch 1 header records and its detail records, send updates to the remote data server, if updates successful, fetch again next header record and its detailed records and so on after the tenth records, disconnect my connection. Is that practice bad to you?Q2. You want to insert 1 record into table C. So you make a connection before you insert the record, "your network connection link is broken or your SQL server just experience a problem and it goes down?"
So what do YOU do in that case? Your Insert will fail anyway.
We'll, I just alert the user that connection cant be stablish to the SQL/mySQl server, check network link or server status.A sample coding below...
cDsn="eRetail"
STORE SQLCONNECT('&cDsn','root','') TO lha
IF lha < 0
MESSAGEBOX("Connection Failed! Check Server Status, network link",0+16,"Remote Server",1500)
Else
= SQLSETPROP(lha, 'Transactions', 2)
= SQLEXEC(lha, "INSERT INTO Config (branch_name, branch_data);
Values(?cBranch_name,?cBranch_data)")
= SQLCOMMIT(lha)
= SQLDISCONNECT(lha)
MESSAGEBOX("Shop Name Creation Successful!",0+64,"",1500)
lSuccess = .T.
ENDIFQ3. Similarly your query could fail. Then what do you do?
In any case you cannot continue running your app since there is no connection to data. Just opening and closing connections will not help if the Network or SQL server itself is down, whether you follow my method or yours.
Thats my point, by doing this, my handle is always up-to-date. Lets say I stored my connection to SQL/mySQL server during my app start up. My connection is valid, what it my users keep my app running for about 4 hours then continues the works but he didn't know that something wrong with the server. Did my handle still valid? I dont think so, server already down.If this happens, I just give my user the option to quit the APP or wait till the server back in operation.Your method though uses more resources and is unable to do transactions.
If my handle is valid, why cannot? More resources? base on my experience, a persistent connection is slower.. There is no advantage I can see going for it.
Well, by doing this, there is an advantage and disadvantage, having a persistent connection to a remote server can slow down application performance, compared to connecting and disconnecting as per my experience. Thats why, I ask you guys if my practice is a "Best practice".
