DbConnection Class

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)

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


// variable holding a database connection
let db;

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

// the remaining forms are left commented out because they point at
// servers you almost certainly don't have; uncomment one and fill in
// your own host, port and credentials to use it.  A connection to a
// host that isn't listening takes some time to time out.

// example connection 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");

// example connection 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");

// example connection 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
const input = "";

// TODO: enter the output table path to which to write the schema
const output = "example_schema_output";

// 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
const project = HostApp.getDatabase();

// translate a DbType value into the SQL type name we want to
// record in the output table
const typeName = (type) => {
    switch (type) {
        case DbType.Character:     return "CHARACTER";
        case DbType.WideCharacter: return "WIDECHARACTER";
        case DbType.Binary:        return "BINARY";
        case DbType.Numeric:       return "NUMERIC";
        case DbType.Double:        return "DOUBLE";
        case DbType.Float:         return "FLOAT";
        case DbType.Integer:       return "INTEGER";
        case DbType.BigInteger:    return "BIGINTEGER";
        case DbType.SmallInteger:  return "SMALLINTEGER";
        case DbType.TinyInteger:   return "TINYINTEGER";
        case DbType.Date:          return "DATE";
        case DbType.DateTime:      return "DATETIME";
        case DbType.Boolean:       return "BOOLEAN";
        case DbType.Uuid:          return "UUID";
        default:                   return "UNDEFINED";
    }
};

// the work happens inside a function so the checks below can bail out
// with a plain 'return'; a return at the top level of a script is a
// syntax error in standard JavaScript
function writeSchemaTable() {
    // make sure the paths above were actually filled in; without these
    // checks the script would fail deep inside the SQL below with a far
    // less obvious message.  They are checked separately so the message
    // names the one that is actually missing.

    // input has no default -- the script cannot guess which table you
    // want described
    if (input === "") {
        alert("Please fill in the input table path at the top of this script.");
        return;
    }

    // output does ship with a default, so this only fires if it was
    // deliberately blanked
    if (output === "") {
        alert("Please fill in the output table path at the top of this script.");
        return;
    }

    if (!project.exists(input)) {
        alert(`The input table '${input}' does not exist.`);
        return;
    }

    // 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
    project.execute(`
        DROP TABLE IF EXISTS ${output};

        CREATE TABLE ${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
    const fields = project.describeTable(input);

    const inserter = project.bulkInsert(output,
                         "NAME, TYPE, WIDTH, SCALE, EXPRESSION");

    for (const field of fields) {
        // fill out the inserter elements corresponding to
        // the table fields
        inserter["NAME"]       = field.name;
        inserter["TYPE"]       = typeName(field.type);
        inserter["WIDTH"]      = field.width;
        inserter["SCALE"]      = field.scale;
        inserter["EXPRESSION"] = field.expression;

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

    // finalize the insert
    inserter.finishInsert();

    // open the file for display
    HostApp.open(output);

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

writeSchemaTable();