VOOZH about

URL: https://www.cdata.com/kb/tech/mocean-ado-powershell.rst

⇱ Automate Mocean Integration Tasks from PowerShell

Automate Mocean Integration Tasks from PowerShell

👁 Jerod Johnson
Jerod Johnson
Director, Technology Evangelism
Are you in search of a quick and easy way to access Mocean data from PowerShell? This article demonstrates how to utilize the Mocean Cmdlets for tasks like connecting to Mocean data, automating operations, downloading data, and more.

The CData API Driver for ADO.NET is a standard ADO.NET Provider that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time access to Mocean.

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Mocean; this tutorial shows how to use the Provider to retrieve Mocean data.

Once you have acquired the necessary connection properties, accessing Mocean data in PowerShell can be enabled in three steps.

Using API Key Authentication

Mocean uses API key authentication to control access to the API. To obtain an API Key:

  1. Log in to your Mocean account at https://dashboard.moceanapi.com
  2. Navigate to your account settings or API credentials section
  3. Copy your API Key

After obtaining your API Key, set the following connection properties:

  • AuthScheme: Set this to APIKey.
Set the following in the ProfileSettings connection property:
  • APIKey: Set this to your Mocean API Key. This is transmitted as a Bearer token in the Authorization header.

Example Connection String

Profile=C:\profiles\Mocean.apip;AuthScheme=APIKey;ProfileSettings='APIKey=your_api_key';

Connecting to Mocean

Once the authentication is configured, you can connect to Mocean and query data from any of the available tables such as AccountBalance, AccountPricing, MessageStatus, and NumberLookup.

  1. Load the provider's assembly: 

    [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData API Driver for ADO.NET\lib\System.Data.CData.API.dll")

  2. Connect to Mocean:

     
$conn= New-Object System.Data.CData.API.APIConnection("Profile=C:\profiles\Mocean.apip;AuthScheme=APIKey;ProfileSettings='APIKey=your_api_key';")
$conn.Open()

  3. Instantiate the APIDataAdapter, execute an SQL query, and output the results: 

    $sql="SELECT , from AccountBalance"

$da= New-Object System.Data.CData.API.APIDataAdapter($sql, $conn)
$dt= New-Object System.Data.DataTable
$da.Fill($dt)

$dt.Rows | foreach {
	Write-Host $_. $_.
}
CodeProject