I posted another problem before but I found exactly the problem, deleted it and made this one. I've been struggling with managing database connections. What is the best way to manage database connections?
Option 1 is Opening a database connection, running a query and closing the connection. That each time with each particular query. Opening a database connection with each query is particular expensive, it takes 500ms just the opening part.
Option 2 is Opening a database connection only once, then running a fast "ping" query about each 30s to keep the connection open on the server side. So it means I can just run the query all the time and its faster. I use "Select version()" as the ping query and it takes just around 200ms each ping to keep it alive. It works fine until certain point but for some reason it messes up all my code. I have to use "send KeepItAlive to me in x seconds" and of course I check the pending messages but eventually it becomes a mess. I dislike the idea of having a permanent query running all the time. It's messing it all.
Option 3 is checking the connection status before running a query. If it is not open then open it. If it is open then just run the query. Checking the status is the tricky part. How can I do that? I mean a real FAST check. Many queries take long and make the program crash. Also, if the connection is closed and I run a query it takes way tooo long and almost crashes.
I use this to check internet connections, it works fine and takes just 1ms or less. (better than pinging google for sure!)
Code: Select all
function db.CheckInternet
local mySock, myIP1
put "255.255.255.255:44444" into mySock
open datagram socket to mySock
put hostAddress(mySock) into myIP1
close socket mySock
if myIP1 is "127.0.0.1" then
return false
else
return true
end if
end db.CheckInternet
So, any ideas how to quickly check database connection status? best way to manage it?