Utility Methods

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

---

Table of Contents

• 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

1. 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

2. 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

1. Check connection state
2. If disconnected, attempt to reconnect
3. 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