CODEWITHSUNDEEP
c#sql-servert-sqlspotfire
Kevin Presley
Kevin Presley

Reputation: 87

Need to convert (x,y) points stored in SQL Server as binary image to float array at query time

(position, load) points are being stored in SQL Server as an image. Each time the machine strokes, one record is created to store this position/load plot along with other data.

I need to convert the 'image' into numbers for analysis. I plan to do the analysis in Spotfire, so any Spotfire features can be used in the solution.

I have a C# program which queries the data from SQL and converts it to CSV; however, I want a way to skip this step directly query the points for viewing/analysis in Spotfire.

This C# works and does what I want. How can I use this (or some variant) to process the data on query from SQL so that users don't run a separate "converter" console app before opening their Spotfire file?

// Return a list of points from an array of bytes:
public static IList<PositionLoadPoint> GetPositionLoadPoints(byte[] bytes)
{
     IList<PositionLoadPoint> result = new List<PositionLoadPoint>();
     int midIndex = bytes.Length / 2;

     for (int i = 0; i < midIndex; i += 4)
     {
         byte[] load = new byte[4];
         byte[] position = new byte[4];

         Array.Copy(bytes, i, load, 0, 4);
         Array.Copy(bytes, midIndex + i, position, 0, 4);

         var point = new PositionLoadPoint(BitConverter.ToSingle(load, 0),
                                           BitConverter.ToSingle(position, 0));

        result.Add(point);
    }

    return result;
}

Upvotes: 1

Views: 346

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

You could use a CLR Table-Valued Function to run that C# code and translate the binary data into a resultset.

A CLR TVF has an "init" method that returns a collection, and then SQL will run your "FillRow" method for each member of the returned collection. The FillRow method translates the object into a "row" of output paramaters. EG:

using System;  
using System.Data.Sql;  
using Microsoft.SqlServer.Server;  
using System.Collections;  
using System.Data.SqlTypes;  
using System.Diagnostics;  

public class TabularEventLog  
{  
    [SqlFunction(FillRowMethodName = "FillRow")]  
    public static IEnumerable InitMethod(String logname)  
    {  
        return new EventLog(logname).Entries;    
    }  

    public static void FillRow(Object obj, out SqlDateTime timeWritten, out SqlChars message, out SqlChars category, out long instanceId)  
    {  
        EventLogEntry eventLogEntry = (EventLogEntry)obj;  
        timeWritten = new SqlDateTime(eventLogEntry.TimeWritten);  
        message = new SqlChars(eventLogEntry.Message);  
        category = new SqlChars(eventLogEntry.Category);  
        instanceId = eventLogEntry.InstanceId;  
    }  
}

Upvotes: 2

Related Questions