12D: Sharepoint the OData interface

In my last post I wrote about my agonies when I tried to extract data from a SharePoint list. Finally I put together an OData request that worked, but there were still one thing nagging me, in the request there was a ‘dynamic’ filter:

$filter=Year eq '2016' and Month eq 7

Since I want the last month’s currency rate figures I tried to create a view in SharePoint showing only the last month’s currency rates something similar to an SQL request like:

Create view last_period as

select R1.* from MY_EXCHANGE_RATE as R1

join

(select * from MY_EXCHANGE_RATE

group by year,month

having year = max(year) and month = max(month)) as R2

on R1.year = R2.year and R1.month = R2.month

I knew this was not going to work with SharePoint lists, since this is pretty advanced and SharePoint lists are just lists plain and simple, these lists do not even qualify as a basic database manager, of course I was right, for now I’m stuck with my dynamic filter.

I struggled on with my Odata interface, in the last post I wrote the response from the Odata call was a shitload of complex XML:

This is just the very first part of the output, I knew even before I tried to parse it it was going to be a rough ride. This is a good example why XML has got such a bad reputation. Just because there is an option you do not necessarily have to use it, keep it simple stupid. I decided to go Powershell scripting, I have played around with it before and I liked it. My intention with this script was call Sharepoint to extract the monthly currency rates, then parse the response into a text file, and finally deliver the text file via FTP to a target application, in this case my Data Warehouse.

After the struggle of figure out how to set up the OData request ,the first part of my script call SharePoint Odata and extract the XML response is straightforward:

#Before we start, where are we?

$cwd = Get-Location

#write-host "$MyInvocation.ScriptName runs in $cwd"

write-host "Starting"

#Set up constants

$currsel = 'Currency_x0020_Code/currencyName,Currency_x0020_Code/Title,Currency_x0020_Code/currencyNumCode
,Currency_x0020_Code/currencyAC_Reporting,Currency_x0020_CodeId,Currency_x0020_Code/Title
,Currency_x0020_Code/currencyNumCode,Currency_x0020_Code/currencyAC_Reporting';

$rtesel = 'Currency_x0020_CodeId,Year,Month,Value%5Fx0020%5FEnd%5Fx0020%5Fof%5Fx0020%5FP
,Average%5Fx0020%5FValue%5Fx0020%5FEnd%5Fx0,Value,Average%5Fx0020%5FValue';

$spsel = '$select='+"$currsel,$rtesel"

$spexp = '$expand=Currency_x0020_Code';

$splist = 'MonthlyCurrencyRates'

$sproot = 'https://thecompany/codes/_api/Web/Lists'

$tempfile = 'ftp.txt';

$FTPserver = "ftp://thedatawarehouse/"

$FTPmap = "ftpload/indw/"

$FTPfileName = "monthlyCurrencyRates.txt"

#Credentials

$Username = 'xxx'

$Password = 'xxx'

$FTPuser = 'xxx'

$FTPpw = 'xxx'

#Load the webinterface and fix credentials

#The credentials ares supposed to be a certificate but it is unclear to me how to use certificates

# in Powerhell scripting at this time. For now I use userid & password

$wc = New-Object system.Net.WebClient

$credentials = New-Object System.Management.Automation.PSCredential $Username, $Password

$wc.Credentials = $credentials

#Get Year and month for the web service

Get-Date -format "yyyy-mm-dd HH:mm";

$defaultValue = (get-date).year;

($defaultValue,(Read-Host "Press enter to accept [$($defaultValue)]")) -match '\S' |% {$yr = $_}

$defaultValue = (get-date).month;

($defaultValue,(Read-Host "Press enter to accept [$($defaultValue)]")) -match '\S' |% {$mo = $_}

write-host "Fetching currency rates for $yr - $mo"

#Set up the parameters for the web service

$uri = "$sproot/GetByTitle('$splist')/Items?" + "$spsel&$spexp" + '&$filter=' + "Year eq '" + $yr + "' and Month eq " + $mo;

write-host "Call SharePoint via the websevice"

$xml = [xml]$wc.downloadString("$uri");

I got substantial help setting up the authentication to SharePoint from a colleague, apart from that the first part was a walk in the park. I

Next thing parse the horrendous XML output proved to be trickier. I know this First I tried a number of googled solutions that promised to deal with namespaces and other ‘fancy stuff’ in the OData XML, nothing worked, after many failed attempts I decided to try the built in XPATH capabilities of PowerShell. It turned out to be a very nice experience, simple straight to the point and pragmatic, except for one little detail, that took me more that a day to figure out, in the much overly verbose response some of the columns were declared with both attributes and text/data.

<d:Month m:type="Edm.Double">2</d:Month>

Whatever I tried I could not grab the text part, I only got the attribute. After many hours and many many foul words, I decided to step back and think. Over the years I have learned not to mix attribute and data in an XML tag, it always come back and bite you sooner rather than later. All of a sudden I remember I once had seen ‘#text’ in an article on XML parsing, maybe… And yes it worked adding an extra ‘#text’ node gave me the month node text. All of a sudden it was a breeze to skip all the bogus, just point to the data and get it

#For every currency extract the monthly figures

$op = "Code NumCode ACrpt Year Month val avg_val orig_val orig_avg_value currency name `n" #Header line

