Plugin: SQL client

Introduction

One of the challenges when moving from an old system to a new one is data migration. The SQL client plugin makes data migration easy. You can use it to query data from an MSSQL database, then transform and insert the data into Casewhere.

Features

Execute a data query

Execute a SQL query and retrieve data. The plugin accepts parameters to protect against SQL injection vulnerabilities.

Method Name: ExecuteQuery

Input

• Query (required): a SQL statement
  • Example: select top(500) from [DimProduct] where [ProductKey] > @ProductKey
• Parameters (optional): parameters used in the SQL statement. It should be a JSON string.
• Timeout (optional): command timeout.
• ConnectionString (optional).

Output

• Data: list of objects.

Example

var documentApi = ctx.Use<IDocumentApi>(); 
var pluginApi = ctx.Use<IPluginApi>(); 
var dataApi = ctx.Use<IDataApi>();

// Construct plugin parameters
var parameters = new Dictionary<string, object>();
parameters.Add("Query", "select top(500) * from [DimProduct] where [ProductKey] > @ProductKey");
parameters.Add("Parameters", JsonConvert.SerializeObject(new { ProductKey = 10 }));

// Invoke plugin
var pluginResult = pluginApi.Invoke("SqlClient", "ExecuteQuery", parameters);
var products = pluginResult.GetArray("Data");

// Insert data to DB
foreach(dynamic product in products)
{
  // binary data is received in base64 format
  var docInfo = documentApi.Create($"photo{product.ProductKey}.jpg");
  documentApi.Store(docInfo, Convert.FromBase64String(product.LargePhoto.ToString()));
  dataApi.Add("CwSqlDimProduct", new 
  {
      ProductKey = product.ProductKey,
      Description = product.EnglishDescription,
      ModelName = product.ModelName,
      ProductName = product.EnglishProductName,
      LargePhoto = docInfo,
      ListPrice = product.ListPrice,
      DealerPrice = product.DealerPrice,
      Color = product.Color,
      Style = product.Style
  });
}

Execute a scalar query

Execute a SQL query and retrieve a scalar value. The plugin accepts parameters to protect against SQL injection vulnerabilities.

Method Name: ExecuteQueryScalar

Input

• Query (required): a SQL statement.

  • Example:

    select [LargePhoto] from [DimProduct] where [ProductKey] = @ProductKey
    

• Parameters (optional): parameters used in the SQL statement. It should be a JSON string.

• Timeout (optional): command timeout.

• ConnectionString (optional).

Output

• Data: a scalar value.

Example

var documentApi = ctx.Use<IDocumentApi>(); 
var pluginApi = ctx.Use<IPluginApi>(); 
var dataApi = ctx.Use<IDataApi>(); 

// Query an existing record in Casewhere
var filter = FilterBuilder.Create().Eq("ProductKey", 13).Build();
var result = dataApi.Search(DataObjectApiQuery
  .For("CwSqlDimProduct")
  .FilterBy(filter));
dynamic product = result.Data[0];

// Construct plugin parameters to query an image (bytes) for a given product
var parameters = new Dictionary<string, object>();
parameters.Add("Query", "select LargePhoto from [DimProduct] where [ProductKey] = @ProductKey");
parameters.Add("Parameters", JsonConvert.SerializeObject(new { ProductKey = 13 }));

// Execute plugin
var pluginResult = pluginApi.Invoke("SqlClient", "ExecuteQueryScalar", parameters);
var bytes = pluginResult.Get<byte[]>("Data");

// Retrieve the image and update back to Casewhere
var docInfo = documentApi.Create($"photo{product.ProductKey}.jpg");
documentApi.Store(docInfo, bytes);
dataApi.Update(product.Id, new 
{
  LargePhoto = docInfo
});

Get table schema

Get table schema by name.

Method Name: GetTableSchema

Input

• Name(required): table name.
• ConnectionString (optional).

Output

• Schema: an object in following format.

{
  "Name": "DimProduct",
  "Columns": [
   {
      "Name": "ProductKey",
      "DataType": "Int32",
      "IsKey": true,
      "IsNullable": false
   },

   {
      "Name": "ProductAlternateKey",
      "DataType": "String",
      "IsKey": false,
      "IsNullable": true
   }
  ]
 }

Example

var pluginApi = ctx.Use<IPluginApi>(); 

// Construct plugin parameters
var parameters = new Dictionary<string, object>();
parameters.Add("Name", "DimProduct"); 

// Invoke plugin
var pluginResult = pluginApi.Invoke("SqlClient", "GetTableSchema", parameters);
var schema = pluginResult.GetObject("Schema");
Log.Info("{schema}", JsonConvert.SerializeObject(schema, Newtonsoft.Json.Formatting.Indented));

Installation

Requirements

• Casewhere v2.6 or later.
• MSSQL active connection.

Configuration

• ConnectionString: default connection string.

Example

{
    "ConnectionString":"Data Source=MSSQLSERVER;Initial Catalog=AdventureWorksDW;UserId=sa;Password=a_password;MultipleActiveResultSets=true;"
}

Releases

1.0.0 - 23/04/2021

Changelog

• Execute a data query
• Execute a scalar query
• Get table schema

Download (login required)

• SQL client plugin v1.0.0

• Sample Database