Learn how to compare Oracle database schemas using Toad DevOps Toolkit and the new REST API. 

The latest release of Toad® DevOps Toolkit 1.3, out this November, includes a new optional REST API. This feature can be enabled during installation or started later as a Windows service. The REST API provides a modern, language agnostic approach to interacting with Toad DevOps Toolkit. When running the REST API service it also allows for external access to remote computers. No longer are you limited to running scripts on the same server that Toad DevOps Toolkit is installed on.

Requests and responses to the REST API are made in JSON (https://www.json.org/). These requests will direct the server on which actions we’d like to perform which can be anything from executing a script to comparing database tables.

Let's get started and dive right into comparing Oracle database schemas using Toad DevOps Toolkit and the new REST API. Online documentation for the REST API is located in the install directory and online at https://qsft.github.io/tdt-rest/.

Getting started

The server has the concept of jobs. Examples of jobs are running code analysis, comparing database schemas, or executing a script. These jobs may take a considerable amount of time to run and in-order to not block additional requests they run asynchronously in a queue. Once a job is submitted its status may be pulled to determine if it is pending, running, or completed. Once a job has completed we gather the results via an additional request. Let's walk through the steps using PowerShell.

Create the compare database schemas request

The first step is to create a PowerShell request object. This request object is then converted to JSON and sent to the server as the body of the request. In our request we'll compare two schemas, all of their object types, and output the sync script upon completion. Here's an example of what this may look like.

$Body = @{
   Source = @{
     Connection = 'source_user/source_password@source_database'
     Schema = 'source_schema'
   }
   Target = @{
     Connection = 'target_user/target_password@target_database'
     Schema = 'target_schema'
   }
   Compare = @{
     ObjectTypes = @{
       All = $True
     }
   }
   Output = @{
     SyncScript = @{
       FilePath = 'C:\Examples\SyncScript.sql'
     }
   }
  }

Once the PowerShell object has been created it is then converted to JSON using the ConvertTo-Json function.

  $Json = $Body | ConvertTo-Json

Comparing schemas is done by making a single POST request to the …/schemas/compare endpoint. We set the content type header to application/json which informs the server that the request body is JSON.

Using PowerShell our request to the server looks like this.

  $Response = Invoke-WebRequest -Uri 'https://localhost:5000/api/schemas/compare' -Method Post -Body $Json -ContentType 'application/json'

Notice that we're also getting a JSON response object. This object contains information related to the job we just scheduled.

Doing the reverse of what we did previously with the request body we'll take the JSON response and convert it to a PowerShell object using the ConvertFrom-Json function. This allows us to easily reference the different properties in our script.

  $CompareSchemasJob = $Response.Content | ConvertFrom-Json

Poll the compare schemas job

Since the job runs asynchronously in a queue we need a way to get the status of the compare schemas job that we just submitted. This is where we'll be using the job object. The job object contains a unique identifier that we can send to the server and get a response for. The server may not have finished comparing the database schemas by the time our status request is made and that's okay. We'll keep pulling the server every few seconds (or minutes) until it has completed, and error occurred, or we've reached some maximum number of attempts. Here's what the code looks like to do that.

# Poll the job until the compare schemas job has an expected status of 'Finished' or the maximum number of retries has been hit.
for ($i = 0; $i -lt 10; $i++) {
    'Polling the compare schemas status {0}...'-f ($i + 1)

   # Wait a number of seconds between each polling of the status.
   Start-Sleep -S 5

   # Get the job's latest status.
   $Response = Invoke-WebRequest -Uri ('https://localhost:5000/api/jobs/{0}' -f $CompareSchemasJob.Id) -Method Get -ContentType 'application/json'
    $CompareSchemasJob = $Response.Content | ConvertFrom-Json

   if ($CompareSchemasJob.Status -eq 'Finished') { break }
  }

As you might be able to see from the comments, we're attempting to get the status of the job 10 times. Before each of those attempts we wait 5 seconds. Both of these numbers might need to be adjusted depending on your work environment. Some comparisons might take several minutes and with these settings you would always hit the maximum number of retries.

Getting the status of a job is done via a GET request using the …/jobs/{id} endpoint where id is the value returned from our initial request. Again, when we receive a JSON response we convert it to a PowerShell object. Once the response content is converted we can check the status to see if it has finished, if it has we can exit the loop and start gathering the results.

Gathering schema comparison results

Gathering the results is done using another GET request with the endpoint of …/schemas/compare/results/{id} where id is the same job id used in previous calls.

# Get the job's results once the status has finished.
$Response = Invoke-WebRequest -Uri ('https://localhost:5000/api/schemas/compare/results/{0}' -f $CompareSchemasJob.Id) -Method Get -ContentType 'application/json'
  $CompareSchemasResults = $Response.Content | ConvertFrom-Json

We take the response content from the server and again convert if from JSON to a PowerShell object using the ConvertFrom-Json function. This object now contains all the information from the database schema comparison such as the number of differences and any output file locations.

Learn more about database DevOps solutions

Hopefully this has shown you how easy it is to get started with the Toad DevOps Toolkit REST API, don't forget to watch for the newest release available this November. Learn more about the toolkit and how you can boost the velocity of your DevOps pipeline.

Questions?

If you have any questions about this topic, click “Start Discussion” below, and this topic will go to our Forums Blog Posts page.