Plugin: SQL client

Introduction

One of the challenges when moving an old system to a new one is data migration. With the SQL client plugin, data migration made easy. User can use SQL client plugin to query data from a MSSQL database, transform and insert 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