TIBCO Scribe® Online Connector For MongoDB

The TIBCO Scribe® Online Connector for MongoDB is based on the CData driver for MongoDB. Use this Connector to integrate with CRM, accounting, eCommerce, and marketing systems. If you use the Connector to store data for an application you have developed, you can integrate that application using TIBCO Cloud™ Integration - Connect without building a custom Connector. This Connector is based on Scribe.Connector.AdoNet library and CData MongoDB ADO.NET provider.

Possible use cases for the MongoDB Connector include:

Integrate with any application across your business that uses MongoDB as a back end
Model MongoDB instances as relational and hierarchal databases
Use native MongoDB queries and javascript to work with the data

Connector Specifications

	Supported
Agent Types
Connect on-premise	X
Connect cloud	X
Data Replication Apps
Source
Target
On Schedule Apps
Source	X
Target	X
On Event Apps
Source	X
Target	X
Flows
Integration	X
Request-Reply	X
Message

Note: This Connector is available from the TIBCO Cloud™ Integration Marketplace. See Marketplace Connectors for more information.

Supported Entities

The MongoDB collections are exposed as entities.

Setup Considerations

The MongoDB Connector supports up to MongoDB version 5.

Advanced scenarios such as customizing the SSL configuration or connecting through a firewall or a proxy, fine-tuning of discovering metadata for nested JSON objects, or property types are described in the Advanced Settings section of the CData documentation and in the Metadata Notes section of this document.

Selecting An Agent Type For MongoDB

Refer to TIBCO Cloud™ Integration - Connect Agents for information on available agent types and how to select the best agent for your app.

Connecting To MongoDB

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 Cloud™ Integration - Connect users. Depending on the entities supported, a TIBCO Cloud™ Integration - Connect user could alter user accounts in the target system.

