Utility Methods

This document details the various utility methods provided by the cDataBase class to simplify common database operations.

Count Method
LastInsertId Method
TableExists Method
GetTableFields Method
GetTables Method
GetDatabases Method
GetVersion Method
Escape Method
CheckConnection Method

Count Method

Syntax

Counts the number of records in a table.

Function Count(Optional ByVal TableName As String = "") As Long

Parameters

Parameter	Type	Description
`TableName`	`String`	Table name (optional, uses current SQL if empty)

Return Value

Returns record count (Long type).

Example

' Count records in specified table
Dim lCount As Long
lCount = db.Count("users")
Debug.Print "Total users: " & lCount

' Count records in current SQL query result
db.Sql("SELECT * FROM users WHERE age > 18")
lCount = db.Count()  ' Use current SQL
Debug.Print "Adult users: " & lCount

Implementation

' If table name specified
SELECT COUNT(*) AS cnt FROM users

' If using current SQL
SELECT COUNT(*) AS cnt FROM (SELECT * FROM users WHERE age > 18) AS T

LastInsertId Method

Syntax

Gets the auto-increment primary key ID of the last insert.

Function LastInsertId() As Variant

Return Value

Returns last inserted ID (Variant type).

Supported Databases

Database	Implementation
SQL Server	`SCOPE_IDENTITY()`
MySQL	`LAST_INSERT_ID()`
Access	`@@IDENTITY`

Example

' Insert data
If db.Sql("INSERT INTO users (name, age) VALUES ('John', 25)").Exec Then
    ' Get last inserted ID
    Dim lId As Variant
    lId = db.LastInsertId
    Debug.Print "New user ID: " & lId
End If

Notes

Must call immediately after INSERT

' Correct
db.Sql("INSERT INTO users (name) VALUES ('John')").Exec
Dim lId As Variant
lId = db.LastInsertId

' Wrong: Other operations in between
db.Sql("INSERT INTO users (name) VALUES ('John')").Exec
db.Sql("SELECT * FROM users").Query  ' This resets the ID
Dim lId As Variant
lId = db.LastInsertId  ' May not get the correct ID

Only supports auto-increment primary key

' Table must have auto-increment primary key
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,  -- SQL Server
    name NVARCHAR(50)
)

TableExists Method

Syntax

Checks if a table exists.

Function TableExists(ByVal TableName As String) As Boolean

Parameters

Parameter	Type	Description
`TableName`	`String`	Table name (required)

Return Value

True - Table exists
False - Table does not exist

Supported Databases

Database	Implementation
SQL Server	`INFORMATION_SCHEMA.TABLES`
MySQL	`INFORMATION_SCHEMA.TABLES`
Access	`MSysObjects`

Example

' Check if table exists
If db.TableExists("users") Then
    Debug.Print "users table exists"
Else
    Debug.Print "users table does not exist"
    ' Create table
    db.Sql("CREATE TABLE users (id INT PRIMARY KEY, name NVARCHAR(50))").Exec
End If

Usage Scenarios

' Scenario 1: Check before creating table
If Not db.TableExists("users") Then
    db.Sql("CREATE TABLE users (...)").Exec
End If

' Scenario 2: Check before deleting table
If db.TableExists("temp_table") Then
    db.Sql("DROP TABLE temp_table").Exec
End If

GetTableFields Method

Syntax

Gets the field list of a table.

Function GetTableFields(ByVal TableName As String) As Collection

Parameters

Parameter	Type	Description
`TableName`	`String`	Table name (required)

Return Value

Returns field name collection (Collection type).

Supported Databases

Database	Implementation
SQL Server	`INFORMATION_SCHEMA.COLUMNS`
MySQL	`INFORMATION_SCHEMA.COLUMNS`
Access	`MSysObjects`

Example

' Get field list
Dim colFields As Collection
Set colFields = db.GetTableFields("users")

' Iterate fields
Dim i As Long
For i = 1 To colFields.Count
    Debug.Print "Field " & i & ": " & colFields(i)
Next

Usage Scenarios

' Scenario 1: Dynamically generate SQL
Function BuildSelectSQL(sTableName As String) As String
    Dim colFields As Collection
    Set colFields = db.GetTableFields(sTableName)

    Dim sFields As String
    Dim i As Long
    For i = 1 To colFields.Count
        If sFields <> "" Then sFields = sFields & ", "
        sFields = sFields & colFields(i)
    Next

    BuildSelectSQL = "SELECT " & sFields & " FROM " & sTableName
End Function

' Scenario 2: Validate if field exists
Function FieldExists(sTableName As String, sFieldName As String) As Boolean
    Dim colFields As Collection
    Set colFields = db.GetTableFields(sTableName)

    Dim i As Long
    For i = 1 To colFields.Count
        If colFields(i) = sFieldName Then
            FieldExists = True
            Exit Function
        End If
    Next

    FieldExists = False
