EVE Technology Lab

Disclaimer: I am no programmer, just someone that loves to hobby arround. So here it is.

I have made a simple script for those who swear by MSSQL and like PowerShell like I do to automate some work for you. You need to have setup a few things here and there. Edit the scripts to your liking.

Required: Powershell 4.0 (download free on ms site)
Table that you are updating to has to be set to not allow duplicates.

Ok, first I wrote a fairly simple and straight forward script that will pull API data. I have made it only for the characters for now, but can easily be changed to apply to other calls as well.

Simple but effective, pulls character data and stores it in an XML file in your tempfolder. Make sure that the temp folder exists, couldn't be bothered creating a test for that aswell.

 
$vCode = '&vCode=### YOUR VCODE HERE ###'
$keyID = '?keyID=### YOUR KEYID HERE ###'
$uri = 'https://api.eveonline.com/account/Characters.xml.aspx'
$outfile = 'C:\temp\Characters.xml'
$testpath = test-path $outfile

if ($testpath -eq $False){
        Invoke-RestMethod -Method Get $uri$keyID$vCode -OutFile C:\temp\Characters.xml
        [xml]$xml = (Get-Content "C:\temp\Characters.xml") 
}else{
        [xml]$xml = (Get-Content "C:\temp\Characters.xml")
}

$currenttime = (Get-Date).ToShortTimeString()
$cacheduntilinxml = Get-Date $xml.eveapi.cachedUntil
$cacheduntil = $cacheduntilinxml.AddHours(1).ToShortTimeString()

if($currenttime -gt $cacheduntil){
    Invoke-RestMethod -Method Get $uri$keyID$vCode -OutFile C:\temp\Characters.xml

}else{

Write-Host "Do Nothing"
}
 

Storing it in SQL:

This will store the entire XML in SQL as a raw XML and then puts it in the Characters table.

bulkdata: table to store raw XML into the xml_characters column.
Characters: table to store the character data when unfucked.

USE YOUR_DATABASE
GO

INSERT INTO bulkdata(xml_characters)
SELECT * FROM OPENROWSET(BULK 'C:\temp\Characters.xml', SINGLE_BLOB) AS x

DECLARE @XML AS XML, @Doc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = xml_characters FROM Bulkdata
EXEC sp_xml_preparedocument @Doc OUTPUT, @XML

INSERT INTO Characters (characterName, characterID, corporationName, corporationID, allianceID, allianceName, factionID, factionName)
SELECT *
FROM OPENXML(@Doc, 'eveapi/result/rowset/row')
WITH 
(
name [varchar](255) '@name',
characterID [int] '@characterID',
corporationName [varchar](255)'@corporationName',
corporationID [int] '@corporationID',
allianceID [int] '@allianceID',
allianceName [varchar](255) '@allianceName',
factionID [int] '@factionID',
factionName [varchar](255) '@factionName'
)

EXEC sp_xml_removedocument @Doc
GO

As I said, I am not a programmer but this stuff got the job done for me and I can actually use this in a task. The Powershell script takes the cached timer in account (change the 'Addhours()' to your timezone).

If you have something that works better, let me know. Thank you.