SQL Server
SQL Server
REST Download Bandwidth Throttle
See more REST Examples
Demonstrates how to use download bandwidth throttling with the REST API. This example will download a file from Drobox using a file stream, with a limit on the bandwidth that can be used for the transfer.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
DECLARE @iTmp1 int
DECLARE @iTmp2 int
DECLARE @iTmp3 int
DECLARE @iTmp4 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.
-- A Dropbox access token should have been previously obtained.
-- Dropbox access tokens do not expire.
-- See Dropbox Access Token.
-- To use bandwidth throttling, the connection should be made using the socket API.
-- This provides numerous properties to customize the connection, such as
-- BandwidthThrottleDown, BandwidthThrottleUp, ClientIpAddress, ClintPort, Http Proxy,
-- KeepAlive, PreferIpv6, RequireSslCertVerify, SoRcvBuf, SoSndBuf, SoReuseAddr,
-- SOCKS proxy, TcpNoSDelay, TlsPinSet, TlsCipherSuite, SslAllowedCiphers, etc.
DECLARE @socket int
EXEC @hr = sp_OACreate 'Chilkat.Socket', @socket OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @maxWaitMs int
SELECT @maxWaitMs = 5000
EXEC sp_OAMethod @socket, 'Connect', @success OUT, 'content.dropboxapi.com', 443, 1, @maxWaitMs
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @socket, 'ConnectFailReason', @iTmp0 OUT
PRINT 'Connect Fail Reason: ' + @iTmp0
EXEC @hr = sp_OADestroy @socket
RETURN
END
-- Set the download bandwidth throttle rate to 50000 bytes per second.
EXEC sp_OASetProperty @socket, 'BandwidthThrottleDown', 50000
DECLARE @rest int
EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
-- Tell the REST object to use the connected socket.
EXEC sp_OAMethod @rest, 'UseConnection', @success OUT, @socket, 1
-- The remainder of this example is identical to the example at:
-- Dropbox Download File.
-- Add request headers.
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Authorization', 'Bearer DROPBOX_ACCESS_TOKEN'
-- The download "parameters" are contained in JSON passed in an HTTP request header.
-- This is the JSON indicating the file to be downloaded:
-- {
-- "path": "/Homework/lit/hamlet.xml",
-- }
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'AppendString', @success OUT, 'path', '/Homework/lit/hamlet.xml'
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Dropbox-API-Arg', @sTmp0
-- Setup a file stream for the download
DECLARE @fileStream int
EXEC @hr = sp_OACreate 'Chilkat.Stream', @fileStream OUT
EXEC sp_OASetProperty @fileStream, 'SinkFile', 'qa_output/hamletFromDropbox.xml'
-- Indicate that the call to FullRequestNoBody should send the response body
-- to fileStream if the response status code is 200.
-- If a non-success response status code is received, then nothing
-- is streamed to the output file and the error response is returned by FullRequestNoBody.
DECLARE @expectedStatus int
SELECT @expectedStatus = 200
EXEC sp_OAMethod @rest, 'SetResponseBodyStream', @success OUT, @expectedStatus, 1, @fileStream
DECLARE @responseStr nvarchar(4000)
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseStr OUT, 'POST', '/2/files/download'
EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @socket
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @fileStream
RETURN
END
-- When successful, Dropbox responds with a 200 response code.
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
-- Examine the request/response to see what happened.
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
PRINT 'response status code = ' + @iTmp0
EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
PRINT 'response status text = ' + @sTmp0
EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
PRINT 'response header: ' + @sTmp0
PRINT 'response body (if any): ' + @responseStr
PRINT '---'
EXEC sp_OAGetProperty @rest, 'LastRequestStartLine', @sTmp0 OUT
PRINT 'LastRequestStartLine: ' + @sTmp0
EXEC sp_OAGetProperty @rest, 'LastRequestHeader', @sTmp0 OUT
PRINT 'LastRequestHeader: ' + @sTmp0
EXEC @hr = sp_OADestroy @socket
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @fileStream
RETURN
END
-- Information about the downloaded file is also available as JSON in a response header.
-- The "dropbox-api-result" response header contains the information. For example:
DECLARE @apiResult nvarchar(4000)
EXEC sp_OAMethod @rest, 'ResponseHdrByName', @apiResult OUT, 'dropbox-api-result'
PRINT @apiResult
-- In this case, the pretty-formatted dropbox-api-result JSON looks like this:
-- {
-- "name": "hamlet.xml",
-- "path_lower": "/homework/lit/hamlet.xml",
-- "path_display": "/Homework/lit/hamlet.xml",
-- "id": "id:74FkdeNuyKAAAAAAAAAAAQ",
-- "client_modified": "2016-06-02T23:19:00Z",
-- "server_modified": "2016-06-02T23:19:00Z",
-- "rev": "9482db15f",
-- "size": 279658
-- }
-- Load the JSON, pretty-print it, and demonstrate how to get some values...
DECLARE @jsonResult int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResult OUT
EXEC sp_OASetProperty @jsonResult, 'EmitCompact', 0
EXEC sp_OAMethod @jsonResult, 'Load', @success OUT, @apiResult
-- Show the JSON pretty-printed...
EXEC sp_OAMethod @jsonResult, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Sample code to get data from the JSON response:
DECLARE @size int
EXEC sp_OAMethod @jsonResult, 'IntOf', @size OUT, 'size'
PRINT 'size = ' + @size
DECLARE @rev nvarchar(4000)
EXEC sp_OAMethod @jsonResult, 'StringOf', @rev OUT, 'rev'
PRINT 'rev = ' + @rev
DECLARE @clientModified nvarchar(4000)
EXEC sp_OAMethod @jsonResult, 'StringOf', @clientModified OUT, 'client_modified'
DECLARE @ckdt int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @ckdt OUT
EXEC sp_OAMethod @ckdt, 'SetFromTimestamp', @success OUT, @clientModified
DECLARE @bLocalTime int
SELECT @bLocalTime = 1
DECLARE @dt int
EXEC @hr = sp_OACreate 'Chilkat.DtObj', @dt OUT
EXEC sp_OAMethod @ckdt, 'ToDtObj', NULL, @bLocalTime, @dt
EXEC sp_OAGetProperty @dt, 'Day', @iTmp0 OUT
EXEC sp_OAGetProperty @dt, 'Month', @iTmp1 OUT
EXEC sp_OAGetProperty @dt, 'Year', @iTmp2 OUT
EXEC sp_OAGetProperty @dt, 'Hour', @iTmp3 OUT
EXEC sp_OAGetProperty @dt, 'Minute', @iTmp4 OUT
PRINT @iTmp0 + '/' + @iTmp1 + '/' + @iTmp2 + ' ' + @iTmp3 + ':' + @iTmp4
EXEC @hr = sp_OADestroy @socket
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @fileStream
EXEC @hr = sp_OADestroy @jsonResult
EXEC @hr = sp_OADestroy @ckdt
EXEC @hr = sp_OADestroy @dt
END
GO