cDatabase Testing Guide

📖 Introduction

Demo_Database.bas is a complete test case module for the cDatabase class, providing comprehensive testing from basic connection to advanced features.

📁 File Location

src/Demos/database/Demo_Database.bas

⚙️ Configuration

Before running tests, modify the database configuration parameters at the top of the module:

'Database Configuration - Modify according to your environment
Private Const TEST_DB_TYPE As Long = enumDbType.Mysql     '1:Access, 2:Mysql, 3:MsSql, 4:Csv
Private Const TEST_DB_ADDRESS As String = "172.18.131.71,3306"
Private Const TEST_DB_USERNAME As String = "root"
Private Const TEST_DB_PASSWORD As String = "root"
Private Const TEST_DB_DATABASE As String = "testdb"

Supported Database Types

Value	Constant	Description
1	enumDbType.Mysql	MySQL database
2	enumDbType.Mysql	MySQL database
3	enumDbType.MsSql	SQL Server database
4	enumDbType.Csv	CSV file data source

Connection String Examples

MySQL Standard Connection:

TEST_DB_ADDRESS = "172.18.131.71,3306"

MySQL ODBC Custom DSN:

TEST_DB_ADDRESS = "DRIVER=VBMAN Mysql ODBC Driver;MULTI_HOST=1;SERVER=10.0.0.252,3306;DATABASE=mysql;UID=root;PWD=root"

🚀 Running Tests

Run All Tests

'Execute in Immediate window
Demo_Database.RunAllTests

The following tests will be executed in order:

#	Test Name	Description
0	CreateDatabase	Create test database
1	ConnectDisconnect	Connect and disconnect database
2	CreateTestTable	Create test table
3	InsertData	Insert data test
4	QueryData	Query data test
5	UpdateData	Update data test
6	DeleteData	Delete data test
7	Transaction	Transaction processing test
8	ParameterizedQuery	Parameterized query test
9	Pagination	Pagination query test
10	LastInsertId	Get last insert ID test
11	Count	Record count test
12	TableExists	Check table exists test
13	GetTableFields	Get table field list test
14	BatchInsert	Batch insert data test
15	GetDatabases	Get database list test
16	GetTables	Get table list test
17	Escape	SQL escape test
18	CheckConnection	Check connection status test
19	GetVersion	Get version info test
20	ConnectionPool	Connection pool feature test
21	AsyncExecute	Async execution test

Run Single Test

'Run specific test case
Demo_Database.TestSpecificCase "connect"
Demo_Database.TestSpecificCase "query"
Demo_Database.TestSpecificCase "transaction"

Supported Test Case Names:

Name	Test Content
database	Create database
connect	Connection test
insert	Insert test
query	Query test
transaction	Transaction test
pagination	Pagination test

Show Help Info

Demo_Database.ShowTestHelp

📋 Test Content Details

Basic Connection Test

'Test connection and disconnect
Private Sub Test_ConnectDisconnect()
    Dim bConnect As Boolean
    bConnect = TestDB.Connect(TEST_DB_TYPE, TEST_DB_ADDRESS, _
                              TEST_DB_USERNAME, TEST_DB_PASSWORD, TEST_DB_DATABASE)
    
    If bConnect Then
        Debug.Print "Database connection successful"
        Debug.Print "Connection status: " & TestDB.IsConnect
        Debug.Print "Connection version: " & TestDB.GetVersion()
    End If
End Sub

CRUD Operations Test

Insert Data:

'Single insert
sqlInsert = "INSERT INTO test_users (username, email, age, score) " & _
            "VALUES ('John', 'john@test.com', 25, 95.5)"
bInsert = TestDB.Sql(sqlInsert).Exec(lAffected)

'Batch insert
For i = 1 To 10
    sqlInsert = "INSERT INTO test_users (...) VALUES (...)"
    bInsert = TestDB.Sql(sqlInsert).Exec(lAffected)
Next i

Query Data:

'Query using Recordset
sqlSelect = "SELECT * FROM test_users"
bQuery = TestDB.Sql(sqlSelect).Query()
If bQuery Then
    Debug.Print "Total records: " & TestDB.Rs.RecordCount
End If

'Query using Rows collection
bQuery = TestDB.Sql(sqlSelect).Fetch()
If bQuery Then
    Debug.Print "Rows collection count: " & TestDB.Rows.Count
End If

Transaction Test

