Troubleshooting JDBC Data Connectors

[intapiuser]

Sisense supports a wide variety of data sources. Some of these sources require connection strings to import data from them.

This page describes how to generate logs that will help Sisense Support troubleshoot issues that may occur when using connection strings to connect to any of the following data sources:

• Bing
• Box
• DynamoDB
• Ebay
• Exact
• Facebook
• Facebook Ads
• HubSpot
• Intacct
• MailChimp
• Microsoft Active Directory
• Microsoft Dynamics CRM
• Microsoft Dynamics NAV
• PayPal
• Quickbooks Desktop
• QuickBooks Online
• ServiceNow
• Sharepoint
• Stripe
• Twitter
• Xero
• Youtube
• Youtube Analytics

The connection string properties describe the various options that can be used when establishing a connection to a data source.

Connection strings have the following structure:

jdbc:DataSourceName:Property1=Value1;Property2=Value2;

The following is an example of an Exact connection string:

jdbc:exactonline:OAuthClientId=xxxxxxxxxxxxx;OAuthClientSecret=xxxxxxxxxxxxx;Region=’United States’;Division=xxxx;InitiateOAuth=GETANDREFRESH;OAuthCallbackURL=localhost:12345;

The following are mandatory properties each string must have:

• OAuthClientId: The client ID assigned when you register your application with an OAuth authorization server.
• OAuthClientSecret: Set this to the App Secret in your app settings.
• CallbackURL: The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. Set this to http://localhost.
• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.
• Timeout: The value in seconds until the timeout error is thrown, cancelling the operation. Set to 0 to disable time outs.

In addition, Sisense recommends you include the following properties:

• OAuthSettingsLocation: The location of the settings file where OAuth values are saved for each user when InitiateOAuth is set to GETANDREFRESH.
• Logfile: Creates a log file. The value of this parameter is the location on your drive where the log file is to be created. To determine how much detail is included in the log file, see the parameter Verbosity below.
• Verbosity: The verbosity level that determines the amount of detail included in the log file. Set the verbosity from 1 – 5.
• 1 will log the query, the number of rows returned by it, the start of execution and the time taken, and any errors.
• 2 will log everything included in Verbosity 1, cache queries, and HTTP headers.
• 3 will additionally log the body of the HTTP requests.
• 4 will additionally log transport-level communication with the data source. This includes SSL negotiation.
• 5 will additionally log communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands.

An example string would look something like this:

jdbc:facebook:

OAuthSettingsLocation=D:\facebook\auth\user.txt;

OAuthClientId=xxxxxxxxxxxxxxx;

OAuthClientSecret=xxxxxxxxxxxxxxxxxxxx;

InitiateOAuth=GETANDREFRESH;

Version=2.8;

CallbackURL=http://localhost/;

Timeout=0;

Logfile=C:\data_connectors_logs\hubspot_logs.log;

Verbosity=3;

jdbc:facebook: OAuthSettingsLocation=D:\facebook\auth\user.txt; OAuthClientId=xxxxxxxxxxxxxxx; OAuthClientSecret=xxxxxxxxxxxxxxxxxxxx; InitiateOAuth=GETANDREFRESH; Version=2.8; CallbackURL=http://localhost/; Timeout=0; Logfile=C:\data_connectors_logs\hubspot_logs.log; Verbosity=4;

Troubleshooting Connections

As each data source is unique, a wide range of issues may occur. To help Sisense Support troubleshoot any connection issue you have, the Verbosity property should be set to 3. This will help Sisense determine where the connection issue is occuring.

After trying to connect a log file will be generated. The location of this file is the value of the property LogFile in the connection string you define.

After the file is generated, include this file in your correspondence to Sisense Support.