How can database queries be run using native KTM script objects?

Question / Problem:

How can database queries be run using native KTM script objects?

Answer / Solution:

The following code assumes a relational database has been defined in Project Settings named "TestDB" which points to a relational database table with columns NAME and ID:

'get database set in Project Settings
Dim db As CscDatabase
Set db = Project.Databases.ItemByName("TestDB"

'Create query
Dim query As CscSQLQuery
Set query = db.SQLTable.CreateQuery

'Set where clause
query.SetCustomWhere("ID=1")

'run query
Dim Result As CscSQLRecordset
Result=query.ExecuteQuery

'get rows
Dim rows As CscSQLRows
Set rows = Result.Rows

'if the query returns one result, display msgbox with the name
If rows.Count = 1 Then
    Dim row As CscSQLRow
    Set row = rows.ItemByIndex(0)
    MsgBox "Name column is" & row.CellByName("NAME").Value
End If

The Scripting Object References help (Help ¦ Scripting Object References... from the script window) contains full explanations of the methods and properties available for the objects used in the code above.

Applies to: