TIBCO Scribe® Online Connector For PostgreSQL

The TIBCO Scribe® Online Connector for PostgreSQL allows you to easily integrate your PostgreSQL databases with other business systems such as ERP, CRM, and Marketing Automation. This Connector discovers tables, views, and columns. In addition, you can use native SQL queries to access, filter, and join your data.

Use the PostgreSQL Connector as a source or target Connection for for On schedule or On event apps. This Connector is based on the Scribe.Connector.AdoNet library and CData PostgreSQL ADO.NET provider.

Use the TIBCO Scribe® Connector for PostgreSQL to:

• Integrate with any application across your business that uses PostgreSQL as a back end
• Move legacy PostgreSQL data to other systems

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

Supported Entities

• Tables and Views from the PostgreSQL database are exposed as entities.
• Views support only Query.
• Updateable Views support Query, Create, Update, and Delete.

Advanced considerations, such as customizing the SSL configuration, or connecting through a firewall or a proxy, are described in the Advanced Settings section of the CData documentation.

Setup Considerations

• Supports PostgreSQL versions 7.4 and higher.

• Supports PostgreSQL hosted on Amazon Aurora.

• Regional settings for the Windows user account used by the Connect on-premise agent should be set to English (United States) to prevent Format Exception errors. The default account used by the Connect on-premise agent is Local System. For more information on modifying regional settings and or changing the account used by your Connect on-premise agent, see the PostgreSQL Connector Input string was not in correct format Knowledge Base article in the TIBCO Community.

Selecting An Agent Type For PostgreSQL

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 PostgreSQL

1. Select Connections from the menu.
2. From the Connections page select Create to open the Create a connection dialog.
3. 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 — IP address or URL of the target PostgreSQL server instance. Requires a valid IPv4 IP or Url address.
   • Port — PostgreSQL server port number, which must be in the 1023 to 65535 range. Default value is 5432.
   • Database — Name of the database to which you want to connect.
   • Username — User name for this PostgreSQL database instance.
   • Password — Password for this Username. PostgreSQL databases do not require a password.
   • 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 PostgreSQL ADO.NET provider does not fully support all of the possible parameters.
     

     Syntax for the Additional Parameters field is as follows:

     • Schema name is required for tables and views in the format schemaname.tablename or schemaname.viewname. For example: Tables=scribe.mhs_test;Views=scribe.mhs_list;Timeout=300;
     • 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
       • Port
       • User
       • Password
       • 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
       • UseConnectionPooling
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/Save to save the Connection.

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.

Entities

PostgreSQL Entity names shown in TIBCO Cloud™ Integration - Connect display both the schema name and the entity name in the following format:

schemaname.entityname

myschema.contact

Arrays

All arrays are considered strings, regardless of the data type of the values within the array.

When mapping or filtering on array fields, the value of the array must be enclosed in curly braces ({}), the values must be separated by commas, and the entire array must be enclosed in quotes because arrays are considered strings for the PostgreSQL Connector.

Examples:

"{Text4, Text5, Text6}"

"{1, 100, 1000}"

Data Types

PostgreSQL data types are converted as follows: 

PostgreSQL Data Type	TIBCO Cloud™ Integration - Connect Data Type
bigint	Int64
boolean	Boolean
bytea	Byte[] (Not supported)
date	DateTime
double precision	Double
integer	Int32
jsonb	Byte[] (Not supported)
money	Decimal
numeric	Decimal
real	Single
smallint	Int16
time	TimeSpan (Not Supported)
timestamp	DateTime
All other types	String All character, enumerated, geometric, network address, bit string, text search, UUID, XML, JSON, array, composite, range types are exposed as strings

PostgreSQL Connector As An App Source

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

• Hierarchical data is not supported.
• Relationships are not supported.

Filtering

Net Change

Using Net Change to query for new and updated records is supported for date and timestamp fields. For Filters all logical and comparison operators are supported.

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.

Native Query

The PostgreSQL Connector supports SQL queries in Native Query Blocks to create customized queries for PostgreSQL. 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 PostgreSQL exactly as it is entered without any modifications.

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 using SQL queries, you must reference both the schema name and the table name, as shown in the following examples:

Select * from schemaname.tablename

Select * from bfschema1.table1

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:

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

PostgreSQL Connector As An App Target

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

• Batch processing is not supported.
• Delete and Update operations generate record errors if more than one record matches the Matching Criteria.

TIBCO Cloud™ Integration - Connect API Considerations

To create connections with the TIBCO Cloud™ Integration - Connect API, the PostgreSQL Connector requires the following information:

Connector Name	PostgreSQL
Connector ID	463ACB70-4730-478A-91CE-DE5CB80C6FD4

TIBCO Cloud™ Integration - Connect Connection Properties

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

Name	Data Type	Required	Secured	Usage
Server	string	Yes	No
Port	string	No	No	Integer
Database	string	Yes	No
User	string	Yes	No
Password	string	No	Yes
ConnectionString	string	No	No

License Agreement

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