ExternalDb

The ExternalDb Moobot module allows Moogsoft Enterprise to access the following external relational databases, as well as any relational database that supports JDBC:

MySQL
Microsoft SQL Server
IBM DB2
Oracle
PostgreSQL

Using ExternalDb, Moogsoft Enterprise can retrieve information from external databases for use in alerts and Situations. The ExternalDb method can also update external databases with information from Moogsoft Enterprise.

Load the module

The ExternalDb Moobot module is available to load into any standard Moobot. To load it, define a new global variable at the top of the Moobot Javascript file. For example:

var externalDb = MooBot.loadModule('ExternalDb');

Method reference

The ExternalDb module uses the following methods.

connect

Establishes a connection to an external database with defined connection properties.

Request arguments

The method takes the following arguments.

Name	Type	Required	Description
`properties`	Object	Yes	A Javascript object containing connection properties. See Database connection properties below.

Database connection properties

The properties object is a Javascript object that can contain the following keys. You can also define connection properties in the file moog_external_db_details.conf.

Key	Description
`type`	Database type. If you omit type you must specify the URL, jar files and JDBC class name. To use an external database other than those in the supported list, omit the `type` from the connection properties.
`host`	Database host name or IP address. Default is localhost.
`database`	Database name.
`port`	Port number. Default values: MySQL: 3306 SQL Server: 1433 DB2: 50000 Oracle: 1521 PostgreSQL: 5432
`user`	Username to connect to the database. If omitted you can specify it in the URL (for some databases) or the properties.
`password`	Password to connect to the database. If omitted you can specify it in the URL (for some databases) or the properties.
`encrypted_password`	Encrypted version of the password.
`properties`	A map of key-value pairs of properties to specify the connection properties. For example, `loginTimeout` for SQL Server or `useCompression` for MySQL.
`jar_files`	The JDBC driver jar file locations. Default values: SQL Server: sqljdbc4.jar DB2: db2jcc4.jar Oracle: ojdbc6.jar PostgreSql: postgresql-9.3-1102.jdbc41.jar The assumed location is `$MOOGSOFT_HOME/lib/cots/`. These files are not bundled in a standard Moogsoft Enterprise installation.
`class_name`	The name of the JDBC class. Default values: SQL Server: com.microsoft.sqlserver.jdbc.SQLServerDriver MySQL: com.mysql.jdbc.Driver DB2: com.ibm.db2.jcc.DB2Driver Oracle: oracle.jdbc.OracleDriver PostgreSql: org.postgresql.Driver
`URL`	JDBC-specific URL. If specified, it can override other properties.
`pool_properties`	A map of key-value pairs of properties of the connection pool that will be created. You can define the number of connections made available to the external database by including the `pool_size` key. `pool_size`: Number of connections in the pool. Must be 1 or more. Default is 10. Generally, this should match the number of threads configured to run the Moobot.

Response

The method returns the following parameter.

Type	Description
`Object`	A Java object containing the connection details. Returns `null` if the connection fails.

Examples

The method can accept a single parameter with connection properties, or two parameters: one with the generic connection properties and one specific to this connection.

The following command establishes a connection using the details in the customers object:

var customersConnection = externalDb.connect(dbTypes.customers);

The following command establishes a connection using the same details, but the username and password in the object are overridden by the ones provided in the command:

var customersConnection = externalDb.connect(dbTypes.customers, {user: 'myuser', password: 'mypassword'});

execute

Performs an SQL update on the external database.

Request arguments

The method takes the following arguments.

Name	Type	Required	Description
`argument`	String	Yes	SQL string argument containing the update.

Response

The method returns the following parameter.

Type	Description
`Boolean`	Indicates if the operation was successful: `true` = success, `false` = fail.

Examples

The following example uses the execute method to update the species column in a pets database.

employeesConnection.execute('update pets set species="dog" where species null');

query

Performs an SQL query on the external database.

Request arguments

The method takes the following arguments.

Name	Type	Required	Description
`argument`	String	Yes	SQL string argument containing the query.

Response

The method returns the following parameter.

Type	Description
`Object`	A Java object containing the query results. Returns `null` if the connection fails.

Response methods

The response can contain the following methods.

Name	Description
`rows`	Returns the number of rows.
`next`	Returns the next row.
`rewind`	Goes back to the first row.
`hasNext`	Indicates whether the current row is the last one.
`row(i)`	Returns row `i` (zero based index).
`first`	Returns the first row.
`type(name)`	Returns the type of column called `name` (or `null` if no such column exists).
`columnName(i)`	Returns the name of column `i` (zero based index).
`isNumber(name)`	Returns `true` if the column name is a numeric column.
`isString(name)`	Returns `true` if the column name is a not numeric.

Row methods

You can use the following row methods in the query.

Name	Description
`value(name)`	Returns the value for column named name as a string.
`columns`	Returns the number of columns.
`rewind`	Goes back to the first column.
`hasNext`	Indicates whether the current column is the last one.
`next`	Returns the value in the next column as a string.
`column(i)`	Returns the value in column `i` as a string.
`first`	Returns the value in the first column as a string.
`last`	Returns the value in the last column as a string.

Example

The following example uses the query method to query the customers database.

var customers = customersConnection.query('Select * from customers');
while(customers.hasNext()==true)
{
    var customer = customers.next();
    var firstName = customer.value("first_name");
    var lastName = customer.value("last_name");
    logger.info(firstName + " " + lastName +" is a customer");
}

prepare

Performs SQL queries and updates on the external database. You can use the prepare method for complicated operations. For example, you can reuse the same SQL statement with different arguments, and you can use external data within a statement.

Request arguments

The method takes the following arguments.

