Ms SQL

Description

Create an API to allow MS SQL to be accessible via REST.

UI

Access the DataTrucker URL via a browser

Create a credentials

1. Go to Credentials in the sidebar > MS SQL Credentials
2. Scroll down to Credential Management
3. Fill the form to create the connection
   1. Credential name – an arbitrary value to identify the credential object
   2. The hostname of the MS SQL. Note, make sure the API server(not the UI) has access into MS SQL
   3. Username of MS SQL,
      1. Limit access to this user to the specific API. Please reach out to a DBA to provide specific grant access to this user
   4. The password of the MS SQL User
   5. Database Name
   6. Port of the Database
   7. Min /Max Pool for connection pooling.

Make an API

1. Go to Database API in the Sidebar > MS SQL API’s
2. Fill the form to create the API
   1. Resource Name: an arbitrary name to identify the resource
   2. The Credential linked to the resources
   3. Method: The type of REST Call
   4. Timeout: To ensure query times out after a set period in time
   5. Script: The SQL Query
      1. The SQL query can have parameters identified by ‘[[]]’ Example ‘[[id]]’ [ single quotes recommended instead of double quotes ]”
      2. Parameters are case sensitive in some cases
   6. Validation Regex of Input values, input sanitization before querying the database
      1. Examples provided in the UI when you try to create a new API

Query the resource you created

URL: /api/v1/jobs/<resource name>TYPE: <method defined>HEADER: Authorization: "Bearer <JWT Token>"BODY (JSON): {        "<input_key>": "<value>",        "<input_key>": "<value>",        "<input_key>": "<value>",        "<input_key>": "<value>"}
Response: 200 OK{   jsondata....}

As a CRD in Openshift / Kubernetes

For credentials use the the API below to the management end point

---apiVersion: datatrucker.datatrucker.io/v1kind: DatatruckerFlowmetadata:  name: datatruckerflow-samplespec:  Resources:    requests:      memory: "256Mi"      cpu: "250m"    limits:      memory: "256Mi"      cpu: "500m"  JobDefinitions:  - resourcename: mssql1    name: mssql1    type: DB-Mssql    credentialname: mssql1    tenant: Admin    restmethod: POST    script: SELECT GETDATE() as 'current_date' ,'[[dbname]]' AS replacevalue,  DB_NAME(dbid)      as DBName, COUNT(dbid) as NumberOfConnections,loginame as LoginName FROM  sys.sysprocesses      WHERE  dbid > 0 GROUP BY  dbid, loginame;    job_timeout: 4354    validations:      type: object      properties:        dbname:          type: string          pattern: "^[a-z0-9]*$"          maxLength: 8  Keys:    configmap: placeholder  Scripts:    configmap: placeholder  Type: Job  DatatruckerConfig: datatruckerconfig-sample  Replicas: 1  API:    name: api    Image:      repository: docker.io      imageName: datatruckerio/datatrucker-api      tagName: latest  

API

Create a credential via REST

URL: /api/v1/credentials/TYPE: POSTHEADER: Authorization: "Bearer <JWT Token>"BODY (JSON): {        "credentialname": "mssql1",        "type": "DB-Mssql",        "hostname": "localhost",        "username": "sa",        "password": "SpikePrototype2016!",        "database": "master",        "port": 152,        "minpool": 100,        "maxpool": 200}
Response: 201 OK

Create the API via REST

URL: /api/v1/resourcesTYPE: POSTHEADER: Authorization: "Bearer <JWT Token>"BODY (JSON): {        "resourcename": "mssql1",        "type": "DB-Mssql",        "credentialname": "mssql1",        "restmethod": "POST",        "script": "SELECT GETDATE() as 'current_date' ,'[[dbname]]' AS replacevalue,  DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections,loginame as LoginName FROM  sys.sysprocesses WHERE  dbid > 0 GROUP BY  dbid, loginame;",        "job_timeout": 4354,        "validations": {          "type": "object",          "properties": {            "dbname": {              "type": "string",              "pattern": "^[a-z0-9]*$",              "maxLength": 8            }          }        }      }
Response: 201 OK