Chilkat Online Tools

SQL Server / New FreshBooks / Create Single Project

Back to Collection Items

-- 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 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 this online tool to generate code from sample JSON: Generate Code to Create JSON

    -- The following JSON is sent in the request body.

    -- {
    --   "project": {
    --     "title": "Titikaka Hiking Guide",
    --     "client_id": 123,
    --     "project_type": "fixed_price",
    --     "fixed_price": "3000",
    --     "due_date": "2019-04-18",
    --     "services": [
    --       {
    --         "id": 123,
    --         "billable": true,
    --         "vis_state": 0
    --       },
    --       {
    --         "id": 123,
    --         "billable": true,
    --         "vis_state": 0
    --       },
    --       {
    --         "id": 123,
    --         "billable": true,
    --         "vis_state": 0
    --       },
    --       {
    --         "id": 123,
    --         "billable": true,
    --         "vis_state": 0
    --       }
    --     ]
    --   }
    -- }

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

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'project.title', 'Titikaka Hiking Guide'
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'project.client_id', 123
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'project.project_type', 'fixed_price'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'project.fixed_price', '3000'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'project.due_date', '2019-04-18'
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'project.services[0].id', 123
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'project.services[0].billable', 1
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'project.services[0].vis_state', 0
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'project.services[1].id', 123
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'project.services[1].billable', 1
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'project.services[1].vis_state', 0
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'project.services[2].id', 123
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'project.services[2].billable', 1
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'project.services[2].vis_state', 0
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'project.services[3].id', 123
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'project.services[3].billable', 1
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'project.services[3].vis_state', 0

    -- Adds the "Authorization: Bearer <access_token>" header.
    EXEC sp_OASetProperty @http, 'AuthToken', '<access_token>'
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'application/json'

    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, 'https://api.freshbooks.com/projects/business/{{businessId}}/projects', 'application/json', @json
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    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 @resp, 'GetBodySb', @success OUT, @sbResponseBody

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

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


    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 @resp

        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sbResponseBody
        EXEC @hr = sp_OADestroy @jResp
        RETURN
      END
    EXEC @hr = sp_OADestroy @resp

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

    -- {
    --   "project": {
    --     "id": 3018266,
    --     "title": "Titikaka Hiking Guide",
    --     "description": null,
    --     "due_date": "2019-04-18",
    --     "client_id": 31006,
    --     "internal": false,
    --     "budget": null,
    --     "fixed_price": "3000.00",
    --     "rate": null,
    --     "billing_method": null,
    --     "project_type": "fixed_price",
    --     "active": true,
    --     "complete": false,
    --     "sample": false,
    --     "created_at": "2019-05-08T20:06:55",
    --     "updated_at": "2019-05-08T20:06:55",
    --     "logged_duration": null,
    --     "services": [
    --     ],
    --     "billed_amount": "0.00",
    --     "billed_status": "unbilled",
    --     "retainer_id": null,
    --     "group": {
    --       "id": 5049390,
    --       "members": [
    --         {
    --           "id": 6578346,
    --           "identity_id": 37256,
    --           "role": "owner",
    --           "first_name": "Marshall",
    --           "last_name": "Johnston",
    --           "email": "api.freshbooks@freshbooks.com",
    --           "company": "Postman Sandbox",
    --           "active": true
    --         }
    --       ],
    --       "pending_invitations": null
    --     }
    --   }
    -- }

    -- Sample code for parsing the JSON response...
    -- Use this online tool to generate parsing code from sample JSON: Generate JSON Parsing Code

    DECLARE @id int

    DECLARE @identity_id int

    DECLARE @role nvarchar(4000)

    DECLARE @first_name nvarchar(4000)

    DECLARE @last_name nvarchar(4000)

    DECLARE @email nvarchar(4000)

    DECLARE @company nvarchar(4000)

    DECLARE @active int

    DECLARE @Id int
    EXEC sp_OAMethod @jResp, 'IntOf', @Id OUT, 'project.id'
    DECLARE @Title nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @Title OUT, 'project.title'
    DECLARE @Description nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @Description OUT, 'project.description'
    DECLARE @Due_date nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @Due_date OUT, 'project.due_date'
    DECLARE @Client_id int
    EXEC sp_OAMethod @jResp, 'IntOf', @Client_id OUT, 'project.client_id'
    DECLARE @Internal int
    EXEC sp_OAMethod @jResp, 'BoolOf', @Internal OUT, 'project.internal'
    DECLARE @Budget nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @Budget OUT, 'project.budget'
    DECLARE @Fixed_price nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @Fixed_price OUT, 'project.fixed_price'
    DECLARE @Rate nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @Rate OUT, 'project.rate'
    DECLARE @Billing_method nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @Billing_method OUT, 'project.billing_method'
    DECLARE @Project_type nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @Project_type OUT, 'project.project_type'
    DECLARE @Active int
    EXEC sp_OAMethod @jResp, 'BoolOf', @Active OUT, 'project.active'
    DECLARE @Complete int
    EXEC sp_OAMethod @jResp, 'BoolOf', @Complete OUT, 'project.complete'
    DECLARE @Sample int
    EXEC sp_OAMethod @jResp, 'BoolOf', @Sample OUT, 'project.sample'
    DECLARE @Created_at nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @Created_at OUT, 'project.created_at'
    DECLARE @Updated_at nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @Updated_at OUT, 'project.updated_at'
    DECLARE @Logged_duration nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @Logged_duration OUT, 'project.logged_duration'
    DECLARE @Billed_amount nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @Billed_amount OUT, 'project.billed_amount'
    DECLARE @Billed_status nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @Billed_status OUT, 'project.billed_status'
    DECLARE @Retainer_id nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @Retainer_id OUT, 'project.retainer_id'
    DECLARE @GroupId int
    EXEC sp_OAMethod @jResp, 'IntOf', @GroupId OUT, 'project.group.id'
    DECLARE @Pending_invitations nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @Pending_invitations OUT, 'project.group.pending_invitations'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'project.services'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'project.group.members'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        EXEC sp_OAMethod @jResp, 'IntOf', @id OUT, 'project.group.members[i].id'
        EXEC sp_OAMethod @jResp, 'IntOf', @identity_id OUT, 'project.group.members[i].identity_id'
        EXEC sp_OAMethod @jResp, 'StringOf', @role OUT, 'project.group.members[i].role'
        EXEC sp_OAMethod @jResp, 'StringOf', @first_name OUT, 'project.group.members[i].first_name'
        EXEC sp_OAMethod @jResp, 'StringOf', @last_name OUT, 'project.group.members[i].last_name'
        EXEC sp_OAMethod @jResp, 'StringOf', @email OUT, 'project.group.members[i].email'
        EXEC sp_OAMethod @jResp, 'StringOf', @company OUT, 'project.group.members[i].company'
        EXEC sp_OAMethod @jResp, 'BoolOf', @active OUT, 'project.group.members[i].active'
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @jResp