End Function

GetTables Method

Syntax

Gets the table name list in the database.

Function GetTables(Optional ByVal DatabaseName As String = "") As Collection

Parameters

Parameter	Type	Description
`DatabaseName`	`String`	Database name (optional, uses current database if empty)

Return Value

Returns table name collection (Collection type).

Supported Databases

Database	Implementation
SQL Server	`INFORMATION_SCHEMA.TABLES`
MySQL	`INFORMATION_SCHEMA.TABLES`
Access	`MSysObjects`

Example

' Get table list of current database
Dim colTables As Collection
Set colTables = db.GetTables()

' Iterate table names
Dim i As Long
For i = 1 To colTables.Count
    Debug.Print "Table " & i & ": " & colTables(i)
Next

' Get table list of specified database (SQL Server/MySQL)
Set colTables = db.GetTables("mydb")

Usage Scenarios

' Scenario 1: List all tables
Sub ListAllTables()
    Dim colTables As Collection
    Set colTables = db.GetTables()

    Dim i As Long
    For i = 1 To colTables.Count
        Debug.Print colTables(i)
    Next
End Sub

' Scenario 2: Backup all tables
Sub BackupAllTables()
    Dim colTables As Collection
    Set colTables = db.GetTables()

    Dim i As Long
    For i = 1 To colTables.Count
        Dim sTableName As String
        sTableName = colTables(i)
        db.Sql("SELECT * INTO " & sTableName & "_backup FROM " & sTableName).Exec
    Next
End Sub

GetDatabases Method

Syntax

Gets the database list on the database server.

Function GetDatabases() As Collection

Return Value

Returns database name collection (Collection type).

Supported Databases

Database	Implementation
SQL Server	`sys.databases`
MySQL	`SHOW DATABASES`

Example

' Get database list
Dim colDatabases As Collection
Set colDatabases = db.GetDatabases()

' Iterate database names
Dim i As Long
For i = 1 To colDatabases.Count
    Debug.Print "Database " & i & ": " & colDatabases(i)
Next

Usage Scenarios

' Scenario 1: List all databases
Sub ListAllDatabases()
    Dim colDatabases As Collection
    Set colDatabases = db.GetDatabases()

    Dim i As Long
    For i = 1 To colDatabases.Count
        Debug.Print colDatabases(i)
    Next
End Sub

' Scenario 2: Switch database
Sub SwitchDatabase(sDatabaseName As String)
    ' Check if database exists
    Dim colDatabases As Collection
    Set colDatabases = db.GetDatabases()

    Dim i As Long
    Dim bExists As Boolean
    bExists = False
    For i = 1 To colDatabases.Count
        If colDatabases(i) = sDatabaseName Then
            bExists = True
            Exit For
        End If
    Next

    If bExists Then
        db.Sql("USE " & sDatabaseName).Exec
    Else
        Debug.Print "Database does not exist: " & sDatabaseName
    End If
End Sub

GetVersion Method

Syntax

Gets database version information.

Function GetVersion() As String

Return Value

Returns version string (String type).

Example

' Get database version
Dim sVersion As String
sVersion = db.GetVersion
Debug.Print "Database version: " & sVersion

Usage Scenarios

' Scenario 1: Check database version
Sub CheckDatabaseVersion()
    Dim sVersion As String
    sVersion = db.GetVersion
    Debug.Print "Current database version: " & sVersion
End Sub

' Scenario 2: Version compatibility check
Function IsVersionCompatible(sMinVersion As String) As Boolean
    Dim sVersion As String
    sVersion = db.GetVersion
    ' Version comparison logic...
    IsVersionCompatible = True
End Function

Escape Method

Syntax

Escapes special characters in SQL strings (prevents SQL injection).

Function Escape(ByVal Str As String) As String

Parameters

Parameter	Type	Description
`Str`	`String`	String to escape (required)

Return Value

Returns escaped string (String type).

Implementation

Converts single quote ' to two single quotes ''.

Example

' Escape string
Dim sName As String
sName = "O'Brien"
Dim sEscaped As String
sEscaped = db.Escape(sName)
' Result: "O''Brien"

' Use escaped string
db.Sql("SELECT * FROM users WHERE name = '" & sEscaped & "'").Query

Notes

Recommended to use parameterized queries instead of Escape method:

' Recommended: Use parameterized query
db.Sql("SELECT * FROM users WHERE name = ?") _
    .Param("name", "O'Brien", VBMAN.adVarWChar) _
    .QueryParam

' OK alternative: Use Escape (but not as safe as parameterized query)
db.Sql("SELECT * FROM users WHERE name = '" & db.Escape("O'Brien") & "'").Query

CheckConnection Method

