Sample code for 30+ languages & platforms
SQL Server Requires Chilkat v11.0.0+

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.

Service documentation

Chilkat SQL Server Downloads

SQL Server
-- 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