SQL Server
SQL Server
Quickbooks Create a New Customer
See more QuickBooks Examples
Demonstrates how to create a new customer via the Quickbooks REST API.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
-- 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 requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- First get our previously obtained OAuth2 access token.
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 to the REST server.
DECLARE @bTls int
SELECT @bTls = 1
DECLARE @port int
SELECT @port = 443
DECLARE @bAutoReconnect int
SELECT @bAutoReconnect = 1
EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'sandbox-quickbooks.api.intuit.com', @port, @bTls, @bAutoReconnect
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
-- --------------------------------------------------------------------------
-- Note: The above code to setup the initial REST connection
-- can be done once. After connecting, any number of REST calls can be made.
-- If the connection is lost, the next REST method call will automatically
-- reconnect if needed.
-- --------------------------------------------------------------------------
-- Create the following JSON:
-- {
-- "FullyQualifiedName": "King Groceries",
-- "PrimaryEmailAddr": {
-- "Address": "jdrew@myemail.com"
-- },
-- "DisplayName": "King's Groceries",
-- "Suffix": "Jr",
-- "Title": "Mr",
-- "MiddleName": "B",
-- "Notes": "Here are other details.",
-- "FamilyName": "King",
-- "PrimaryPhone": {
-- "FreeFormNumber": "(555) 555-5555"
-- },
-- "CompanyName": "King Groceries",
-- "BillAddr": {
-- "CountrySubDivisionCode": "CA",
-- "City": "Mountain View",
-- "PostalCode": "94042",
-- "Line1": "123 Main Street",
-- "Country": "USA"
-- },
-- "GivenName": "James"
-- }
--
-- Use the this online tool to generate the code from sample JSON:
-- Generate Code to Create JSON
DECLARE @jsonReq int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonReq OUT
EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'FullyQualifiedName', 'King Groceries'
EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'PrimaryEmailAddr.Address', 'jdrew@myemail.com'
EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'DisplayName', 'King''s Groceries'
EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'Suffix', 'Jr'
EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'Title', 'Mr'
EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'MiddleName', 'B'
EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'Notes', 'Here are other details.'
EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'FamilyName', 'King'
EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'PrimaryPhone.FreeFormNumber', '(555) 555-5555'
EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'CompanyName', 'King Groceries'
EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'BillAddr.CountrySubDivisionCode', 'CA'
EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'BillAddr.City', 'Mountain View'
EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'BillAddr.PostalCode', '94042'
EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'BillAddr.Line1', '123 Main Street'
EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'BillAddr.Country', 'USA'
EXEC sp_OAMethod @jsonReq, 'UpdateString', @success OUT, 'GivenName', 'James'
DECLARE @sbRequestBody int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbRequestBody OUT
EXEC sp_OAMethod @jsonReq, 'EmitSb', @success OUT, @sbRequestBody
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'application/json'
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Accept', 'application/json'
EXEC sp_OASetProperty @rest, 'AllowHeaderFolding', 0
DECLARE @sbResponseBody int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT
EXEC sp_OAMethod @rest, 'FullRequestSb', @success OUT, 'POST', '/v3/company/<realmID>/customer', @sbRequestBody, @sbResponseBody
IF @success <> 1
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
EXEC @hr = sp_OADestroy @jsonReq
EXEC @hr = sp_OADestroy @sbRequestBody
EXEC @hr = sp_OADestroy @sbResponseBody
RETURN
END
DECLARE @respStatusCode int
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @respStatusCode OUT
-- Success is indicated by a 200 response status code.
PRINT 'response status code = ' + @respStatusCode
DECLARE @jsonResponse int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResponse OUT
EXEC sp_OAMethod @jsonResponse, 'LoadSb', @success OUT, @sbResponseBody
EXEC sp_OASetProperty @jsonResponse, 'EmitCompact', 0
EXEC sp_OAMethod @jsonResponse, 'Emit', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbAuth
EXEC @hr = sp_OADestroy @jsonReq
EXEC @hr = sp_OADestroy @sbRequestBody
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jsonResponse
RETURN
END
-- Sample output...
-- (See the parsing code below..)
--
-- Use the this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
-- {
-- "Customer": {
-- "domain": "QBO",
-- "PrimaryEmailAddr": {
-- "Address": "jdrew@myemail.com"
-- },
-- "DisplayName": "King's Groceries",
-- "CurrencyRef": {
-- "name": "United States Dollar",
-- "value": "USD"
-- },
-- "DefaultTaxCodeRef": {
-- "value": "2"
-- },
-- "PreferredDeliveryMethod": "Print",
-- "GivenName": "James",
-- "FullyQualifiedName": "King's Groceries",
-- "BillWithParent": false,
-- "Title": "Mr",
-- "Job": false,
-- "BalanceWithJobs": 0,
-- "PrimaryPhone": {
-- "FreeFormNumber": "(555) 555-5555"
-- },
-- "Taxable": true,
-- "MetaData": {
-- "CreateTime": "2015-07-23T10:58:12-07:00",
-- "LastUpdatedTime": "2015-07-23T10:58:12-07:00"
-- },
-- "BillAddr": {
-- "City": "Mountain View",
-- "Country": "USA",
-- "Line1": "123 Main Street",
-- "PostalCode": "94042",
-- "CountrySubDivisionCode": "CA",
-- "Id": "112"
-- },
-- "MiddleName": "B",
-- "Notes": "Here are other details.",
-- "Active": true,
-- "Balance": 0,
-- "SyncToken": "0",
-- "Suffix": "Jr",
-- "CompanyName": "King Groceries",
-- "FamilyName": "King",
-- "PrintOnCheckName": "King Groceries",
-- "sparse": false,
-- "Id": "67"
-- },
-- "time": "2015-07-23T10:58:12.099-07:00"
-- }
--
DECLARE @CustomerDomain nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerDomain OUT, 'Customer.domain'
DECLARE @CustomerPrimaryEmailAddrAddress nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerPrimaryEmailAddrAddress OUT, 'Customer.PrimaryEmailAddr.Address'
DECLARE @CustomerDisplayName nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerDisplayName OUT, 'Customer.DisplayName'
DECLARE @CustomerCurrencyRefName nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerCurrencyRefName OUT, 'Customer.CurrencyRef.name'
DECLARE @CustomerCurrencyRefValue nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerCurrencyRefValue OUT, 'Customer.CurrencyRef.value'
DECLARE @CustomerDefaultTaxCodeRefValue nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerDefaultTaxCodeRefValue OUT, 'Customer.DefaultTaxCodeRef.value'
DECLARE @CustomerPreferredDeliveryMethod nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerPreferredDeliveryMethod OUT, 'Customer.PreferredDeliveryMethod'
DECLARE @CustomerGivenName nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerGivenName OUT, 'Customer.GivenName'
DECLARE @CustomerFullyQualifiedName nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerFullyQualifiedName OUT, 'Customer.FullyQualifiedName'
DECLARE @CustomerBillWithParent int
EXEC sp_OAMethod @jsonResponse, 'BoolOf', @CustomerBillWithParent OUT, 'Customer.BillWithParent'
DECLARE @CustomerTitle nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerTitle OUT, 'Customer.Title'
DECLARE @CustomerJob int
EXEC sp_OAMethod @jsonResponse, 'BoolOf', @CustomerJob OUT, 'Customer.Job'
DECLARE @CustomerBalanceWithJobs int
EXEC sp_OAMethod @jsonResponse, 'IntOf', @CustomerBalanceWithJobs OUT, 'Customer.BalanceWithJobs'
DECLARE @CustomerPrimaryPhoneFreeFormNumber nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerPrimaryPhoneFreeFormNumber OUT, 'Customer.PrimaryPhone.FreeFormNumber'
DECLARE @CustomerTaxable int
EXEC sp_OAMethod @jsonResponse, 'BoolOf', @CustomerTaxable OUT, 'Customer.Taxable'
DECLARE @CustomerMetaDataCreateTime nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerMetaDataCreateTime OUT, 'Customer.MetaData.CreateTime'
DECLARE @CustomerMetaDataLastUpdatedTime nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerMetaDataLastUpdatedTime OUT, 'Customer.MetaData.LastUpdatedTime'
DECLARE @CustomerBillAddrCity nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerBillAddrCity OUT, 'Customer.BillAddr.City'
DECLARE @CustomerBillAddrCountry nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerBillAddrCountry OUT, 'Customer.BillAddr.Country'
DECLARE @CustomerBillAddrLine1 nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerBillAddrLine1 OUT, 'Customer.BillAddr.Line1'
DECLARE @CustomerBillAddrPostalCode nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerBillAddrPostalCode OUT, 'Customer.BillAddr.PostalCode'
DECLARE @CustomerBillAddrCountrySubDivisionCode nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerBillAddrCountrySubDivisionCode OUT, 'Customer.BillAddr.CountrySubDivisionCode'
DECLARE @CustomerBillAddrId nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerBillAddrId OUT, 'Customer.BillAddr.Id'
DECLARE @CustomerMiddleName nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerMiddleName OUT, 'Customer.MiddleName'
DECLARE @CustomerNotes nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerNotes OUT, 'Customer.Notes'
DECLARE @CustomerActive int
EXEC sp_OAMethod @jsonResponse, 'BoolOf', @CustomerActive OUT, 'Customer.Active'
DECLARE @CustomerBalance int
EXEC sp_OAMethod @jsonResponse, 'IntOf', @CustomerBalance OUT, 'Customer.Balance'
DECLARE @CustomerSyncToken nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerSyncToken OUT, 'Customer.SyncToken'
DECLARE @CustomerSuffix nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerSuffix OUT, 'Customer.Suffix'
DECLARE @CustomerCompanyName nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerCompanyName OUT, 'Customer.CompanyName'
DECLARE @CustomerFamilyName nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerFamilyName OUT, 'Customer.FamilyName'
DECLARE @CustomerPrintOnCheckName nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerPrintOnCheckName OUT, 'Customer.PrintOnCheckName'
DECLARE @CustomerSparse int
EXEC sp_OAMethod @jsonResponse, 'BoolOf', @CustomerSparse OUT, 'Customer.sparse'
DECLARE @CustomerId nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @CustomerId OUT, 'Customer.Id'
DECLARE @time nvarchar(4000)
EXEC sp_OAMethod @jsonResponse, 'StringOf', @time OUT, 'time'
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbAuth
EXEC @hr = sp_OADestroy @jsonReq
EXEC @hr = sp_OADestroy @sbRequestBody
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jsonResponse
END
GO