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.
// 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(); A DbBulkInsert object ready for insertion. Null is returned if a problem was encountered
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().
True if a successful connection was established to the specified database, false if an error occurred
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.
True if the view was created, false otherwise
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.
Returns an array of DbColumn objects. If an error is encountered, null is returned.
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.
A DbViewInfo object, or null if path is not a view
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.
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.
A valid DbResult object upon success, null if the command failed
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.
Returns true if the file at the specified project path exists, and false otherwise.
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.
A DbDatabaseType constant, or DbDatabaseType.Invalid if the connection is not open
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.
Returns a DbError object
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.
Returns an array of DbObjectInfo objects
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.
Returns true if a connection exists to a database, otherwise false.
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.
The quoted identifier, or null if the connection is not open
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.
The quoted string literal
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.