SQL Server
SQL Server
QuickBooks - Read an Employee
See more QuickBooks Examples
Demonstrates how to fetch the information for a specific Quickbooks employee. The employee information is fetched by Id. The employee Ids can be obtained by querying the employees and parsing the JSON that is returned.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
-- The company ID is 123146096291789
-- The employee ID is 58
DECLARE @responseBody nvarchar(4000)
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseBody OUT, 'GET', '/v3/company/123146096291789/employee/58?minorversion=45'
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
-- These will be used for parsing date/time strings..
DECLARE @dtime int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dtime OUT
-- Show the JSON.
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Get some information from the JSON..
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'Employee.DisplayName'
PRINT 'Name: ' + @sTmp0
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'Employee.Id'
PRINT 'Id: ' + @sTmp0
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'Employee.PrimaryAddr.City'
PRINT 'City: ' + @sTmp0
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'Employee.PrimaryAddr.PostalCode'
PRINT 'PostalCode: ' + @sTmp0
-- Load the CreateTime into a CkDateTime...
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'Employee.MetaData.CreateTime'
EXEC sp_OAMethod @dtime, 'SetFromTimestamp', @success OUT, @sTmp0
DECLARE @bLocalTime int
SELECT @bLocalTime = 1
DECLARE @dt int
EXEC @hr = sp_OACreate 'Chilkat.DtObj', @dt OUT
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
PRINT 'Success.'
-- Use this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
-- ------------------------------------------------------
-- The JSON response looks like this:
-- {
-- "Employee": {
-- "SSN": "XXX-XX-XXXX",
-- "PrimaryAddr": {
-- "Id": "116",
-- "Line1": "45 N. Elm Street",
-- "City": "Middlefield",
-- "CountrySubDivisionCode": "CA",
-- "PostalCode": "93242"
-- },
-- "BillableTime": false,
-- "domain": "QBO",
-- "sparse": false,
-- "Id": "98",
-- "SyncToken": "0",
-- "MetaData": {
-- "CreateTime": "2015-07-24T09:34:35-07:00",
-- "LastUpdatedTime": "2015-07-24T09:34:35-07:00"
-- },
-- "GivenName": "Bill",
-- "FamilyName": "Miller",
-- "DisplayName": "Bill Miller",
-- "PrintOnCheckName": "Bill Miller",
-- "Active": true,
-- "PrimaryPhone": {
-- "FreeFormNumber": "234-525-1234"
-- }
-- },
-- "time": "2015-07-24T09:35:54.805-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
END
GO