DataSource Management

 

1 What is DataSource
2 DataSource Manager
3 Create New DataSource
4 DataSource Management Menu
5 About DataSource State
6 About the Default Data Source

1 What is DataSource

The DataSource contains information such as the types of databases to connect to, the server address, the username, and other relevant details. SQLMessenger utilizes this information to determine how to establish connections to the databases. Therefore, before querying data from the databases using SQLMessenger, you need to configure the DataSource in the system.

SQLMessenger supports the following types of databases:

Oracle

MySQL

SQL Server

PostgreSQL

Informix

2 DataSource Manager

You can click the "DataSource Manager" icon in the MainMenu to open the DataSource Manager.

Click the "DataSource Manager" icon in the MainMenu

DataSource Manager

In DataSource Manager, you can add new data source, modify, disable, or delete existing ones that have already been configured.

3 Create New DataSource

Click the "Create New" button in the DataSource Manager's toolbar to open the "Create New DataSource" dialog.

Click "Create New" button to open open the "Create New DataSource" dialog

DataSource Config dialog

In this dialog box:

Database Type

Select the database type for this data source.

Database Name

Set a name for the data source. If left blank, the system will automatically assign a name for the data source.

Server Address

For an Oracle database, enter the TNS string of the database here, for example, "10.42.124.11:1521:orcl".

For other databases, enter the domain name or IP address of the database server here.

Server Port

Enter the network port for the database server. If left blank, the system will connect using the default port for the selected database. The default port for MS SQL Server is 1433, for MySQL is 3306, for Oracle is 1521, and for PostgreSQL is 5432.

For Informix database, this parameter must be entered.

Database Username

Enter the username for the database server.

Database Password

Enter the password for the database server.

Default Database

Enter the default database name to be used when connecting to the database. Note that this parameter is not required for Oracle database.

JDBC Options

You can enter JDBC options here for connecting to this database. For example, when connecting to a MySQL database, you can set the timezone in the JDBC Options: "serverTimezone=UTC".

Remark

You can enter some description for the data source here.

DataSource Config dialog - External Interface

External Interface:

Allow sending emails from the interface table on this data source

If this option is selected, you can send emails through the interface table on this data source. The SQL statements will look like the following:

INSERT INTO sqlmessenger_intf
            (send_to,
             subject,
             body)
VALUES      ('test@sqlmessenger.com',--Recipient Address
             'Test mail',--Email subject
             'This is a test mail.' --Email Body
);

Allow triggering tasks from the interface table on this data source

If this option is selected, you can start tasks in SQLMessenger through the interface table on this data source. The SQL statements will look like the following:

INSERT INTO sqlmessenger_intf
            (oper_type,
             task_id)
VALUES      ( 'T',--Operation type. "T" indicates to start a task.
              123 --Task ID to start
);

Select Tasks If the "Allow triggering tasks from the interface table on this data source" is selected, you can select which tasks can be started from the interface table on this data source.
Scan the interface table every xx second(s) Set the interval in seconds for checking new requests from the interface tables. The default is to check every 60 seconds.

Using External Interface

DataSource Config dialog - Advanced Options

Advanced Options:

Auto Commit(Do not use transactions)

Set whether to immediately commit when UPDATE, INSERT, DELETE, and other statements are executed on the data source. By default, SQLMessenger enables transaction support when connecting to the data source, and modifications made by data modification statements will only take effect after explicitly committing the transaction with a COMMIT statement. After enabling this option, data modification statements will take effect immediately, and there is no need to execute a COMMIT statement.

Note: If this option is enabled, data inconsistency may occur if the task fails to execute. Please refer to the "Transaction" description in the relevant database for more details.

Use old version MySQL JDBC driver (MySQL only) By selecting this option, SQLMessenger will use the older version of the MySQL JDBC driver when connecting to MySQL databases version 5.1 and below.

Allow accessing the Address Book views

(Default Data Source only) If this option is selected, you can access the following address book-related views in SQLMessenger through the default data source:

v_contacts: contacts information such as names, email addresses etc.

v_contact_group: contact groups

v_contact_group_member: membership information for contact groups

4 DataSource Management Menu

You can click the menu button on the right side of the DataSource entry to open the DataSource management menu. You can also right-click on the DataSource entry to open the menu.

DataSource Management Menu

DataSource Management Menu:

Config DataSource Open the Data Source Properties dialog to modify the data source configuration.
Enable/Disable Enable or disable the data source. If the data source is disabled, tasks that use this data source will fail to execute.
Delete Delete the data source. If the data source is deleted, tasks that use this data source will fail to execute.
External Interface Log View the external interface logs for this data source.
Tasks Using The DataSource Query the task list that uses this data source.

5 About DataSource State

DataSource State

The DataSource is enabled.
The DataSource is disabled. Tasks that use this data source will fail to execute.
The DataSource is deleted. Tasks that use this data source will fail to execute.

6 About the Default Data Source

After the SQLMessenger installation is completed, a data source named "Default Data Source" will be created. The type of this data source is PostgreSQL. You can utilize PostgreSQL functions or SQL statements within this data source to perform various data processing tasks. The demo tasks in SQLMessenger will also utilize this data source. For specific usage instructions, please refer to the relevant PostgreSQL database documentation.

Default DataSource

Default Data Source uses the username "user01" to connect to the PostgreSQL database in SQLMessenger. You can change the password for the user in the data source configuration dialog.