(SQL Server) Google Cloud SQL - Get Instance Information

Demonstrates how to get information about your Cloud SQL instances, including instance summary information, usage data, operation logs, and database logs.

For more information, see https://cloud.google.com/sql/docs/mysql/instance-info

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 @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- In this example, Get Google Cloud SQL OAuth2 Access Token, 
    -- the service account access token was saved to a text file.  This example fetches the access token from the file..
    DECLARE @sbToken int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbToken OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @sbToken, 'LoadFile', @success OUT, 'qa_data/tokens/google_cloud_sql_access_token.txt', 'utf-8'

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Http', @http OUT

    DECLARE @success int

    -- Implements the following CURL command:

    -- curl -X GET \
    -- -H "Authorization: Bearer access-token" \
    -- https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

    -- Use the following online tool to generate HTTP code from a CURL command
    -- Convert a cURL Command to HTTP Source Code

    -- Causes the "Authorization: Bearer "$(gcloud auth print-access-token)" header to be added.
    EXEC sp_OAMethod @sbToken, 'GetAsString', @sTmp0 OUT
    EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0

    -- Replace "project-id" with your actual Google project ID.
    -- Replace "instance-id" with your database instance ID, which is the name of your database.  (For example, when I created my test database I named it "chilkat", and therefore my instance-id is "chilkat".)
    DECLARE @sbResponseBody int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id', @sbResponseBody
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbToken
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

    DECLARE @jResp int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @jResp OUT

    EXEC sp_OAMethod @jResp, 'LoadSb', @success OUT, @sbResponseBody
    EXEC sp_OASetProperty @jResp, 'EmitCompact', 0


    PRINT 'Response Body:'
    EXEC sp_OAMethod @jResp, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @respStatusCode int
    EXEC sp_OAGetProperty @http, 'LastStatus', @respStatusCode OUT

    PRINT 'Response Status Code = ' + @respStatusCode
    IF @respStatusCode = 401
      BEGIN

        PRINT 'It may be that your access token expired.'

        PRINT 'Try refreshing the access token by re-fetching it.'
      END
    IF @respStatusCode >= 400
      BEGIN

        PRINT 'Response Header:'
        EXEC sp_OAGetProperty @http, 'LastHeader', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @sbToken
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbResponseBody
        EXEC @hr = sp_OADestroy @jResp
        RETURN
      END

    -- Sample JSON response:
    -- (Sample code for parsing the JSON response is shown below)

    -- {
    --   "kind": "sql#instance",
    --   "state": "RUNNABLE",
    --   "databaseVersion": "MYSQL_5_7",
    --   "settings": {
    --     "authorizedGaeApplications": [
    --     ],
    --     "tier": "db-f1-micro",
    --     "kind": "sql#settings",
    --     "pricingPlan": "PER_USE",
    --     "replicationType": "SYNCHRONOUS",
    --     "activationPolicy": "ALWAYS",
    --     "ipConfiguration": {
    --       "authorizedNetworks": [
    --       ],
    --       "ipv4Enabled": true
    --     },
    --     "locationPreference": {
    --       "zone": "asia-east1-a",
    --       "kind": "sql#locationPreference"
    --     },
    --     "dataDiskType": "PD_SSD",
    --     "backupConfiguration": {
    --       "startTime": "08:00",
    --       "kind": "sql#backupConfiguration",
    --       "enabled": true,
    --       "pointInTimeEnabled": true
    --     },
    --     "settingsVersion": "13",
    --     "storageAutoResizeLimit": "0",
    --     "storageAutoResize": true,
    --     "dataDiskSizeGb": "10"
    --   },
    --   "etag": "etag-id",
    --   "ipAddresses": [
    --     {
    --       "type": "PRIMARY",
    --       "ipAddress": "10.0.0.1"
    --     }
    --   ],
    --   "serverCaCert": {
    --     "kind": "sql#sslCert",
    --     "certSerialNumber": "0",
    --     "cert": "certificate-id",
    --     "commonName": "C=US,O=Google\\, Inc,CN=Google Cloud SQL Server CA,dnQualifier=dn-qualifier-id",
    --     "sha1Fingerprint": "sha-id",
    --     "instance": "instance-id",
    --     "createTime": "2019-06-28T22:46:35.052Z",
    --     "expirationTime": "2029-06-25T22:47:35.052Z"
    --   },
    --   "instanceType": "CLOUD_SQL_INSTANCE",
    --   "project": "project-id",
    --   "serviceAccountEmailAddress": "service-acct-id@gcp-sa-cloud-sql.iam.gserviceaccount.com",
    --   "backendType": "SECOND_GEN",
    --   "selfLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",
    --   "connectionName": "project-id:region:instance-id",
    --   "name": "instance-id",
    --   "region": "asia-east1",
    --   "gceZone": "asia-east1-a"
    -- }

    -- Sample code for parsing the JSON response...
    -- Use the following online tool to generate parsing code from sample JSON:
    -- Generate Parsing Code from JSON

    DECLARE @v_type nvarchar(4000)

    DECLARE @ipAddress nvarchar(4000)

    DECLARE @kind nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @kind OUT, 'kind'
    DECLARE @state nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @state OUT, 'state'
    DECLARE @databaseVersion nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @databaseVersion OUT, 'databaseVersion'
    DECLARE @settingsTier nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @settingsTier OUT, 'settings.tier'
    DECLARE @settingsKind nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @settingsKind OUT, 'settings.kind'
    DECLARE @settingsPricingPlan nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @settingsPricingPlan OUT, 'settings.pricingPlan'
    DECLARE @settingsReplicationType nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @settingsReplicationType OUT, 'settings.replicationType'
    DECLARE @settingsActivationPolicy nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @settingsActivationPolicy OUT, 'settings.activationPolicy'
    DECLARE @settingsIpConfigurationIpv4Enabled int
    EXEC sp_OAMethod @jResp, 'BoolOf', @settingsIpConfigurationIpv4Enabled OUT, 'settings.ipConfiguration.ipv4Enabled'
    DECLARE @settingsLocationPreferenceZone nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @settingsLocationPreferenceZone OUT, 'settings.locationPreference.zone'
    DECLARE @settingsLocationPreferenceKind nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @settingsLocationPreferenceKind OUT, 'settings.locationPreference.kind'
    DECLARE @settingsDataDiskType nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @settingsDataDiskType OUT, 'settings.dataDiskType'
    DECLARE @settingsBackupConfigurationStartTime nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @settingsBackupConfigurationStartTime OUT, 'settings.backupConfiguration.startTime'
    DECLARE @settingsBackupConfigurationKind nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @settingsBackupConfigurationKind OUT, 'settings.backupConfiguration.kind'
    DECLARE @settingsBackupConfigurationEnabled int
    EXEC sp_OAMethod @jResp, 'BoolOf', @settingsBackupConfigurationEnabled OUT, 'settings.backupConfiguration.enabled'
    DECLARE @settingsBackupConfigurationPointInTimeEnabled int
    EXEC sp_OAMethod @jResp, 'BoolOf', @settingsBackupConfigurationPointInTimeEnabled OUT, 'settings.backupConfiguration.pointInTimeEnabled'
    DECLARE @settingsSettingsVersion nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @settingsSettingsVersion OUT, 'settings.settingsVersion'
    DECLARE @settingsStorageAutoResizeLimit nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @settingsStorageAutoResizeLimit OUT, 'settings.storageAutoResizeLimit'
    DECLARE @settingsStorageAutoResize int
    EXEC sp_OAMethod @jResp, 'BoolOf', @settingsStorageAutoResize OUT, 'settings.storageAutoResize'
    DECLARE @settingsDataDiskSizeGb nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @settingsDataDiskSizeGb OUT, 'settings.dataDiskSizeGb'
    DECLARE @etag nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @etag OUT, 'etag'
    DECLARE @serverCaCertKind nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @serverCaCertKind OUT, 'serverCaCert.kind'
    DECLARE @serverCaCertCertSerialNumber nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @serverCaCertCertSerialNumber OUT, 'serverCaCert.certSerialNumber'
    DECLARE @serverCaCertCert nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @serverCaCertCert OUT, 'serverCaCert.cert'
    DECLARE @serverCaCertCommonName nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @serverCaCertCommonName OUT, 'serverCaCert.commonName'
    DECLARE @serverCaCertSha1Fingerprint nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @serverCaCertSha1Fingerprint OUT, 'serverCaCert.sha1Fingerprint'
    DECLARE @serverCaCertInstance nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @serverCaCertInstance OUT, 'serverCaCert.instance'
    DECLARE @serverCaCertCreateTime nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @serverCaCertCreateTime OUT, 'serverCaCert.createTime'
    DECLARE @serverCaCertExpirationTime nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @serverCaCertExpirationTime OUT, 'serverCaCert.expirationTime'
    DECLARE @instanceType nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @instanceType OUT, 'instanceType'
    DECLARE @project nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @project OUT, 'project'
    DECLARE @serviceAccountEmailAddress nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @serviceAccountEmailAddress OUT, 'serviceAccountEmailAddress'
    DECLARE @backendType nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @backendType OUT, 'backendType'
    DECLARE @selfLink nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @selfLink OUT, 'selfLink'
    DECLARE @connectionName nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @connectionName OUT, 'connectionName'
    DECLARE @name nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @name OUT, 'name'
    DECLARE @region nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @region OUT, 'region'
    DECLARE @gceZone nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @gceZone OUT, 'gceZone'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'settings.authorizedGaeApplications'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'settings.ipConfiguration.authorizedNetworks'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'ipAddresses'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        EXEC sp_OAMethod @jResp, 'StringOf', @v_type OUT, 'ipAddresses[i].type'
        EXEC sp_OAMethod @jResp, 'StringOf', @ipAddress OUT, 'ipAddresses[i].ipAddress'
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @sbToken
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @jResp


END
GO