Step 1 - Set up Ocelot/Slate Connector: Installing "Get Queries"

Step 1 - Set up Ocelot/Slate Connector: Installing "Get Queries"

In order to use the Ocelot/Slate connector, there are several setup steps necessary. This article provides instructions on how to install Get Queries into Slate and capture the Web Services URL. 

This article is broken down into two sections:

• Installing Get Queries in Slate
• Capture Web Service URL

You will need the IP addresses to set up the OcelotAPI User in step 9. Submit a support ticket requesting the IP addresses that need to be whitelisted in the Slate connector.

Installing Get Queries in Slate

Install the Get Queries Saved Query inside of Slate so that it can be utilized in Ocelot's connector. The "Get Queries" Saved Query is an important part of the automatic registration of Saved Queries into Ocelot.

1. On the Navigation Bar, under the Database menu, select Database.

   

2. On the Queries page, select New Query.
3. Enter the following information:
   1. Name: Get Queries
   2. User: OcelotAPI
   3. Sharing: Select the checkbox.
   4. Folder: Ocelot Integration
   5. Type: Local
   6. Base: Custom SQL
      
4. Select Save.
   
   

5. In the Custom SQL field, enter the following SQL code:

   declare @CurrentTimeZone varchar(50)
   
    
   
   /* Until the current_timezone_id() function becomes available, we are reduced to
   
   using this conversion logic.
   
   See https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/timezones-overview
   
   for the conversion table if your timezome is not included in the logic.
   
   */
   
   declare @current_timezone nvarchar(50) = current_timezone();
   
    
   
   select  @CurrentTimeZone =
   
     case @current_timezone
   
       when '(UTC-04:00) Atlantic Time (Canada)' then 'Atlantic Standard Time'
   
       when '(UTC-04:00) Georgetown, La Paz, Manaus, San Juan' then 'SA Western Standard Time'
   
         when '(UTC-05:00) Eastern Time (US & Canada)' then 'Eastern Standard Time'
   
       when '(UTC-05:00) Indiana (East)' then 'US Eastern Standard Time'
   
         when '(UTC-06:00) Central Time (US & Canada)' then 'Central Standard Time'
   
         when '(UTC-07:00) Mountain Time (US & Canada)' then 'Mountain Standard Time'
   
       when '(UTC-07:00) Arizona' then 'US Mountain Standard Time'
   
       when '(UTC-08:00) Pacific Time (US & Canada)' then 'Pacific Standard Time'
   
       when '(UTC-09:00) Alaska' then 'Alaskan Standard Time'
   
       when '(UTC-10:00) Hawaii' then 'Hawaiian Standard Time'
   
       when '(UTC+10:00) Guam, Port Moresby' then 'West Pacific Standard Time'
   
       when '(UTC+13:00) Samoa' then 'Samoa Standard Time'
   
       else 'UTC'
   
       end
   
    
   
   select
   
       Q.[name] as [Query Name],
   
       dbo.toGuidString(Q.[id]) as [Query Guid],
   
       Q.folder as [Folder],
   
       L.name as [Query Base],
   
       A1.[grantee_token] as [Token],
   
       A1.[name] as [Token Name],
   
       A1.[permissions] as [Token Permissions],
   
       /* Return [update] in UTC to assist with disambiguating same named queries in 'All Queries' page.
   
       The first 'at time zone' adds a timezone to the datetime, the second 'at time zone'
   
       shifts the timezone.
   
       */
   
       convert(datetime, (Q.[updated] at time zone (@CurrentTimeZone)) at time zone 'UTC') as [Query Updated],
   
       /* Return [sql] to detect non-OOB tweaking to the GetQuery query.. */
   
       Q.[sql] as [Query Sql]
   
   from [query] Q
   
   inner join [access] A1
   
   on A1.[entity] = Q.[id]
   
   left join [lookup.base] L
   
   on Q.[base] = L.[id]
   
   where
   
   (
   
       /* Disallow archived queries even if their permissions
   
       have been reanimated */
   
       (Q.[archived] != 1)
   
       and
   
       /* Exclude quick queries */
   
       (Q.[folder] != 'Temp')
   
       and
   
       (
   
       /* Web Service Type must be 'json' */
   
       (isnull(Q.[config].value('(p[k = "share_type"]/v)[1]', 'varchar(max)'), 0) IN ('json'))
   
       )
   
       /* Grant must be active */
   
       and A1.[active] = 1
   
       /* Grantee name must be 'OcelotAPI' */
   
       and lower(A1.[name]) = 'ocelotapi'
   
       /* Grantee type must be 'token' */
   
       and A1.[grantee_type] = 'token'
   
       /* Permission 'service' must be selected */
   
       and A1.[permissions].value('t[1]', 'varchar(max)') in ('service')
   
   )
   
   orderby[QueryName],[Token]

   

6. Select Save.

7. Select the Edit Permissions button on the upper right corner of the page.
   

8. On the Edit Permissions dialog page, select the Add Grantee link.
   

9.   On the Edit Grantee dialog page, set the Type to User Token.
   
10. On the Edit Grantee dialog page, the Name, Token, and Permissions fields will appear.
    1. In the Name field, enter OcelotAPI.
    2. Submit a support ticket to request the IP addresses to enter into the Allowed Networks field.
    3. In the Permissions field, select the Web Service box.
    4. Select Save
       
11. On the Edit Permissions dialog page, the Ocelot Grantee will now be listed. Select Save.
    
    

Continue on to Step 2 - Set up Ocelot/Slate Connector: Installing Contact Lookup Query to set up the Contact Lookup Query.