Name	Type	Required	Description
`argument`	String	Yes	SQL string argument containing the query or update.

Response

The method returns the following parameter.

Type	Description
`Object`	A Java object containing the query results. Returns `null` if the connection fails.

Response methods

The response object can contain the following methods.

Name	Description
`set(i, value)`	Sets parameter `i` (1 based index) to a value. Returns `false` in case of failure.
`bind(value1, value2, value3,...)`	Sets parameter 1 to value 1, parameter 2 to value 2 and so on. Returns `false` in case of failure.
`bindCount`	Returns the number of parameters needed to bind. Some vendors do not support this method in all cases, if not supported `-1` is returned.
`execute(value1, value2, value3,...)`	Sets parameter 1 to value 1, parameter 2 to value 2 and so on, and then executes the prepared statement. Returns false in case of a failure in one of the stages. If values are omitted, uses the previously `set` or `bind`.
`query(value1, value2, value3,...)`	Sets parameter 1 to value 1, parameter 2 to value 2 and so on, and then performs the query. Returns `null` in case of failure. Returns a result set if the operation was successful. If values are omitted, uses the previously `set` or `bind`.
`close`	Closes the prepared statement. Note: It is important to close the statement with this method when no longer needed.

Example

The following example uses the prepare method to set all pet species to "dog" if the breed is one of those specified:

var petsChange = employeesConnection.prepare('Update pets set species=? where breed = ?');
petsChange.set(1, 'dog');
for (var breed in ['Labrador', 'Terrier', 'Beagle', 'Boxer', 'Poodle'])
{
    petsChange.set(2, breed);
    petsChange.execute();
}
petsChange.close();

Database drivers and declarations

When downloading JDBC drivers:

Be sure to download the correct version of the driver for your database.
Copy or move the downloaded drivers to $MOOGSOFT_HOME/lib/cots/.

Microsoft SQL Server

JDBC driver: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774

Connection properties: http://technet.microsoft.com/en-us/library/ms378672(v=sql.110).aspx

Example declarations:

testdb: 
{
    type: 'sqlServer',
    host: '172.16.87.248',
    port: '1433',
    database: 'my_db',
    user: 'myuser',
    password: 'mypassword'
}

testdb: 
{
    jar_files: ["/usr/share/moogsoft/lib/cots/sqljdbc4.jar"],
    class_name: "com.microsoft.sqlserver.jdbc.SQLServerDriver",
    url: "jdbc:sqlserver://172.16.87.248:1433;databaseName=my_db",
    properties: { user: "myuser", password: "mypassword" }
}

MySQL

JDBC driver: Already included in Moogsoft Enterprise - no need to download.

Connection properties: http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html

Example declarations:

testdb: 
{
    type: 'mySql',
    host: '172.16.87.247',
    port: '3306',
    database: 'my_db',
    user: 'myuser',
    password: 'mypassword'
}

testdb: 
{
    jar_files: ["/usr/share/moogsoft/lib/cots/mysql-connector-java-5.1.37-bin.jar"],
    class_name: "com.mysql.jdbc.Driver",
    url: "jdbc:mysql://172.16.87.247:3306/my_db",
    properties: { user: "myuser", password: "mypassword" }
}

IBM DB2

JDBC driver: http://www-01.ibm.com/support/docview.wss?uid=swg21363866

Connection properties: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.1.0/com.ibm.db2.udb.apdv.java.doc/doc/tjvjcccn.htm?cp=SSEPGG_9.1.0%2F8-1-4-2-1-0

Example declarations:

testdb: 
{
    type: 'db2',
    host: '172.16.87.248',
    port: '50000',
    database: 'my_db',
    user: 'myuser',
    password: 'mypassword'
}

testdb: 
{
    jar_files: ["/usr/share/moogsoft/lib/cots/db2jcc4.jar"],
    class_name: "com.ibm.db2.jcc.DB2Driver",
    url: "jdbc:db2://172.16.87.248:50000/my_db",
    properties: { user: "myuser", password: "mypassword" }
}

Oracle

JDBC driver: http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

Connection properties: http://docs.oracle.com/cd/B28359_01/java.111/b31224/urls.htm

Example declarations:

testdb: 
{
    type: 'oracle',
    host: '172.16.87.248',
    port: '1521',
    database: 'my_db',
    user: 'myuser',
    password: 'mypassword'
}

testdb: 
{
    jar_files: ["/usr/share/moogsoft/lib/cots/ojdbc6.jar"],
    class_name: "oracle.jdbc.OracleDriver",
    url: "jdbc:oracle:thin:System/myuser@172.16.87.248:1521:my_db"
}

PostgreSql

JDBC driver: https://jdbc.postgresql.org/download.html

Connection properties: http://jdbc.postgresql.org/documentation/head/connect.html

Example declarations:

testdb: 
{
    type: 'postgresql',
    host: '172.16.87.248',
    port: '5432',
    database: 'my_db',
    user: 'myuser',
    password: 'mypassword'
}

testdb: 
{
    jar_files: ["/usr/share/moogsoft/lib/cots/postgresql-9.3-1102.jdbc41.jar"],
    class_name: "org.postgresql.Driver",
    url: "jdbc:postgresql://172.16.87.248:5432/my_db",
    properties: { user: "myuser", password: "mypassword" }
}

In this section:

ExternalDb

Load the module

Method reference

connect

Request arguments

Database connection properties

Response

Examples

execute

Request arguments

Response

Examples

query

Request arguments

Response

Response methods

Row methods

Example

prepare

Request arguments

Response

Response methods

Example

Database drivers and declarations

Microsoft SQL Server

MySQL

IBM DB2

Oracle

PostgreSql

Search results