Tabular IQ

DbConnection Class

Class Index By Category

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.createView
Creates a SQL view
DbConnection.describeTable
Returns an array of DbColumn objects which describe the structure of the specified table
DbConnection.describeView
Describes a SQL view
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.getDatabaseType
Returns the type of the connected database
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
DbConnection.quoteIdentifier
Quotes a table or column name for use in a SQL statement
DbConnection.quoteString
Quotes a value as a SQL string literal

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.  The output path is a database path
    // everywhere else in this script, but inside SQL it is an
    // identifier, so it goes through quoteIdentifier(), which picks
    // the quote characters of whatever engine the project runs on
    const outputSql = project.quoteIdentifier(output);

    project.execute(`
        DROP TABLE IF EXISTS ${outputSql};

        CREATE TABLE ${outputSql}
        (
            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();

DbConnection.bulkInsert

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

Arguments

table_name
The path 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. table_name is a database path, not a SQL identifier, so it is never quoted; entries in field_list may be bare names or the spelling returned by quoteIdentifier().

DbConnection.connect

function DbConnection.connect(connection_string : String) : Boolean

Arguments

connection_string
The connection string describing the database to connect to.

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.createView

function DbConnection.createView(path : String, query : String, replace : Boolean) : Boolean

Arguments

path
The path of the view to create
query
The SELECT statement the view runs
replace
Optional. True to replace a view of the same name; false, the default, to fail if one exists

Returns

True if the view was created, false otherwise

Description

Creates a SQL view named path whose rows are the result of query, a SELECT statement. path is a database path such as "my_view" or "/folder/my_view", not a SQL identifier; do not quote it. A view reads like a table: select from it with execute(), look at its columns with describeTable(), and read its query back with describeView(). If a view of that name exists already, it is replaced when replace is true and the call fails otherwise; a table is never replaced. To name the view's columns, give the columns aliases in query. The same can be done with a CREATE VIEW statement through execute(), in the database's own SQL; createView() works the same way on every database that creates views. Not every database does: on one that does not, the call fails. A failure is reported like a failed execute(): through getError(), or as a thrown DbException when exceptions are enabled.

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. path is a database path, not a SQL identifier, so it is never quoted. The column names come back bare and in the engine's exact spelling; pass one through quoteIdentifier() before using it in SQL.

DbConnection.describeView

function DbConnection.describeView(path : String) : DbViewInfo

Arguments

path
The path of the view to describe

Returns

A DbViewInfo object, or null if path is not a view

Description

Returns a DbViewInfo object describing the view at path: its name, the query that defines it, and whether it is materialized or updatable. path is a database path, as for describeTable(); do not quote it. A view's columns are described by describeTable(), as a table's are. Returns null when path is not a view (a table, or nothing at all), or when the database cannot describe its views.

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. path is a database path such as "my_table" or "/folder/my_table", not a SQL identifier; do not quote it.

DbConnection.getDatabaseType

function DbConnection.getDatabaseType() : Integer

Returns

A DbDatabaseType constant, or DbDatabaseType.Invalid if the connection is not open

Description

Returns one of the DbDatabaseType constants describing the engine behind this connection, for example DbDatabaseType.Xdnative or DbDatabaseType.Xdtabula. A plain directory of data files reports DbDatabaseType.Filesystem; it speaks the same SQL dialect as DbDatabaseType.Xdtabula. Use this to branch on dialect-specific SQL; for identifier quoting, prefer quoteIdentifier(), which needs no branching.

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, mount and tableType. A SQL view is listed with the type "TABLE" and the tableType "VIEW". Names are bare database paths; pass one through quoteIdentifier() before using it in SQL. On a project in the previous format, the project's hidden entries (names beginning with a period, such as .appdata and .system) are not listed.

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.

DbConnection.quoteIdentifier

function DbConnection.quoteIdentifier(name : String) : String

Arguments

name
The bare table or column name to quote

Returns

The quoted identifier, or null if the connection is not open

Description

Returns name wrapped in the identifier quote characters of the connected database, so the same script line yields [name] on an Xdnative database, "name" on an Xdtabula or Postgres database and `name` on MySQL. Any quote character inside name is escaped. The name is always quoted, which keeps its exact spelling on engines that fold bare names to upper or lower case and sidesteps reserved words. Quote a single part at a time: a qualified reference is built as quoteIdentifier(alias) + "." + quoteIdentifier(column). Use this only for text passed to execute(); the path parameters of describeTable(), exists(), save() and bulkInsert() are database paths and must stay unquoted.

DbConnection.quoteString

function DbConnection.quoteString(value : String) : String

Arguments

value
The value to quote

Returns

The quoted string literal

Description

Returns value as a single-quoted SQL string literal with every embedded single quote doubled, which is the literal syntax every supported engine accepts. Null and undefined become the bare keyword NULL. Non-string values are converted to their string form first, so a number passed here becomes a string literal, not a numeric literal. Every value a script splices into a statement should go through this method; text that came from a file or a user is never safe to concatenate directly.