SQL Server
SQL Server
Invalidating Dependent Variables When Inputs Change
See more CURL Examples
This example demonstrates how HttpCurl automatically updates the execution plan when a known variable is cleared or changed.
After a multi-step execution plan runs successfully, variables such as site_id and drive_id may become known. Later calls to DoYourThing can reuse those values, resulting in a shorter execution plan.
However, if an input variable is cleared using ClearVar, or changed using SetVar, any variables that depend on that value are automatically invalidated. This ensures that stale values are not reused.
For example, if site_name changes, then the previously resolved site_id is no longer valid. Because drive_id depends on site_id, it is also invalidated. The next execution plan is rebuilt to resolve the dependency chain again using the new input value.
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
DECLARE @httpCurl int
EXEC @hr = sp_OACreate 'Chilkat.HttpCurl', @httpCurl OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- The target curl command we ultimately want to execute.
-- It requires drive_id.
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 produces drive_id.
-- This 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'
EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, 'value[0].id', 'drive_id'
-- Define another helper function that produces site_id.
-- This requires site_name.
SELECT @fnName = 'getSite'
EXEC sp_OAMethod @httpCurl, 'AddFunction', @success OUT, @fnName, 'curl -X GET https://graph.microsoft.com/v1.0/sites/root:/sites/{{site_name}}'
EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, 'id', 'site_id'
-- site_name is the starting known value.
EXEC sp_OAMethod @httpCurl, 'SetVar', NULL, 'site_name', 'test'
-- Configure OAuth2 authentication.
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
-- -----------------------------------------------------------------------------
-- First execution plan:
-- site_id and drive_id are not known yet, so the full dependency chain is needed.
-- -----------------------------------------------------------------------------
DECLARE @planJson int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @planJson OUT
EXEC sp_OASetProperty @planJson, 'EmitCompact', 0
PRINT 'Execution plan before first call:'
EXEC sp_OAMethod @httpCurl, 'ExaminePlan', @success OUT, @targetCurl, @planJson
EXEC sp_OAMethod @planJson, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Expected:
--
-- {
-- "plan": [{
-- "function": "getSite",
-- "inputs": ["site_name"],
-- "outputs": ["site_id"]
-- },{
-- "function": "getDrives",
-- "inputs": ["site_id"],
-- "outputs": ["drive_id"]
-- },{
-- "function": "targetCurl",
-- "inputs": ["drive_id"],
-- "outputs": []
-- }]
-- }
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
EXEC @hr = sp_OADestroy @planJson
RETURN
END
PRINT 'After first call:'
EXEC sp_OAMethod @httpCurl, 'GetVar', @sTmp0 OUT, 'site_id'
PRINT 'site_id = ' + @sTmp0
EXEC sp_OAMethod @httpCurl, 'GetVar', @sTmp0 OUT, 'drive_id'
PRINT 'drive_id = ' + @sTmp0
-- -----------------------------------------------------------------------------
-- Second execution plan:
-- site_id and drive_id are now known, so only the target curl command is needed.
-- -----------------------------------------------------------------------------
PRINT 'Execution plan after first call:'
EXEC sp_OAMethod @httpCurl, 'ExaminePlan', @success OUT, @targetCurl, @planJson
EXEC sp_OAMethod @planJson, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Expected:
--
-- {
-- "plan": [{
-- "function": "targetCurl",
-- "inputs": ["drive_id"],
-- "outputs": []
-- }]
-- }
-- -----------------------------------------------------------------------------
-- Change the original input.
-- Because site_id was produced from site_name, changing site_name invalidates site_id.
-- Because drive_id depends on site_id, drive_id is also invalidated.
-- -----------------------------------------------------------------------------
-- Note: Make sure this site exists on in your SharePoint...
EXEC sp_OAMethod @httpCurl, 'SetVar', NULL, 'site_name', 'anotherSite'
PRINT 'After changing site_name:'
EXEC sp_OAMethod @httpCurl, 'VarDefined', @iTmp0 OUT, 'site_id'
PRINT 'site_id defined? ' + @iTmp0
EXEC sp_OAMethod @httpCurl, 'VarDefined', @iTmp0 OUT, 'drive_id'
PRINT 'drive_id defined? ' + @iTmp0
-- -----------------------------------------------------------------------------
-- The execution plan is automatically rebuilt.
-- Since site_id and drive_id were invalidated, the full dependency chain is needed again.
-- -----------------------------------------------------------------------------
PRINT 'Execution plan after changing site_name:'
EXEC sp_OAMethod @httpCurl, 'ExaminePlan', @success OUT, @targetCurl, @planJson
EXEC sp_OAMethod @planJson, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Expected:
--
-- {
-- "plan": [{
-- "function": "getSite",
-- "inputs": ["site_name"],
-- "outputs": ["site_id"]
-- },{
-- "function": "getDrives",
-- "inputs": ["site_id"],
-- "outputs": ["drive_id"]
-- },{
-- "function": "targetCurl",
-- "inputs": ["drive_id"],
-- "outputs": []
-- }]
-- }
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
EXEC @hr = sp_OADestroy @planJson
RETURN
END
PRINT 'After running again with the new site_name:'
EXEC sp_OAMethod @httpCurl, 'GetVar', @sTmp0 OUT, 'site_id'
PRINT 'site_id = ' + @sTmp0
EXEC sp_OAMethod @httpCurl, 'GetVar', @sTmp0 OUT, 'drive_id'
PRINT 'drive_id = ' + @sTmp0
-- -----------------------------------------------------------------------------
-- Clearing a variable also invalidates values that depend on it.
-- Here, clearing site_id also invalidates drive_id.
-- -----------------------------------------------------------------------------
EXEC sp_OAMethod @httpCurl, 'ClearVar', NULL, 'site_id'
PRINT 'After clearing site_id:'
EXEC sp_OAMethod @httpCurl, 'VarDefined', @iTmp0 OUT, 'site_id'
PRINT 'site_id defined? ' + @iTmp0
EXEC sp_OAMethod @httpCurl, 'VarDefined', @iTmp0 OUT, 'drive_id'
PRINT 'drive_id defined? ' + @iTmp0
PRINT 'Execution plan after clearing site_id:'
EXEC sp_OAMethod @httpCurl, 'ExaminePlan', @success OUT, @targetCurl, @planJson
EXEC sp_OAMethod @planJson, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Expected:
--
-- {
-- "plan": [{
-- "function": "getSite",
-- "inputs": ["site_name"],
-- "outputs": ["site_id"]
-- },{
-- "function": "getDrives",
-- "inputs": ["site_id"],
-- "outputs": ["drive_id"]
-- },{
-- "function": "targetCurl",
-- "inputs": ["drive_id"],
-- "outputs": []
-- }]
-- }
EXEC @hr = sp_OADestroy @httpCurl
EXEC @hr = sp_OADestroy @jsonOAuth2
EXEC @hr = sp_OADestroy @planJson
END
GO