Page Comparison

...

Test: eaapi-test-01.uit.tufts.edu
Prod: eaapi-prod-01.uit.tufts.edu

Server contact: Bill Sivret

User: apiadm

Path: /home/apiadm/clickFeed

...

To schedule the person feed, run the command "crontab -e" and uncomment the click_irb_person_feed line as shown below. By default, the job is scheduled to run nightly at midnight, though this can be changed in the crontab file if necessary. Output from the job will be redirected to the click_irb_person_feed.log file.

Source

The source code for the jar file is in an Eclipse project which can be found in the RAS GitLab repository: https://gitlab.it.tufts.edu/ras/click-irb-person-feed 

Click Database Schema

Person table schema:
[ClickIntegration].[Person_Staging](
       [ID] [int] IDENTITY(1,1) NOT NULL,
       [UserId] [nvarchar](255) NULL,
       [CompanyID] [nvarchar](50) NULL,
       [FirstName] [nvarchar](255) NULL,
       [LastName] [nvarchar](255) NULL,
       [MiddleName] [nvarchar](255) NULL,
       [PhoneNumber] [nvarchar](50) NULL,
       [EmailAddress] [nvarchar](255) NULL,
       [Roles] [nvarchar](4000) NULL,
       [IsActive] [bit] NULL,
       [Address1] [nvarchar](255) NULL,
       [City] [nvarchar](255) NULL,
       [State] [nvarchar](255) NULL,
       [Country] [nvarchar](255) NULL,
       [PostalCode] [nvarchar](255) NULL,
       [EmployeeID] [nvarchar](255) NULL,
CONSTRAINT [PK_Person_Staging] PRIMARY KEY CLUSTERED

Org table schema:
[ClickIntegration].[Org_Staging](
       [ID] [nvarchar](255) NOT NULL,
       [IsFundingSource] [bit] NULL,
       [Name] [nvarchar](255) NULL,
       [Parent] [nvarchar](255) NULL,
CONSTRAINT [PK_Org_Staging] PRIMARY KEY CLUSTERED

Troubleshooting

The Huron database requires connecting over a VPN tunnel, which is set up on the two eaapi servers listed above. Due to this, it's not possible to directly connect to the Click database from your local machine. This makes troubleshooting a bit trickier.

I've set up a command-line JDBC script on the eaapi servers to run simple SQL queries against the Click DB. 

Path: /home/apiadm/jdbcSql/

Usage: ./stage-clickSql.sh [query] 

Example: ./stage-clickSql.sh "select * from Person_Staging where UserId='rscaffid';"

Image Added

The stage-clickSql.sh script contains the connection information for the Stage database. To create a script for Production once the prod DB is available, simply copy the script and replace the host/db/user info at the top of the file.