END
GO

Curl Command

curl -X POST
	-H "Authorization: Bearer <access_token>"
	-H "Content-Type: application/json"
	-d '{
  "project": {
    "title": "Titikaka Hiking Guide",
    "client_id": {{customerId}},
    "project_type": "fixed_price",
    "fixed_price": "3000",
    "due_date": "2019-04-18",
    "services": 
    [
    	{"id": {{serviceid}}, "billable": true, "vis_state": 0}, {"id": {{serviceid}},"billable": true, "vis_state": 0}, {"id": {{serviceid}}, "billable": true, "vis_state": 0}, {"id": {{serviceid}}, "billable": true, "vis_state": 0}
    	]
  }
}'
https://api.freshbooks.com/projects/business/{{businessId}}/projects

Postman Collection Item JSON

{
  "name": "Create Single Project",
  "event": [
    {
      "listen": "test",
      "script": {
        "exec": [
          "let jsonData = JSON.parse(responseBody);",
          "pm.environment.set(\"projectId\", jsonData.project.id);",
          ""
        ],
        "type": "text/javascript"
      }
    }
  ],
  "request": {
    "method": "POST",
    "header": [
      {
        "key": "Content-Type",
        "type": "text",
        "value": "application/json"
      }
    ],
    "body": {
      "mode": "raw",
      "raw": "{\n  \"project\": {\n    \"title\": \"Titikaka Hiking Guide\",\n    \"client_id\": {{customerId}},\n    \"project_type\": \"fixed_price\",\n    \"fixed_price\": \"3000\",\n    \"due_date\": \"2019-04-18\",\n    \"services\": \n    [\n    \t{\"id\": {{serviceid}}, \"billable\": true, \"vis_state\": 0}, {\"id\": {{serviceid}},\"billable\": true, \"vis_state\": 0}, {\"id\": {{serviceid}}, \"billable\": true, \"vis_state\": 0}, {\"id\": {{serviceid}}, \"billable\": true, \"vis_state\": 0}\n    \t]\n  }\n}"
    },
    "url": {
      "raw": "https://api.freshbooks.com/projects/business/{{businessId}}/projects",
      "protocol": "https",
      "host": [
        "api",
        "freshbooks",
        "com"
      ],
      "path": [
        "projects",
        "business",
        "{{businessId}}",
        "projects"
      ]
    }
  },
  "response": [
    {
      "name": "Create Single Project",
      "originalRequest": {
        "method": "POST",
        "header": [
          {
            "key": "Content-Type",
            "type": "text",
            "value": "application/json"
          }
        ],
        "body": {
          "mode": "raw",
          "raw": "{\n  \"project\": {\n    \"title\": \"Titikaka Hiking Guide\",\n    \"client_id\": {{customerId}},\n    \"project_type\": \"fixed_price\",\n    \"fixed_price\": \"3000\",\n    \"due_date\": \"2019-04-18\"\n  }\n}"
        },
        "url": {
          "raw": "https://api.freshbooks.com/projects/business/{{businessId}}/projects",
          "protocol": "https",
          "host": [
            "api",
            "freshbooks",
            "com"
          ],
          "path": [
            "projects",
            "business",
            "{{businessId}}",
            "projects"
          ]
        }
      },
      "status": "OK",
      "code": 200,
      "_postman_previewlanguage": "json",
      "header": [
        {
          "key": "Server",
          "value": "nginx"
        },
        {
          "key": "Content-Type",
          "value": "application/vnd.api+json"
        },
        {
          "key": "X-Version",
          "value": "1905.7.0"
        },
        {
          "key": "X-NewRelic-App-Data",
          "value": "PxQBWV5TCBABVllUBAQOU10TGhE1AwE2QgNWEVlbQFtcCxYnRA9QFg1ZWU4FDFZFVRYSBxlDUhULRERQBxVNEkINUgdbTEccQQBKDkQTUAdeRkUODAZbR0NGTVIbARlUVAcEB1VTVVoBUQhWAQsFBB9UDlQdQ1dSVVBSBFFRD1dVVldVAAIVSgJQWkAHOw=="
        },
        {
          "key": "X-RateLimit-Limit",
          "value": "20"
        },
        {
          "key": "X-RateLimit-Remaining",
          "value": "8"
        },
        {
          "key": "X-RateLimit-Reset",
          "value": "1557346076"
        },
        {
          "key": "Retry-After",
          "value": "60"
        },
        {
          "key": "Access-Control-Allow-Origin",
          "value": "*"
        },
        {
          "key": "Expires",
          "value": "Tue, 08 May 2018 20:06:55 GMT"
        },
        {
          "key": "Cache-Control",
          "value": "no-cache"
        },
        {
          "key": "Via",
          "value": "1.1 google"
        },
        {
          "key": "Via",
          "value": "1.1 varnish"
        },
        {
          "key": "Content-Length",
          "value": "759"
        },
        {
          "key": "Accept-Ranges",
          "value": "bytes"
        },
        {
          "key": "Date",
          "value": "Wed, 08 May 2019 20:06:55 GMT"
        },
        {
          "key": "Connection",
          "value": "keep-alive"
        },
        {
          "key": "X-Served-By",
          "value": "cache-mdw17361-MDW"
        },
        {
          "key": "X-Cache",
          "value": "MISS"
        },
        {
          "key": "X-Cache-Hits",
          "value": "0"
        },
        {
          "key": "Country",
          "value": "CA"
        },
        {
          "key": "Strict-Transport-Security",
          "value": "max-age=31536000; includeSubDomains; preload"
        }
      ],
      "cookie": [
      ],
      "body": "{\n    \"project\": {\n        \"id\": 3018266,\n        \"title\": \"Titikaka Hiking Guide\",\n        \"description\": null,\n        \"due_date\": \"2019-04-18\",\n        \"client_id\": 31006,\n        \"internal\": false,\n        \"budget\": null,\n        \"fixed_price\": \"3000.00\",\n        \"rate\": null,\n        \"billing_method\": null,\n        \"project_type\": \"fixed_price\",\n        \"active\": true,\n        \"complete\": false,\n        \"sample\": false,\n        \"created_at\": \"2019-05-08T20:06:55\",\n        \"updated_at\": \"2019-05-08T20:06:55\",\n        \"logged_duration\": null,\n        \"services\": [],\n        \"billed_amount\": \"0.00\",\n        \"billed_status\": \"unbilled\",\n        \"retainer_id\": null,\n        \"group\": {\n            \"id\": 5049390,\n            \"members\": [\n                {\n                    \"id\": 6578346,\n                    \"identity_id\": 37256,\n                    \"role\": \"owner\",\n                    \"first_name\": \"Marshall\",\n                    \"last_name\": \"Johnston\",\n                    \"email\": \"api.freshbooks@freshbooks.com\",\n                    \"company\": \"Postman Sandbox\",\n                    \"active\": true\n                }\n            ],\n            \"pending_invitations\": null\n        }\n    }\n}"
    }
  ]
}