(SQL Server) WordPress API Key Authentication with miniOrange Plugin

See more WordPress Examples

Demonstrates API key authentication using the miniOrange API Authentication plugin.

For more information, see https://wordpress.org/plugins/wp-rest-api-authentication/

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    -- Use your API key here, such as TaVFjSBu8IMR0MbvZNn7A6P04GXrbtHm
    -- This causes the "Authorization: Bearer <api_key>" header to be added to each HTTP request.
    EXEC sp_OASetProperty @http, 'AuthToken', 'api_key'

    DECLARE @sbResponseBody int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://www.yoursite.com/wp-json/wp/v2/posts?page=1', @sbResponseBody
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

    DECLARE @jarrResp int
    -- Use "Chilkat_9_5_0.JsonArray" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @jarrResp OUT

    EXEC sp_OAMethod @jarrResp, 'LoadSb', @success OUT, @sbResponseBody
    EXEC sp_OASetProperty @jarrResp, 'EmitCompact', 0


    PRINT 'Response Body:'
    EXEC sp_OAMethod @jarrResp, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @respStatusCode int
    EXEC sp_OAGetProperty @http, 'LastStatus', @respStatusCode OUT

    PRINT 'Response Status Code = ' + @respStatusCode
    IF @respStatusCode >= 400
      BEGIN

        PRINT 'Response Header:'
        EXEC sp_OAGetProperty @http, 'LastHeader', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbResponseBody
        EXEC @hr = sp_OADestroy @jarrResp
        RETURN
      END

    -- Sample JSON response:
    -- (Sample code for parsing the JSON response is shown below)

    -- [
    --   {
    --     "id": 1902,
    --     "date": "2020-11-16T09:54:09",
    --     "date_gmt": "2020-11-16T16:54:09",
    --     "guid": {
    --       "rendered": "http:\/\/cknotes.com\/?p=1902"
    --     },
    --     "modified": "2020-11-16T09:54:09",
    --     "modified_gmt": "2020-11-16T16:54:09",
    --     "slug": "xero-redirect-uri-for-oauth2-and-desktop-apps",
    --     "status": "publish",
    --     "type": "post",
    --     "link": "https:\/\/cknotes.com\/xero-redirect-uri-for-oauth2-and-desktop-apps\/",
    --     "title": {
    --       "rendered": "Xero Redirect URI for OAuth2 and Desktop Apps"
    --     },
    --     "content": {
    --       "rendered": "<p>...",
    --       "protected": false
    --     },
    --     "excerpt": {
    --       "rendered": "<p>...",
    --       "protected": false
    --     },
    --     "author": 1,
    --     "featured_media": 0,
    --     "comment_status": "closed",
    --     "ping_status": "open",
    --     "sticky": false,
    --     "template": "",
    --     "format": "standard",
    --     "meta": [
    --     ],
    --     "categories": [
    --       815
    --     ],
    --     "tags": [
    --       594,
    --       816
    --     ],
    --     "_links": {
    --       "self": [
    --         {
    --           "href": "https:\/\/cknotes.com\/wp-json\/wp\/v2\/posts\/1902"
    --         }
    --       ],
    --       "collection": [
    --         {
    --           "href": "https:\/\/cknotes.com\/wp-json\/wp\/v2\/posts"
    --         }
    --       ],
    --       "about": [
    --         {
    --           "href": "https:\/\/cknotes.com\/wp-json\/wp\/v2\/types\/post"
    --         }
    --       ],
    --       "author": [
    --         {
    --           "embeddable": true,
    --           "href": "https:\/\/cknotes.com\/wp-json\/wp\/v2\/users\/1"
    --         }
    --       ],
    --       "replies": [
    --         {
    --           "embeddable": true,
    --           "href": "https:\/\/cknotes.com\/wp-json\/wp\/v2\/comments?post=1902"
    --         }
    --       ],
    --       "version-history": [
    --         {
    --           "count": 1,
    --           "href": "https:\/\/cknotes.com\/wp-json\/wp\/v2\/posts\/1902\/revisions"
    --         }
    --       ],
    --       "predecessor-version": [
    --         {
    --           "id": 1904,
    --           "href": "https:\/\/cknotes.com\/wp-json\/wp\/v2\/posts\/1902\/revisions\/1904"
    --         }
    --       ],
    --       "wp:attachment": [
    --         {
    --           "href": "https:\/\/cknotes.com\/wp-json\/wp\/v2\/media?parent=1902"
    --         }
    --       ],
    --       "wp:term": [
    --         {
    --           "taxonomy": "category",
    --           "embeddable": true,
    --           "href": "https:\/\/cknotes.com\/wp-json\/wp\/v2\/categories?post=1902"
    --         },
    --         {
    --           "taxonomy": "post_tag",
    --           "embeddable": true,
    --           "href": "https:\/\/cknotes.com\/wp-json\/wp\/v2\/tags?post=1902"
    --         }
    --       ],
    --       "curies": [
    --         {
    --           "name": "wp",
    --           "href": "https:\/\/api.w.org\/{rel}",
    --           "templated": true
    --         }
    --       ]
    --     }
    --   },
    -- ...
    -- ]

    -- 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 @date_gmt int
    -- Use "Chilkat_9_5_0.DtObj" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.DtObj', @date_gmt OUT

    DECLARE @json int

    DECLARE @id int

    DECLARE @date nvarchar(4000)

    DECLARE @guidRendered nvarchar(4000)

    DECLARE @modified nvarchar(4000)

    DECLARE @modified_gmt nvarchar(4000)

    DECLARE @slug nvarchar(4000)

    DECLARE @status nvarchar(4000)

    DECLARE @v_type nvarchar(4000)

    DECLARE @link nvarchar(4000)

    DECLARE @titleRendered nvarchar(4000)

    DECLARE @contentRendered nvarchar(4000)

    DECLARE @contentProtected int

    DECLARE @excerptRendered nvarchar(4000)

    DECLARE @excerptProtected int

    DECLARE @author int

    DECLARE @featured_media int

    DECLARE @comment_status nvarchar(4000)

    DECLARE @ping_status nvarchar(4000)

    DECLARE @sticky int

    DECLARE @template nvarchar(4000)

    DECLARE @format nvarchar(4000)

    DECLARE @j int

    DECLARE @count_j int

    DECLARE @intVal int

    DECLARE @href nvarchar(4000)

    DECLARE @embeddable int

    DECLARE @count int

    DECLARE @taxonomy nvarchar(4000)

    DECLARE @name nvarchar(4000)

    DECLARE @templated int

    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAGetProperty @jarrResp, 'Size', @count_i OUT
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OAMethod @jarrResp, 'ObjectAt', @json OUT, @i
        EXEC sp_OAMethod @json, 'IntOf', @id OUT, 'id'
        EXEC sp_OAMethod @json, 'StringOf', @date OUT, 'date'
        EXEC sp_OAMethod @json, 'DtOf', @success OUT, 'date_gmt', 0, @date_gmt
        EXEC sp_OAMethod @json, 'StringOf', @guidRendered OUT, 'guid.rendered'
        EXEC sp_OAMethod @json, 'StringOf', @modified OUT, 'modified'
        EXEC sp_OAMethod @json, 'StringOf', @modified_gmt OUT, 'modified_gmt'
        EXEC sp_OAMethod @json, 'StringOf', @slug OUT, 'slug'
        EXEC sp_OAMethod @json, 'StringOf', @status OUT, 'status'
        EXEC sp_OAMethod @json, 'StringOf', @v_type OUT, 'type'
        EXEC sp_OAMethod @json, 'StringOf', @link OUT, 'link'
        EXEC sp_OAMethod @json, 'StringOf', @titleRendered OUT, 'title.rendered'
        EXEC sp_OAMethod @json, 'StringOf', @contentRendered OUT, 'content.rendered'
        EXEC sp_OAMethod @json, 'BoolOf', @contentProtected OUT, 'content.protected'
        EXEC sp_OAMethod @json, 'StringOf', @excerptRendered OUT, 'excerpt.rendered'
        EXEC sp_OAMethod @json, 'BoolOf', @excerptProtected OUT, 'excerpt.protected'
        EXEC sp_OAMethod @json, 'IntOf', @author OUT, 'author'
        EXEC sp_OAMethod @json, 'IntOf', @featured_media OUT, 'featured_media'
        EXEC sp_OAMethod @json, 'StringOf', @comment_status OUT, 'comment_status'
        EXEC sp_OAMethod @json, 'StringOf', @ping_status OUT, 'ping_status'
        EXEC sp_OAMethod @json, 'BoolOf', @sticky OUT, 'sticky'
        EXEC sp_OAMethod @json, 'StringOf', @template OUT, 'template'
        EXEC sp_OAMethod @json, 'StringOf', @format OUT, 'format'
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'meta'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'categories'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'IntOf', @intVal OUT, 'categories[j]'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'tags'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'IntOf', @intVal OUT, 'tags[j]'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_links.self'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @href OUT, '_links.self[j].href'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_links.collection'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @href OUT, '_links.collection[j].href'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_links.about'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @href OUT, '_links.about[j].href'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_links.author'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'BoolOf', @embeddable OUT, '_links.author[j].embeddable'
            EXEC sp_OAMethod @json, 'StringOf', @href OUT, '_links.author[j].href'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_links.replies'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'BoolOf', @embeddable OUT, '_links.replies[j].embeddable'
            EXEC sp_OAMethod @json, 'StringOf', @href OUT, '_links.replies[j].href'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_links.version-history'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'IntOf', @count OUT, '_links.version-history[j].count'
            EXEC sp_OAMethod @json, 'StringOf', @href OUT, '_links.version-history[j].href'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_links.predecessor-version'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'IntOf', @id OUT, '_links.predecessor-version[j].id'
            EXEC sp_OAMethod @json, 'StringOf', @href OUT, '_links.predecessor-version[j].href'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_links.wp:attachment'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @href OUT, '_links.wp:attachment[j].href'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_links.wp:term'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @taxonomy OUT, '_links.wp:term[j].taxonomy'
            EXEC sp_OAMethod @json, 'BoolOf', @embeddable OUT, '_links.wp:term[j].embeddable'
            EXEC sp_OAMethod @json, 'StringOf', @href OUT, '_links.wp:term[j].href'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '_links.curies'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @name OUT, '_links.curies[j].name'
            EXEC sp_OAMethod @json, 'StringOf', @href OUT, '_links.curies[j].href'
            EXEC sp_OAMethod @json, 'BoolOf', @templated OUT, '_links.curies[j].templated'
            SELECT @j = @j + 1
          END
        EXEC @hr = sp_OADestroy @json

        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @jarrResp
    EXEC @hr = sp_OADestroy @date_gmt


END
GO