SQL Server Requires Chilkat v11.0.0+
SQL Server
Look up an IP address with ipstack over HTTPS
See more Geolocation Examples
Set your ipstack access key and request one public IP address over HTTPS. HTTP status errors and the API’s success=false error object are checked separately. Account quotas and optional modules depend on the subscription. The example displays core geolocation fields and handles null values.
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 @iTmp2 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'
DECLARE @accessKey nvarchar(4000)
SELECT @accessKey = 'YOUR_ACCESS_KEY'
DECLARE @url int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @url OUT
EXEC sp_OAMethod @url, 'Append', @success OUT, 'https://api.ipstack.com/'
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, 'access_key', @accessKey
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
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, 'error.type'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'error.type'
PRINT @sTmp0
END
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp0 OUT, 'error.info'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'error.info'
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, 'JsonTypeOf', @iTmp0 OUT, 'success'
EXEC sp_OAMethod @json, 'BoolOf', @iTmp1 OUT, 'success'
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp2 OUT, 'error'
IF ((@iTmp0 = 5) and (@iTmp1 <> 1)) or (@iTmp2 = 3)
BEGIN
PRINT 'The service could not complete the lookup.'
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp0 OUT, 'error.type'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'error.type'
PRINT @sTmp0
END
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp0 OUT, 'error.info'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'error.info'
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, 'ip'
EXEC sp_OAMethod @json, 'StringOfEquals', @iTmp1 OUT, 'ip', '', 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, 'ip'
PRINT 'IP address: ' + @sTmp0
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp0 OUT, 'country_name'
EXEC sp_OAMethod @json, 'StringOfEquals', @iTmp1 OUT, 'country_name', '', 1
IF (@iTmp0 = 1) and (@iTmp1 <> 1)
BEGIN
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'country_name'
PRINT 'Country: ' + @sTmp0
END
ELSE
BEGIN
PRINT 'Country: unavailable'
END
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp0 OUT, 'country_code'
EXEC sp_OAMethod @json, 'StringOfEquals', @iTmp1 OUT, 'country_code', '', 1
IF (@iTmp0 = 1) and (@iTmp1 <> 1)
BEGIN
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'country_code'
PRINT 'Country code: ' + @sTmp0
END
ELSE
BEGIN
PRINT 'Country code: unavailable'
END
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp0 OUT, 'region_name'
EXEC sp_OAMethod @json, 'StringOfEquals', @iTmp1 OUT, 'region_name', '', 1
IF (@iTmp0 = 1) and (@iTmp1 <> 1)
BEGIN
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'region_name'
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, 'zip'
EXEC sp_OAMethod @json, 'StringOfEquals', @iTmp1 OUT, 'zip', '', 1
IF (@iTmp0 = 1) and (@iTmp1 <> 1)
BEGIN
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'zip'
PRINT 'Postal code: ' + @sTmp0
END
ELSE
BEGIN
PRINT 'Postal code: unavailable'
END
-- Missing coordinates stay unavailable. A numeric zero is a valid coordinate.
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp0 OUT, 'latitude'
EXEC sp_OAMethod @json, 'JsonTypeOf', @iTmp1 OUT, 'longitude'
IF (@iTmp0 = 2) and (@iTmp1 = 2)
BEGIN
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'latitude'
EXEC sp_OAMethod @json, 'StringOf', @sTmp1 OUT, 'longitude'
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