SQL Server
SQL Server
JSON FindRecord Example
See more JSON Examples
Demonstrates the FindRecord method for searching an array of JSON records. The data used in this example is available at JSON sample data for FindRecord.Note: This example requires Chilkat v9.5.0.63 or later.
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
-- Note: This example requires Chilkat v9.5.0.63 or later.
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/json/qb_accounts.json'
-- A sample of the content of qb_accounts.json is shown at the bottom of this example.
-- The goal is to search the array of Account records to return the 1st match
-- Find the account with the name "Advertising"
DECLARE @arrayPath nvarchar(4000)
SELECT @arrayPath = 'QueryResponse.Account'
DECLARE @relativePath nvarchar(4000)
SELECT @relativePath = 'Name'
DECLARE @value nvarchar(4000)
SELECT @value = 'Advertising'
DECLARE @caseSensitive int
SELECT @caseSensitive = 1
DECLARE @accountRec int
EXEC sp_OAMethod @json, 'FindRecord', @accountRec OUT, @arrayPath, @relativePath, @value, @caseSensitive
EXEC sp_OAGetProperty @json, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
PRINT 'Record not found.'
EXEC @hr = sp_OADestroy @json
RETURN
END
-- The accountRec should contain this:
-- {
-- "Name": "Advertising",
-- "SubAccount": false,
-- "FullyQualifiedName": "Advertising",
-- "Active": true,
-- "Classification": "Expense",
-- "AccountType": "Expense",
-- "AccountSubType": "AdvertisingPromotional",
-- "CurrentBalance": 0,
-- "CurrentBalanceWithSubAccounts": 0,
-- "CurrencyRef": {
-- "value": "USD",
-- "name": "United States Dollar"
-- },
-- "domain": "QBO",
-- "sparse": false,
-- "Id": "7",
-- "SyncToken": "0",
-- "MetaData": {
-- "CreateTime": "2016-09-09T14:42:07-07:00",
-- "LastUpdatedTime": "2016-09-09T14:42:07-07:00"
-- }
-- }
EXEC sp_OAMethod @accountRec, 'StringOf', @sTmp0 OUT, 'FullyQualifiedName'
PRINT 'FullyQualifiedName: ' + @sTmp0
EXEC sp_OAMethod @accountRec, 'StringOf', @sTmp0 OUT, 'AccountType'
PRINT 'AccountType: ' + @sTmp0
EXEC sp_OAMethod @accountRec, 'StringOf', @sTmp0 OUT, 'AccountSubType'
PRINT 'AccountSubType: ' + @sTmp0
PRINT '----'
EXEC @hr = sp_OADestroy @accountRec
-- ------------------------------------------------------------------
-- Find the first account where the currency is USD
SELECT @relativePath = 'CurrencyRef.value'
SELECT @value = 'USD'
SELECT @caseSensitive = 1
EXEC sp_OAMethod @json, 'FindRecord', @accountRec OUT, @arrayPath, @relativePath, @value, @caseSensitive
EXEC sp_OAGetProperty @json, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
PRINT 'Record not found.'
EXEC @hr = sp_OADestroy @json
RETURN
END
EXEC sp_OAMethod @accountRec, 'StringOf', @sTmp0 OUT, 'Name'
PRINT 'Name: ' + @sTmp0
EXEC sp_OAMethod @accountRec, 'StringOf', @sTmp0 OUT, 'CurrencyRef.name'
PRINT 'CurrencyRef.name: ' + @sTmp0
PRINT '----'
EXEC @hr = sp_OADestroy @accountRec
-- ------------------------------------------------------------------
-- Find the first account with "receivable" in the name (case insensitive)
SELECT @relativePath = 'Name'
SELECT @value = '*receivable*'
SELECT @caseSensitive = 0
EXEC sp_OAMethod @json, 'FindRecord', @accountRec OUT, @arrayPath, @relativePath, @value, @caseSensitive
EXEC sp_OAGetProperty @json, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
PRINT 'Record not found.'
EXEC @hr = sp_OADestroy @json
RETURN
END
EXEC sp_OAMethod @accountRec, 'StringOf', @sTmp0 OUT, 'Name'
PRINT 'Name: ' + @sTmp0
PRINT '----'
EXEC @hr = sp_OADestroy @accountRec
-- -----------------------------------------------------------------
-- qb_accounts.json contains this data
--
-- {
-- "QueryResponse": {
-- "Account": [
-- {
-- "Name": "Accounts Payable (A/P)",
-- "SubAccount": false,
-- "Description": "Description added during update.",
-- "FullyQualifiedName": "Accounts Payable (A/P)",
-- "Active": true,
-- "Classification": "Liability",
-- "AccountType": "Accounts Payable",
-- "AccountSubType": "AccountsPayable",
-- "CurrentBalance": -1602.67,
-- "CurrentBalanceWithSubAccounts": -1602.67,
-- "CurrencyRef": {
-- "value": "USD",
-- "name": "United States Dollar"
-- },
-- "domain": "QBO",
-- "sparse": false,
-- "Id": "33",
-- "SyncToken": "1",
-- "MetaData": {
-- "CreateTime": "2016-09-10T10:12:02-07:00",
-- "LastUpdatedTime": "2016-10-24T16:41:39-07:00"
-- }
-- },
-- {
-- "Name": "Accounts Receivable (A/R)",
-- "SubAccount": false,
-- "FullyQualifiedName": "Accounts Receivable (A/R)",
-- "Active": true,
-- "Classification": "Asset",
-- "AccountType": "Accounts Receivable",
-- "AccountSubType": "AccountsReceivable",
-- "CurrentBalance": 5281.52,
-- "CurrentBalanceWithSubAccounts": 5281.52,
-- "CurrencyRef": {
-- "value": "USD",
-- "name": "United States Dollar"
-- },
-- "domain": "QBO",
-- "sparse": false,
-- "Id": "84",
-- "SyncToken": "0",
-- "MetaData": {
-- "CreateTime": "2016-09-14T14:49:29-07:00",
-- "LastUpdatedTime": "2016-09-17T13:16:17-07:00"
-- }
-- },
-- {
-- "Name": "Advertising",
-- "SubAccount": false,
-- "FullyQualifiedName": "Advertising",
-- "Active": true,
-- "Classification": "Expense",
-- "AccountType": "Expense",
-- "AccountSubType": "AdvertisingPromotional",
-- "CurrentBalance": 0,
-- "CurrentBalanceWithSubAccounts": 0,
-- "CurrencyRef": {
-- "value": "USD",
-- "name": "United States Dollar"
-- },
-- "domain": "QBO",
-- "sparse": false,
-- "Id": "7",
-- "SyncToken": "0",
-- "MetaData": {
-- "CreateTime": "2016-09-09T14:42:07-07:00",
-- "LastUpdatedTime": "2016-09-09T14:42:07-07:00"
-- }
-- },
-- {
-- "Name": "Arizona Dept. of Revenue Payable",
-- "SubAccount": false,
-- "FullyQualifiedName": "Arizona Dept. of Revenue Payable",
-- "Active": true,
-- "Classification": "Liability",
-- "AccountType": "Other Current Liability",
-- "AccountSubType": "GlobalTaxPayable",
-- "CurrentBalance": 0,
-- "CurrentBalanceWithSubAccounts": 0,
-- "CurrencyRef": {
-- "value": "USD",
-- "name": "United States Dollar"
-- },
-- "domain": "QBO",
-- "sparse": false,
-- "Id": "89",
-- "SyncToken": "0",
-- "MetaData": {
-- "CreateTime": "2016-09-16T12:17:04-07:00",
-- "LastUpdatedTime": "2016-09-17T13:05:01-07:00"
-- }
-- },
-- {
-- "Name": "Automobile",
-- "SubAccount": false,
-- "FullyQualifiedName": "Automobile",
-- "Active": true,
-- "Classification": "Expense",
-- "AccountType": "Expense",
-- "AccountSubType": "Auto",
-- "CurrentBalance": 0,
-- "CurrentBalanceWithSubAccounts": 0,
-- "CurrencyRef": {
-- "value": "USD",
-- "name": "United States Dollar"
-- },
-- "domain": "QBO",
-- "sparse": false,
-- "Id": "55",
-- "SyncToken": "0",
-- "MetaData": {
-- "CreateTime": "2016-09-14T10:15:53-07:00",
-- "LastUpdatedTime": "2016-09-14T10:16:05-07:00"
-- }
-- },
-- ...
--
EXEC @hr = sp_OADestroy @json
END
GO