'Test transaction commit
TestDB.TransBegin()
TestDB.Sql("INSERT INTO ...").Exec
TestDB.Sql("INSERT INTO ...").Exec
TestDB.TransCommit()

'Test transaction rollback
TestDB.TransBegin()
TestDB.Sql("INSERT INTO ...").Exec
TestDB.TransRollback()

Parameterized Query Test

'Parameterized insert
sqlInsert = "INSERT INTO test_users (username, email, age, score) VALUES (?, ?, ?, ?)"
bExec = TestDB.Sql(sqlInsert) _
    .Param("username", "Param User1", adVarChar) _
    .Param("email", "param1@test.com", adVarChar) _
    .Param("age", 35, adInteger) _
    .Param("score", 92.5, adDouble) _
    .ExecParam()

'Parameterized query
sqlSelect = "SELECT * FROM test_users WHERE username = ? AND age > ?"
bQuery = TestDB.Sql(sqlSelect) _
    .Param("username", "Param User1", adVarChar) _
    .Param("age", 30, adInteger) _
    .QueryParam()

Pagination Test

'Query page 1, 3 items per page
sqlSelect = "SELECT * FROM test_users ORDER BY id"
bQuery = TestDB.Sql(sqlSelect).Page(1, 3).Query()

'Query page 2 using Fetch
bQuery = TestDB.Sql(sqlSelect).Page(2, 3).Fetch()
Debug.Print "Rows collection count: " & TestDB.Rows.Count

Batch Insert Test

'Prepare batch data
Dim Data As New Collection
Dim Row As Scripting.Dictionary

For i = 1 To 5
    Set Row = New Scripting.Dictionary
    Row.Add "username", "Batch User" & i
    Row.Add "email", "batch" & i & "@test.com"
    Row.Add "age", 50 + i
    Row.Add "score", 90 + i
    Data.Add Row
Next

'Execute batch insert
bBatch = TestDB.BatchInsert("test_users", Data)

Connection Pool Test

'Create connection pool instances
Set dbPool1 = TestDB.ConnInst("Pool1")
Set dbPool2 = TestDB.ConnInst("Pool2")

'Query using connection pool instance
bQuery = dbPool1.Sql("SELECT COUNT(*) AS cnt FROM test_users").Query()

'Remove connection pool instances
TestDB.ConnInstRemove "Pool1"
TestDB.ConnInstRemove "Pool2"

Async Execute Test

'Async execute SQL
sqlInsert = "INSERT INTO test_users (...) VALUES (...)"
bExec = TestDB.Sql(sqlInsert).Async().Exec()

📊 Test Output Example

======================================================
Starting cDatabase Class Full Test
======================================================

>>> Test 0: Create Test Database
  [OK] Connected to database server
       Database 'testdb' exists, skipping creation

>>> Test 1: Connect and Disconnect Database
  [OK] Database connection successful
       Connection status: True
       Connection version: ADO 6.1
       Connection status check: Normal

>>> Test 2: Create Test Table
  [OK] Test table created successfully
       Table exists check: Exists

>>> Test 3: Insert Data
  [OK] Single record insert successful
       Affected rows: 1
       Insert ID: 1
  Starting batch insert...
  [OK] Batch insert completed

>>> Test 4: Query Data
  [OK] Query successful
       Total records: 11

  First 3 records:
    ID: 1, Username: John, Age: 25
    ID: 2, Username: User1, Age: 21
    ID: 3, Username: User2, Age: 22

  [OK] Fetch query successful
       Rows collection count: 11
       First row data:
         id: 1
         username: John
         email: john@test.com
...
======================================================
All tests completed! Time: 2.35 seconds
======================================================

⚠️ Notes

1. Auto create database: Test will automatically create database specified by TEST_DB_DATABASE (if doesn't exist)
2. Test table management: Test will create test_users table, automatically deleted after test
3. Connection config: Ensure database server is accessible, username and password are correct
4. Permission requirements: Need permissions to create database, create table, insert, update, delete
5. ODBC driver: MySQL ODBC driver needs to be installed when using MySQL

🔧 Troubleshooting

Connection Failed

[FAIL] Database connection failed
       Error: [08001] [Microsoft][ODBC Driver Manager] Data source name not found...

Solutions:

• Check if TEST_DB_ADDRESS is correct
• Confirm MySQL ODBC driver is installed
• Try custom DSN connection string

Create Database Failed

[FAIL] Create database failed
       Error: Access denied for user...

Solutions:

• Confirm user has create database permission
• Check if username and password are correct

---

Last Updated: 2026-05-17