Database

The database object provides access to an external SQL database via simulation script.

To create a new database object, go to the Project Explorer and right click on the Databases folder. Select “New Database” from the context menu. A new database object will be added to the Databases folder.

To configure the new Database object, select it in the Project Explorer and open the Property Editor:

Each database object has the following configurable parameters:

• Name: A string describing the Database
• ID: An integer used to uniquely identify the Database from the simulation script.
• Database Provider Type: The provider type of the database connection. Eg. SQLite, SQL Client, Oracle Client Data Server . May vary depending on what is available on the host machine.
• Connection String: The SQL connection string passed to the provider when establishing a database connection. Format and content of the connection string will vary depending on the type of database and database settings required.
• Sub-System: The sub system associated with the database object.

Test Connection

This functionality allows the user to confirm the database open in the property editor is accessible and a connection can be established to it.

Clicking the button will trigger a connection attempt from Sym3. If the attempt is successful, this dialog will be displayed:

If the connection attempt is unsuccessful, a dialog containing an error message will be displayed.

Accessing the Database from Simulation Script

These are the Sym3 database related API functions:

• GetDatabaseById: Retrieves a Database Object given a numeric database ID (as described above).
• GetDatabaseByName: Retrieves a Database Object given the string name (as described above).
• GetDatabases: Retrieves a list of Database Objects for the current project.
• SetSQLCmdEx: Asynchronously executes a query for a given database. User supplied event handlers are called when either an error occurs or the function is successful.

Connection Examples

Example configurations are provided here to a SQLite or SQL Server database connection from Simulation Script using a Sym3 Database Object.

Other data provider connections are also supported. See your data provider or driver documentation for specific details on connection string format, ODBC configuration etc. For further information on SQL data provider connection strings, see here: https://www.connectionstrings.com/

Configuring the data provider

SQLite

1. Install the appropriate SQLite ODBC data provider. Download link here: http://www.ch-werner.de/sqliteodbc/
2. Configure SQLite as an ODBC data source in the windows ODBC settings menus.

SQL Server

The .Net “SQL Client” should already be available in Sym3 without the installation of any additional drivers.

Connecting to the database

1. Create a database and ensure your database server is running and is accessible from Sym3 server.
2. Configure username and password for your new database.
3. Add a new database object with a connection string as shown below, customized with settings specific to your particular database configuration.
4. Click “Test Connection” to verify the new database connection is working.

Sample SQL Server connection string

Server=localhost;
Database=TestDB;
User Id=sa;
Password=1#126d4a1sd2@3;

Sample SQLite connection string:

DSN=SQLite3 Datasource;
Database=c:\sql\test.db;

Accessing the Database Object

// Using GetDatabases()
var arr = GetDatabases();
for (var i = 0; i < arr.length; ++i)
{
    LogDebug("Database ID " + arr[i].DatabaseId);
    LogDebug("Database Name " + arr[i].Name);
    LogDebug("Database ConnectionString " + arr[i].ConnectionString);
    LogDebug("Database DatabaseProviderName " + arr[i].DatabaseProviderName);
}

// Using GetDatabaseById()
var db = GetDatabaseById(0);
LogDebug("Database Name " + db.Name);

// Using GetDatabaseByName()
var db = GetDatabaseByName("TestDB");
LogDebug("Database ID " + db.DatabaseId);

Querying the database

function queryCallback(id, result)
{
    // One record per line
    // Each line is a comma delimited list of field values
    var selectData=result.split("\n");

    for (var i=0; i<=selectData.length; i++)
    {
var fieldData = selectData[i].split(",");
var col1 = fieldData[0];
var col2 = fieldData[1]; 
// ... extend as needed for more fields/columns
    }
}

function errorHandler(id, errorMessage)
{
    LogError(errorMessage);
}

function OnSimulationStart()
{
    try
    {
var db = GetDatabaseByName("TestDB");

SetSQLCmdEx(db, 1, "SELECT * FROM TestTable", queryCallback, errorHandler);
    }
    catch (ex)
    {
LogError(ex);
    }
}