Chilkat HOME .NET Core C# Android™ AutoIt C C# C++ Chilkat2-Python CkPython Classic ASP DataFlex Delphi ActiveX Delphi DLL Go Java Lianja Mono C# Node.js Objective-C PHP ActiveX PHP Extension Perl PowerBuilder PowerShell PureBasic Ruby SQL Server Swift 2 Swift 3,4,5... Tcl Unicode C Unicode C++ VB.NET VBScript Visual Basic 6.0 Visual FoxPro Xojo Plugin
(SQL Server) SCIS SearchSee more SCiS Schools Catalogue ExamplesDemonstrates the SCIS (Schools Catalogue Information Service) search API call.
-- 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 |
© 2000-2024 Chilkat Software, Inc. All Rights Reserved.