SQL Server

JSON FindRecordString Example

See more JSON Examples

Demonstrates the FindRecordString method for searching an array of JSON records. The data used in this example is available at JSON sample data for FindRecordString.

Note: This example requires Chilkat v9.5.0.63 or later.

SQL Server

-- 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 @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 idea of FindRecordString is to search for a record matching one field,
    -- and then return the value of another field.

    -- For example, we want to find the "Id" for the record where 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 @retRelPath nvarchar(4000)
    SELECT @retRelPath = 'Id'

    DECLARE @id nvarchar(4000)
    EXEC sp_OAMethod @json, 'FindRecordString', @id OUT, @arrayPath, @relativePath, @value, @caseSensitive, @retRelPath
    EXEC sp_OAGetProperty @json, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

        PRINT 'Record not found.'
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- The Id should be 7.

    PRINT 'The Id of the Advertising account is ' + @id

    -- -----------------------------------------------------------------
    -- 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