(Excel) SCIS Search

See more SCiS Schools Catalogue Examples

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

Download Excel Class Modules Chilkat Excel Class Modules

' This example assumes the Chilkat API to have been previously unlocked.
' See Global Unlock Sample for sample code.

Dim http As Chilkat.Http
Set http = Chilkat.NewHttp

' 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.
http.Login = "myLogin"
http.Password = "myPassword"
http.BasicAuth = True

Dim sbQuery As Chilkat.StringBuilder
Set sbQuery = Chilkat.NewStringBuilder
Dim success As Boolean
success = sbQuery.Append("titleSearch:dogs AND publicationYear:2015")

Dim sbUrl As Chilkat.StringBuilder
Set sbUrl = Chilkat.NewStringBuilder
success = sbUrl.Append("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..
success = sbUrl.Append(sbQuery.GetEncoded("url","utf-8"))
success = sbUrl.Append("&from=0&size=20&sort=author&order=asc")

Dim sbResponseBody As Chilkat.StringBuilder
Set sbResponseBody = Chilkat.NewStringBuilder
success = http.QuickGetSb(sbUrl.GetAsString(),sbResponseBody)
If (success = False) Then
    Debug.Print http.LastErrorText
    Exit Sub
End If

Dim jResp As Chilkat.JsonObject
Set jResp = Chilkat.NewJsonObject
success = jResp.LoadSb(sbResponseBody)
jResp.EmitCompact = False

Debug.Print "Response Body:"
Debug.Print jResp.Emit()


respStatusCode = http.LastStatus
Debug.Print "Response Status Code = "; respStatusCode
If (respStatusCode >= 400) Then
    Debug.Print "Response Header:"
    Debug.Print http.LastHeader
    Debug.Print "Failed."
    Exit Sub
End If

' 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


dataHitsTotal = jResp.IntOf("data.hits.total")

dataHitsMax_score = jResp.StringOf("data.hits.max_score")

dataTook = jResp.IntOf("data.took")

dataTimed_out = jResp.BoolOf("data.timed_out")

i = 0

count_i = jResp.SizeOfArray("data.hits.hits")
Do While i < count_i
    jResp.I = i
    v_index = jResp.StringOf("data.hits.hits[i]._index")
    v_type = jResp.StringOf("data.hits.hits[i]._type")
    v_sourceDateIssuedMarc = jResp.IntOf("data.hits.hits[i]._source.dateIssuedMarc")
    v_sourceIsbn13 = jResp.StringOf("data.hits.hits[i]._source.isbn13")
    v_sourceImageFileName = jResp.StringOf("data.hits.hits[i]._source.imageFileName")
    v_sourcePublicationDetails = jResp.StringOf("data.hits.hits[i]._source.publicationDetails")
    v_sourceIsbn10 = jResp.StringOf("data.hits.hits[i]._source.isbn10")
    v_sourceId = jResp.StringOf("data.hits.hits[i]._source.id")
    v_id = jResp.StringOf("data.hits.hits[i]._id")
    v_score = jResp.StringOf("data.hits.hits[i]._score")
    j = 0
    count_j = jResp.SizeOfArray("data.hits.hits[i]._source.isbn")
    Do While j < count_j
        jResp.J = j
        strVal = jResp.StringOf("data.hits.hits[i]._source.isbn[j]")
        j = j + 1
    Loop
    j = 0
    count_j = jResp.SizeOfArray("data.hits.hits[i]._source.languageTermCode")
    Do While j < count_j
        jResp.J = j
        strVal = jResp.StringOf("data.hits.hits[i]._source.languageTermCode[j]")
        j = j + 1
    Loop
    j = 0
    count_j = jResp.SizeOfArray("data.hits.hits[i]._source.mainAuthor.namePersonalPrimary")
    Do While j < count_j
        jResp.J = j
        strVal = jResp.StringOf("data.hits.hits[i]._source.mainAuthor.namePersonalPrimary[j]")
        j = j + 1
    Loop
    j = 0
    count_j = jResp.SizeOfArray("data.hits.hits[i]._source.title.titlePrimary")
    Do While j < count_j
        jResp.J = j
        strVal = jResp.StringOf("data.hits.hits[i]._source.title.titlePrimary[j]")
        j = j + 1
    Loop
    j = 0
    count_j = jResp.SizeOfArray("data.hits.hits[i]._source.title.noteStmOfResponsibility")
    Do While j < count_j
        jResp.J = j
        strVal = jResp.StringOf("data.hits.hits[i]._source.title.noteStmOfResponsibility[j]")
        j = j + 1
    Loop
    j = 0
    count_j = jResp.SizeOfArray("data.hits.hits[i]._source.scisType")
    Do While j < count_j
        jResp.J = j
        strVal = jResp.StringOf("data.hits.hits[i]._source.scisType[j]")
        j = j + 1
    Loop
    j = 0
    count_j = jResp.SizeOfArray("data.hits.hits[i]._source.languageTermValue")
    Do While j < count_j
        jResp.J = j
        strVal = jResp.StringOf("data.hits.hits[i]._source.languageTermValue[j]")
        j = j + 1
    Loop
    j = 0
    count_j = jResp.SizeOfArray("data.hits.hits[i].sort")
    Do While j < count_j
        jResp.J = j
        strVal = jResp.StringOf("data.hits.hits[i].sort[j]")
        j = j + 1
    Loop
    j = 0
    count_j = jResp.SizeOfArray("data.hits.hits[i]._source.contributor.namePersonalOther")
    Do While j < count_j
        jResp.J = j
        strVal = jResp.StringOf("data.hits.hits[i]._source.contributor.namePersonalOther[j]")
        j = j + 1
    Loop
    j = 0
    count_j = jResp.SizeOfArray("data.hits.hits[i]._source.contributor.nameCorporateOther")
    Do While j < count_j
        jResp.J = j
        strVal = jResp.StringOf("data.hits.hits[i]._source.contributor.nameCorporateOther[j]")
        j = j + 1
    Loop
    i = i + 1
Loop