Query Operations

This document details the query functionality of the cDataBase class, including basic queries, result set processing, etc.

---

Table of Contents

• Basic Query
• Query Methods
• Result Set Processing
• Query Options
• Common Query Scenarios

---

Basic Query

Sql Method

The Sql method sets the SQL query statement.

Syntax

Function Sql(ByVal RawSqlString As String) As cDataBase

Example

' Set SQL statement
db.Sql "SELECT * FROM users WHERE age > 18"

Query Method

The Query method executes a query and returns a Boolean value. Query results are stored in the db.Rs property, not as the return value.

Syntax

Function Query(Optional CurType As CursorTypeEnum = adOpenKeyset, _
              Optional LockType As LockTypeEnum = adLockOptimistic, _
              Optional Options As Long = -1) As Boolean

Parameters

Parameter	Type	Description
CurType	CursorTypeEnum	Cursor type (optional, default adOpenKeyset)
LockType	LockTypeEnum	Lock type (optional, default adLockOptimistic)
Options	Long	Query options (optional, default -1)

Return Value

• True - Query successful, results stored in db.Rs
• False - Query failed (view error via LastErr)

Important: The Query method returns a Boolean value, not a Recordset. Query results need to be accessed through the db.Rs property.

Example

' Basic query
If db.Sql("SELECT * FROM users").Query Then
    ' Query successful, use db.Rs to access Recordset (not the return value)
    Do Until db.Rs.EOF
        Debug.Print db.Rs("name")
        db.Rs.MoveNext
    Loop
    ' Close Recordset when done
    db.Rs.Close
End If

' Wrong example: Don't assign Query return value to Recordset variable
' Dim Rs As ADODB.Recordset
' Set Rs = db.Query("SELECT * FROM users")  ' Wrong! Query returns Boolean

' Correct example: Use db.Rs to access results
If db.Sql("SELECT * FROM users").Query Then
    ' Use db.Rs to access results
    Do Until db.Rs.EOF
        Debug.Print db.Rs("name")
        db.Rs.MoveNext
    Loop
    db.Rs.Close
End If

Fetch Method

The Fetch method executes a query and automatically converts to Dictionary collection. Main use case is for JSON output and data exchange, especially for use with cHttpServer and other components.

Syntax

Function Fetch(Optional CurType As CursorTypeEnum = adOpenKeyset, _
               Optional LockType As LockTypeEnum = adLockOptimistic, _
               Optional Options As Long = -1) As Boolean

Functionality

• Execute query
• Automatically convert Recordset to Collection (stored in Rows property)
• Automatically set first row to Row property
• Can be directly used for JSON serialization, works with cJson and cHttpServerResponse

Example

' Query and auto-convert
If db.Sql("SELECT * FROM users WHERE age > 18").Fetch Then
    ' Access first row
    Debug.Print db.Row("name")
    Debug.Print db.Row("age")

    ' Iterate all rows
    Dim i As Long
    For i = 1 To db.Rows.Count
        Debug.Print db.Rows(i)("name")
    Next
End If

JSON Output Scenario (Main Use Case)

The main advantage of Fetch is convenient conversion of query results to JSON strings for external data exchange:

' Usage in HttpServer route
Private Sub Server_OnRoute(ctx As VBMAN.cHttpServerContext)
    ' Query data
    If ctx.Db.Sql("SELECT * FROM users").Fetch Then
        ' Direct JSON output (one line)
        ctx.Response.Json ctx.Db.Rows
    End If
End Sub

' Or use cJson object
Dim json As New VBMAN.cJson
If db.Sql("SELECT * FROM users").Fetch Then
    Dim sJson As String
    sJson = json.Encode(db.Rows)  ' Convert to JSON string
    Debug.Print sJson
End If

Note: cHttpServerResponse.Json method automatically recognizes Recordset and converts, but using Fetch and passing db.Rows (Collection) is more efficient as conversion is already done.

---

Query Method Comparison

Method	Return Type	Auto Convert	Use Scenario
Query	Recordset	No	Need direct Recordset operation, need Recordset advanced features
Fetch	Collection	Yes	Mainly for JSON output and data exchange, use with cHttpServer, cJson

---

Result Set Processing

Rs Property

The Rs property provides direct access to the ADO Recordset.

' Use Recordset
If db.Sql("SELECT * FROM users").Query Then
    Do Until db.Rs.EOF
        Debug.Print db.Rs("name")
        Debug.Print db.Rs("age")
        db.Rs.MoveNext
    Loop

    ' Get record count
    Debug.Print "Total records: " & db.Rs.RecordCount
End If

Rows Property

The Rows property is the converted Collection containing all row Dictionaries. Can be directly used for JSON serialization.