Syntax

Checks connection state and attempts to reconnect.

Function CheckConnection() As Boolean

Return Value

True - Connection normal or reconnection successful
False - Connection lost and reconnection failed

Functionality

Check connection state
If disconnected, attempt to reconnect
Update connection flag

Example

' Check connection
If Not db.CheckConnection Then
    Debug.Print "Connection lost, attempting to reconnect..."
    ' Reconnect
    db.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "mydb"
End If

Usage Scenarios

' Scenario 1: Regular connection check
Private Sub Timer1_Timer()
    If Not db.CheckConnection Then
        Debug.Print "Connection lost, need to reconnect"
    End If
End Sub

' Scenario 2: Check connection before operation
Sub ExecuteQuery()
    ' Check connection
    If Not db.CheckConnection Then
        Debug.Print "Connection unavailable"
        Exit Sub
    End If

    ' Execute query
    db.Sql("SELECT * FROM users").Query
End Sub

Comprehensive Examples

Example 1: Database Info Viewer

Sub ShowDatabaseInfo()
    ' Show database version
    Debug.Print "Database version: " & db.GetVersion

    ' Show database list
    Dim colDatabases As Collection
    Set colDatabases = db.GetDatabases()
    Debug.Print "Database list:"
    Dim i As Long
    For i = 1 To colDatabases.Count
        Debug.Print "  - " & colDatabases(i)
    Next

    ' Show table list
    Dim colTables As Collection
    Set colTables = db.GetTables()
    Debug.Print "Table list:"
    For i = 1 To colTables.Count
        Debug.Print "  - " & colTables(i)

        ' Show fields of each table
        Dim colFields As Collection
        Set colFields = db.GetTableFields(colTables(i))
        Dim j As Long
        For j = 1 To colFields.Count
            Debug.Print "    * " & colFields(j)
        Next
    Next
End Sub

Example 2: Table Structure Comparison

Function CompareTableStructure(sTable1 As String, sTable2 As String) As Boolean
    Dim colFields1 As Collection
    Dim colFields2 As Collection
    Set colFields1 = db.GetTableFields(sTable1)
    Set colFields2 = db.GetTableFields(sTable2)

    ' Compare field count
    If colFields1.Count <> colFields2.Count Then
        CompareTableStructure = False
        Exit Function
    End If

    ' Compare field names
    Dim i As Long
    For i = 1 To colFields1.Count
        If colFields1(i) <> colFields2(i) Then
            CompareTableStructure = False
            Exit Function
        End If
    Next

    CompareTableStructure = True
End Function

Last Updated: 2026-01-21

Utility Methods ​

Table of Contents ​

Count Method ​

Syntax ​

Parameters ​

Return Value ​

Example ​

Implementation ​

LastInsertId Method ​

Syntax ​

Return Value ​

Supported Databases ​

Example ​

Notes ​

TableExists Method ​

Syntax ​

Parameters ​

Return Value ​

Supported Databases ​

Example ​

Usage Scenarios ​

GetTableFields Method ​

Syntax ​

Parameters ​

Return Value ​

Supported Databases ​

Example ​

Usage Scenarios ​

GetTables Method ​

Syntax ​

Parameters ​

Return Value ​

Supported Databases ​

Example ​

Usage Scenarios ​

GetDatabases Method ​

Syntax ​

Return Value ​

Supported Databases ​

Example ​

Usage Scenarios ​

GetVersion Method ​

Syntax ​

Return Value ​

Example ​

Usage Scenarios ​

Escape Method ​

Syntax ​

Parameters ​

Return Value ​

Implementation ​

Example ​

Notes ​

CheckConnection Method ​

Syntax ​

Return Value ​

Functionality ​

Example ​

Usage Scenarios ​

Comprehensive Examples ​

Example 1: Database Info Viewer ​

Example 2: Table Structure Comparison ​

Utility Methods

Table of Contents

Count Method

Syntax

Parameters

Return Value

Example

Implementation

LastInsertId Method

Syntax

Return Value

Supported Databases

Example

Notes

TableExists Method

Syntax

Parameters

Return Value

Supported Databases

Example

Usage Scenarios

GetTableFields Method

Syntax

Parameters

Return Value

Supported Databases

Example

Usage Scenarios

GetTables Method

Syntax

Parameters

Return Value

Supported Databases

Example

Usage Scenarios

GetDatabases Method

Syntax

Return Value

Supported Databases

Example

Usage Scenarios

GetVersion Method

Syntax

Return Value

Example

Usage Scenarios

Escape Method

Syntax

Parameters

Return Value

Implementation

Example

Notes

CheckConnection Method

Syntax

Return Value

Functionality

Example

Usage Scenarios

Comprehensive Examples

Example 1: Database Info Viewer

Example 2: Table Structure Comparison