QuickBooks Online
Interact with the QuickBooks Online API (v3) using SQL Server
- Install the SQLHTTP database on your SQL Server
- Obtain a Client ID, Client Secret, Token and a RedirectURL from developer.intuit.com
- Create the stored procedures documented below
- Execute the following SQL statement:
|
1 2 3 4 5 6 |
EXEC usp_QuickBooks_v3_Auth_Init @Profile = 'enter-profile-name-of-your-choosing', @ClientID = 'your-client-id', @ClientSecret = 'your-client-secret', @RedirectURL = 'your-redirecturl' --example: http://localhost:53200 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 |
CREATE PROCEDURE usp_QuickBooks_v3_Auth_Init( @Profile nvarchar(100), @ClientID varchar(50), @ClientSecret varchar(50), @RedirectURL varchar(100)) AS SET NOCOUNT ON DECLARE @UserBrowseToURL varchar(MAX) DECLARE @QueryString nvarchar(MAX) EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'ClientID', @Value = @ClientID EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'ClientSecret', @Value = @ClientSecret EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'RedirectURL', @Value = @RedirectURL SET @UserBrowseToURL = 'https://appcenter.intuit.com/connect/oauth2' EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT, @Profile, 'response_type', 'code', 'client_id', @ClientID, --'redirect_uri', @RedirectURL, 'scope', 'com.intuit.quickbooks.accounting', 'state', 'change-if-desired' SET @QueryString = @QueryString + '&redirect_uri=' COLLATE SQL_Latin1_General_CP1_CI_AS + @RedirectURL SET @UserBrowseToURL = @UserBrowseToURL + @QueryString DECLARE @URL nvarchar(MAX) DECLARE @Body nvarchar(MAX) DECLARE @HTTPSessionID uniqueidentifier DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) DECLARE @RedirectURLCode varchar(50) DECLARE @RealmID varchar(50) DECLARE @Timeout int DECLARE @TimeoutReached bit = 0 SET @Timeout = 180 --three minutes wait EXEC SQLHTTP.net.AuthListener @UserBrowseToURL = @UserBrowseToURL, @RedirectURL = @RedirectURL, @Timeout = @Timeout, @QueryString = @QueryString OUTPUT, @TimeoutReached = @TimeoutReached OUTPUT IF @TimeoutReached = 1 BEGIN RAISERROR('Timeout reached waiting for browser authentication', 16, 1) RETURN END SET @RedirectURLCode = SQLHTTP.net.MidText(@QueryString, 'code=', '&', 1) SET @RealmID = SQLHTTP.net.Split(@QueryString, 'realmId=', 2) EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'RealmID', @value = @RealmID SET @URL = 'https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer' EXEC SQLHTTP.net.FormDataBuilder @Body OUTPUT, @Profile, 'grant_type', 'authorization_code', 'code', @RedirectURLCode SET @Body = @Body + '&redirect_uri=' COLLATE SQL_Latin1_General_CP1_CI_AS + @RedirectURL EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC SQLHTTP.net.BasicAuthHeader @HttpSessionID, @ClientID, @ClientSecret EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'POST', @Body = @Body, @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT IF @StatusCode >= 400 EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response ELSE BEGIN DECLARE @BearerToken varchar(MAX) DECLARE @RefreshToken varchar(MAX) SELECT @BearerToken = [value] FROM SQLHTTP.net.Json_To_NodeTable(@Response) WHERE [Name] = 'access_token' SELECT @RefreshToken = [value] FROM SQLHTTP.net.Json_To_NodeTable(@Response) WHERE [Name] = 'refresh_token' EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'BearerToken', @value = @BearerToken EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'RefreshToken', @Value = @RefreshToken DECLARE @TokenCreatedDateTime nvarchar(100) SET @TokenCreatedDateTime = GetDate() EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'TokenCreatedDateTime', @Value = @TokenCreatedDateTime END GO |
|
1 2 3 4 5 6 7 8 9 10 |
CREATE PROCEDURE usp_QuickBooks_v3_Auth_Header( @Profile nvarchar(100), @HttpSessionID uniqueidentifier) AS DECLARE @AuthorizationHeaderValue varchar(MAX) SET @AuthorizationHeaderValue = 'Bearer ' + SQLHTTP.net.AuthParam(@Profile, 'BearerToken') EXEC SQLHTTP.net.RequestHeaderSet @HTTPSessionID, 'Authorization', @AuthorizationHeaderValue GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
CREATE PROCEDURE usp_QuickBooks_v3_Auth_Refresh( @Profile nvarchar(100)) AS DECLARE @TokenCreatedDateTime nvarchar(100) SET @TokenCreatedDateTime = SQLHTTP.net.AuthParam(@Profile, 'TokenCreatedDateTime') IF DATEDIFF(hour, @TokenCreatedDateTime, GetDate()) < 12 RETURN --Too early for a token refresh DECLARE @URL nvarchar(MAX) DECLARE @ClientID varchar(50) DECLARE @ClientSecret varchar(50) DECLARE @HTTPSessionID uniqueidentifier DECLARE @Body nvarchar(MAX) DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) SET @URL = 'https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer' SET @ClientID = SQLHTTP.net.AuthParam( @Profile, 'ClientID') SET @ClientSecret = SQLHTTP.net.AuthParam( @Profile, 'ClientSecret') EXEC SQLHTTP.net.FormDataBuilder @Body OUTPUT, @Profile, 'grant_type', 'refresh_token', 'refresh_token', '#RefreshToken' EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC SQLHTTP.net.BasicAuthHeader @HttpSessionID, @ClientID, @ClientSecret EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'POST', @Body = @Body, @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT IF @StatusCode >= 400 EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response ELSE BEGIN DECLARE @BearerToken varchar(MAX) DECLARE @RefreshToken varchar(MAX) SELECT @BearerToken = [value] FROM SQLHTTP.net.Json_To_NodeTable(@Response) WHERE [Name] = 'access_token' SELECT @RefreshToken = [value] FROM SQLHTTP.net.Json_To_NodeTable(@Response) WHERE [Name] = 'refresh_token' EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'BearerToken', @value = @BearerToken EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'RefreshToken', @Value = @RefreshToken SET @TokenCreatedDateTime = GetDate() EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'TokenCreatedDateTime', @Value = @TokenCreatedDateTime END GO |
- Account – Create
- Account – Fetch
- Account – Query
- Account – Update
- Attachable – Create
- Attachable – Delete
- Attachable – Fetch
- Attachable – Query
- Attachable – Update
- BatchRequest – Create
- Bill – Create
- Bill – Delete
- Bill – Fetch
- Bill – Query
- Bill – Update
- BillPayment – Create
- BillPayment – Delete
- BillPayment – Fetch
- BillPayment – Query
- BillPayment – Update
- Budget – Query
- Change Data Capture – Query
- Class – Create
- Class – Fetch
- Class – Query
- Class – Update
- CompanyInfo – Fetch
- CompanyInfo – Query
- CreditMemo – Create
- CreditMemo – Delete
- CreditMemo – Fetch
- CreditMemo – Query
- CreditMemo – Update
- Customer – Create
- Customer – Fetch
- Customer – Query
- Customer – Update
- Customer – Update (Sparse)
- Department – Create
- Department – Fetch
- Department – Query
- Department – Update
- Deposit – Create
- Deposit – Delete
- Deposit – Fetch
- Deposit – Query
- Deposit – Update
- Deposit – Update (Sparse)
- Employee – Create
- Employee – Fetch
- Employee – Query
- Employee – Update
- Estimate – Create
- Estimate – Delete
- Estimate – Fetch
- Estimate – Query
- Estimate – Send
- Estimate – Update
- Estimate – Update (Sparse)
- Invoice – Create
- Invoice – Delete
- Invoice – Fetch
- Invoice – Query
- Invoice – Send
- Invoice – Update
- Invoice – Update (Sparse)
- Item – Create
- Item – Fetch
- Item – Inactivate
- Item – Query
- Item – Update
- JournalEntry – Create
- JournalEntry – Delete
- JournalEntry – Fetch
- JournalEntry – Query
- JournalEntry – Update
- JournalEntry – Update (Sparse)
- Payment – Create
- Payment – Delete
- Payment – Fetch
- Payment – Query
- PaymentMethod – Create
- PaymentMethod – Fetch
- PaymentMethod – Query
- PaymentMethod – Update
- Preferences – Query
- Preferences – Read
- Preferences – Update
- Purchase – Create
- Purchase – Delete
- Purchase – Fetch
- Purchase – Query
- Purchase – Update
- Purchase Order – Update
- PurchaseOrder – Create
- PurchaseOrder – Delete
- PurchaseOrder – Fetch
- PurchaseOrder – Query
- RefundReceipt – Create
- RefundReceipt – Delete
- RefundReceipt – Fetch
- RefundReceipt – Query
- RefundReceipt – Update
- RefundReceipt – Update (Sparse)
- SalesReceipt – Create
- SalesReceipt – Delete
- SalesReceipt – Fetch
- SalesReceipt – Query
- SalesReceipt – Send
- SalesReceipt – Update
- SalesReceipt – Update (Sparse)
- TaxAgency – Create
- TaxAgency – Fetch
- TaxAgency – Query
- TaxCode – Fetch
- TaxCode – Query
- TaxRate – Fetch
- TaxRate – Query
- TaxService – Create
- Term – Create
- Term – Fetch
- Term – Query
- Term – Update
- TimeActivity – Create
- TimeActivity – Delete
- TimeActivity – Fetch
- TimeActivity – Query
- TimeActivity – Update
- Transfer – Create
- Transfer – Delete
- Transfer – Fetch
- Transfer – Query
- Transfer – Update
- Transfer – Update (Sparse)
- Vendor – Create
- Vendor – Fetch
- Vendor – Query
- Vendor – Update
- VendorCredit – Create
- VendorCredit – Delete
- VendorCredit – Fetch
- VendorCredit – Query
- VendorCredit – Update
IMPORTANT DISCLAIMER
CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.
USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.
USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.