SQL Server
SQL Server
UK Vehicle Data VRM Lookup
See more REST Examples
Demonstrates how to query the UK Vehicle Data VRM online API's. (See https://ukvehicledata.co.uk/)Chilkat SQL Server Downloads
-- 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
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @rest int
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
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
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