Developer Resources

DbConnection

Overview

The DbConnection class represents and manages a connection to a database. Users of this class can open database connections to local or remote database and execute queries. It is the root class of the database class tree.

Constructor

DbConnection(connection_string : String)

Arguments

connection_string: The connection string describing the database to connect to. Passing an empty string to this parameter will cause the call to attempt a connection to the host application's currently open database

Methods

DbConnection.bulkInsert: Initiate a bulk insert operation
DbConnection.connect: Connects to local and remote database via a connection string
DbConnection.describeTable: Returns an array of DbColumn objects which describe the structure of the specified table
DbConnection.enableExceptions: Sets whether exceptions should be thrown on database errors
DbConnection.execute: Executes a SQL statement on the database
DbConnection.exists: Returns true if the file at the specified project path exists, and false otherwise.
DbConnection.getError: Returns an error object indicating the last database error
DbConnection.getObjects: Returns an array of DbObjectInfo objects which describe objects found in the database.
DbConnection.isConnected: Tests whether the connection object has an active connection to a database

Example

// example 1: connecting to a database;
// TODO: enter proper values or else connection
// will take some time to timeout


var db;

// connect to the host application's local database, which
// is the project that's currently open
db = HostApp.getDatabase();

// connect to a MySQL database called 'dbname', hosted on
// mysql.my.domain at port 3306 with user name 'user' and
// password 'pw'
db = new DbConnection("mysql://user:pw@mysql.my.domain:3306/dbname");

// connect to a SQL Server database called 'dbname', hosted on
// mssql.my.domain at port 1433 with user name 'user' and
// password 'pw'
db = new DbConnection("mssql://user:pw@mssql.my.domain:1433/dbname");

// connect to an Oracle database called 'dbname', hosted on
// oracle.my.domain at port 1521 with user name 'user' and
// password 'pw'
db = new DbConnection("oracle://user:pw@oracle.my.domain:1521/dbname");



// example 2: determining the structure of a table using
// describeTable() and inserting the results into a table
// using bulkInsert()


// TODO: enter input table path for which to get the schema
var g_input = "";

// TODO: enter the output table path to which to write the schema
var g_output = "";

// g_project is the database where the input table is located
// and to which the output table will be written; here, we use
// the application's current database
var g_project = HostApp.getDatabase();

