SQL Server
SQL Server
Inspect the Complete Set of Defined Variables
See more CURL Examples
This example shows how to inspect the complete set of defined variables after executing the curl dependency plan. The code that enumerates and displays all variables is located at the end of the example.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 shows how to inspect the complete set of defined variables
-- after executing the curl dependency plan.
-- The code that enumerates and displays all variables is located at the end of the example.
DECLARE @httpCurl int
EXEC @hr = sp_OACreate 'Chilkat.HttpCurl', @httpCurl OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- The final curl command we want to execute.
-- It depends on {{drive_id}}, which is not yet known.
DECLARE @targetCurl nvarchar(4000)
SELECT @targetCurl = 'curl -X GET https://graph.microsoft.com/v1.0/drives/{{drive_id}}/root/children'
-- Define a helper function that can produce drive_id.
-- This function lists drives for a given site, which requires {{site_id}}.
DECLARE @fnName nvarchar(4000)
SELECT @fnName = 'getDrives'
EXEC sp_OAMethod @httpCurl, 'AddFunction', @success OUT, @fnName, 'curl -X GET https://graph.microsoft.com/v1.0/sites/{{site_id}}/drives'
-- Define the output of getDrives.
-- The JSON response contains an array named "value".
-- We take the first element (value[0]) and extract its "id" as drive_id.
DECLARE @jsonPath nvarchar(4000)
SELECT @jsonPath = 'value[0].id'
EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, @jsonPath, 'drive_id'
-- drive_id depends on site_id, which is also not yet known.
-- Define another helper function to obtain site_id.
SELECT @fnName = 'getSite'
EXEC sp_OAMethod @httpCurl, 'AddFunction', @success OUT, @fnName, 'GET https://graph.microsoft.com/v1.0/sites/root:/sites/{{site_name}}'
-- Map the "id" field from the getSite response to the variable site_id.
SELECT @jsonPath = 'id'
EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, @jsonPath, 'site_id'
-- Provide the initial known input.
-- site_name is the starting value that allows the dependency chain to be resolved.
EXEC sp_OAMethod @httpCurl, 'SetVar', NULL, 'site_name', 'test'
-- ----------------------------------------------------------------------------------------------------------------------
-- Configure OAuth2 authentication using the client credentials flow.
-- Secrets (client_id, client_secret, token_endpoint) are retrieved from the
-- local secrets manager because EnableSecrets is enabled.
DECLARE @jsonOAuth2 int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonOAuth2 OUT
EXEC sp_OASetProperty @jsonOAuth2, 'EnableSecrets', 1
EXEC sp_OAMethod @jsonOAuth2, 'UpdateString', @success OUT, 'oauth2.client_id', '!!sharepoint|oauth2|client_id'
EXEC sp_OAMethod @jsonOAuth2, 'UpdateString', @success OUT, 'oauth2.client_secret', '!!sharepoint|oauth2|client_secret'
EXEC sp_OAMethod @jsonOAuth2, 'UpdateString', @success OUT, 'oauth2.scope', 'https://graph.microsoft.com/.default'
EXEC sp_OAMethod @jsonOAuth2, 'UpdateString', @success OUT, 'oauth2.token_endpoint', '!!sharepoint|oauth2|token_endpoint'
EXEC sp_OAMethod @httpCurl, 'SetAuth', @success OUT, @jsonOAuth2
-- ----------------------------------------------------------------------------------------------------------------------
-- Execute the target curl command.
-- Internally, the system will:
--
-- 1) Detect that {{drive_id}} is required but not defined.
-- 2) Find that "getDrives" can produce drive_id.
-- 3) Detect that getDrives requires {{site_id}}, which is also not defined.
-- 4) Find that "getSite" can produce site_id.
-- 5) Verify that getSite requires {{site_name}}, which is already known.
-- 6) Build a multi-step execution plan:
-- - First: run getSite (produces site_id)
-- - Second: run getDrives (produces drive_id)
-- - Third: run the target curl (uses drive_id)
EXEC sp_OAMethod @httpCurl, 'DoYourThing', @success OUT, @targetCurl
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @httpCurl, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @httpCurl
EXEC @hr = sp_OADestroy @jsonOAuth2
RETURN
END
-- Retrieve and display the HTTP response status code.
DECLARE @statusCode int
EXEC sp_OAGetProperty @httpCurl, 'StatusCode', @statusCode OUT
PRINT 'response status code: ' + @statusCode
-- Load and display the JSON response from the final curl command.
DECLARE @responseJson int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @responseJson OUT
EXEC sp_OASetProperty @responseJson, 'EmitCompact', 0
EXEC sp_OAMethod @httpCurl, 'GetResponseJson', @success OUT, @responseJson
EXEC sp_OAMethod @responseJson, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- ----------------------------------
-- Examine all defined variables.
-- ----------------------------------
DECLARE @definedVarsJson int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @definedVarsJson OUT
EXEC sp_OASetProperty @definedVarsJson, 'EmitCompact', 0
EXEC sp_OAMethod @httpCurl, 'GetAllVars', NULL, @definedVarsJson
EXEC sp_OAMethod @definedVarsJson, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Sample output:
-- {
-- "vars": [
-- {
-- "name": "site_name",
-- "value": "test"
-- },
-- {
-- "name": "site_id",
-- "value": "example.sharepoint.com,9b923c5e-5117-44ad-8b03-adbb8e19be85,b2451e19-290f-5f29-9f5d-674c2950a9f7"
-- },
-- {
-- "name": "drive_id",
-- "value": "b!XjySmxdRrrtLA827jhm-nRkeRbIPKSlPn11nTClQqfeVOj5VUvQMQae34rwOHdiP"
-- }
-- ]
-- }
-- Sample code to parse the above JSON.
DECLARE @i int
SELECT @i = 0
DECLARE @numVars int
EXEC sp_OAMethod @definedVarsJson, 'SizeOfArray', @numVars OUT, 'vars'
WHILE @i < @numVars
BEGIN
EXEC sp_OASetProperty @definedVarsJson, 'I', @i
EXEC sp_OAMethod @definedVarsJson, 'StringOf', @sTmp0 OUT, 'vars[i].name'
PRINT 'name: ' + @sTmp0
EXEC sp_OAMethod @definedVarsJson, 'StringOf', @sTmp0 OUT, 'vars[i].value'
PRINT 'value: ' + @sTmp0
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @httpCurl
EXEC @hr = sp_OADestroy @jsonOAuth2
EXEC @hr = sp_OADestroy @responseJson
EXEC @hr = sp_OADestroy @definedVarsJson
END
GO