foreach($entry in $xml.feed.entry){

$op += '"' + $entry.link.inline.entry.content.properties.Title+ '"'

$op += ',"' + $entry.link.inline.entry.content.properties.currencyNumCode.'#text' + '"'

$op += ',"' + $entry.link.inline.entry.content.properties.currencyAC_Reporting + '"'

$op += ',"' + $entry.content.properties.Year + '"'

$op += ',"' + $entry.content.properties.Month.'#text' + '"'

$op += "," + $entry.content.properties.Value_x0020_End_x0020_of_x0020_P.'#text'

$op += "," + $entry.content.properties.Average_x0020_Value_x0020_End_x0.'#text'

$op += "," + $entry.content.properties.Value

$op += "," + $entry.content.properties.Average_x0020_Value

$op += ',"' + $entry.link.inline.entry.content.properties.currencyName + '"'

$op += "`n"

}

$op = $op.TrimEnd("`n") #Remove the last new line

I know the produced verbose XML is of standard XML formats, normally Atom feeds and that you with the help of support libraries do not need to bother about the details, but I could not make any of those work. And for me parsing the XML it would have been easier with something less verbose, the fancy Atom feed I had to deal with was just crappy noise surrounding the data.

Now I wanted to save the data in a file before I FTP it away, of course I used the Out-File command to do that. It worked nicely, except for one little thing. When I FTP the file it was corrupted when it reached the target FTP server. After many futile attempts I recalled a problem I had a year ago. For some reason Microsoft software insist writing BOM markers in UTF-8 files, but Microsoft's software seldom handle these BOM markers well, to get rid of the unwanted BOM marker I replace Out-File command with [IO.File]::WriteAllLines

write-host "Write the result to file ($tempfile)"

$Localfile = "$cwd\$tempfile"

If (Test-Path $Localfile){

Remove-Item $Localfile

}

[IO.File]::WriteAllLines($Localfile, $op) # Note! No utf-8 BOM marker.

# Out-File command plus System.Net.FtpWebRequest send a currupt file!

The rest, sending the data over the FTP server was easy-peasy:

$FileContent = gc -en byte $Localfile

$FileLength = $FileContent.Length

#Send the result to the recipient via FTP

#Constants

$FTPfile = $FTPserver + $FTPmap + $FTPfileName

#Create Request Object

$FTPRequest = [System.Net.FtpWebRequest]::Create("$FTPfile")

$FTPRequest.Method = [System.Net.WebRequestMethods+Ftp]::UploadFile

$FTPRequest.Credentials = new-object System.Net.NetworkCredential($FTPuser, $FTPpw)

$FTPRequest.UseBinary = $FALSE

$FTPRequest.ContentLength = $FileContent.Length

$Run = $FTPRequest.GetRequestStream()

write-host "Send $FileLength bytes from $Localfile to $FTPfile"

$Run.Write($FileContent, 0, $FileContent.Length)

#FTP Cleanup

$Run.Close()

$Run.Dispose()

write-host "Done"

Now all I had to - run the script:

And write a Data Warehouse import job:

<?xml version='1.0' encoding='UTF-8' standalone='yes'?>

<sql>

USE test;

drop table if exists `@RTETABLE`;

CREATE TABLE if not exists `@RTETABLE` (

`currcd` char(3) NOT NULL DEFAULT '',

`currcdn` char(3) NOT NULL DEFAULT '',

`rpt` char(1) NOT NULL DEFAULT '',

`year` char(4) NOT NULL DEFAULT '',

`month` char(2) NOT NULL DEFAULT '',

`rpt_value` decimal(11,8) DEFAULT NULL,

`rpt_value_avg` decimal(11,8) DEFAULT NULL,

`rate` decimal(11,8) DEFAULT NULL,

`rate_avg` decimal(11,8) DEFAULT NULL,

`name` char(30) NOT NULL DEFAULT '',

`periodstart` date NOT NULL,

`CREATED` datetime DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`currcd`,`year`,`month`)

) COMMENT='Monthly Exchange Rates';

LOAD DATA LOCAL INFILE '@J_infile0' replace INTO TABLE @RTETABLE

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

IGNORE 1 LINES

set periodstart = DATE(concat(year,'-',month,'-01'))

;

</sql>

</job>

</schedule>

And run it:

Finally look at the glorious result:

The experience of writing a SharePoint OData interface was not entirely unpleasant, PowerShell scripting is fun. Even though I still not got the hang of it (and .Net), I can produce some pretty advanced stuff, at least if I compare with what I can do with BAT scripts and with BASH scripts too, but it is not a fair comparison, PowerShell script with .Net are so much more. I cannot say the same about SharePoint, e.g. the lists sucks when you try to do anything more than the simplest of tasks with them. SharePoint design goal is only to do simple tasks in a simple way, but marketed as as a platform that can do more.
Unfortunately you have to make design decisions and compromises between functionality and user friendliness. The internal field name of SharePoint lists is a good example. The internal names are very important for a developer trying to do something beyond the simplest of tasks. As it is now you are encouraged to neglect internal names when you define your data.

“SharePoint is so simple and intuitive anyone can create advanced applications”. This is just a marketing hoax, but more about this some other time, now I am happy with the working integration.

This is a 'no frills' integration, next is to make an OData client work, I mentioned in this post I failed to make such clients work, now is the time do a serious attempt to make one work.

2016-08-06

Sharepoint the OData interface

No comments:

Post a Comment