SQL Server
SQL Server
OneNote - Create Page
See more OneNote Examples
Creates a new OneNote page with a rendered image and an attached PDF.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 @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.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- To create a OneNote page, we want to send an HTTP request like the following:
-- POST https://graph.microsoft.com/v1.0/me/onenote/sections/{section_id}/pages
-- Content-length: 312
-- Content-type: multipart/form-data; boundary=MyPartBoundary198374
--
-- --MyPartBoundary198374
-- Content-Disposition:form-data; name="Presentation"
-- Content-Type:text/html
--
-- <!DOCTYPE html>
-- <html>
-- <head>
-- <title>A page with <i>rendered</i> images and an <b>attached</b> file</title>
-- <meta name="created" content="2015-07-22T09:00:00-08:00" />
-- </head>
-- <body>
-- <p>Here's an image from an online source:</p>
-- <img src="https://..." alt="an image on the page" width="500" />
-- <p>Here's an image uploaded as binary data:</p>
-- <img src="name:imageBlock1" alt="an image on the page" width="300" />
-- <p>Here's a file attachment:</p>
-- <object data-attachment="FileName.pdf" data="name:fileBlock1" type="application/pdf" />
-- </body>
-- </html>
--
-- --MyPartBoundary198374
-- Content-Disposition:form-data; name="imageBlock1"
-- Content-Type:image/jpeg
--
-- ... binary image data ...
--
-- --MyPartBoundary198374
-- Content-Disposition:form-data; name="fileBlock1"
-- Content-Type:application/pdf
--
-- ... binary file data ...
--
-- --MyPartBoundary198374--
-- Build the request in a Chilkat HTTP request object:
DECLARE @req int
EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT
-- Our URL is https://graph.microsoft.com/v1.0/me/onenote/sections/{section_id}/pages
-- The path part of the URL is "/v1.0/me/onenote/sections/{section_id}/pages"
-- In this example, our section ID is "0-3A33FCEB9B74CC15!20350"
EXEC sp_OASetProperty @req, 'Path', '/v1.0/me/onenote/sections/0-3A33FCEB9B74CC15!20350/pages'
-- We'll be sending a POST.
EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
-- The Content-Type is multipart/form-data
-- Chilkat will automatically generate a boundary string.
EXEC sp_OASetProperty @req, 'ContentType', 'multipart/form-data'
-- When Chilkat HTTP was written many years ago, multipart requests were primarily for uploads.
-- Thus the names of methods that add a multipart section end with "ForUpload".
-- The multipart request we wish to build has 3 sections: text/html, image/jpeg, and application/pdf.
-- ------------------------------
-- Add the text/html part.
-- ------------------------------
DECLARE @sbHtml int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbHtml OUT
DECLARE @bCrlf int
SELECT @bCrlf = 1
EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, '<!DOCTYPE html>', @bCrlf
EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, '<html>', @bCrlf
EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, ' <head>', @bCrlf
EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, ' <title>A page with <i>rendered</i> images and an <b>attached</b> file</title>', @bCrlf
EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, ' <meta name="created" content="TIMESTAMP_CURRENT" />', @bCrlf
EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, ' </head>', @bCrlf
EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, ' <body>', @bCrlf
EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, ' <p>Here''s an image from an online source:</p>', @bCrlf
EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, ' <img src="https://www.chilkatsoft.com/images/starfish.jpg" alt="an image on the page" width="500" />', @bCrlf
EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, ' <p>Here''s an image uploaded as binary data:</p>', @bCrlf
EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, ' <img src="name:imageBlock1" alt="an image on the page" width="300" />', @bCrlf
EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, ' <p>Here''s a file attachment:</p>', @bCrlf
EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, ' <object data-attachment="FileName.pdf" data="name:fileBlock1" type="application/pdf" />', @bCrlf
EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, ' </body>', @bCrlf
EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, '</html>', @bCrlf
DECLARE @dtNow int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dtNow OUT
EXEC sp_OAMethod @dtNow, 'SetFromCurrentSystemTime', @success OUT
DECLARE @numReplaced int
EXEC sp_OAMethod @dtNow, 'GetAsTimestamp', @sTmp0 OUT, 1
EXEC sp_OAMethod @sbHtml, 'Replace', @numReplaced OUT, 'TIMESTAMP_CURRENT', @sTmp0
EXEC sp_OAMethod @sbHtml, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @req, 'AddStringForUpload2', @success OUT, 'Presentation', '', @sTmp0, 'utf-8', 'text/html'
-- ------------------------------
-- Add the JPG image.
-- ------------------------------
EXEC sp_OAMethod @req, 'AddFileForUpload2', @success OUT, 'imageBlock1', 'qa_data/jpg/penguins2.jpg', 'image/jpeg'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @req, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @sbHtml
EXEC @hr = sp_OADestroy @dtNow
RETURN
END
-- ------------------------------
-- Add the PDF attachment.
-- ------------------------------
DECLARE @bdPdf int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdPdf OUT
EXEC sp_OAMethod @bdPdf, 'LoadFile', @success OUT, 'qa_data/pdf/helloWorld.pdf'
IF @success = 0
BEGIN
PRINT 'Failed to load PDF file.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @sbHtml
EXEC @hr = sp_OADestroy @dtNow
EXEC @hr = sp_OADestroy @bdPdf
RETURN
END
EXEC sp_OAMethod @req, 'AddBdForUpload', @success OUT, 'fileBlock1', 'FileName.pdf', @bdPdf, 'application/pdf'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @req, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @sbHtml
EXEC @hr = sp_OADestroy @dtNow
EXEC @hr = sp_OADestroy @bdPdf
RETURN
END
-- Adds the "Authorization: Bearer ACCESS_TOKEN" header.
EXEC sp_OASetProperty @http, 'AuthToken', 'ACCESS_TOKEN'
-- POST to https://graph.microsoft.com/v1.0/me/onenote/sections/{section_id}/pages
-- The path part of the URL is already specified in the req object.
-- We only need to specify the domain and the fact that we're doing "https" (SSL/TLS).
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpSReq', @success OUT, 'graph.microsoft.com', 443, 1, @req, @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @sbHtml
EXEC @hr = sp_OADestroy @dtNow
EXEC @hr = sp_OADestroy @bdPdf
EXEC @hr = sp_OADestroy @resp
RETURN
END
DECLARE @sbResponseBody int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT
EXEC sp_OAMethod @resp, 'GetBodySb', @success OUT, @sbResponseBody
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
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Response status code: ' + @iTmp0
PRINT 'Response Body:'
EXEC sp_OAMethod @jResp, 'Emit', @sTmp0 OUT
PRINT @sTmp0
DECLARE @respStatusCode int
EXEC sp_OAGetProperty @resp, 'StatusCode', @respStatusCode OUT
PRINT 'Response Status Code = ' + @respStatusCode
IF @respStatusCode >= 400
BEGIN
PRINT 'Response Header:'
EXEC sp_OAGetProperty @resp, 'Header', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @sbHtml
EXEC @hr = sp_OADestroy @dtNow
EXEC @hr = sp_OADestroy @bdPdf
EXEC @hr = sp_OADestroy @resp
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)
-- {
-- "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('admin%40chilkat.io')/onenote/sections('0-3A33FCEB9B74CC15%2120350')/pages/$entity",
-- "id": "0-18ac61117a664f7e946bcceaeebd6f57!36-3A33FCEB9B74CC15!20350",
-- "self": "https://graph.microsoft.com/v1.0/users/admin@chilkat.io/onenote/pages/0-18ac61117a664f7e946bcceaeebd6f57!36-3A33FCEB9B74CC15!20350",
-- "createdDateTime": "2020-10-22T19:02:12Z",
-- "title": "A page with rendered images and an attached file",
-- "createdByAppId": "WLID-00000000441C9990",
-- "contentUrl": "https://graph.microsoft.com/v1.0/users/admin@chilkat.io/onenote/pages/0-18ac61117a664f7e946bcceaeebd6f57!36-3A33FCEB9B74CC15!20350/content",
-- "lastModifiedDateTime": "2020-10-23T00:02:13.3254289Z",
-- "links": {
-- "oneNoteClientUrl": {
-- "href": "onenote:https://d.docs.live.net/3a33fceb9b74cc15/Documents/Testing%20Notebook/Ddd.one#A%20page%20with%20rendered%20images%20and%20an%20attached%20file§ion-id=9d78c221-486e-45f8-8355-1810e475f6c0&page-id=36cd1982-1ef1-4b11-b5a1-30b3dbc43d05&end"
-- },
-- "oneNoteWebUrl": {
-- "href": "https://onedrive.live.com/redir.aspx?cid=3a33fceb9b74cc15&page=edit&resid=3A33FCEB9B74CC15!20344&parId=3A33FCEB9B74CC15!187&wd=target%28Ddd.one%7C9d78c221-486e-45f8-8355-1810e475f6c0%2FA%20page%20with%20rendered%20images%20and%20an%20attached%20file%7C36cd1982-1ef1-4b11-b5a1-30b3dbc43d05%2F%29"
-- }
-- }
-- }
-- 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 @odata_context nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @odata_context OUT, '"@odata.context"'
DECLARE @id nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @id OUT, 'id'
DECLARE @self nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @self OUT, 'self'
DECLARE @createdDateTime nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @createdDateTime OUT, 'createdDateTime'
DECLARE @title nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @title OUT, 'title'
DECLARE @createdByAppId nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @createdByAppId OUT, 'createdByAppId'
DECLARE @contentUrl nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @contentUrl OUT, 'contentUrl'
DECLARE @lastModifiedDateTime nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @lastModifiedDateTime OUT, 'lastModifiedDateTime'
DECLARE @linksOneNoteClientUrlHref nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @linksOneNoteClientUrlHref OUT, 'links.oneNoteClientUrl.href'
DECLARE @linksOneNoteWebUrlHref nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @linksOneNoteWebUrlHref OUT, 'links.oneNoteWebUrl.href'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @sbHtml
EXEC @hr = sp_OADestroy @dtNow
EXEC @hr = sp_OADestroy @bdPdf
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jResp
END
GO