TIBCO Scribe® Online Connector For Oracle MySQL

TheTIBCO Scribe® Online Connector for Oracle MySQL allows you to easily integrate your MySQL databases with your CRM, ERP, or marketing automation applications. The connector discovers tables, views, and fields and understands relationships between tables.

Connector Specifications

  Supported

Agent Types

On Premise X
Cloud X

Replication Services

Source  
Target X

Integration Services

Source X
Target X

Migration Services

Source X
Target X

Maps

Integration X
Request-Reply X
Message  

Note: This Connector is available from the TIBCO Scribe® Online Marketplace. See Marketplace TIBCO Scribe® Certified Connectors for more information.

Setup Considerations

The Oracle MySQL Connector for TIBCO Scribe® Online is supported with the following:

SSL Certificates

Oracle MySQL uses the PEM format for certificates and private keys, but .NET does not support this format natively. To work around the issue, convert certificates to PFX also known as PKCS#12 format using openssl. The complete instructions can be found in the Oracle MySQL official documentation, Tutorial: Configuring SSL with Connector/Net and the Connecting To MySQL Using SSL Knowledge Base article in the TIBCO Community.

After conversion copy the certificate to TIBCO Scribe® Agent manually.

Selecting An Agent Type For Oracle MySQL

Refer to TIBCO Scribe® Online Agents for information on available Agent types and how to select the best Agent for your Solution.

Connecting To Oracle MySQL

Note: Best practice is to create Connections with credentials that limit permissions in the target system, following the principle of least privilege. Using Administrator level credentials in a Connection provides Administrator level access to the target system for TIBCO Scribe® Online users. Depending on the entities supported, a TIBCO Scribe® Online user could alter user accounts in the target system.

  1. Select More > Connections from the menu.
  2. From the Connections page select Add to open the Add a New Connection dialog.
  3. Select the Connector from the drop-down list in the Connection Type field, and then enter the following information for this Connection:
    • Name — This can be any meaningful name, up to 25 characters.
    • Alias — An alias for this Connection name. The alias is generated from the Connection name, and can be up to 25 characters. The Connection alias can include letters, numbers, and underscores. Spaces and special characters are not accepted. You can change the alias. For more information, see Connection Alias.
    • Hostname — Name of the Oracle MySQL host to which you want to connect. This can be either a machine name or an IP address. By default, TIBCO Scribe® Online uses port 3306 to connect to the host. You can specify a different port by appending :<port_number> to the host name.

      For example, to use port 1234 to connect to host, myhost.com, enter:

      myhost.com:1234

    • Database Schema — Database to which you want to connect.
    • Username — User name for this Oracle MySQL database instance.
    • Password — Password for this Username.
    • Additional Parameters — Optional field where you can specify one or more parameters. If left blank, default MySQL parameter values are used. See the Connector/Net Connection-String Options Reference section of the Oracle MySQL documentation for a list of parameters that can be used and their default values.

      Syntax for the Additional Parameters field is as follows:

      • Parameters are case insensitive.
      • If misspelled or invalid, they are ignored.
      • Separate each parameter with a semicolon (;).
      • Parameters that are handled by other fields in the Connection dialog are ignored if used in the Additional Parameters field, including: 
        • User Id, UserID, Username, Uid, User name, User
        • Password, pwd
        • Host, Server, Data Source, DataSource, Address, Addr,
        • Network Address
        • Initial Catalog, Database
      • Syntax examples:

        AllowZeroDateTime=false;ConvertZeroDateTime=true;

        TreatTinyAsBoolean = true;

  4. Select Test to ensure that the Agent can connect to your database. Be sure to test the Connection against all Agents that use this Connection. See Testing Connections.
  5. Select OK to save the Connection.

Note: The Username of the TIBCO Scribe® Online MySQL Connection must have access to the MySQL host and to the computer where the Agent is installed.

Metadata Notes

Naming

Connection metadata must have unique entity, relationship, and field names. If your Connection metadata has duplicate names, review the source system to determine if the duplicates can be renamed.

Oracle MySQL only supports lower case table names.

Oracle MySQL Connector As RS Target

Net Change

The first time a Replication Solution executes it creates the tables and adds all the records for the selected or recommended entities in the target Oracle MySQL datastore. Subsequent replications attempt to process only new and updated records. To filter for only new and updated records, TIBCO Scribe® does the following:

Note: In cases where there are entities that do not have an associated date field, all records for those entities are processed each time a Replication Solution runs.

Deleted Records In Oracle MySQL RS Solutions

Records deleted in the source datastore are not deleted in the corresponding table in the target Oracle MySQL replication database. Instead, each record has an associated value in the SCRIBE_DELETEDON field for the replicated table. The SCRIBE_DELETEDON field contains a datetime stamp that corresponds to the date and time the record was deleted from the source datastore.

Note: Occasionally, some deleted records are reprocessed because the source modified date field controls the target field and date used to filter for new, changed, and deleted records from the source datastore. See Net Change. If the most recent modified date is older than the most recent SCRIBE_DELETEDON date, the deleted records between the two dates are reprocessed.

Entities Without Modification Dates

Typically, entities without modification date fields are not included in the Recommended Entities list, but can be chosen using the Selected Entities option on the Replication Solution edit page. If a selected source entity does not have a date for tracking deleted records, when the Replication Solution executes, TIBCO Scribe® Online does the following:

Duplicate Records

Important: Modifying MySQL database table Index Properties can cause duplicate records during subsequent executions of an Replication Solution when Oracle MySQL is the target for a Replication Solution. See Duplicate Records When Using SQL As Replication Service Target for additional information.

Miscellaneous

Oracle MySQL Connector As IS Target

Batch Processing

Miscellaneous

Changing Access And Privileges In Oracle MySQL

As part of troubleshooting a Oracle MySQL Connection, you may need to change access in privileges in Oracle MySQL.

  1. From Oracle MySQL Workbench, open the Users & Privileges Administration page.
  2. Open the Server Access Management tab.
  3. From the Server Access Management tab, associate the Login username with the hostname of the computer where the TIBCO Scribe® Online Agent is installed. The hostname can be either the machine name or IP address of the machine where the Agent is installed. For example:
  4. MySQL Connector: Setting privileges

Failed Connection Test

If the Username for this Connection does not have appropriate privileges, the Connection Test fails with the following message.

Host <hostname> is not allowed to connect to MySQL Server

Set privileges from the Server Access Management tab of the MySQL Workbench Users and Privileges page. For more information, see your MySQL Database Administrator.

TIBCO Scribe® Online API Considerations

To create Connections with the TIBCO Scribe® Online API, the Oracle MySQL Connector requires the following information:

Connector Name

Oracle MySQL

Connector ID

905F0025-7DB2-4EA3-B22B-C88B099680EB

TIBCO Scribe® Online Connection Properties

In addition, this Connector uses the Connection properties shown in the following table.

Note: Connection property names are case-sensitive.

Name Data Type Required Secured

Server

String

Yes

No

Database

String

Yes

No

UserName

String

Yes

No

Password

String

Yes

Yes

ExtendedConnectionProperties

String

No

No

License Agreement

The TIBCO Scribe® Online End User License Agreement for the Oracle MySQL Connector describes TIBCO and your legal obligations and requirements. TIBCO suggests that you read the End User License Agreement.

More Information

For additional information on this Connector, refer to the Knowledge Base and Discussions in the TIBCO Community.