SQL Server
SQL Server
Google Cloud SQL - Get Instance Information
See more Google Cloud SQL Examples
Demonstrates how to get information about your Cloud SQL instances, including instance summary information, usage data, operation logs, and database logs.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
-- 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 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.StringBuilder', @sbToken OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
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.Http', @http OUT
-- 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.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.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