' Use Collection
If db.Sql("SELECT * FROM users").Fetch Then
    Dim i As Long
    For i = 1 To db.Rows.Count
        Debug.Print db.Rows(i)("name")
        Debug.Print db.Rows(i)("age")
    Next
End If

JSON Output Examples

' Scenario 1: Direct JSON output in HttpServer
Private Sub Server_OnRoute(ctx As VBMAN.cHttpServerContext)
    If ctx.Db.Sql("SELECT * FROM users").Fetch Then
        ' Direct JSON output (recommended)
        ctx.Response.Json ctx.Db.Rows
    End If
End Sub

' Scenario 2: Use cJson object to convert to JSON string
Dim json As New VBMAN.cJson
If db.Sql("SELECT * FROM users").Fetch Then
    Dim sJson As String
    sJson = json.Encode(db.Rows)
    ' Output: [{"id":1,"name":"John","age":25},{"id":2,"name":"Mary","age":30}]
End If

' Scenario 3: JSON output with pagination
If db.Sql("SELECT * FROM users").Page(1, 10).Fetch Then
    Dim lTotal As Long
    lTotal = db.Count("users")
    ' Output JSON with total count and pagination info
    ctx.Response.Json db.Rows, 200, "Success", lTotal
End If

Row Property

The Row property is the Dictionary of the current first row.

' Access first row
If db.Sql("SELECT * FROM users WHERE id = 1").Fetch Then
    Debug.Print db.Row("name")
    Debug.Print db.Row("age")
End If

Result Set Structure

Rows (Collection)
├── Rows(1) (Dictionary)
│   ├── "id" => 1
│   ├── "name" => "John"
│   └── "age" => 25
├── Rows(2) (Dictionary)
│   ├── "id" => 2
│   ├── "name" => "Mary"
│   └── "age" => 30
└── ...

---

Query Options

Cursor Type (CursorTypeEnum)

Type	Value	Description
adOpenForwardOnly	0	Forward-only cursor (fastest)
adOpenKeyset	1	Keyset cursor (default)
adOpenDynamic	2	Dynamic cursor
adOpenStatic	3	Static cursor

' Use forward-only cursor (best performance)
db.Sql("SELECT * FROM users").Query adOpenForwardOnly

' Use static cursor (supports RecordCount)
db.Sql("SELECT * FROM users").Query adOpenStatic

Lock Type (LockTypeEnum)

Type	Value	Description
adLockReadOnly	1	Read-only (default for query)
adLockPessimistic	2	Pessimistic locking
adLockOptimistic	3	Optimistic locking
adLockBatchOptimistic	4	Batch optimistic locking

' Read-only query (best performance)
db.Sql("SELECT * FROM users").Query adOpenKeyset, adLockReadOnly

---

Common Query Scenarios

Scenario 1: Single Record Query

' Query single record
If db.Sql("SELECT * FROM users WHERE id = 1").Fetch Then
    If db.Rows.Count > 0 Then
        Debug.Print "Username: " & db.Row("name")
        Debug.Print "Age: " & db.Row("age")
    Else
        Debug.Print "Record not found"
    End If
End If

Scenario 2: Conditional Query

' Multi-condition query
Dim sSql As String
sSql = "SELECT * FROM users WHERE age > 18 AND status = 'active'"
If db.Sql(sSql).Fetch Then
    Dim i As Long
    For i = 1 To db.Rows.Count
        Debug.Print db.Rows(i)("name")
    Next
End If

Scenario 3: Sorted Query

' Descending order by age
If db.Sql("SELECT * FROM users ORDER BY age DESC").Fetch Then
    Dim i As Long
    For i = 1 To db.Rows.Count
        Debug.Print db.Rows(i)("name") & " - " & db.Rows(i)("age")
    Next
End If

Scenario 4: Aggregate Query

' Statistical query
If db.Sql("SELECT COUNT(*) AS cnt, AVG(age) AS avg_age FROM users").Fetch Then
    If db.Rows.Count > 0 Then
        Debug.Print "Total users: " & db.Row("cnt")
        Debug.Print "Average age: " & db.Row("avg_age")
    End If
End If

Scenario 5: Grouped Query

' Grouped statistics
If db.Sql("SELECT status, COUNT(*) AS cnt FROM users GROUP BY status").Fetch Then
    Dim i As Long
    For i = 1 To db.Rows.Count
        Debug.Print db.Rows(i)("status") & ": " & db.Rows(i)("cnt")
    Next
End If

Scenario 6: Join Query

' Inner join query
Dim sSql As String
sSql = "SELECT u.name, p.title " & _
       "FROM users u " & _
       "INNER JOIN posts p ON u.id = p.user_id"
