Overview

AdvantageBuilder macros can access databases using three main approaches:

The AdvantageBuilder Database API provides four core operations:

The single-parameter versions use the current connection - the current connection as selected in the Data tab (e.g. SQL Server tab or Generic Builder tab). The three-parameter versions additonally accept connType and connString, allowing you to connect to SQL Server, Oracle, OleDb, ODBC, MySQL, PostgreSQL, and more.

Current Connection Examples

These examples use the current SQL Server connection configured in AdvantageBuilder (no explicit connection string). They call the AdvantageBuilder Database API directly from macros.

PowerShell - SELECT with ExecuteDataset


try {
    $sql = @"
select Id, Name, CreatedDate
from dbo.Customers
order by Name
"@

    $ds = ExecuteDataset $sql

    if ($ds -ne $null -and $ds.Tables.Count -gt 0) {
        $table = $ds.Tables[0]

        foreach ($row in $table.Rows) {
            $name = $row["Name"]
            $created = $row["CreatedDate"]
            # use $name, $created
        }
    }

    $ds = $null
}
catch {
    alert("Database error: " + $_.Exception.Message)
}

PowerShell - INSERT with ExecuteNonQuery


try {
    $sql = @"
insert into dbo.Customers (Name, CreatedDate)
values ('New Customer', getdate())
"@

    $recordsAffected = ExecuteNonQuery $sql
    alert("Records affected: " + $recordsAffected)
}
catch {
    alert("Database error: " + $_.Exception.Message)
}

JavaScriptV8 - SELECT with ExecuteDataset


try {
    let sql = `
select Id, Name, CreatedDate
from dbo.Customers
order by Name
`;

    let ds = ExecuteDataset(sql);

    if (ds != null && ds.Tables.Count > 0) {
        let table = ds.Tables.Item(0);

        for (let row of table.Rows) {
            let name = row.Item("Name");
            let created = row.Item("CreatedDate");
            // use name, created
        }
    }

    ds = null;
}
catch (ex) {
    alert("Database error: " + ex.Message);
}

JavaScriptV8 - UPDATE with ExecuteNonQuery


try {
    let sql = `
update dbo.Customers
set Name = 'Updated Name'
where Id = 123
`;

    let recordsAffected = ExecuteNonQuery(sql);
    alert("Records affected: " + recordsAffected);
}
catch (ex) {
    alert("Database error: " + ex.Message);
}

JScript - SELECT with ExecuteDataset


try {
    var sql = "select Id, Name, CreatedDate from dbo.Customers order by Name ";

    var ds = ExecuteDataset(sql);

    if (ds != null && ds.Tables.Count() > 0) {
        var table = ds.Tables[0];

        for (var row in table.Rows) {
            var name = table.Rows[row]["Name"];
            var created = table.Rows[row]["CreatedDate"];
            // use name, created
        }
    }

    ds = null;
}
catch(e) {
    alert("Database error: " + e.message);
}

JScript - DELETE with ExecuteNonQuery


try {
    var sql = "delete from dbo.Customers where Id = 123 ";

    var recordsAffected = ExecuteNonQuery(sql);
    alert("Records affected: " + recordsAffected);
}
catch(e) {
    alert("Database error: " + e.message);
}

Specified Connection Examples (Any Database)

Use the ExecuteNonQuery, ExecuteDataset, ExecuteDatasetXML, and ExecuteBatchTran functions to connect to any supported database type by specifying connType and connString.

Common connection types:

PowerShell - MySQL SELECT with ExecuteDataset2


try {
    $connectionString = @"
Server=localhost;Database=MyDb;User Id=myuser;Password=mypassword;
"@

    $connectionType = 4   # MySQL

    $sql = @"
select Id, Name
from Customers
order by Name
"@

    $ds = ExecuteDataset $sql $connectionType $connectionString

    if ($ds -ne $null -and $ds.Tables.Count -gt 0) {
        $table = $ds.Tables[0]

        foreach ($row in $table.Rows) {
            $name = $row["Name"]
            # use $name
        }
    }

    $ds = $null
}
catch {
    alert("Database error: " + $_.Exception.Message)
}

JavaScriptV8 - PostgreSQL INSERT with ExecuteNonQuery2


