(SQL Server) Akeneo: Create New Product

Demonstrates how to create a new product.

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
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires 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

    -- Use your previously obtained access token.
    -- See Get Akeneo Access Token
    EXEC sp_OASetProperty @http, 'AuthToken', 'access_token'

    -- Build the following JSON to be sent in the request body:
    -- Use this online tool to generate the code from sample JSON: 
    -- Generate Code to Create JSON

    -- {
    --   "identifier": "top",
    --   "enabled": true,
    --   "family": "tshirt",
    --   "categories": [
    --     "summer_collection"
    --   ],
    --   "groups": [],
    --   "parent": null,
    --   "values": {
    --     "name": [
    --       {
    --         "data": "Top",
    --         "locale": "en_US",
    --         "scope": null
    --       },
    --       {
    --         "data": "D�bardeur",
    --         "locale": "fr_FR",
    --         "scope": null
    --       }
    --     ],
    --     "description": [
    --       {
    --         "data": "Summer top",
    --         "locale": "en_US",
    --         "scope": "ecommerce"
    --       },
    --       {
    --         "data": "Top",
    --         "locale": "en_US",
    --         "scope": "tablet"
    --       },
    --       {
    --         "data": "D�bardeur pour l'�t�",
    --         "locale": "fr_FR",
    --         "scope": "ecommerce"
    --       },
    --       {
    --         "data": "D�bardeur",
    --         "locale": "fr_FR",
    --         "scope": "tablet"
    --       }
    --     ],
    --     "price": [
    --       {
    --         "locale": null,
    --         "scope": null,
    --         "data": [
    --           {
    --             "amount": "150.5",
    --             "currency": "EUR"
    --           },
    --           {
    --             "amount": "150",
    --             "currency": "USD"
    --           }
    --         ]
    --       }
    --     ],
    --     "color": [
    --       {
    --         "locale": null,
    --         "scope": null,
    --         "data": "black"
    --       }
    --     ],
    --     "size": [
    --       {
    --         "locale": null,
    --         "scope": null,
    --         "data": "m"
    --       }
    --     ]
    --   },
    --   "associations": {
    --     "PACK": {
    --       "products": [
    --         "sunglass"
    --       ],
    --       "groups": []
    --     }
    --   }
    -- }
    -- 

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

    DECLARE @success int
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'identifier', 'top'
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'enabled', 1
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'family', 'tshirt'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'categories[0]', 'summer_collection'
    EXEC sp_OAMethod @json, 'UpdateNewArray', @success OUT, 'groups'
    EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'parent'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.name[0].data', 'Top'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.name[0].locale', 'en_US'
    EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'values.name[0].scope'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.name[1].data', 'D�bardeur'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.name[1].locale', 'fr_FR'
    EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'values.name[1].scope'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.description[0].data', 'Summer top'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.description[0].locale', 'en_US'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.description[0].scope', 'ecommerce'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.description[1].data', 'Top'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.description[1].locale', 'en_US'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.description[1].scope', 'tablet'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.description[2].data', 'D�bardeur pour l''�t�'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.description[2].locale', 'fr_FR'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.description[2].scope', 'ecommerce'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.description[3].data', 'D�bardeur'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.description[3].locale', 'fr_FR'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.description[3].scope', 'tablet'
    EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'values.price[0].locale'
    EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'values.price[0].scope'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.price[0].data[0].amount', '150.5'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.price[0].data[0].currency', 'EUR'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.price[0].data[1].amount', '150'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.price[0].data[1].currency', 'USD'
    EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'values.color[0].locale'
    EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'values.color[0].scope'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.color[0].data', 'black'
    EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'values.size[0].locale'
    EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'values.size[0].scope'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values.size[0].data', 'm'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'associations.PACK.products[0]', 'sunglass'
    EXEC sp_OAMethod @json, 'UpdateNewArray', @success OUT, 'associations.PACK.groups'

    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    -- Show the JSON to be sent..
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @url nvarchar(4000)
    SELECT @url = 'http://pim.my-akeneo-site.com/api/rest/v1/products'
    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, @url, 'application/json', @json
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        RETURN
      END


    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'Response Status Code: ' + @iTmp0

    PRINT 'Response Body: '
    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    PRINT @sTmp0

    -- The akeneo response will include a "Location" header, such as the following:
    -- HTTP/1.1 201 Created
    -- Date: Tue, 22 Jan 2019 10:36:35 GMT
    -- Server: Apache/2
    -- X-Powered-By: PHP/7.1.25
    -- Cache-Control: max-age=0, private, must-revalidate, no-cache, private
    -- Set-Cookie: abcdefg; path=/; HttpOnly
    -- Location: http://xyz.example.com/api/rest/v1/products/L0000123
    -- Vary: User-Agent
    -- Content-Length: 0
    -- Keep-Alive: timeout=2, max=100
    -- Connection: Keep-Alive
    -- Content-Type: application/json

    -- Get the location header using resp.GetHeaderField
    DECLARE @location nvarchar(4000)
    EXEC sp_OAMethod @resp, 'GetHeaderField', @location OUT, 'Location'

    PRINT 'Location: ' + @location

    EXEC @hr = sp_OADestroy @resp


    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @json


END
GO