(SQL Server) Google Maps Geolocation Request

Demonstrates how make a Google Maps Geolocation REST API request.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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)
    -- This example duplicates the following CURL request:
    -- curl -d @your_filename.json -H "Content-Type: application/json" -i "https://www.googleapis.com/geolocation/v1/geolocate?key=YOUR_API_KEY"

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Connect to the Google API REST server.
    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    DECLARE @success int
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'www.googleapis.com', @port, @bTls, @bAutoReconnect

    -- Add the Content-Type request header.
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'application/json'

    -- Add your API key as a query parameter
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'key', 'YOUR_API_KEY'

    -- The JSON query is contained in the body of the HTTP POST.
    -- This is a sample query (which we'll dynamically build in this example)
    -- { 
    --  "homeMobileCountryCode": 310,
    --  "homeMobileNetworkCode": 260,
    --  "radioType": "gsm",
    --  "carrier": "T-Mobile",
    --  "cellTowers": [
    --   {
    --    "cellId": 39627456,
    --    "locationAreaCode": 40495,
    --    "mobileCountryCode": 310,
    --    "mobileNetworkCode": 260,
    --    "age": 0,
    --    "signalStrength": -95
    --   }
    --  ],
    --  "wifiAccessPoints": [
    --   { 
    --    "macAddress": "01:23:45:67:89:AB",
    --    "signalStrength": 8,
    --    "age": 0,
    --    "signalToNoiseRatio": -65,
    --    "channel": 8
    --   },
    --   { 
    --    "macAddress": "01:23:45:67:89:AC",
    --    "signalStrength": 4,
    --    "age": 0
    --   }
    --  ]
    -- }

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'AppendInt', @success OUT, 'homeMobileCountryCode', 310
    EXEC sp_OAMethod @json, 'AppendInt', @success OUT, 'homeMobileNetworkCode', 260
    EXEC sp_OAMethod @json, 'AppendString', @success OUT, 'radioType', 'gsm'
    EXEC sp_OAMethod @json, 'AppendString', @success OUT, 'carrier', 'T-Mobile'
    DECLARE @aCellTowers int
    EXEC sp_OAMethod @json, 'AppendArray', @aCellTowers OUT, 'cellTowers'
    EXEC sp_OAMethod @aCellTowers, 'AddObjectAt', @success OUT, 0
    DECLARE @oCellTower int
    EXEC sp_OAMethod @aCellTowers, 'ObjectAt', @oCellTower OUT, 0
    EXEC sp_OAMethod @oCellTower, 'AppendInt', @success OUT, 'cellId', 39627456
    EXEC sp_OAMethod @oCellTower, 'AppendInt', @success OUT, 'locationAreaCode', 40495
    EXEC sp_OAMethod @oCellTower, 'AppendInt', @success OUT, 'mobileCountryCode', 310
    EXEC sp_OAMethod @oCellTower, 'AppendInt', @success OUT, 'mobileNetworkCode', 260
    EXEC sp_OAMethod @oCellTower, 'AppendInt', @success OUT, 'age', 0
    EXEC sp_OAMethod @oCellTower, 'AppendInt', @success OUT, 'signalStrength', -95
    EXEC @hr = sp_OADestroy @oCellTower

    EXEC @hr = sp_OADestroy @aCellTowers

    DECLARE @aWifi int
    EXEC sp_OAMethod @json, 'AppendArray', @aWifi OUT, 'wifiAccessPoints'
    EXEC sp_OAMethod @aWifi, 'AddObjectAt', @success OUT, 0
    DECLARE @oPoint int
    EXEC sp_OAMethod @aWifi, 'ObjectAt', @oPoint OUT, 0
    EXEC sp_OAMethod @oPoint, 'AppendString', @success OUT, 'macAddress', '01:23:45:67:89:AB'
    EXEC sp_OAMethod @oPoint, 'AppendInt', @success OUT, 'signalStrength', 8
    EXEC sp_OAMethod @oPoint, 'AppendInt', @success OUT, 'age', 0
    EXEC sp_OAMethod @oPoint, 'AppendInt', @success OUT, 'signalToNoiseRatio', -65
    EXEC sp_OAMethod @oPoint, 'AppendInt', @success OUT, 'channel', 8
    EXEC @hr = sp_OADestroy @oPoint

    EXEC sp_OAMethod @aWifi, 'AddObjectAt', @success OUT, 1
    EXEC sp_OAMethod @aWifi, 'ObjectAt', @oPoint OUT, 1
    EXEC sp_OAMethod @oPoint, 'AppendString', @success OUT, 'macAddress', '01:23:45:67:89:AC'
    EXEC sp_OAMethod @oPoint, 'AppendInt', @success OUT, 'signalStrength', 4
    EXEC sp_OAMethod @oPoint, 'AppendInt', @success OUT, 'age', 0
    EXEC @hr = sp_OADestroy @oPoint

    EXEC @hr = sp_OADestroy @aWifi

    DECLARE @responseJson nvarchar(4000)
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    EXEC sp_OAMethod @rest, 'FullRequestString', @responseJson OUT, 'POST', '/geolocation/v1/geolocate', @sTmp0
    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

    -- When successful, the response code is 200.
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        -- Examine the request/response to see what happened.

        EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
        PRINT 'response status code = ' + @iTmp0

        EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
        PRINT 'response status text = ' + @sTmp0

        EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
        PRINT 'response header: ' + @sTmp0

        PRINT 'response JSON: ' + @responseJson

        PRINT '---'

        EXEC sp_OAGetProperty @rest, 'LastRequestStartLine', @sTmp0 OUT
        PRINT 'LastRequestStartLine: ' + @sTmp0

        EXEC sp_OAGetProperty @rest, 'LastRequestHeader', @sTmp0 OUT
        PRINT 'LastRequestHeader: ' + @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT 'JSON request body: ' + @sTmp0

    -- The JSON response should look like this:
    -- { 
    --  "location": {
    --   "lat": 37.4248297,
    --   "lng": -122.07346549999998
    --  },
    --  "accuracy": 1145.0
    -- }


    PRINT 'JSON response: ' + @responseJson

    DECLARE @jsonResp int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResp OUT

    EXEC sp_OAMethod @jsonResp, 'Load', @success OUT, @responseJson

    DECLARE @jsonLoc int
    EXEC sp_OAMethod @jsonResp, 'ObjectOf', @jsonLoc OUT, 'location'
    -- Any JSON value can be obtained as a string..
    DECLARE @latitude nvarchar(4000)
    EXEC sp_OAMethod @jsonLoc, 'StringOf', @latitude OUT, 'lat'

    PRINT 'latitude = ' + @latitude
    DECLARE @longitude nvarchar(4000)
    EXEC sp_OAMethod @jsonLoc, 'StringOf', @longitude OUT, 'lng'

    PRINT 'longitude = ' + @longitude
    EXEC @hr = sp_OADestroy @jsonLoc

    DECLARE @accuracy nvarchar(4000)
    EXEC sp_OAMethod @jsonResp, 'StringOf', @accuracy OUT, 'accuracy'

    PRINT 'accuracy = ' + @accuracy

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @jsonResp


END
GO