SQL Search REST Interface Example
I am trying to help another group within our organization access a SQL Table that I have created to populate Epic Department Data within our environment and came across the ability to use SQL Seach REST Interface using iKnow.
However, I am having issues trying to get it to work via POSTMAN before I hand off the solution...
the POST URL... https://<servername>/api/iKnow/latest/TESTCLIN/table/osuwmc_Epic_Clarity.DepartmentMaster/search
where osuwmc_Epic_Clarity.DepartmentMaster is the table
In the body...
{"query":SELECT ID, Abbr, Name, ExternalName, PhoneNumber, ApptPhone, FaxNumber, Address1, Address2, City, Zip, Specialty, RevLocID, RevLocName, BuildingCategoryID, BuildingName, DepCategoryTypeID, DepType, Center, EAFParent FROM osuwmc_Epic_Clarity.DepartmentMaster WHERE ID = 300000000","index":"IDKEY","option":1}but I am receiving..
{
"errors": [
{
"error": "ERROR #5035: General exception Name 'Parsing error' Code '3' Data ''",
"code": 5035,
"domain": "%ObjectErrors",
"id": "GeneralException",
"params": [
"Parsing error",
"3",
""
]
}
],
"summary": "ERROR #5035: General exception Name 'Parsing error' Code '3' Data ''",
"warnings": [
{
"warning": "You are not accessing the latest version of this REST API. Use /api/iKnow/latest/* to access the most up-to-date version"
}
]
}Am I using this wrong? What might be the magic chant to get this to work without having to create a REST service?
Comments
When I reformat the request to
{
"query": "SELECT ID, Name FROM osuwmc_Epic_Clarity.DepartmentMaster WHERE ID = 300000000",
"index": "IDKEY",
"option": 1,
"debug": true
}I am not getting any results back when I should be
It looks like you are using the
/api/iKnow/latest/
endpoint which is for iKnow. Is ths for an iKnow domain?
I found this information here... https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQLSRCH_rest#GSQLSRCH_rest_cmds
That REST API is indeed for querying iFind indices (hence the direct reference to an index you can provide) and the somewhat confusingly named "query" argument is actually to pass in the iFind search string. The API will then build a full SQL query for you and run it right away.
Here's the OpenAPI spec for this endpoint (from self-documentation endpoint /api/iKnow/v1/USER/swagger):
/table/{table}/search:
post:
operationId: /table/{table}/search-POST
summary: |
Search the given iFind index in the given table
tags: ["iFind"]
parameters:
- $ref: '#/parameters/tableParam'
- name: RequestBody
description: JSON object with a list of query-specific arguments
in: body
schema:
type: object
properties:
query:
description: This is the only necessary parameter with no default value. The search terms to query against the iFind index.
type: string
index:
description: the iFind index would be searched against, if you don't specify it, the first found iFind index would be used .
type: string
option:
$ref: '#/definitions/OptionSpec'
distance:
description: only valid when option is fuzzy search (when option is 3)
type: string
example: "3"
language:
description: iKnow-supported language model to apply, for example "en"
type: string
includeText:
description: whether the returned columns should include the column beging indexed by 'index'
type: integer
default: 0
enum: [0, 1]
columns:
description: specify the columns which also needed to be returned. For example, ["column1","column2"]
type: array
example: []
items:
type: string
highlightSpec:
$ref: '#/definitions/HighlightSpec'
description: the parameters needed for Highlight
rankSpec:
$ref: '#/definitions/RankSpec'
description: the parameters needed for Rank
where:
description: the valid SQL logical condition statement. For example, "column1 = ? AND column2 = ?"
type: string
responses:
200:
description: Successful response
schema:
type: object
properties:
rows:
type: array
default: []
items:
type: objectwhile it wouldnt be difficult to build a REST API to accept an arbitrary SQL statement and return results there are several things to consider.
1. a single SQL SELECT statement might return 10s of thousands of rows making the REST service problematic with respect to timeouts and payload response size
2. you will want to make sure SQL security so that someone isnt allowed to
- perform a delete if they dont have access to do so
- select data from tables the dont have access to.
etc.
I just realized the Document I was looking at was for 2025.2 not 2025.1 that I am on. I think I will ahve to go down the route of building the API manually.
The REST API is for
SQL Search
The InterSystems IRIS® SQL Search tool integrates with the InterSystems IRIS Natural Language Processor (NLP) to perform context-aware text search operations on unstructured data, with capabilities such as fuzzy search, regular expressions, stemming, and decompounding. Searching is performed using a standard SQL query with a WHERE clause containing special InterSystems SQL Search syntax.
Is your table using the NLP features?
I don't believe I built it within that table. It is a simple table to reference Epic Department information that I query from Epic Clarity.
See using the Query API of the "Source Code File REST API" -
Thanks, but when I try that I am getting nothing in return when I know the query should be returning something.
{
"status": {
"errors": [],
"summary": ""
},
"console": [],
"result": {}
}This should work.
What HTTP Method are you using POST (or GET)?
What HTTP status are you getting - 200 OK (or 405 Method Not Allowed)?
When I had originally tried executing the POST request, it came back blank, but now it is coming back populated
Great! So you have a built-in REST API for your SQL queries.
By the way (apart from the /api/atelier/.../query API which I mentioned in my other response, which is a built-in API) there is also this custom API which @Evgeny Shvarov built - sql-rest-api, see also related article.
💡 This question is considered a Key Question. More details here.