SQL Server
SQL Server
QuickBooks - Query Employees
See more QuickBooks Examples
Demonstrates how to query the QuickBooks employee data.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 assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- Get our previously obtained OAuth2 access token, which should contain JSON like this:
-- {
-- "expires_in": 3600,
-- "x_refresh_token_expires_in": 8726400,
-- "refresh_token": "L011546037639r ... 3vR2DrbOmg0Sdagw",
-- "access_token": "eyJlbmMiOiJBMTI4Q0 ... oETJEMbeggg",
-- "token_type": "bearer"
-- }
DECLARE @jsonToken int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/qb-access-token.json'
DECLARE @rest int
EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
-- Connect using TLS.
-- A single REST object, once connected, can be used for many Quickbooks REST API calls.
-- The auto-reconnect indicates that if the already-established HTTPS connection is closed,
-- then it will be automatically re-established as needed.
DECLARE @bAutoReconnect int
SELECT @bAutoReconnect = 1
EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'sandbox-quickbooks.api.intuit.com', 443, 1, @bAutoReconnect
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @rest
RETURN
END
DECLARE @sbAuth int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbAuth OUT
EXEC sp_OAMethod @sbAuth, 'Append', @success OUT, 'Bearer '
EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OAMethod @sbAuth, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @sbAuth, 'GetAsString', @sTmp0 OUT
EXEC sp_OASetProperty @rest, 'Authorization', @sTmp0
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Accept', 'application/json'
EXEC sp_OASetProperty @rest, 'AllowHeaderFolding', 0
DECLARE @query nvarchar(4000)
SELECT @query = 'select * from Employee'
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'query', @query
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'minorversion', '45'
-- The company ID is 123146096291789
DECLARE @responseBody nvarchar(4000)
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseBody OUT, 'GET', '/v3/company/123146096291789/query'
EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbAuth
RETURN
END
-- We should expect a 200 response if successful.
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
PRINT 'Request Header: '
EXEC sp_OAGetProperty @rest, 'LastRequestHeader', @sTmp0 OUT
PRINT @sTmp0
PRINT '----'
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
PRINT 'Response StatusCode = ' + @iTmp0
EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
PRINT 'Response StatusLine: ' + @sTmp0
PRINT 'Response Header:'
EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
PRINT @sTmp0
PRINT @responseBody
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbAuth
RETURN
END
-- Load the JSON response into a JSON object for parsing.
-- A sample JSON response is shown below.
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @responseBody
-- Show the full JSON response.
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Use this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
-- These will be used for parsing date/time strings..
DECLARE @dtime int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dtime OUT
DECLARE @bLocalTime int
SELECT @bLocalTime = 1
DECLARE @dt int
EXEC @hr = sp_OACreate 'Chilkat.DtObj', @dt OUT
-- Iterate over each account returned.
DECLARE @numRecords int
EXEC sp_OAMethod @json, 'SizeOfArray', @numRecords OUT, 'QueryResponse.Employee'
DECLARE @i int
SELECT @i = 0
WHILE @i < @numRecords
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'QueryResponse.Employee[i].DisplayName'
PRINT 'DisplayName: ' + @sTmp0
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'QueryResponse.Employee[i].Id'
PRINT 'Id: ' + @sTmp0
EXEC sp_OAMethod @json, 'BoolOf', @iTmp0 OUT, 'QueryResponse.Employee[i].Active'
PRINT 'Active: ' + @iTmp0
-- Load the CreateTime into a CkDateTime...
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'QueryResponse.Employee[i].MetaData.CreateTime'
EXEC sp_OAMethod @dtime, 'SetFromTimestamp', @success OUT, @sTmp0
EXEC sp_OAMethod @dtime, 'ToDtObj', NULL, @bLocalTime, @dt
EXEC sp_OAGetProperty @dt, 'Month', @iTmp0 OUT
EXEC sp_OAGetProperty @dt, 'Day', @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
-- The the birth year, month, and day, if it exists.
-- The BirthDate contains something like this: "1985-09-11"
EXEC sp_OAMethod @json, 'HasMember', @iTmp0 OUT, 'QueryResponse.Employee[i].BirthDate'
IF @iTmp0 = 1
BEGIN
DECLARE @sTab int
EXEC @hr = sp_OACreate 'Chilkat.StringTable', @sTab OUT
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'QueryResponse.Employee[i].BirthDate'
EXEC sp_OAMethod @sTab, 'SplitAndAppend', @success OUT, @sTmp0, '-', 1, 1
DECLARE @year nvarchar(4000)
EXEC sp_OAMethod @sTab, 'StringAt', @year OUT, 0
DECLARE @month nvarchar(4000)
EXEC sp_OAMethod @sTab, 'StringAt', @month OUT, 1
DECLARE @day nvarchar(4000)
EXEC sp_OAMethod @sTab, 'StringAt', @day OUT, 2
PRINT 'BirthDate: ' + @month + '/' + @day + '/' + @year
END
PRINT '----'
SELECT @i = @i + 1
END
PRINT 'Success.'
-- ------------------------------------------------------
-- The JSON response looks like this:
-- {
-- "QueryResponse": {
-- "Employee": [
-- {
-- "BillableTime": false,
-- "domain": "QBO",
-- "sparse": false,
-- "Id": "55",
-- "SyncToken": "0",
-- "MetaData": {
-- "CreateTime": "2016-09-15T11:21:48-07:00",
-- "LastUpdatedTime": "2016-09-15T11:21:48-07:00"
-- },
-- "GivenName": "Emily",
-- "FamilyName": "Platt",
-- "DisplayName": "Emily Platt",
-- "PrintOnCheckName": "Emily Platt",
-- "Active": true
-- },
-- {
-- "BillableTime": false,
-- "BirthDate": "1985-09-11",
-- "HiredDate": "2016-08-17",
-- "domain": "QBO",
-- "sparse": false,
-- "Id": "54",
-- "SyncToken": "1",
-- "MetaData": {
-- "CreateTime": "2016-09-15T11:21:28-07:00",
-- "LastUpdatedTime": "2016-09-15T11:46:26-07:00"
-- },
-- "GivenName": "John",
-- "FamilyName": "Johnson",
-- "DisplayName": "John Johnson",
-- "PrintOnCheckName": "John Johnson",
-- "Active": true,
-- "PrimaryPhone": {
-- "FreeFormNumber": "(540) 555-9645"
-- }
-- },
-- {
-- "SSN": "XXX-XX-XXXX",
-- "PrimaryAddr": {
-- "Id": "98",
-- "Line1": "45 N. Elm Street",
-- "City": "Middlefield",
-- "CountrySubDivisionCode": "CA",
-- "PostalCode": "93242"
-- },
-- "BillableTime": false,
-- "domain": "QBO",
-- "sparse": false,
-- "Id": "60",
-- "SyncToken": "0",
-- "MetaData": {
-- "CreateTime": "2016-10-25T14:08:18-07:00",
-- "LastUpdatedTime": "2016-10-25T14:08:18-07:00"
-- },
-- "GivenName": "John",
-- "FamilyName": "Meuller",
-- "DisplayName": "John Meuller",
-- "PrintOnCheckName": "John Meuller",
-- "Active": true,
-- "PrimaryPhone": {
-- "FreeFormNumber": "408-525-1234"
-- }
-- }
-- ],
-- "startPosition": 1,
-- "maxResults": 3
-- },
-- "time": "2016-10-25T14:18:38.487-07:00"
--
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbAuth
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @dtime
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @sTab
END
GO