Results 1 to 5 of 5

Thread: MySQL database connecting or reconnecting

  1. #1

    Thread Starter
    Addicted Member Davor Geci's Avatar
    Join Date
    Sep 2009
    Posts
    222

    MySQL database connecting or reconnecting

    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
    Last edited by Davor Geci; Aug 23rd, 2018 at 07:42 AM.
    My projects:
    Virtual Forms
    VBA Telemetry

  2. #2
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: MySQL database connecting or reconnecting

    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)
    Last edited by DEXWERX; Aug 23rd, 2018 at 08:39 AM.

  3. #3
    Addicted Member
    Join Date
    Jul 2016
    Posts
    230

    Re: MySQL database connecting or reconnecting

    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

    Code:
    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,
    Code:
        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:
    Code:
        If m_cn.State <> adStateOpen Then
            Call m_cn.Open
        End If
    2. Create a new command object:
    Code:
        If m_cmd Is Nothing Then
            Set m_cmd = New ADODB.Command
            m_cmd.CommandType = adCmdText
        End If
    3. Execute your query:
    Code:
        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:
    Code:
        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.
    Code:
        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.

  4. #4
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: MySQL database connecting or reconnecting

    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.
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  5. #5

    Thread Starter
    Addicted Member Davor Geci's Avatar
    Join Date
    Sep 2009
    Posts
    222

    Re: MySQL database connecting or reconnecting

    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
    My projects:
    Virtual Forms
    VBA Telemetry

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width