SQL Server
SQL Server
Google Photos List Media Items
See more Google Photos Examples
List all media items from a user's Google Photos library.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.
-- Get the previously obtained access token.
-- See Get Google Photos Access Token.
DECLARE @jsonToken int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/googlePhotos.json'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @jsonToken, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @jsonToken
RETURN
END
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
-- For more information, see Google Photos List Media Items API Documentation
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', 'https://photoslibrary.googleapis.com/v1/mediaItems?pageSize=100', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
-- Examine the response status code. Success is indicated by a status code of 200.
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'response status code: ' + @iTmp0
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0
-- Sample response:
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- (See parsing code below...)
-- {
-- "mediaItems": [
-- {
-- "id": "AKcbugGCOVK46E2qq6tv_wEuQuoK_a0iHet9rGH0-UWoZFnTMpEZ2jNjHWggz7Aq3hhjJv4YJsDY6ntowKsvnfnr1Bu3ASnS_w",
-- "productUrl": "https://photos.google.com/lr/photo/AKcbugGCOVK46E2qq6tv_wEuQuoK_a0iHet9rGH0-UWoZFnTMpEZ2jNjHWggz7Aq3hhjJv4YJsDY6ntowKsvnfnr1Bu3ASnS_w",
-- "baseUrl": "https://lh3.googleusercontent.com/lr/AGWb-e7ZcHJhUmxO8EgRSBFyBaGAyXE...YMWCAMDlNTA6-L5LiijXAUfKgD1ULhr9IabJBGTT7ETUdeeFTQTM",
-- "mimeType": "image/jpeg",
-- "mediaMetadata": {
-- "creationTime": "2019-04-11T17:55:53Z",
-- "width": "640",
-- "height": "480",
-- "photo": {
-- "cameraMake": "Apple",
-- "cameraModel": "iPhone 5s",
-- "focalLength": 4.15,
-- "apertureFNumber": 2.2,
-- "isoEquivalent": 32
-- }
-- },
-- "filename": "IMG_1206.JPG"
-- },
-- {
-- "id": "AKcbugFACWDNCQJ2nwhDemR6vRupHSvOUSKukyOHqTWO1CCIs0qw-C0b5MGmrveyjYe8ps8z1rZTRQ8hmSQsODSsobNqDl8vTg",
-- "productUrl": "https://photos.google.com/lr/photo/AKcbugFACWDNCQJ2nwhDemR6vRupHSvOUSKukyOHqTWO1CCIs0qw-C0b5MGmrveyjYe8ps8z1rZTRQ8hmSQsODSsobNqDl8vTg",
-- "baseUrl": "https://lh3.googleusercontent.com/lr/AGWb-e7NQe0qdoZrDHkfkK7LOsw-V...YJ1mrohD5duHsY2QJCzLTXZ65EOUrJefars3xDSwsCYlelY470kk",
-- "mimeType": "image/jpeg",
-- "mediaMetadata": {
-- "creationTime": "2016-10-09T01:49:58Z",
-- "width": "600",
-- "height": "427",
-- "photo": {}
-- },
-- "filename": "hedgehogs.jpg"
-- },
-- {
-- "id": "AKcbugHEVyTFgIYb7GzhRFP5KvhBRIIsRcb6Cd4-2ByHzad2oqZnWF2aMPoXY-vfrXmefdTlyF3yZ22Odz6IFLs8pnJlOoVBUg",
-- "productUrl": "https://photos.google.com/lr/photo/AKcbugHEVyTFgIYb7GzhRFP5KvhBRIIsRcb6Cd4-2ByHzad2oqZnWF2aMPoXY-vfrXmefdTlyF3yZ22Odz6IFLs8pnJlOoVBUg",
-- "baseUrl": "https://lh3.googleusercontent.com/lr/AGWb-e64wP9gpPXKq5b8FCFKzu9C...QTFxMLVRaLeQZBvYGZVIe7xCcRPnRhMpiXwL_RV_aU",
-- "mimeType": "image/jpeg",
-- "mediaMetadata": {
-- "creationTime": "2015-09-21T20:30:02Z",
-- "width": "59",
-- "height": "69",
-- "photo": {}
-- },
-- "filename": "chilkat_gravatar.jpg"
-- },
-- {
-- "id": "AKcbugHdYTp-S-D_EYnSBA4EhrKT86IFbmdRuiDclKka9T5b6UIbeDvhTAu1bebYOCFKH-gv1ME1z6RyMN4eW_9J5ol9_qizMg",
-- "productUrl": "https://photos.google.com/lr/photo/AKcbugHdYTp-S-D_EYnSBA4EhrKT86IFbmdRuiDclKka9T5b6UIbeDvhTAu1bebYOCFKH-gv1ME1z6RyMN4eW_9J5ol9_qizMg",
-- "baseUrl": "https://lh3.googleusercontent.com/lr/AGWb-e4e69WQpYcQX...m87rR6gDBhcB55TL__3ckBYNQ8MjbqpBtmojjYWrLG_j4VsVSA",
-- "mimeType": "image/jpeg",
-- "mediaMetadata": {
-- "creationTime": "2008-05-29T15:30:55Z",
-- "width": "120",
-- "height": "120",
-- "photo": {}
-- },
-- "filename": "starfish.jpg"
-- },
-- {
-- "id": "AKcbugGwtCih2tdl3s1-NMvZFaco3W7XnRwvwtj02J9DJyl6JmrLZDVUFcxQl4AT04LaNrPpsrojeTsDSzOVOF5IgKefD0Y-MQ",
-- "description": "Photo of Penguins",
-- "productUrl": "https://photos.google.com/lr/photo/AKcbugGwtCih2tdl3s1-NMvZFaco3W7XnRwvwtj02J9DJyl6JmrLZDVUFcxQl4AT04LaNrPpsrojeTsDSzOVOF5IgKefD0Y-MQ",
-- "baseUrl": "https://lh3.googleusercontent.com/lr/AGWb-e5Cj3EgyhscdT...X3-W562iiKtTa8lc_QPxCrpJu-m7cZyNGwGTANVpGe1XG",
-- "mimeType": "image/jpeg",
-- "mediaMetadata": {
-- "creationTime": "2008-02-18T05:07:31Z",
-- "width": "1024",
-- "height": "768",
-- "photo": {}
-- },
-- "filename": "penguins.jpg"
-- }
-- ]
-- }
--
-- Use this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
DECLARE @i int
DECLARE @count_i int
DECLARE @id nvarchar(4000)
DECLARE @productUrl nvarchar(4000)
DECLARE @baseUrl nvarchar(4000)
DECLARE @mimeType nvarchar(4000)
DECLARE @mediaMetadataCreationTime nvarchar(4000)
DECLARE @mediaMetadataWidth nvarchar(4000)
DECLARE @mediaMetadataHeight nvarchar(4000)
DECLARE @mediaMetadataPhotoCameraMake nvarchar(4000)
DECLARE @mediaMetadataPhotoCameraModel nvarchar(4000)
DECLARE @mediaMetadataPhotoFocalLength nvarchar(4000)
DECLARE @mediaMetadataPhotoApertureFNumber nvarchar(4000)
DECLARE @mediaMetadataPhotoIsoEquivalent int
DECLARE @filename nvarchar(4000)
DECLARE @description nvarchar(4000)
SELECT @i = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'mediaItems'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @id OUT, 'mediaItems[i].id'
EXEC sp_OAMethod @json, 'StringOf', @productUrl OUT, 'mediaItems[i].productUrl'
EXEC sp_OAMethod @json, 'StringOf', @baseUrl OUT, 'mediaItems[i].baseUrl'
EXEC sp_OAMethod @json, 'StringOf', @mimeType OUT, 'mediaItems[i].mimeType'
EXEC sp_OAMethod @json, 'StringOf', @mediaMetadataCreationTime OUT, 'mediaItems[i].mediaMetadata.creationTime'
EXEC sp_OAMethod @json, 'StringOf', @mediaMetadataWidth OUT, 'mediaItems[i].mediaMetadata.width'
EXEC sp_OAMethod @json, 'StringOf', @mediaMetadataHeight OUT, 'mediaItems[i].mediaMetadata.height'
EXEC sp_OAMethod @json, 'StringOf', @mediaMetadataPhotoCameraMake OUT, 'mediaItems[i].mediaMetadata.photo.cameraMake'
EXEC sp_OAMethod @json, 'StringOf', @mediaMetadataPhotoCameraModel OUT, 'mediaItems[i].mediaMetadata.photo.cameraModel'
EXEC sp_OAMethod @json, 'StringOf', @mediaMetadataPhotoFocalLength OUT, 'mediaItems[i].mediaMetadata.photo.focalLength'
EXEC sp_OAMethod @json, 'StringOf', @mediaMetadataPhotoApertureFNumber OUT, 'mediaItems[i].mediaMetadata.photo.apertureFNumber'
EXEC sp_OAMethod @json, 'IntOf', @mediaMetadataPhotoIsoEquivalent OUT, 'mediaItems[i].mediaMetadata.photo.isoEquivalent'
EXEC sp_OAMethod @json, 'StringOf', @filename OUT, 'mediaItems[i].filename'
EXEC sp_OAMethod @json, 'StringOf', @description OUT, 'mediaItems[i].description'
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @json
END
GO