try {
    let connectionString = `
Host=localhost;Database=MyDb;Username=myuser;Password=mypassword;
`;
    let connectionType = 5; // PostgreSQL

    let sql = `
insert into Customers (Name)
values ('New Customer')
`;

    let recordsAffected = ExecuteNonQuery(sql, connectionType, connectionString);
    alert("Records affected: " + recordsAffected);
}
catch (ex) {
    alert("Database error: " + ex.Message);
}

JScript - Oracle SELECT with ExecuteDataset2


try {
    var connectionString = "Data Source=MyOracle;User Id=myuser;Password=mypassword;";
    var connectionType = 1; // OracleClient

    var sql = "select Id, Name from Customers order by Name ";

    var ds = ExecuteDataset(sql, connectionType, connectionString);

    if (ds != null && ds.Tables.Count() > 0) {
        var table = ds.Tables[0];

        for (var row in table.Rows) {
            var name = table.Rows[row]["Name"];
            // use name
        }
    }

    ds = null;
}
catch(e) {
    alert("Database error: " + e.message);
}

Using Dataset XML in GUI Macros

GUI macros often run inside an IE browser context, which cannot automate .NET DataSet objects directly. In these cases, use ExecuteDatasetXML or ExecuteDatasetXML2 to return XML representing the DataSet, then pass that XML into a JavaScript DataSet object that mimics the .NET DataSet model.

JScript - Using ExecuteDatasetXML with a JavaScript DataSet


var sql = "select * from tblMarketType ";

var dsXML = ExecuteDatasetXML(sql);

// DataSet is a JavaScript class that parses the XML and exposes
// Tables, Columns, Rows, and ItemArray similar to .NET DataSet.
var ds = new DataSet(dsXML);

if (ds != null && ds.Tables.Count() > 0) {
    var table = ds.Tables[0];

    for (var row in table.Rows) {
        // Access by index
        writeln(table.Rows[row][0]);

        // Or by column name
        // var value = table.Rows[row]["ColumnName"];
    }
}

ds = null;

The JavaScript DataSet implementation handles:

This pattern is ideal for Macro GUI scripts that need to display or process database data inside the browser.

Using the DB Helper (Recommended)

AdvantageBuilder includes a DB Helper macro that generates database access code snippets for you. It is available in the Smart, Extensible Macro Editor under:

DB Helper lets you:

Example - PowerShell snippet generated by DB Helper


$connectionString = @"
Server=localhost;Database=MyDb;User Id=myuser;Password=mypassword;
"@

$connectionType = 4

$sql = @"
select Id, Name
from Customers
order by Name
"@

$ds = ExecuteDataset $sql $connectionType $connectionString

if ($ds -ne $null -and $ds.Tables.Count -gt 0)
{
    $table = $ds.Tables[0]

    foreach ($row in $table.Rows) {

        $varName1 = $row["ColumnName"]
        $varName2 = $row[columnIndex]

    }
}

$ds = $null

Example - JavaScriptV8 snippet generated by DB Helper


let connectionString = `
Server=localhost;Database=MyDb;User Id=myuser;Password=mypassword;
`;

let connectionType = 4;

let sql = `
select Id, Name
from Customers
order by Name
`;

let ds = ExecuteDataset(sql, connectionType, connectionString);

if (ds != null && ds.Tables.Count > 0)
{
    let table = ds.Tables.Item(0);

    for (row of table.Rows)
    {
        let varName1 = row.Item("ColumnName");
        let varName2 = row.Item(columnIndex);
    }
}

ds = null;

Example - JScript snippet generated by DB Helper


var connectionString = "Server=localhost;Database=MyDb;User Id=myuser;Password=mypassword;";
var connectionType = 4;

var sql = "select Id, Name from Customers order by Name ";

var ds = ExecuteDataset(sql, connectionType, connectionString);

if (ds != null && ds.Tables.Count() > 0)
{
    var table = ds.Tables[0];

    for (var row in table.Rows)
    {
        var varName1 = row.Item("columnName");
        var varName2 = row.Item(columnIndex);
        var varName3 = row["columnName"];
    }
}

ds = null;

Using DB Helper is the fastest way to generate correct, engine-specific database access code that follows AdvantageBuilder best practices.

Putting It All Together

Use the AdvantageBuilder Database API to run SQL against SQL Server and other databases, returning either affected record counts or DataSets (or XML DataSets for GUI macros). PowerShell, JavaScriptV8, and JScript all support these functions, and the DB Helper macro can generate ready-to-use snippets for you.

For most macros:

Continue to Getting Started