MySQL database connecting or reconnecting

[Davor Geci]

Hello guys,

I have a very big project that previously was only used in a local environment and now it goes over the internet. The database in on a web server.
The project is designed to once connect to the database and use this global connection everywhere. It was using Access mdb database.
Now that the database is MySQL and on the web server if the connection is not used it triggers the timeout.
Would you:
1. go and reconnect the connection before using, or
2. would you rewrite the code to use a local connection and connect and disconnect

Thanks,
Davor

[DEXWERX]

3. rewrite the code to use a web service, and put a php web service on the web server with the DB.
4. Rewrite it as a web app. ( I know that may not be practical, just had to put it out there - as it's most often what I've ended up doing)

[OldClock]

VB6/MariaDB user here. The documentation on the subject is a web of incomplete, outdated and conflicting information, much of it 404 and requiring a visit to archive.org. I performed many tests to arrive at the below.

The project is designed to once connect to the database and use this global connection everywhere.

That's fine.

Now that the database is MySQL and on the web server if the connection is not used it triggers the timeout.

That's fine too. Open and close a connection on every query, but don't destroy it. Session/connection pooling will work if you open and close but not destroy

Private m_cn As ADODB.Connection
Private m_rs As ADODB.Recordset
Private m_cmd As ADODB.Command

When starting the program, instantiate and initialize a new connection,

    Set m_cn = New ADODB.Connection

    ' Configure connection
    connStr = m_config.dbConnDriver _
        & ";SERVER=" & cfg.dbServer _
        & ";DATABASE=" & cfg.dbName _
        & ";UID=" & cfg.dbUser _
        & ";PWD=" & cfg.dbPwd _
        & ";PORT=" & cfg.dbPort _
        & ";POOLING=true" _
        & ";OPTION=3"

    m_cn.ConnectionString = connStr
    m_cn.CommandTimeout = 60

On every query you're executing, do this:
1. Check if the connection is open. If not, open it:

    If m_cn.State <> adStateOpen Then
        Call m_cn.Open
    End If

2. Create a new command object:

    If m_cmd Is Nothing Then
        Set m_cmd = New ADODB.Command
        m_cmd.CommandType = adCmdText
    End If

3. Execute your query:

    m_cmd.ActiveConnection = m_cn
    m_cmd.CommandText = sqlQuery
    Set m_rs = m_cmd.Execute

Do not close the connection yet as that would prevent access to m_rs.
4. Retrieve data from the recordset if you need to:

    Dim apl As Apple
    Dim col As Collection

    Set col = New Collection

    Do While Not m_rs.EOF
        Set apl = New Apple

        apl.id = m_rs("id") ' The column names
        apl.color = m_rs("color")
        If IsDate(m_rs("expiry")) Then
            apl.expiry = m_rs("expiry")
        Else
            apl.expiry = 0
        EndIf
        apl.price = CCur(m_rs("price"))

        col.Add apl, "key" & CStr(apl.id)
        m_rs.MoveNext
    Loop

5. Now you can and should close the connection (but do not destroy it), destroy the command (parameterized queries require that the command is destroyed once its used, so that when a new query is run, old parameters don't linger) and destroy the recordset.

    If Not m_rs Is Nothing Then
        If m_rs.State <> adStateClosed Then
            Call m_rs.Close
        End If
        Set m_rs = Nothing
    End If

    If m_cn.State <> adStateClosed Then
        Call m_cn.Close
    End If

Repeat the numbered steps for every query.

mysql-connector-odbc (or Windows?) should handle the pooling automatically. To test, you should be able to execute 10 000 queries one immediately after the other with no problems. It takes about 1 second to execute 1000 queries on my setup.

Heads-up: if you go to Control Panel > ODBC Sources, ignore the "Connection Pooling" stuff there. It doesn't seem to apply to mysql-connector-odbc.

Hope it helps.

[Daniel Duta]

Devor, take the OldClock advice: "Open and close the connection on every query, but don't destroy it". I am in a similar situation but I preferred the SQLight instead of the mySQL. To rewrite a desktop app as web application could take months and the gain is not guaranteed in advance.

[Davor Geci]

Thanks, guys,

I will do a combination, in most cases, I will connect and disconnect this global connection. But in some cases where I have a situation where inside a function or sub another sub or function is called, there I will use a local connection and get the connection string from this global connection.

Thanks again,
Davor