SQL Server Requires Chilkat v11.0.0+
SQL Server
Look up an IP address with the ip-api free endpoint
See more Geolocation Examples
This example uses the free, keyless ip-api endpoint for non-commercial use. The free service supports HTTP and is limited to 45 requests per minute per source IP. Its Pro service provides HTTPS and commercial use. ip-api Pro.
Always include status and message when selecting fields. Invalid or private addresses can produce status=fail inside a JSON response. Retain the X-Rl and X-Ttl response values between calls, and pause further requests when X-Rl reaches zero.
IP geolocation estimates a network location. VPNs, mobile gateways, and anycast routing can affect the result. City and coordinate fields can be missing or approximate. The sample address 8.8.8.8 is a public DNS address used to demonstrate the request.
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 @iTmp1 int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @sTmp1 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- Assumes Chilkat is unlocked. See Global Unlock Sample.
-- Use a public IP address. Provider results can change as their databases are updated.
DECLARE @ipAddress nvarchar(4000)
SELECT @ipAddress = '8.8.8.8'
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @http, 'RequireSslCertVerify', 1
EXEC sp_OASetProperty @http, 'ConnectTimeout', 10
EXEC sp_OASetProperty @http, 'ReadTimeout', 30
EXEC sp_OASetProperty @http, 'Accept', 'application/json'
-- The free endpoint is HTTP-only. This single lookup sends no API credentials.
DECLARE @url int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @url OUT
EXEC sp_OAMethod @url, 'Append', @success OUT, 'http://ip-api.com/json/'
EXEC sp_OAMethod @http, 'UrlEncode', @sTmp0 OUT, @ipAddress
EXEC sp_OAMethod @url, 'Append', @success OUT, @sTmp0
DECLARE @parameters int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @parameters OUT
EXEC sp_OAMethod @parameters, 'UpdateString', @success OUT, 'fields', 'status,message,query,country,countryCode,regionName,city,lat,lon,timezone'
DECLARE @response int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @response OUT
EXEC sp_OAMethod @url, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @http, 'HttpParams', @success OUT, 'GET', @sTmp0, @parameters, @response
EXEC sp_OAGetProperty @response, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
-- Request diagnostics can contain an API key. Report transport failure without logging credentials.
PRINT 'No HTTP response. Check connectivity, DNS, and TLS configuration.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @parameters
EXEC @hr = sp_OADestroy @response
RETURN
END
-- Save these values if the application will make further requests.
DECLARE @remaining nvarchar(4000)
EXEC sp_OAMethod @response, 'GetHeaderField', @remaining OUT, 'X-Rl'
IF @remaining = '0'
BEGIN
EXEC sp_OAMethod @response, 'GetHeaderField', @sTmp0 OUT, 'X-Ttl'
PRINT 'Rate limit reached. Wait ' + @sTmp0 + ' seconds before the next request.'
END
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
DECLARE @parsed int
EXEC sp_OAGetProperty @response, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @json, 'Load', @parsed OUT, @sTmp0
EXEC sp_OAGetProperty @response, 'StatusCode', @iTmp0 OUT
EXEC sp_OAGetProperty @response, 'StatusCode', @iTmp1 OUT
IF (@success <> 1) or (@iTmp0 < 200) or (@iTmp1 >= 300)
BEGIN
EXEC sp_OAGetProperty @response, 'StatusCode', @iTmp0 OUT
PRINT 'Lookup failed. HTTP ' + @iTmp0
IF @parsed = 1
BEGIN
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp0 OUT, 'message'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'message'
PRINT @sTmp0
END
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @parameters
EXEC @hr = sp_OADestroy @response
EXEC @hr = sp_OADestroy @json
RETURN
END
IF @parsed <> 1
BEGIN
PRINT 'Expected a JSON object in the lookup response.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @parameters
EXEC @hr = sp_OADestroy @response
EXEC @hr = sp_OADestroy @json
RETURN
END
EXEC sp_OAMethod @json, 'StringOfEquals', @iTmp0 OUT, 'status', 'success', 1
IF @iTmp0 <> 1
BEGIN
PRINT 'The service could not complete the lookup.'
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp0 OUT, 'message'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'message'
PRINT @sTmp0
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @parameters
EXEC @hr = sp_OADestroy @response
EXEC @hr = sp_OADestroy @json
RETURN
END
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp0 OUT, 'query'
EXEC sp_OAMethod @json, 'StringOfEquals', @iTmp1 OUT, 'query', '', 1
IF (@iTmp0 <> 1) or (@iTmp1 = 1)
BEGIN
PRINT 'The response did not contain the expected IP address.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @parameters
EXEC @hr = sp_OADestroy @response
EXEC @hr = sp_OADestroy @json
RETURN
END
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'query'
PRINT 'IP address: ' + @sTmp0
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp0 OUT, 'country'
EXEC sp_OAMethod @json, 'StringOfEquals', @iTmp1 OUT, 'country', '', 1
IF (@iTmp0 = 1) and (@iTmp1 <> 1)
BEGIN
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'country'
PRINT 'Country: ' + @sTmp0
END
ELSE
BEGIN
PRINT 'Country: unavailable'
END
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp0 OUT, 'countryCode'
EXEC sp_OAMethod @json, 'StringOfEquals', @iTmp1 OUT, 'countryCode', '', 1
IF (@iTmp0 = 1) and (@iTmp1 <> 1)
BEGIN
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'countryCode'
PRINT 'Country code: ' + @sTmp0
END
ELSE
BEGIN
PRINT 'Country code: unavailable'
END
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp0 OUT, 'regionName'
EXEC sp_OAMethod @json, 'StringOfEquals', @iTmp1 OUT, 'regionName', '', 1
IF (@iTmp0 = 1) and (@iTmp1 <> 1)
BEGIN
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'regionName'
PRINT 'Region: ' + @sTmp0
END
ELSE
BEGIN
PRINT 'Region: unavailable'
END
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp0 OUT, 'city'
EXEC sp_OAMethod @json, 'StringOfEquals', @iTmp1 OUT, 'city', '', 1
IF (@iTmp0 = 1) and (@iTmp1 <> 1)
BEGIN
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'city'
PRINT 'City: ' + @sTmp0
END
ELSE
BEGIN
PRINT 'City: unavailable'
END
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp0 OUT, 'timezone'
EXEC sp_OAMethod @json, 'StringOfEquals', @iTmp1 OUT, 'timezone', '', 1
IF (@iTmp0 = 1) and (@iTmp1 <> 1)
BEGIN
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'timezone'
PRINT 'Time zone: ' + @sTmp0
END
ELSE
BEGIN
PRINT 'Time zone: unavailable'
END
-- Missing coordinates stay unavailable. A numeric zero is a valid coordinate.
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp0 OUT, 'lat'
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp1 OUT, 'lon'
IF (@iTmp0 = 2) and (@iTmp1 = 2)
BEGIN
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'lat'
EXEC sp_OAMethod @json, 'StringOf', @sTmp1 OUT, 'lon'
PRINT 'Coordinates: ' + @sTmp0 + ', ' + @sTmp1
END
ELSE
BEGIN
PRINT 'Coordinates: unavailable'
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @parameters
EXEC @hr = sp_OADestroy @response
EXEC @hr = sp_OADestroy @json
END
GO