// the execute function executes SQL statements on a database;
// in this case, if the table already exists, delete it, then
// create the output table with five fields: name, type, width,
// scale, and expression
g_project.execute
("
    DROP TABLE IF EXISTS " + g_output + ";

    CREATE TABLE " + g_output + "
    (
        NAME         VARCHAR(500),
        TYPE         VARCHAR(25),
        WIDTH        NUMERIC(10,0),
        SCALE        NUMERIC(10,0),
        EXPRESSION   VARCHAR(500)
    );
");

// get the schema from the input table and
// create the inserter to insert the schema
// items
var fields = g_project.describeTable(g_input);

var inserter = g_project.bulkInsert(g_output,
                   "NAME, TYPE, WIDTH, SCALE, EXPRESSION");

for (var item in fields)
{
    // find out the type
    var nametype;
    if (fields[item].type == DbType::Character)
        nametype = "CHARACTER";
    if (fields[item].type == DbType::WideCharacter)
        nametype = "WIDECHARACTER";
    if (fields[item].type == DbType::Binary)
        nametype = "BINARY";
    if (fields[item].type == DbType::Numeric)
        nametype = "NUMERIC";
    if (fields[item].type == DbType::Double)
        nametype = "DOUBLE";
    if (fields[item].type == DbType::Integer)
        nametype = "INTEGER";
    if (fields[item].type == DbType::Date)
        nametype = "DATE";
    if (fields[item].type == DbType::DateTime)
        nametype = "DATETIME";
    if (fields[item].type == DbType::Boolean)
        nametype = "BOOLEAN";

    // fill out the inserter elements corresponding to
    // the table fields
    inserter["NAME"]       = fields[item].name;
    inserter["TYPE"]       = nametype;
    inserter["WIDTH"]      = fields[item].width;
    inserter["SCALE"]      = fields[item].scale;
    inserter["EXPRESSION"] = fields[item].expression;

    // insert the row
    inserter.insertRow();
}

// finalize the insert
inserter.finishInsert();

// refresh the project tree
HostApp.refresh();

DbConnection.bulkInsert

function DbConnection.bulkInsert(table_name : String, field_list : String) : DbBulkInsert

Arguments

table_name: The name of the table to do the insert on
field_list: A comma-delimited list of fields in which values will be inserted. Passing "*" or omitting the parameter entirely will indicate that all fields will be involved in the bulk insert operation

Returns

A DbBulkInsert object ready for insertion. Null is returned if a problem was encountered

Description

Initiates a bulk insert operation on table_name. In the field_list parameter the caller can specify a list of fields in which values will be inserted. The motivation for bulk inserts is speed; using bulkInsert() can yield a significant performance increase over SQL INSERT statements run with the execute() method.

DbConnection.connect

function DbConnection.connect(connection_string : String) : Boolean

Arguments

connection_string: The connection string describing the database to connect to. Passing an empty string to this parameter will cause the call to attempt a connection to the host application's currently open database.

Returns

True if a successful connection was established to the specified database, false if an error occurred

Description

Attempts to open the database specified in the connection. If the connection_string parameter is omitted or empty, the call will connect to the host application's currently open database.

DbConnection.describeTable

function DbConnection.describeTable(path : String) : Array(DbColumn)

Arguments

path: The location of the file from which the structure shall be retrieved.

Returns

Returns an array of DbColumn objects. If an error is encountered, null is returned.

Description

Returns an array of DbColumn objects which describe the structure of the specified table. Each DbColumn object in the array has the following properties: name, type, width, scale, and expression.

DbConnection.enableExceptions

function DbConnection.enableExceptions(enabled : Boolean)

Description

This method allows the caller to configure whether or not database exception objects are thrown when database errors occur. By default, database exceptions are not thrown and errors are returned.

DbConnection.execute

function DbConnection.execute(sql_query : String) : DbResult

Arguments

sql_statement: The SQL statement to execute

Returns

A valid DbResult object upon success, null if the command failed

Description

Executes a SQL statement on the database. If the command was a query and it succeeded, a DbResult object is returned which will provide iteration functionality for the resulting data set. For non-query commands, such as INSERT or DROP, boolean true is returned upon success. If the command or query failed, null is returned.

DbConnection.exists

function DbConnection.exists(path : String) : Boolean

Arguments

path: The location of the project file to check for existence.

Returns

Returns true if the file at the specified project path exists, and false otherwise.

Description

Returns true if the file at the specified project path exists, and false otherwise.

DbConnection.getError

function DbConnection.getError() : DbError

Returns

Returns a DbError object

Description

getLastError() returns a DbError object which describes the last error condition the database encountered. If no error was encountered, a DbError object is still returned, but with a zero error code.

DbConnection.getObjects

function DbConnection.getObjects(condition1 : String, condition2 : String, ...) : Array(DbObjectInfo)

Arguments

condition: The condition of the search, such as the schema or folder to search

Returns

Returns an array of DbObjectInfo objects

Description

Returns an array of DbObjectInfo objects which describe objects found in the database. One or more conditions may be specified as parameters to limit the search results. Condition parameters have the format KEY=VALUE, where KEY can be SCHEMA, CATALOG or TABLE. If no condition is specified, the input string is interpreted as a SCHEMA. The DbObjectInfo objects returned have the following members: name, type, catalog, schema, and mount.

DbConnection.isConnected

function DbConnection.isConnected() : Boolean

Returns

Returns true if a connection exists to a database, otherwise false.

Description

Tests whether the connection object has an active connection to a database. This method is useful when specifying the connection string in the object constructor.

About Us·Terms of Use·Privacy Policy·Contact Us