SQL Server
SQL Server
SCIS Search
See more SCiS Schools Catalogue Examples
Demonstrates the SCIS (Schools Catalogue Information Service) search API call.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 @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- 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
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbQuery OUT
EXEC sp_OAMethod @sbQuery, 'Append', @success OUT, 'titleSearch:dogs AND publicationYear:2015'
DECLARE @sbUrl int
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
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
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