Select Connections from the menu.
From the Connections page select Create to open the Create a connection dialog.
Select the Connector from the list to open the Connection dialog, 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.
- Server — Enter the IP address or DNS name of the MongoDB server.
- Port — Optional. Enter the port number for the MongoDB server. Must be in the 1023 to 65535 range.
- Database — Name of the MongoDB database you are accessing.
- User — Name of the database user with access to connect to MongoDB.
- Password — Password for the database user.
- Additional Parameters — Optional field where you can specify one or more connection string parameters. See the Connection String Options section of the CData documentation for a list of parameters that can be used and their default values. Note that in some cases the CData MongoDB ADO.NET Provider does not fully support all of the possible parameters.
  Syntax for the Additional Parameters field is as follows:
  - All blank characters, except those within a value or within quotation marks, are ignored
  - Preceding and trailing spaces are ignored unless enclosed in single or double quotes, such as Keyword=" value"
  - Semicolons (;) within a value must be delimited by quotation marks
  - Use a single quote (') if the value begins with a double quote (")
  - Use a double quote (") if the value begins with a single quote (')
  - Parameters are case-insensitive
  - If a KEYWORD=VALUE pair occurs more than once in the connection string, the value associated with the last occurrence is used
  - If a keyword contains an equal sign (=), it must be preceded by an additional equal sign to indicate that the equal sign is part of the keyword
  - Parameters that are handled by other fields or default settings in the Connection dialog are ignored if used in the Additional Parameters field, including:
    - Server
    - Logfile — To enable logging enter a value for the verbosity parameter in the Additional Parameters field. The default log file size is a maximum of 10MB. When the log file reaches 10MB a new log file is started, up to a maximum of five files. Once there are five files, the oldest file is deleted as needed. Any CData log files generated by this setting are stored in the default Connect on-premise Agent Logs directory, C:\Program Files (x86)\Scribe Software\TIBCO Scribe® Online Agent\logs\.
      Note: For information on setting log file verbosity, see Verbosity in the CData Help.
    - MaxLogFileCount — This parameter is set by the Connector to a maximum of five files.
    - MaxLogFileSize — This parameter is set by the Connector to a maximum of 10MB.
    - Other
    - RTK
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.
Select OK/Save to save the Connection.

Connecting To MongoDB Atlas

MongoDB Atlas is a cloud hosted MongoDB. It consists of a cluster of three servers. To make a connection use the primary server as ReplicaSet in the Server field of the connection and specify the AuthDatabase and UseSSL in the additional parameters as well as a few other settings.

ReplicaSet=server-1.mongodb.com; AuthDatabase=admin; UseSSL=true;

Metadata Notes

MongoDB is a schemaless document database. The Connector metadata provider generates the schema based on the data in each MongoDB database Collection.

Retrieving Metadata

The data type for each property is obtained by scanning a number of documents in the collection with matching property names and analyzing the value.
- Set the scanning depth using the RowScanDepth connection string property
- Customize the default type detection method by setting the TypeDetectionScheme connection string property
Properties for each entity are obtained as follows:
- Obtain all the properties from all documents in the collection
- Get the name of each property
- If the name is not present in the Entity Properties collection it is added there
- The property type is set to the most common one

MongoDB Naming Restrictions

As noted in the MongoDB documentation there are some restrictions for entity names.

Properties that have "." in them are ignored in metadata.

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.

Collection Names

Collection names should begin with an underscore or a letter, and cannot:
- Contain the $
- Be an empty string, such as ""
- Contain a null character
- Begin with the prefix system.
Collections with the same name in different cases are not supported. Metadata is provided for only one collection among those that have the same name with the only difference being the case of letters in their names.

Field Names

Field names cannot contain null characters.
Although valid, avoid using \, . or $ in field names since querying on such fields is not supported.

Arrays And Nested Objects

MongoDB documents containing arrays and nested objects are supported for source and target side operations. Arrays show as one field and are returned in JSON format. The objects show as a field and data is represented as JSON for both query and target. It is best to use the Mapper connector to handle the JSON data.

Binary Data

Binary fields are not supported.

Generate And Modify Schema Files

You can generate, and then change the schema files to customize metadata by setting the GenerateSchemaFiles connection string property value to:

Never — A schema file is never generated. Default value.
OnUse — A schema file is generated the first time a table is referenced, provided the schema file for the table does not already exist.
OnStart — A schema file is generated at connection time for any tables that do not currently have a schema file.

The location of schema files can be set using the Location connection string option.

Note: This feature is available only for Connect on-premise agents.

Relationships

Relationships and hierarchical data are not supported.

MongoDB Connector As An App Source

Consider the following when using the MongoDB Connector as an app source.

Filtering

When a datetime is configured on the Query block on the Block Properties Net Change Tab to query for new and updated records, that configuration is treated as an additional filter. The Net Change datetime filter is applied as an AND after any other filters specified on the Block Properties Filter Tab. TIBCO Cloud™ Integration - Connect builds a query combining both the Net Change filter and the filters on the Filter tab. See Net Change And Filters for an example.

Some Connectors for TIBCO Cloud™ Integration - Connect only support one filter. For those Connectors you can use either Net Change or one filter on the Filter tab, not both.

Note: The Net Change date is ignored when previewing data on the Preview tab. Filters on the Block Properties Filters tab are used to filter the data on the Preview tab.

Net Change is supported for MongoDB columns with a date and timestamp.

For additional filtering information refer to the CData MongoDB ADO.NET documentation.

Native Query

The MongoDB Connector supports SQL queries in Native Query Blocks to create customized queries for MongoDB. The query can be as simple or complex as you need it to be; however, it should return a single result set. The native query text is sent to MongoDB exactly as it is entered without any modifications.

You can use SELECT , UPDATE , INSERT and DELETE clauses. If support for Enhanced SQL is enabled, you can use Joins, Aggregate functions, and Projection functions. For additional details, see the SQL Compliance and Support Enhanced SQL sections of the CData documentation .

After entering the SQL query, you must select Test to validate the query. Invalid queries are not accepted by the Connector. See Native Query Block and Creating Native Queries For Microsoft SQL Server for additional information.

When testing a Native Query in a flow, if the source datastore does not return any data, TIBCO Cloud™ Integration - Connect cannot build the schema for the underlying metadata and the flow cannot be saved. To allow TIBCO Cloud™ Integration - Connect to build the schema, do the following:

Create a single temporary record in the source datastore that matches the Native Query.
Test the Native Query and ensure that it is successful.
Save the flow.
Remove the temporary record from the source datastore.

JSON Functions

You can execute special functions in SQL queries directly in Native Query Blocks to retrieve various JSON structures from the document. For the full JSON Functions list and examples see the JSON Functions section of the CData documentation.

Using Native MongoDB Queries

The MongoDB Connector supports the db.collection.find() function when the Query Passthrough parameter is set to true in the Additional Parameters field on the Connection dialog.

Example

To query the Customers collection using native MongoDB syntax in the Native Query Block and return the following information:

{
 "_id":"5ac362f5ac83422dd0d25132",
 "name":"John Smith",
 "emails":[
   "jsmith@microsoft.com",
   "jsmith@gmail.com"
 ],
 "address":{
   "country":"USA",
   "state":"Texas",
   "city":"Dallas"
 }
}

Use this syntax in the Native Query Block:

db.Cusotmers.find (
	{name: 'John Smith'}
)

On the Query Block Preview tab, the fields and data for John Smith's record display.

Executing JavaScript Functions Using Stored Procedures

The Native Query Block can be used to call javascript functions with the EVAL stored procedure.

Example

EXECUTE EVAL jsFunction = 'function() {
	var i = 1;
	var j = 2;
	var k = 3;
	var result - (i+j) * k;
	return result;}'

On the Query Block Preview tab, the returned result displays in the retval field.

MongoDB Syntax

The MongoDB Connector supports MongoDB syntax.

MongoDB Connector As An App Target

Consider the following when using the MongoDB Connector as an app target.

Supports the following operations:

Create
Update
Delete
Execute — All stored procedures documented in the CData MongoDB ADO.NET provider under Stored Procedures. See Execute Block for additional information.

Batch Processing

Batch processing is not supported.

TIBCO Cloud™ Integration - Connect API Considerations

To create connections with the TIBCO API, the MongoDB Connector requires the following information:

Connector Name	MongoDB
Connector ID	7BCF0FCF-4825-48E6-8163-F157D67D0161

TIBCO Cloud™ Integration - Connect 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
Port	string	No	No
Database	string	Yes	No
User	string	No	No
Password	string	No	Yes
ConnectionString	string	No	No

License Agreement

The TIBCO End User License Agreement for the MongoDB 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.