If db.Sql(sSql).Fetch Then
    Dim i As Long
    For i = 1 To db.Rows.Count
        Debug.Print db.Rows(i)("name") & " - " & db.Rows(i)("title")
    Next
End If

Scenario 7: Subquery

' Subquery
Dim sSql As String
sSql = "SELECT * FROM users " & _
       "WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000)"
If db.Sql(sSql).Fetch Then
    ' Process results
End If

Scenario 8: Fuzzy Query

' LIKE query
If db.Sql("SELECT * FROM users WHERE name LIKE '%John%'").Fetch Then
    Dim i As Long
    For i = 1 To db.Rows.Count
        Debug.Print db.Rows(i)("name")
    Next
End If

Scenario 9: Paginated Query

' Use Page method (see pagination.md for details)
If db.Sql("SELECT * FROM users").Page(1, 10).Fetch Then
    Dim i As Long
    For i = 1 To db.Rows.Count
        Debug.Print db.Rows(i)("name")
    Next
End If

Scenario 10: Parameterized Query

' Use parameterized query (see parameterized.md for details)
If db.Sql("SELECT * FROM users WHERE name = ? AND age > ?") _
    .Param("name", "John", VBMAN.adVarWChar) _
    .Param("age", 18, VBMAN.adInteger) _
    .QueryParam Then

    Dim i As Long
    For i = 1 To db.Rows.Count
        Debug.Print db.Rows(i)("name")
    Next
End If

Scenario 11: JSON Output (Main Use Case for Fetch)

' Output JSON in HttpServer route
Private Sub Server_OnRoute(ctx As VBMAN.cHttpServerContext)
    ' Query data and convert to Collection
    If ctx.Db.Sql("SELECT * FROM users WHERE status = 'active'").Fetch Then
        ' Direct JSON output (one line)
        ctx.Response.Json ctx.Db.Rows
    End If
End Sub

' Paginated JSON API
Private Sub Server_OnRoute(ctx As VBMAN.cHttpServerContext)
    Dim lPage As Long
    Dim lPageSize As Long
    lPage = CLng(ctx.Request.Query("page"))
    lPageSize = CLng(ctx.Request.Query("pageSize"))

    ' Query paginated data
    If ctx.Db.Sql("SELECT * FROM users ORDER BY id").Page(lPage, lPageSize).Fetch Then
        Dim lTotal As Long
        lTotal = ctx.Db.Count("users")
        ' Output JSON with data, total count, etc.
        ctx.Response.Json ctx.Db.Rows, 200, "Success", lTotal
    End If
End Sub

' Use cJson object to convert to JSON string
Dim json As New VBMAN.cJson
If db.Sql("SELECT * FROM users").Fetch Then
    Dim sJson As String
    sJson = json.Encode(db.Rows)
    ' Can be used for file saving, network transmission, etc.
    Debug.Print sJson
End If

---

Performance Optimization

1. Only Query Needed Fields

' Not recommended: Query all fields
db.Sql("SELECT * FROM users").Query

' Recommended: Only query needed fields
db.Sql("SELECT id, name FROM users").Query

2. Use Indexed Fields

' Recommended: Use indexed fields as conditions
db.Sql("SELECT * FROM users WHERE id = 1").Query

3. Limit Result Set Size

' Recommended: Use TOP to limit results
db.Sql("SELECT TOP 100 * FROM users").Query

4. Use Appropriate Cursor Types

' Recommended: Use ForwardOnly for read-only queries
db.Sql("SELECT * FROM users").Query adOpenForwardOnly, adLockReadOnly

---

Error Handling

' Query error handling
If Not db.Sql("SELECT * FROM users").Query Then
    Debug.Print "Query failed"
    Debug.Print "Error code: " & db.LastErrNumber
    Debug.Print "Error description: " & db.LastErrDescription
    Debug.Print "Full info: " & db.LastErr
    Exit Sub
End If

---

Best Practices

1. Always Check Return Values

' Recommended
If db.Sql("SELECT * FROM users").Query Then
    ' Process results
Else
    ' Error handling
End If

2. Use Fetch for JSON Output

' Recommended: Use Fetch for JSON output (main use case)
If db.Sql("SELECT * FROM users").Fetch Then
    ' Direct output in HttpServer
    ctx.Response.Json db.Rows

    ' Output single record only
    ctx.Response.Json db.Row

    ' Or use cJson object
    Dim json As New VBMAN.cJson
    Dim sJson As String
    sJson = json.Encode(db.Rows)
End If

3. Release Resources Promptly

' Recommended: Close Recordset after query
If db.Sql("SELECT * FROM users").Query Then
    ' Process results
    If db.Rs.State <> adStateClosed Then
        db.Rs.Close
    End If
End If

---

Last Updated: 2026-01-21