SQL Server
SQL Server
ipdata.co IPv4 Geolocation Lookup
See more Geolocation Examples
Demonstrates how to lookup Geolocation data for an IPv4 address using the ipdata.co API.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
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
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 @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @jsonStr nvarchar(4000)
EXEC sp_OAMethod @http, 'QuickGetStr', @jsonStr OUT, 'https://api.ipdata.co/149.250.207.170?api-key=MY_API_KEY'
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
RETURN
END
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAMethod @json, 'Load', @success OUT, @jsonStr
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Sample output:
-- Use this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
-- {
-- "ip": "149.250.207.170",
-- "is_eu": true,
-- "city": null,
-- "region": null,
-- "region_code": null,
-- "country_name": "Germany",
-- "country_code": "DE",
-- "continent_name": "Europe",
-- "continent_code": "EU",
-- "latitude": 51.2993,
-- "longitude": 9.491,
-- "asn": "AS15854",
-- "organisation": "EntServ Deutschland GmbH",
-- "postal": null,
-- "calling_code": "49",
-- "flag": "https://ipdata.co/flags/de.png",
-- "emoji_flag": "\ud83c\udde9\ud83c\uddea",
-- "emoji_unicode": "U+1F1E9 U+1F1EA",
-- "languages": [
-- {
-- "name": "German",
-- "native": "Deutsch"
-- }
-- ],
-- "currency": {
-- "name": "Euro",
-- "code": "EUR",
-- "symbol": "\u20ac",
-- "native": "\u20ac",
-- "plural": "euros"
-- },
-- "time_zone": {
-- "name": "Europe/Berlin",
-- "abbr": "CEST",
-- "offset": "+0200",
-- "is_dst": true,
-- "current_time": "2019-04-20T23:54:30.715507+02:00"
-- },
-- "threat": {
-- "is_tor": false,
-- "is_proxy": false,
-- "is_anonymous": false,
-- "is_known_attacker": false,
-- "is_known_abuser": false,
-- "is_threat": false,
-- "is_bogon": false
-- },
-- "count": "2"
-- }
DECLARE @ip nvarchar(4000)
DECLARE @is_eu int
DECLARE @city nvarchar(4000)
DECLARE @region nvarchar(4000)
DECLARE @region_code nvarchar(4000)
DECLARE @country_name nvarchar(4000)
DECLARE @country_code nvarchar(4000)
DECLARE @continent_name nvarchar(4000)
DECLARE @continent_code nvarchar(4000)
DECLARE @latitude nvarchar(4000)
DECLARE @longitude nvarchar(4000)
DECLARE @asn nvarchar(4000)
DECLARE @organisation nvarchar(4000)
DECLARE @postal nvarchar(4000)
DECLARE @calling_code nvarchar(4000)
DECLARE @flag nvarchar(4000)
DECLARE @emoji_flag nvarchar(4000)
DECLARE @emoji_unicode nvarchar(4000)
DECLARE @currencyName nvarchar(4000)
DECLARE @currencyCode nvarchar(4000)
DECLARE @currencySymbol nvarchar(4000)
DECLARE @currencyNative nvarchar(4000)
DECLARE @currencyPlural nvarchar(4000)
DECLARE @time_zoneName nvarchar(4000)
DECLARE @time_zoneAbbr nvarchar(4000)
DECLARE @time_zoneOffset nvarchar(4000)
DECLARE @time_zoneIs_dst int
DECLARE @time_zoneCurrent_time nvarchar(4000)
DECLARE @threatIs_tor int
DECLARE @threatIs_proxy int
DECLARE @threatIs_anonymous int
DECLARE @threatIs_known_attacker int
DECLARE @threatIs_known_abuser int
DECLARE @threatIs_threat int
DECLARE @threatIs_bogon int
DECLARE @count nvarchar(4000)
DECLARE @i int
DECLARE @count_i int
DECLARE @name nvarchar(4000)
DECLARE @native nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @ip OUT, 'ip'
EXEC sp_OAMethod @json, 'BoolOf', @is_eu OUT, 'is_eu'
EXEC sp_OAMethod @json, 'StringOf', @city OUT, 'city'
EXEC sp_OAMethod @json, 'StringOf', @region OUT, 'region'
EXEC sp_OAMethod @json, 'StringOf', @region_code OUT, 'region_code'
EXEC sp_OAMethod @json, 'StringOf', @country_name OUT, 'country_name'
EXEC sp_OAMethod @json, 'StringOf', @country_code OUT, 'country_code'
EXEC sp_OAMethod @json, 'StringOf', @continent_name OUT, 'continent_name'
EXEC sp_OAMethod @json, 'StringOf', @continent_code OUT, 'continent_code'
EXEC sp_OAMethod @json, 'StringOf', @latitude OUT, 'latitude'
EXEC sp_OAMethod @json, 'StringOf', @longitude OUT, 'longitude'
EXEC sp_OAMethod @json, 'StringOf', @asn OUT, 'asn'
EXEC sp_OAMethod @json, 'StringOf', @organisation OUT, 'organisation'
EXEC sp_OAMethod @json, 'StringOf', @postal OUT, 'postal'
EXEC sp_OAMethod @json, 'StringOf', @calling_code OUT, 'calling_code'
EXEC sp_OAMethod @json, 'StringOf', @flag OUT, 'flag'
EXEC sp_OAMethod @json, 'StringOf', @emoji_flag OUT, 'emoji_flag'
EXEC sp_OAMethod @json, 'StringOf', @emoji_unicode OUT, 'emoji_unicode'
EXEC sp_OAMethod @json, 'StringOf', @currencyName OUT, 'currency.name'
EXEC sp_OAMethod @json, 'StringOf', @currencyCode OUT, 'currency.code'
EXEC sp_OAMethod @json, 'StringOf', @currencySymbol OUT, 'currency.symbol'
EXEC sp_OAMethod @json, 'StringOf', @currencyNative OUT, 'currency.native'
EXEC sp_OAMethod @json, 'StringOf', @currencyPlural OUT, 'currency.plural'
EXEC sp_OAMethod @json, 'StringOf', @time_zoneName OUT, 'time_zone.name'
EXEC sp_OAMethod @json, 'StringOf', @time_zoneAbbr OUT, 'time_zone.abbr'
EXEC sp_OAMethod @json, 'StringOf', @time_zoneOffset OUT, 'time_zone.offset'
EXEC sp_OAMethod @json, 'BoolOf', @time_zoneIs_dst OUT, 'time_zone.is_dst'
EXEC sp_OAMethod @json, 'StringOf', @time_zoneCurrent_time OUT, 'time_zone.current_time'
EXEC sp_OAMethod @json, 'BoolOf', @threatIs_tor OUT, 'threat.is_tor'
EXEC sp_OAMethod @json, 'BoolOf', @threatIs_proxy OUT, 'threat.is_proxy'
EXEC sp_OAMethod @json, 'BoolOf', @threatIs_anonymous OUT, 'threat.is_anonymous'
EXEC sp_OAMethod @json, 'BoolOf', @threatIs_known_attacker OUT, 'threat.is_known_attacker'
EXEC sp_OAMethod @json, 'BoolOf', @threatIs_known_abuser OUT, 'threat.is_known_abuser'
EXEC sp_OAMethod @json, 'BoolOf', @threatIs_threat OUT, 'threat.is_threat'
EXEC sp_OAMethod @json, 'BoolOf', @threatIs_bogon OUT, 'threat.is_bogon'
EXEC sp_OAMethod @json, 'StringOf', @count OUT, 'count'
SELECT @i = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'languages'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @name OUT, 'languages[i].name'
EXEC sp_OAMethod @json, 'StringOf', @native OUT, 'languages[i].native'
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
END
GO