(SQL Server) UK Vehicle Data VRM Lookup

Demonstrates how to query the UK Vehicle Data VRM online API's. (See https://ukvehicledata.co.uk/)

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Connect using TLS.
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    DECLARE @success int
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'uk1.ukvehicledata.co.uk', 443, 1, @bAutoReconnect
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- Replace UKVD-API-KEY with your actual API KEY,
    -- which looks something like "abcdb1234-0abc-1235-abcf-1234c3d1abcd"
    EXEC sp_OASetProperty @rest, 'Authorization', 'ukvd-ipwhitelist UKVD-API-KEY'

    -- Replace "ABCDE" with a real VRM...
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'key_vrm', 'ABCDE'

    -- Add the Content-Type HTTP request header.
    -- success = rest.AddHeader("Content-Type","application/json; charset=UTF-8");

    -- This request gets vehicle data.
    -- See below for a request to get battery data.
    DECLARE @jsonResponseStr nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestNoBody', @jsonResponseStr OUT, 'GET', '/api/datapackage/vehicledata'
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN

        EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
        PRINT 'Response Status Code = ' + @iTmp0

        PRINT @jsonResponseStr
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'Load', @success OUT, @jsonResponseStr
    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    -- Show the JSON response.
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- A sample vehicledata response will be as shown below.
    -- To get a few bits of data from the response:

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'RequestGuid'
    PRINT 'RequestGuid: ' + @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'DataItems.EngineNumber'
    PRINT 'EngineNumber: ' + @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'DataKeys.Vrm'
    PRINT 'Vrm: ' + @sTmp0

    -- {
    --   "RequestGuid": "xxxxxxxx-cc72-4b63-xxxxxxxxxxxx",
    --   "StatusCode": 0,
    --   "StatusMessage": "Success",
    --   "PackageId": "xxxxxxxx-a4bc-4a84-af22-xxxxxxxxxxxx",
    --   "PackageVersion": 1,
    --   "DataKeys": {
    --     "Vrm": "ABCDE"
    --   },
    --   "DataItems": {
    --     "DriveAxle": "4x2",
    --     "Exported": "False",
    --     "DriverSeatPosition": "RHD",
    --     "EngineNumber": "LN910940728622",
    --     "Class": "Sport",
    --     "ImportedNorthernIreland": "False",
    --     "HistoryPreviousKeeperAcquisitionDate": "2013-01-12",
    --     "RoadFundLicenseSixMonth": "�126.50",
    --     "EnginePowerBhp": "262",
    --     "SpecDateRangeFrom": "1987-10-01",
    --     "RegistrationDateUK": "1994-08-03",
    --     "SoundLevelDriveBy": "0",
    --     "RoadFundLicenseTwelveMonth": "�235.00",
    --     "RoadFundLicenseBand": "Not available",
    --     "Vrm": "J5YVK",
    --     "TrimCode": "BA",
    --     "Derivative": "S4",
    --     "EngineSizeCc": "2174",
    --     "EngineCylinderCount": "4",
    --     "HistoryLatestV5cIssueDate": "2015-05-12",
    --     "Category": "Car",
    --     "HistoryPreviousKeeperCount": "9",
    --     "ConditionScrapped": "False",
    --     "EngineValveCount": "16",
    --     "WeightUnladen": "0",
    --     "RoadFundLicenseRenewalDate": "2017-04-01",
    --     "Model": "Esprit",
    --     "SpecDateRangeTo": "2003-06-01",
    --     "RoadFundLicenseCurrentStatus": "Tax not due",
    --     "BuildYear": "1994",
    --     "BuildDate": "1994-12-31",
    --     "VinLast4": "1448",
    --     "GearCount": "5",
    --     "SeatCount": "2",
    --     "Make": "Lotus",
    --     "Vin": "SCC082910RHA61448",
    --     "ColourChangeCount": "0",
    --     "DriveType": "Rear Wheel Drive",
    --     "WeightGross": "0",
    --     "EngineCode": "N/A",
    --     "EnginePowerKw": "193",
    --     "SoundLevelEngineSpeed": "0",
    --     "ConditionUnscrapped": "False",
    --     "HistoryPreviousKeeperDisposalDate": "2014-04-26",
    --     "EngineAlignment": "Longitudinal",
    --     "Imported": "False",
    --     "BodyType": "Coupe",
    --     "EnginePosition": "Mid",
    --     "TransmissionType": "Manual",
    --     "WeightFullyLaden": "0",
    --     "SoundLevelStationary": "0",
    --     "FuelType": "Petrol",
    --     "Colour": "YELLOW",
    --     "EngineSizeLitre": "2.2",
    --     "RegistrationDate": "1994-08-03",
    --     "Co2Emissions": "000",
    --     "WeightMaxTowingUnbraked": "0",
    --     "HistoryCurrentKeeperAcquisitionDate": "2015-04-25",
    --     "WeightMaxTowingBraked": "0",
    --     "DoorCount": "2",
    --     "EngineCylinderConfig": "Inline",
    --     "EngineFuelDelivery": "Turbo Injection"
    --   }
    -- }

    -- -----------------------------------------------------------------------------------
    -- Now get battery data...
    EXEC sp_OAMethod @rest, 'FullRequestNoBody', @jsonResponseStr OUT, 'GET', '/api/datapackage/batterydata'
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN

        EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
        PRINT 'Response Status Code = ' + @iTmp0

        PRINT @jsonResponseStr
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    EXEC sp_OAMethod @json, 'Load', @success OUT, @jsonResponseStr

    -- Show the JSON response containing battery data.
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- To get a few pieces of information out of the response...
    DECLARE @numBatteries int
    EXEC sp_OAMethod @json, 'SizeOfArray', @numBatteries OUT, 'DataItems.BatteryList'
    DECLARE @i int
    SELECT @i = 0
    WHILE (@i < @numBatteries)
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i


        PRINT '---- Battery ' + @i + ':'

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'DataItems.BatteryList[I].Type'
        PRINT '  Type = ' + @sTmp0

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'DataItems.BatteryList[I].LayoutImage'
        PRINT '  LayoutImage = ' + @sTmp0

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'DataItems.BatteryList[I].TerminalCode'
        PRINT '  TerminalCode = ' + @sTmp0
        SELECT @i = @i + 1
      END

    -- The battery data looks like this:
    -- {
    --   "RequestGuid": "xxxxxxxx-5030-4b3d-a2ed-xxxxxxxxxxxx",
    --   "StatusCode": 0,
    --   "StatusMessage": "Success",
    --   "PackageId": "xxxxxxxx-9ec2-4fd3-9247-xxxxxxxxxxxx",
    --   "PackageVersion": 1,
    --   "DataKeys": {
    --     "Vrm": "ABCDE"
    --   },
    --   "DataItems": {
    --     "BatteryList": [
    --       {
    --         "Type": "Primary",
    --         "UkRef": "075",
    --         "LayoutCode": "0",
    --         "LayoutImage": "http://cdn.ukvehicledata.co.uk/battery/layout/0.png",
    --         "TerminalCode": "T1",
    --         "TerminalImage": "http://cdn.ukvehicledata.co.uk/battery/terminal/T1.png",
    --         "HoldDownCode": "B13",
    --         "HoldDownImage": "http://cdn.ukvehicledata.co.uk/battery/holddown/B13.png"
    --       }
    --     ],
    --     "BatteryCount": "1"
    --   }
    -- }

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @json


END
GO