** Provides a MySQL keep-alive connection pool, allowing .prg applications
** to reuse the same MySQL connection across multiple HTTP requests,
** eliminating the overhead of reconnecting on every request
** (connection setup typically takes 1050ms).
** This significantly improves database throughput under high concurrency.
mh_mysql_pool-e.prg
/*
** mh_mysql_pool-e.prg -- MySQL Keep-Alive Connection Pool for mod_harbour.v2.1
** (c) WenSheng, 2026
** MIT License
**
** ββββββββββββββββββββββββββββββββββββββββββββ
** Design Goals
** ββββββββββββββββββββββββββββββββββββββββββββ
** Provides a MySQL keep-alive connection pool, allowing .prg applications
** to reuse the same MySQL connection across multiple HTTP requests,
** eliminating the overhead of reconnecting on every request
** (connection setup typically takes 10~50ms).
** This significantly improves database throughput under high concurrency.
**
** ββββββββββββββββββββββββββββββββββββββββββββ
** Thread Safety
** ββββββββββββββββββββββββββββββββββββββββββββ
** The pool uses STATIC variables (shared across all threads) protected
** by an hb_mutex lock.
** All access to s_hPool MUST be wrapped between hb_mutexLock / hb_mutexUnlock.
**
** [IMPORTANT] BEGIN SEQUENCE / RECOVER USING are used together to ensure:
** 1. Even if an exception occurs mid-operation, hb_mutexUnlock() is always
** called, preventing a permanent mutex lock (deadlock).
** 2. Exception error messages are stored in THREAD STATIC ts_cLastError
** and can be retrieved after the function returns via mhDB_LastError().
**
** ββββββββββββββββββββββββββββββββββββββββββββ
** Pool Data Structure (s_hPool)
** ββββββββββββββββββββββββββββββββββββββββββββ
** s_hPool is a Hash table structured as follows:
**
** s_hPool {
** 'host:port:user:dbname' => aPool [ <- sub-pool array (shared by same DB+user)
** aEntry { oConn, lBusy, cPass, tLastUsed, tBorrowedAt, nTimeout },
** aEntry { ... },
** ...
** ],
** 'host:port:user:db2' => aPool [ ... ],
** ...
** }
**
** Each aEntry array fields:
** aEntry[1] = oConn : TMySQLServer object (the actual MySQL connection)
** aEntry[2] = lBusy : .T. = checked out (in use), .F. = idle (available)
** aEntry[3] = cPass : Stored password, used for automatic reconnection
** aEntry[4] = tLastUsed : Timestamp of last mhDB_Free() call
** aEntry[5] = tBorrowedAt : Timestamp of last mhDB_Connect() checkout
** aEntry[6] = nTimeout : Maximum borrow duration in seconds (0 = unlimited)
**
** ββββββββββββββββββββββββββββββββββββββββββββ
** Connection Types (nTimeout)
** ββββββββββββββββββββββββββββββββββββββββββββ
** nTimeout > 0 : Regular short-lived connection (HTTP request).
** If not returned within N seconds, it is considered a leak
** (caller forgot mhDB_Free()). It will be force-returned
** before the next checkout, preventing pool exhaustion.
**
** nTimeout = 0 : Long-lived connection (SSE / Server-Sent Events / long-polling).
** Never auto-force-returned. The caller is responsible for
** calling mhDB_Free() explicitly.
** Long-lived and short-lived connections are managed in
** separate sub-pools and do NOT interfere with each other.
**
** ββββββββββββββββββββββββββββββββββββββββββββ
** Public API Overview
** ββββββββββββββββββββββββββββββββββββββββββββ
** mhDB_Connect( cHost, cUser, cPass, nPort, cDBName, nTimeout ) -> oConn
** Borrow a connection from the pool. Creates a new one if none is idle.
**
** mhDB_Free( oConn )
** Return a connection to the pool without closing it. MUST be called
** after use β failing to call this will permanently mark the connection
** as checked out.
**
** mhDB_ForceClean( [cHost], [cUser], [cDBName], [nPort] ) -> nCount
** Force-mark all checked-out connections as idle, including nTimeout=0
** long-lived connections. No params = clean ALL sub-pools.
** Returns the number of connections force-returned.
**
** mhDB_ClosePool( cHost, cUser, nPort, cDBName )
** Close and remove a specific sub-pool. TMySQLServer objects are set to
** NIL and released by the Harbour garbage collector.
**
** mhDB_CloseAll()
** Close and remove all sub-pools. Typically called before server shutdown.
**
** mhDB_PoolStatus() -> cHTML
** Returns an HTML table showing the full pool status, for use in
** debug/admin pages.
**
** mhDB_LastError() -> cError
** Returns the last error message for the current thread
** (THREAD STATIC β each thread has its own independent copy).
**
** ββββββββββββββββββββββββββββββββββββββββββββ
** Quick Usage Example
** ββββββββββββββββββββββββββββββββββββββββββββ
** oConn := mhDB_Connect( '127.0.0.1', 'root', 'pass', 3306, 'mydb' )
** IF oConn == NIL
** ?? 'Connection failed: ' + mhDB_LastError()
** ELSE
** oConn:SetChar( 'utf8' ) // Set UTF-8 charset (required for CJK content)
** oConn:lWeb := .T. // Web mode: FieldGet() strings have no trailing spaces
** oRS := oConn:Query( 'SELECT * FROM users' )
** // ... process data ...
** oRS:End() // Release ResultSet
** mhDB_Free( oConn ) // β οΈ Always return the connection!
** ENDIF
*/
#include "hbclass.ch"
#include "hbthread.ch"
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
// Global static variables (shared across all threads, protected by Mutex)
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
STATIC s_hPool := NIL // Connection pool Hash (see data structure above)
STATIC s_oMutex := NIL // Mutex lock β prevents concurrent modification of s_hPool
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
// Per-thread last error message
// THREAD STATIC: each thread has its own independent copy
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
THREAD STATIC ts_cLastError := ''
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
// Public API: mhDB_LastError()
// Returns the last error message for the current thread.
// Returns: error string (empty string if no error)
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
FUNCTION mhDB_LastError()
RETURN ts_cLastError
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
// Internal: mhDB__Init()
// Lazy initialization β creates the Mutex and empty pool on first call.
// Avoids unnecessary initialization overhead at startup.
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
STATIC PROCEDURE mhDB__Init()
IF s_oMutex == NIL
s_oMutex := hb_mutexCreate() // Create the mutex lock
ENDIF
IF s_hPool == NIL
s_hPool := { => } // Create an empty Hash as the pool container
ENDIF
RETURN
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
// Internal: mhDB__Key()
// Generates a unique string key from connection parameters,
// used as the Hash key in s_hPool.
// Format: 'host:port:user:dbname'
// Connections with the same key share the same sub-pool.
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
STATIC FUNCTION mhDB__Key( cHost, cUser, nPort, cDBName )
RETURN cHost + ':' + hb_ntos( nPort ) + ':' + cUser + ':' + cDBName
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
// Internal: mhDB__NewConn()
// Creates a brand new TMySQLServer connection and selects the database.
// Returns NIL on failure; error details are written to ts_cLastError.
//
// Process:
// 1. TMySQLServer():New() establishes the TCP connection
// 2. oConn:NetErr() checks for connection failure
// 3. oConn:SelectDB() switches to the target database
// 4. oConn:Error() verifies the switch succeeded
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
STATIC FUNCTION mhDB__NewConn( cHost, cUser, cPass, nPort, cDBName )
LOCAL oConn := NIL
LOCAL oError
BEGIN SEQUENCE
// Establish the MySQL TCP connection (no DB selected yet)
oConn := TMySQLServer():New( cHost, cUser, cPass, nPort )
IF oConn:NetErr()
// Network connection failed (host unreachable, bad credentials, etc.)
ts_cLastError := 'TMySQLServer:New() failed to connect!'
oConn := NIL
BREAK // Exit SEQUENCE, continue after END SEQUENCE
ENDIF
IF ! Empty( cDBName )
// Switch to the target database
oConn:SelectDB( cDBName )
IF ! Empty( oConn:Error() )
// Database does not exist, or account lacks privileges
ts_cLastError := 'SelectDB(' + cDBName + ') failed: ' + oConn:Error()
oConn := NIL
BREAK
ENDIF
ENDIF
RECOVER USING oError
// Catch unexpected exceptions (e.g. MySQL library crash)
IF Empty( ts_cLastError )
ts_cLastError := 'Exception during connection: ' + IF( ValType( oError ) == 'O', GetErrorInfo( oError ), '' )
ENDIF
oConn := NIL
END SEQUENCE
RETURN oConn
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
// Internal: mhDB__CleanupStale()
// Scans the sub-pool and force-returns connections that have exceeded
// their borrow timeout.
//
// [NOTE] Only processes connections with nTimeout > 0.
// nTimeout=0 (long-lived) connections are NOT touched here.
// [NOTE] Must be called AFTER hb_mutexLock (caller holds the lock).
//
// Design purpose:
// If an HTTP request has a bug and forgets to call mhDB_Free(),
// the connection stays marked as checked-out permanently.
// Over time, available connections dwindle and every request
// must create a new one, defeating the purpose of the pool.
// This function is called automatically inside mhDB_Connect()
// to prevent pool exhaustion.
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
STATIC PROCEDURE mhDB__CleanupStale( aPool )
LOCAL aEntry, i, nElapsed
FOR i := 1 TO Len( aPool )
aEntry := aPool[ i ]
IF aEntry[ 2 ] .AND. aEntry[ 6 ] > 0 // checked out AND has a timeout (not long-lived)
// Calculate elapsed seconds: DateTime subtraction gives days; multiply by 86400
nElapsed := ( hb_DateTime() - aEntry[ 5 ] ) * 86400
IF nElapsed > aEntry[ 6 ]
// Timed out! Force-return (caller has a bug β forgot mhDB_Free)
aEntry[ 2 ] := .F. // lBusy -> idle
aEntry[ 4 ] := hb_DateTime() // update tLastUsed
aEntry[ 5 ] := hb_DateTime() // reset tBorrowedAt
ENDIF
ENDIF
NEXT i
RETURN
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
// Public API: mhDB_ForceClean()
// Force-marks all checked-out connections as idle,
// including nTimeout=0 long-lived connections.
//
// When to use:
// - An SSE connection died abnormally without calling mhDB_Free()
// - Before server maintenance to ensure all connections are reclaimed
// - Admin interface to manually reset the pool state
//
// Parameters (all optional):
// cHost : MySQL host (omit to clean ALL sub-pools)
// cUser : Login account
// cDBName : Database name
// nPort : Port number (default 3306)
//
// Returns: number of connections force-returned
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
FUNCTION mhDB_ForceClean( cHost, cUser, cDBName, nPort )
LOCAL cKey, aPool, aEntry, i, nCount
LOCAL lTargetAll, oError
// If cHost is not passed (NIL), clean all sub-pools
lTargetAll := ( cHost == NIL )
mhDB__Init()
ts_cLastError := ''
nCount := 0
hb_mutexLock( s_oMutex )
BEGIN SEQUENCE
IF lTargetAll
// Clean all sub-pools
FOR EACH aPool IN s_hPool
FOR i := 1 TO Len( aPool )
aEntry := aPool[ i ]
IF aEntry[ 2 ] // lBusy = checked out
aEntry[ 2 ] := .F. // force-mark as idle
aEntry[ 4 ] := hb_DateTime() // update tLastUsed
aEntry[ 5 ] := hb_DateTime() // reset tBorrowedAt
nCount++
ENDIF
NEXT i
NEXT
ELSE
// Clean only the specified sub-pool
hb_default( @nPort, 3306 )
cKey := mhDB__Key( cHost, cUser, nPort, cDBName )
IF hb_HHasKey( s_hPool, cKey )
aPool := s_hPool[ cKey ]
FOR i := 1 TO Len( aPool )
aEntry := aPool[ i ]
IF aEntry[ 2 ] // lBusy = checked out
aEntry[ 2 ] := .F.
aEntry[ 4 ] := hb_DateTime()
aEntry[ 5 ] := hb_DateTime()
nCount++
ENDIF
NEXT i
ENDIF
ENDIF
RECOVER USING oError
// Catch exception β ensures Mutex can be unlocked below
IF Empty( ts_cLastError )
ts_cLastError := 'mhDB_ForceClean() exception: ' + IF( ValType( oError ) == 'O', GetErrorInfo( oError ), '' )
ENDIF
END SEQUENCE
hb_mutexUnlock( s_oMutex ) // Always executed, even on error
RETURN nCount
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
// Public API: mhDB_Connect()
// Borrow a MySQL connection from the pool.
//
// Checkout process:
// 1. Ensure pool and mutex are initialized (lazy init)
// 2. Scan the sub-pool for stale (timed-out) checked-out connections
// and force-return them (CleanupStale)
// 3. Search the sub-pool for an idle connection of the matching type:
// a. Ping() succeeds -> check it out directly
// b. Ping() fails -> attempt reconnect;
// remove from pool if reconnect also fails
// c. Type matching: nTimeout=0 only borrows from nTimeout=0 entries,
// and vice versa β the two types never mix
// 4. No idle connection available -> create a new one, add to pool, check out
//
// Parameters:
// cHost : MySQL host IP or hostname (default '127.0.0.1')
// cUser : Login account (default 'root')
// cPass : Login password
// nPort : MySQL port (default 3306)
// cDBName : Database name to use
// nTimeout : Max borrow duration in seconds (default 30; 0 = unlimited for SSE)
//
// Returns:
// Success -> TMySQLServer object (MUST call mhDB_Free() when done)
// Failure -> NIL (call mhDB_LastError() for details)
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
FUNCTION mhDB_Connect( cHost, cUser, cPass, nPort, cDBName, nTimeout )
LOCAL cKey, aPool, oConn, aEntry, i, lFound
LOCAL oError
// Apply defaults for any omitted parameters
hb_default( @cHost, '127.0.0.1' )
hb_default( @cUser, 'root' )
hb_default( @cPass, '' )
hb_default( @nPort, 3306 )
hb_default( @cDBName, '' )
hb_default( @nTimeout, 30 ) // Default 30-second timeout
mhDB__Init()
ts_cLastError := '' // Clear previous error
cKey := mhDB__Key( cHost, cUser, nPort, cDBName ) // Build the sub-pool Hash key
oConn := NIL
hb_mutexLock( s_oMutex ) // Lock β protect s_hPool access
BEGIN SEQUENCE
// Create the sub-pool array if this key has never been seen before
IF ! hb_HHasKey( s_hPool, cKey )
s_hPool[ cKey ] := {}
ENDIF
aPool := s_hPool[ cKey ] // Get a reference to the sub-pool array
// Before borrowing, sweep the pool for timed-out connections
// (only handles nTimeout > 0 regular connections)
mhDB__CleanupStale( aPool )
// Search for an idle connection of the matching timeout type
lFound := .F.
FOR i := 1 TO Len( aPool )
aEntry := aPool[ i ]
// Condition 1: lBusy = .F. (idle)
// Condition 2: type match (both long-lived OR both regular β never mixed)
IF ! aEntry[ 2 ] .AND. ( ( nTimeout == 0 ) == ( aEntry[ 6 ] == 0 ) )
IF aEntry[ 1 ]:Ping() == 0 // 0 = Ping succeeded, connection still alive
// Check out this idle connection
aEntry[ 2 ] := .T. // Mark as checked out
aEntry[ 5 ] := hb_DateTime() // Record checkout timestamp
aEntry[ 6 ] := nTimeout // Update timeout setting
oConn := aEntry[ 1 ]
lFound := .T.
EXIT // Found one β stop searching
ELSE
// Ping failed β connection dropped; attempt reconnect using stored password
oConn := mhDB__NewConn( cHost, cUser, aEntry[ 3 ], nPort, cDBName )
IF oConn != NIL
// Reconnect succeeded β update pool entry and check out
aEntry[ 1 ] := oConn
aEntry[ 2 ] := .T.
aEntry[ 5 ] := hb_DateTime()
aEntry[ 6 ] := nTimeout
lFound := .T.
EXIT
ELSE
// Reconnect also failed β remove this dead entry from the pool
hb_ADel( aPool, i, .T. ) // .T. = shrink array
i-- // Adjust index to avoid skipping the next entry
ENDIF
ENDIF
ENDIF
NEXT i
// No idle connection available β create a brand-new connection
IF ! lFound
oConn := mhDB__NewConn( cHost, cUser, cPass, nPort, cDBName )
IF oConn != NIL
// Add to pool: { connection, checked-out, password, returnedAt, borrowedAt, timeout }
AAdd( aPool, { oConn, .T., cPass, hb_DateTime(), hb_DateTime(), nTimeout } )
ELSE
// New connection also failed; ts_cLastError was set inside mhDB__NewConn
BREAK // Exit SEQUENCE, enter RECOVER
ENDIF
ENDIF
RECOVER USING oError
// Catch any unexpected exception; ensures Mutex will be unlocked below
IF Empty( ts_cLastError )
ts_cLastError := 'mhDB_Connect() exception: ' + IF( ValType( oError ) == 'O', GetErrorInfo( oError ), '' )
ENDIF
oConn := NIL
END SEQUENCE
hb_mutexUnlock( s_oMutex ) // Always executed, even on error β prevents deadlock
RETURN oConn
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
// Public API: mhDB_Free()
// Return a borrowed connection back to the pool (without closing it),
// marking it as idle and available for the next checkout.
//
// β οΈ IMPORTANT: Always call this after you are done using a connection!
// If omitted, the connection stays marked as checked-out permanently.
// It will only be force-returned when nTimeout expires (regular connections),
// or never (for nTimeout=0 long-lived connections).
//
// Parameter:
// oConn : The TMySQLServer object returned by mhDB_Connect()
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
PROCEDURE mhDB_Free( oConn )
LOCAL aPool, aEntry, i
LOCAL oError
// Ignore NIL or non-object values silently
IF ValType( oConn ) != 'O' .OR. oConn == NIL
RETURN
ENDIF
mhDB__Init()
hb_mutexLock( s_oMutex )
BEGIN SEQUENCE
// Search all sub-pools for this specific connection object
FOR EACH aPool IN s_hPool
FOR i := 1 TO Len( aPool )
aEntry := aPool[ i ]
IF aEntry[ 1 ] == oConn // Compare by object reference (same TMySQLServer instance)
aEntry[ 2 ] := .F. // Mark as idle (available for re-use)
aEntry[ 4 ] := hb_DateTime() // Record return timestamp
EXIT // Found it β exit the inner loop
ENDIF
NEXT i
NEXT
RECOVER USING oError
IF Empty( ts_cLastError )
ts_cLastError := 'mhDB_Free() exception: ' + IF( ValType( oError ) == 'O', GetErrorInfo( oError ), '' )
ENDIF
END SEQUENCE
hb_mutexUnlock( s_oMutex )
RETURN
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
// Public API: mhDB_ClosePool()
// Close and remove all connections in a specific sub-pool.
// TMySQLServer objects are set to NIL; Harbour GC releases the resources.
// Call this when a particular database is no longer needed.
//
// Parameters:
// cHost : MySQL host
// cUser : Login account
// nPort : Port number (default 3306)
// cDBName : Database name
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
PROCEDURE mhDB_ClosePool( cHost, cUser, nPort, cDBName )
LOCAL cKey, aPool, aEntry, i
LOCAL oError
hb_default( @nPort, 3306 )
mhDB__Init()
cKey := mhDB__Key( cHost, cUser, nPort, cDBName )
hb_mutexLock( s_oMutex )
BEGIN SEQUENCE
IF hb_HHasKey( s_hPool, cKey )
aPool := s_hPool[ cKey ]
FOR i := 1 TO Len( aPool )
aEntry := aPool[ i ]
aEntry[ 1 ] := NIL // Clear object reference β GC will close the MySQL connection
NEXT i
hb_HDel( s_hPool, cKey ) // Remove this sub-pool from the Hash
ENDIF
RECOVER USING oError
IF Empty( ts_cLastError )
ts_cLastError := 'mhDB_ClosePool() exception: ' + IF( ValType( oError ) == 'O', GetErrorInfo( oError ), '' )
ENDIF
END SEQUENCE
hb_mutexUnlock( s_oMutex )
RETURN
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
// Public API: mhDB_CloseAll()
// Close and remove ALL sub-pools and connections.
// Typically called before Apache module unload or server shutdown
// to ensure all MySQL connections are properly released.
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
PROCEDURE mhDB_CloseAll()
LOCAL aPool, aEntry, i
LOCAL oError
mhDB__Init()
hb_mutexLock( s_oMutex )
BEGIN SEQUENCE
// Iterate all sub-pools and release each connection object
FOR EACH aPool IN s_hPool
FOR i := 1 TO Len( aPool )
aEntry := aPool[ i ]
aEntry[ 1 ] := NIL // Let GC release the MySQL connection
NEXT i
NEXT
s_hPool := { => } // Reset the entire pool to an empty Hash
RECOVER USING oError
IF Empty( ts_cLastError )
ts_cLastError := 'mhDB_CloseAll() exception: ' + IF( ValType( oError ) == 'O', GetErrorInfo( oError ), '' )
ENDIF
END SEQUENCE
hb_mutexUnlock( s_oMutex )
RETURN
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
// Public API: mhDB_PoolStatus()
// Returns the full pool status as an HTML table, for debug/admin pages.
//
// Table columns:
// Connection Key : host:port:user:dbname (sub-pool identifier)
// Total : Total number of connections in this sub-pool
// In Use : Currently checked-out connections
// Idle : Available connections ready to borrow
// Type : 'Long-lived' (nTimeout=0) or 'Ns' (N = timeout seconds)
// Elapsed : Seconds since last checkout (shows 'Returned' if idle)
// Borrowed At : Timestamp of last checkout
// Last Returned : Timestamp of last mhDB_Free()
//
// Returns: complete HTML string (<table>...</table>)
// ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
FUNCTION mhDB_PoolStatus()
LOCAL cHTML
LOCAL aPool, aEntry, i
LOCAL nTotal, nBusy, nElapsed
LOCAL oError
mhDB__Init()
hb_mutexLock( s_oMutex )
// Build the table header (8 columns)
cHTML := '<table border="1" cellpadding="4">'
cHTML += '<tr>'
cHTML += '<th>Connection Key</th>'
cHTML += '<th>Total</th><th>In Use</th><th>Idle</th>'
cHTML += '<th>Type</th><th>Elapsed(s)</th><th>Borrowed At</th><th>Last Returned</th>'
cHTML += '</tr>'
BEGIN SEQUENCE
FOR EACH aPool IN s_hPool
// Count checked-out connections in this sub-pool
nTotal := Len( aPool )
nBusy := 0
FOR i := 1 TO nTotal
aEntry := aPool[ i ]
IF aEntry[ 2 ] // lBusy
nBusy++
ENDIF
NEXT i
// Output the sub-pool summary row
// Key column uses rowspan to span the summary row + all detail rows
cHTML += '<tr>'
cHTML += '<td rowspan="' + hb_ntos( nTotal + 1 ) + '">' + aPool:__enumKey() + '</td>'
cHTML += '<td>' + hb_ntos( nTotal ) + '</td>' // Total
cHTML += '<td>' + hb_ntos( nBusy ) + '</td>' // In Use
cHTML += '<td>' + hb_ntos( nTotal - nBusy ) + '</td>' // Idle
cHTML += '<td colspan="4"></td>' // Placeholder for Type/Elapsed/Borrowed/Returned columns
cHTML += '</tr>'
// Output detail rows for each individual connection
FOR i := 1 TO nTotal
aEntry := aPool[ i ]
// Elapsed seconds (only meaningful when checked out; -1 when idle)
nElapsed := IF( aEntry[ 2 ], Int( ( hb_DateTime() - aEntry[ 5 ] ) * 86400 ), -1 )
cHTML += '<tr>'
// Status column (spans Total/In Use/Idle positions)
cHTML += '<td colspan="3">' + IF( aEntry[ 2 ], 'In Use', 'Idle' ) + '</td>'
// Connection type
cHTML += '<td>' + IF( aEntry[ 6 ] == 0, 'Long-lived', hb_ntos( aEntry[ 6 ] ) + 's' ) + '</td>'
// Elapsed borrow time (or 'Returned' if idle)
cHTML += '<td>' + IF( nElapsed >= 0, hb_ntos( nElapsed ) + 's', 'Returned' ) + '</td>'
// Last checkout timestamp (shown even when idle, to track last use)
cHTML += '<td>' + IF( ! Empty( aEntry[ 5 ] ), hb_TToC( aEntry[ 5 ] ), '-' ) + '</td>'
// Last return timestamp
cHTML += '<td>' + hb_TToC( aEntry[ 4 ] ) + '</td>'
cHTML += '</tr>'
NEXT i
NEXT
RECOVER USING oError
IF Empty( ts_cLastError )
ts_cLastError := 'mhDB_PoolStatus() exception: ' + IF( ValType( oError ) == 'O', GetErrorInfo( oError ), '' )
ENDIF
cHTML += '<tr><td colspan="8">Error: ' + ts_cLastError + '</td></tr>'
END SEQUENCE
cHTML += '</table>'
hb_mutexUnlock( s_oMutex )
RETURN cHTMLmh_mysql_pool_example-e.prg
<?prg
/*
** mh_mysql_pool_example-e.prg -- Usage Example for mh_mysql_pool
**
** Prerequisites:
** mh_mysql_pool.prg must be compiled into libmhapache.dll (or .so).
** Add the following line to libmhapache.hbp:
** source/mh_mysql_pool.prg
**
** This example demonstrates:
** 1. Borrowing a connection from the pool (mhDB_Connect)
** 2. Setting UTF-8 charset (SetChar)
** 3. Enabling Web mode (lWeb)
** 4. Executing a Query and rendering results as HTML
** 5. Returning the connection to the pool (mhDB_Free)
** 6. Connecting to multiple different databases simultaneously
** 7. Viewing the pool status (mhDB_PoolStatus)
**
** Key Rules:
** - Every borrowed connection MUST be returned via mhDB_Free() when done.
** - Every ResultSet (oRS) MUST be released via oRS:End() after use.
** - When using multiple databases, each connection object
** (oConn / oConnA / oConnB ...) is completely independent.
** SetChar, lWeb, Query, and mhDB_Free must all use the SAME object β
** never mix them up.
*/
// ================================================================
// All LOCAL variable declarations must appear at the top of the
// program (Harbour language requirement)
// ================================================================
LOCAL oConn, oRS // ss_aaa database connection and query result
LOCAL oConnA // ss_bbb database connection
LOCAL oConnB // ss_ccc database connection
LOCAL oConnC // ss_ddd database connection (demo: 60s timeout)
AP_SetContentType( "text/html; charset=UTF-8" )
// ================================================================
// Example 1: ss_aaa database (Long-lived connection, nTimeout=0)
// ================================================================
// mhDB_Connect parameters: cHost, cUser, cPass, nPort, cDBName, nTimeout
// nTimeout=0 means long-lived β will NOT be auto-force-returned on timeout
?? '<h3>ss_aaa Database (Long-lived connection, nTimeout=0)</h3>'
oConn := mhDB_Connect( '127.0.0.1', 'user', 'password', 3306, 'ss_aaa', 0 )
IF oConn == NIL
// Connection failed β use mhDB_LastError() to get the reason
?? '<p style="color:red"> ss_aaa connection failed: ' + mhDB_LastError() + '</p>'
ELSE
?? '<p style="color:green"> ss_aaa connected successfully!</p>'
IF oConn:SetChar( 'utf8' ) != 0
// SetChar failed β MySQL does not support utf8 (uncommon)
?? 'Charset error!!<br>' + mhDB_LastError()
ELSE
oConn:lWeb := .T. // Web mode: FieldGet() strings have no trailing spaces
// Verify the active database (for debugging)
oRS := oConn:Query( "SELECT DATABASE() AS d" )
IF oRS != NIL .AND. oRS:RecCount() > 0
?? '<p>Current DB = <b>' + hb_valToStr( oRS:FieldGet( 'd' ) ) + '</b></p>'
oRS:End() // Always call End() to release the ResultSet
ENDIF
// Run the main query
oRS := oConn:Query( "SELECT CUTID, CT_NUM, CT_DAM FROM cut LIMIT 10" )
IF oRS != NIL .AND. oRS:RecCount() > 0
?? '<table border="1" cellpadding="4">'
?? '<tr><th>ID</th><th>Name</th><th>Tel</th></tr>'
oRS:GoTop() // Move to the first record
DO WHILE ! oRS:EOF()
?? '<tr>'
?? '<td>' + hb_valToStr( oRS:FieldGet( 'CUTID' ) ) + '</td>'
?? '<td>' + hb_valToStr( oRS:FieldGet( 'CT_NUM' ) ) + '</td>'
?? '<td>' + hb_valToStr( oRS:FieldGet( 'CT_DAM' ) ) + '</td>'
?? '</tr>'
oRS:Skip() // Advance to the next record
ENDDO
?? '</table>'
oRS:End() // Release the ResultSet
ELSE
?? '<p>No records found.</p>'
ENDIF
ENDIF
mhDB_Free( oConn ) // IMPORTANT: Always return the connection to the pool!
ENDIF
// ================================================================
// Example 2: ss_bbb database (Default 30-second timeout)
// ================================================================
// When nTimeout is omitted, it defaults to 30 seconds
?? '<h3>ss_bbb Database (Regular connection, nTimeout=30s)</h3>'
oConnA := mhDB_Connect( '127.0.0.1', 'user', 'password', 3306, 'ss_bbb' )
IF oConnA == NIL
?? '<p style="color:red"> ss_bbb connection failed: ' + mhDB_LastError() + '</p>'
ELSE
?? '<p style="color:green"> ss_bbb connected successfully!</p>'
IF oConnA:SetChar( 'utf8' ) != 0 // Use oConnA here β NOT oConn!
?? 'Charset error!!<br>' + mhDB_LastError()
ELSE
oConnA:lWeb := .T.
// Verify the active database
oRS := oConnA:Query( "SELECT DATABASE() AS d" )
IF oRS != NIL .AND. oRS:RecCount() > 0
?? '<p>Current DB = <b>' + hb_valToStr( oRS:FieldGet( 'd' ) ) + '</b></p>'
oRS:End()
ENDIF
// Run the query (ss_bbb uses table name "cut", different from ss_aaa)
oRS := oConnA:Query( "SELECT CUTID, CT_NUM, CT_DAM FROM cut LIMIT 10" )
IF oRS != NIL .AND. oRS:RecCount() > 0
?? '<table border="1" cellpadding="4">'
?? '<tr><th>ID</th><th>Name</th><th>Tel</th></tr>'
oRS:GoTop()
DO WHILE ! oRS:EOF()
?? '<tr>'
?? '<td>' + hb_valToStr( oRS:FieldGet( 'CUTID' ) ) + '</td>'
?? '<td>' + hb_valToStr( oRS:FieldGet( 'CT_NUM' ) ) + '</td>'
?? '<td>' + hb_valToStr( oRS:FieldGet( 'CT_DAM' ) ) + '</td>'
?? '</tr>'
oRS:Skip()
ENDDO
?? '</table>'
oRS:End()
ELSE
// Display MySQL error for debugging (e.g. table does not exist)
?? '<p>No records found. | oConnA:Error() = ' + oConnA:Error() + '</p>'
ENDIF
ENDIF
mhDB_Free( oConnA ) // oConnA was borrowed, so return oConnA
ENDIF
// ================================================================
// Example 3: ss_ccc database (Default 30-second timeout)
// ================================================================
?? '<h3>ss_ccc Database (Regular connection, nTimeout=30s)</h3>'
oConnB := mhDB_Connect( '127.0.0.1', 'user', 'password', 3306, 'ss_ccc' )
IF oConnB == NIL
?? '<p style="color:red"> ss_ccc connection failed: ' + mhDB_LastError() + '</p>'
ELSE
?? '<p style="color:green"> ss_ccc connected successfully!</p>'
IF oConnB:SetChar( 'utf8' ) != 0 // Use oConnB β NOT oConn or oConnA!
?? 'Charset error!!<br>' + mhDB_LastError()
ELSE
oConnB:lWeb := .T.
// Verify the active database
oRS := oConnB:Query( "SELECT DATABASE() AS d" )
IF oRS != NIL .AND. oRS:RecCount() > 0
?? '<p>Current DB = <b>' + hb_valToStr( oRS:FieldGet( 'd' ) ) + '</b></p>'
oRS:End()
ENDIF
oRS := oConnB:Query( "SELECT CUTID, CT_NUM, CT_DAM FROM cut LIMIT 10" )
IF oRS != NIL .AND. oRS:RecCount() > 0
?? '<table border="1" cellpadding="4">'
?? '<tr><th>ID</th><th>Name</th><th>Tel</th></tr>'
oRS:GoTop()
DO WHILE ! oRS:EOF()
?? '<tr>'
?? '<td>' + hb_valToStr( oRS:FieldGet( 'CUTID' ) ) + '</td>'
?? '<td>' + hb_valToStr( oRS:FieldGet( 'CT_NUM' ) ) + '</td>'
?? '<td>' + hb_valToStr( oRS:FieldGet( 'CT_DAM' ) ) + '</td>'
?? '</tr>'
oRS:Skip()
ENDDO
?? '</table>'
oRS:End()
ELSE
?? '<p>No records found. | oConnB:Error() = ' + oConnB:Error() + '</p>'
ENDIF
ENDIF
mhDB_Free( oConnB )
ENDIF
// ================================================================
// Example 4: ss_ddd database (Custom 60-second timeout)
// ================================================================
// nTimeout=60 means the connection will be force-returned if not
// returned within 60 seconds (on the next checkout attempt)
?? '<h3>ss_ddd Database (Custom nTimeout=60s)</h3>'
oConnC := mhDB_Connect( '127.0.0.1', 'user', 'password', 3306, 'ss_ddd', 60 )
IF oConnC == NIL
?? '<p style="color:red"> ss_ddd connection failed: ' + mhDB_LastError() + '</p>'
ELSE
?? '<p style="color:green"> ss_ddd connected successfully!</p>'
IF oConnC:SetChar( 'utf8' ) != 0
?? 'Charset error!!<br>' + mhDB_LastError()
ELSE
oConnC:lWeb := .T.
// Verify the active database
oRS := oConnC:Query( "SELECT DATABASE() AS d" )
IF oRS != NIL .AND. oRS:RecCount() > 0
?? '<p>Current DB = <b>' + hb_valToStr( oRS:FieldGet( 'd' ) ) + '</b></p>'
oRS:End()
ENDIF
oRS := oConnC:Query( "SELECT CUTID, CT_NUM, CT_DAM FROM cut LIMIT 10" )
IF oRS != NIL .AND. oRS:RecCount() > 0
?? '<table border="1" cellpadding="4">'
?? '<tr><th>ID</th><th>Name</th><th>Tel</th></tr>'
oRS:GoTop()
DO WHILE ! oRS:EOF()
?? '<tr>'
?? '<td>' + hb_valToStr( oRS:FieldGet( 'CUTID' ) ) + '</td>'
?? '<td>' + hb_valToStr( oRS:FieldGet( 'CT_NUM' ) ) + '</td>'
?? '<td>' + hb_valToStr( oRS:FieldGet( 'CT_DAM' ) ) + '</td>'
?? '</tr>'
oRS:Skip()
ENDDO
?? '</table>'
oRS:End()
ELSE
// Error message must use oConnC, not oConnB
?? '<p>No records found. | oConnC:Error() = ' + oConnC:Error() + '</p>'
ENDIF
ENDIF
mhDB_Free( oConnC )
ENDIF
// ================================================================
// Pool Status Report (for debugging / admin)
// ================================================================
// mhDB_PoolStatus() returns an HTML table showing:
// - Connection Key (host:port:user:dbname)
// - Total / In Use / Idle counts per sub-pool
// - Per-connection details: type, elapsed borrow time,
// checkout timestamp, last return timestamp
?? '<h3>Connection Pool Status</h3>'
?? mhDB_PoolStatus()
?>ε¦ζθͺθδΈζεηοΌε―δ»₯εθδΈι’ηΆ²εοΌ
https://www4.zzz.com.tw/phpBB3/viewtopic.php?f=2&t=371
line ID: ssbbstw
WeChat ID: ssbbstw
WeChat ID: ssbbstw