(SQL Server) SCIS Search

See more SCiS Schools Catalogue Examples

Demonstrates the SCIS (Schools Catalogue Information Service) search API call.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    -- Implements the following CURL command:

    -- curl -i -X GET --url 'https://api.scisdata.com/catalogue/api/search?query=titleSearch%3Adogs%20AND%20publicationYear%3A2015&from=0&size=20&sort=author&order=asc' -H 'Authorization: Basic ****'

    -- Use the following online tool to generate HTTP code from a CURL command
    -- Convert a cURL Command to HTTP Source Code

    -- This causes the "Authorization: Basic ****" to be added to each request.
    EXEC sp_OASetProperty @http, 'Login', 'myLogin'
    EXEC sp_OASetProperty @http, 'Password', 'myPassword'
    EXEC sp_OASetProperty @http, 'BasicAuth', 1

    DECLARE @sbQuery int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbQuery OUT

    EXEC sp_OAMethod @sbQuery, 'Append', @success OUT, 'titleSearch:dogs AND publicationYear:2015'

    DECLARE @sbUrl int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbUrl OUT

    EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, 'https://api.scisdata.com/catalogue/api/search?query='
    -- If non-usascii chars are included in the search, we don't know if utf-8 or windows-1252 is desired by the server.  You'll need to find out..
    EXEC sp_OAMethod @sbQuery, 'GetEncoded', @sTmp0 OUT, 'url', 'utf-8'
    EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, @sTmp0
    EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, '&from=0&size=20&sort=author&order=asc'

    DECLARE @sbResponseBody int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @sbUrl, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, @sTmp0, @sbResponseBody
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbQuery
        EXEC @hr = sp_OADestroy @sbUrl
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

    DECLARE @jResp int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jResp OUT

    EXEC sp_OAMethod @jResp, 'LoadSb', @success OUT, @sbResponseBody
    EXEC sp_OASetProperty @jResp, 'EmitCompact', 0


    PRINT 'Response Body:'
    EXEC sp_OAMethod @jResp, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @respStatusCode int
    EXEC sp_OAGetProperty @http, 'LastStatus', @respStatusCode OUT

    PRINT 'Response Status Code = ' + @respStatusCode
    IF @respStatusCode >= 400
      BEGIN

        PRINT 'Response Header:'
        EXEC sp_OAGetProperty @http, 'LastHeader', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbQuery
        EXEC @hr = sp_OADestroy @sbUrl
        EXEC @hr = sp_OADestroy @sbResponseBody
        EXEC @hr = sp_OADestroy @jResp
        RETURN
      END

    -- Sample JSON response:
    -- (Sample code for parsing the JSON response is shown below)

    -- {
    --   "data": {
    --     "hits": {
    --       "hits": [
    --         {
    --           "_index": "scisdata2",
    --           "_type": "bibdatatype",
    --           "_source": {
    --             "isbn": [
    --               "9781517638160"
    --             ],
    --             "languageTermCode": [
    --               "eng"
    --             ],
    --             "mainAuthor": {
    --               "namePersonalPrimary": [
    --                 "Abbott, Eleanor Hallowell."
    --               ]
    --             },
    --             "title": {
    --               "titlePrimary": [
    --                 "Peace on Earth, good-will to dogs"
    --               ],
    --               "noteStmOfResponsibility": [
    --                 "by Eleanor Hallowell Abbott."
    --               ]
    --             },
    --             "scisType": [
    --               "Book"
    --             ],
    --             "dateIssuedMarc": 2015,
    --             "languageTermValue": [
    --               "English"
    --             ],
    --             "contributor": {},
    --             "isbn13": "9781517638160",
    --             "imageFileName": "9781517638160.jpg",
    --             "publicationDetails": "United States, Create Space Independent Publishing Platform, 2015",
    --             "isbn10": "151763816X",
    --             "id": "1867852"
    --           },
    --           "_id": "1867852",
    --           "sort": [
    --             "abbott, eleanor hallowell."
    --           ],
    --           "_score": null
    --         },
    --         {
    --           "_index": "scisdata2",
    --           "_type": "bibdatatype",
    --           "_source": {
    --             "isbn": [
    --               "9781780747910"
    --             ],
    --             "languageTermCode": [
    --               "eng"
    --             ],
    --             "mainAuthor": {
    --               "namePersonalPrimary": [
    --                 "Adams, Richard."
    --               ]
    --             },
    --             "title": {
    --               "titlePrimary": [
    --                 "The plague dogs"
    --               ],
    --               "noteStmOfResponsibility": [
    --                 "Richard Adams."
    --               ]
    --             },
    --             "scisType": [
    --               "Book"
    --             ],
    --             "dateIssuedMarc": 2015,
    --             "languageTermValue": [
    --               "English"
    --             ],
    --             "contributor": {},
    --             "isbn13": "9781780747910",
    --             "imageFileName": "9781780747910.jpg",
    --             "publicationDetails": "New York, Rock the Boat, 2015",
    --             "isbn10": "1780747918",
    --             "id": "1749228"
    --           },
    --           "_id": "1749228",
    --           "sort": [
    --             "adams, richard."
    --           ],
    --           "_score": null
    --         },
    --          ...
    --       ],
    --       "total": 84,
    --       "max_score": null
    --     },
    --     "took": 585,
    --     "timed_out": false
    --   },
    --   "subscriptionStatus": {}
    -- }

    -- Sample code for parsing the JSON response...
    -- Use the following online tool to generate parsing code from sample JSON:
    -- Generate Parsing Code from JSON

    DECLARE @v_index nvarchar(4000)

    DECLARE @v_type nvarchar(4000)

    DECLARE @v_sourceDateIssuedMarc int

    DECLARE @v_sourceIsbn13 nvarchar(4000)

    DECLARE @v_sourceImageFileName nvarchar(4000)

    DECLARE @v_sourcePublicationDetails nvarchar(4000)

    DECLARE @v_sourceIsbn10 nvarchar(4000)

    DECLARE @v_sourceId nvarchar(4000)

    DECLARE @v_id nvarchar(4000)

    DECLARE @v_score nvarchar(4000)

    DECLARE @j int

    DECLARE @count_j int

    DECLARE @strVal nvarchar(4000)

    DECLARE @dataHitsTotal int
    EXEC sp_OAMethod @jResp, 'IntOf', @dataHitsTotal OUT, 'data.hits.total'
    DECLARE @dataHitsMax_score nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @dataHitsMax_score OUT, 'data.hits.max_score'
    DECLARE @dataTook int
    EXEC sp_OAMethod @jResp, 'IntOf', @dataTook OUT, 'data.took'
    DECLARE @dataTimed_out int
    EXEC sp_OAMethod @jResp, 'BoolOf', @dataTimed_out OUT, 'data.timed_out'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'data.hits.hits'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        EXEC sp_OAMethod @jResp, 'StringOf', @v_index OUT, 'data.hits.hits[i]._index'
        EXEC sp_OAMethod @jResp, 'StringOf', @v_type OUT, 'data.hits.hits[i]._type'
        EXEC sp_OAMethod @jResp, 'IntOf', @v_sourceDateIssuedMarc OUT, 'data.hits.hits[i]._source.dateIssuedMarc'
        EXEC sp_OAMethod @jResp, 'StringOf', @v_sourceIsbn13 OUT, 'data.hits.hits[i]._source.isbn13'
        EXEC sp_OAMethod @jResp, 'StringOf', @v_sourceImageFileName OUT, 'data.hits.hits[i]._source.imageFileName'
        EXEC sp_OAMethod @jResp, 'StringOf', @v_sourcePublicationDetails OUT, 'data.hits.hits[i]._source.publicationDetails'
        EXEC sp_OAMethod @jResp, 'StringOf', @v_sourceIsbn10 OUT, 'data.hits.hits[i]._source.isbn10'
        EXEC sp_OAMethod @jResp, 'StringOf', @v_sourceId OUT, 'data.hits.hits[i]._source.id'
        EXEC sp_OAMethod @jResp, 'StringOf', @v_id OUT, 'data.hits.hits[i]._id'
        EXEC sp_OAMethod @jResp, 'StringOf', @v_score OUT, 'data.hits.hits[i]._score'
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'data.hits.hits[i]._source.isbn'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'data.hits.hits[i]._source.isbn[j]'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'data.hits.hits[i]._source.languageTermCode'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'data.hits.hits[i]._source.languageTermCode[j]'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'data.hits.hits[i]._source.mainAuthor.namePersonalPrimary'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'data.hits.hits[i]._source.mainAuthor.namePersonalPrimary[j]'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'data.hits.hits[i]._source.title.titlePrimary'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'data.hits.hits[i]._source.title.titlePrimary[j]'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'data.hits.hits[i]._source.title.noteStmOfResponsibility'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'data.hits.hits[i]._source.title.noteStmOfResponsibility[j]'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'data.hits.hits[i]._source.scisType'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'data.hits.hits[i]._source.scisType[j]'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'data.hits.hits[i]._source.languageTermValue'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'data.hits.hits[i]._source.languageTermValue[j]'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'data.hits.hits[i].sort'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'data.hits.hits[i].sort[j]'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'data.hits.hits[i]._source.contributor.namePersonalOther'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'data.hits.hits[i]._source.contributor.namePersonalOther[j]'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'data.hits.hits[i]._source.contributor.nameCorporateOther'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jResp, 'J', @j
            EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'data.hits.hits[i]._source.contributor.nameCorporateOther[j]'
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbQuery
    EXEC @hr = sp_OADestroy @sbUrl
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @jResp


END
GO