FileMaker Data API

[PaulSamuelson]

I am trying to use the FileMaker Data API to connect to my FileMaker server. I can successfully get a token and perform a find using Python, but I need to make it work in WinBatch. I can share my python if that will help.

This will successfully get a token:

oHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")

;login and get token*******************************************************
cURL = 'https://<server>/fmi/data/v1/databases/WorkFlowPlus/sessions'
userid = 'XXXX'
apikey = 'XXXXXXXX'                                                                   
;oHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
oHTTP.Open("POST", cURL, @FALSE)
sAuth="Basic ":Base64StringFromClearString(B64GetCookie(),userid:":":apikey)

oHTTP.SetRequestHeader("Authorization",sAuth)
oHTTP.SetRequestHeader("Content-Type", "application/json");

oHTTP.Send()

oHTTP.WaitForResponse()
If oHTTP.Status == 200
   token = oHTTP.GetResponseHeader("X-FM-Data-Access-Token")
EndIf


This gives me a 200 code, but says I have invalid JSON:

cURL = 'https://<server>/fmi/data/v1/databases/WorkFlowPlus/layouts/APIJobs/_find'

; Open connection
oHTTP.Open("POST", cURL, @FALSE)
oHTTP.SetRequestHeader("Content-Type", "application/json")
sAuth = "Bearer " : token
oHTTP.SetRequestHeader("Authorization", sAuth)

param = '{"query": [{"WorkOrder": "209157"}]}'

; Convert the param string to UTF-8
utf8_param = ChrStringToHex (param)

; Send request with UTF-8 encoded parameter
;oHTTP.Send(param)
oHTTP.Send(utf8_param)

; Wait for response
oHTTP.WaitForResponse()

; Output full response for debugging
responseText = oHTTP.ResponseText
Pause("Response Text", responseText)

; If status is not 200, print out the response and error message
If oHTTP.Status != 200
   display(1, "Error Response", responseText)
   Exit
EndIf

If oHTTP.Status == 200
   headers = oHTTP.GetAllResponseHeaders()
   display(1, "Response Headers", headers)
   pause("RecordList", responseText)
EndIf

Then this successfully logs out:
cURL = 'https://<server>/fmi/data/v1/databases/WorkFlowPlus/sessions/':token
oHTTP.Open("DELETE", cURL, @FALSE)
oHTTP.Send()

oHTTP.WaitForResponse()
If oHTTP.Status == 200
   headers = oHTTP.GetAllResponseHeaders()
   Display(1,"logged out", "")
EndIf

I have tried with and without the UTF8 encoding. Here is the documentation for the API:
https://help.claris.com/archive/docs/18/en/dataapi/#perform-a-find-request

What am I missing? It seems to be something with the oHTTP.Sent(param) or oHTTP.Send(utf8_param).

Thanks,

Paul

[JTaylor]

What does oHTTP.ResponseText show where it says you have invalid JSON?

Jim

[PaulSamuelson]

What does oHTTP.ResponseText show where it says you have invalid JSON?

Jim

Headers:
Date: Wed, 08 Jan 2025 23:20:25 GMT
Transfer-Encoding: chunked
Content-Type: application/json
Vary: Accept-Encoding
Access-Control-Allow-Origin: *
X-Powered-By: ARR/3.0
X-Frame-Options: SAMEORIGIN
X-XSS-Protection: 1; mode=block
X-Content-Type-Options: nosniff
Strict-Transport-Security: max-age=31536000; includeSubDomains


ResponseText:
{"messages":[{"message":"invalid json","code":"1630"}],"response":{}}

That 1630 FileMaker error is "URL format is incorrect".

[JTaylor]

That is easy then, just send the correct URL format ;-)

What is it supposed to return at that point?   A security token for use in subsequent calls?

If you alter the URL from what you think it is supposed to be do you get the same message or a HTTP error code?

Jim

[PaulSamuelson]

The first part successfully gets a token. I know the token is valid, because I can use it in other calls, such as listing layouts and scripts and logging off.

If I change the query field from WorkOrder to something invalid, I get the same result, so it is not getting to the point of doing the query.

The URL is the same as in my Python test and works fine there. The result is a JSON array of data about the database and query result.

I think it has something to do with 'param', which needs to be a properly-formatted JSON array.

In Python, this works:
work_order = 209157
fms_url = 'https://<server>/fmi/data/v1/databases/WorkFlowPlus/layouts/APIJobs/_find'
fms_headers = {
    'Content-Type': 'application/json',
    'Authorization': f'Bearer {token}'
}
fms_auth = None
fms_payload = {
    "query": [{"WorkOrder": work_order}]
}
# connect to FMS via REST API
fms_response = requests.post(fms_url, headers=fms_headers, auth=fms_auth, data=json.dumps(fms_payload))
fms_data = fms_response.json()
print(fms_data)

[JTaylor]

Okay.  Misunderstood where the problem was occurring.

Don't think I can answer this one.   Not sure how you would format that in WinBatch.  Maybe the JSON Extender?  Not sure if deserialized JSON is the same thing.

Sorry.

Jim

[td]

The lines

; Convert the param string to UTF-8
utf8_param = ChrStringToHex (param)

puzzles me. The function ChrStringToHex does not convert a string to UTF-8, and the string contained in the param variable is already in UTF-8 because low ASCII-encoded text is identical to UTF-8-encoded text.

[PaulSamuelson]

The lines

; Convert the param string to UTF-8
utf8_param = ChrStringToHex (param)

puzzles me. The function ChrStringToHex does not convert a string to UTF-8, and the string contained in the param variable is already in UTF-8 because low ASCII-encoded text is identical to UTF-8-encoded text.

ChatGPT told me the JSON needed to be encoded and said this was correct. It could have been hallucinating...

[td]

Good luck with ChatGPT. Don't get me started...

I could be wrong but I think the Python json.dumps method just spits out human readable UTF-8 and the Python example uses an HTTP POST verb which means it is in the request body. Generally, once you set the "Content-Type" header to "application/json" you do not need to do any conversions on the data unless you are using 8-bit ASCII characters in the JSON data and you do not appear to be using any 8-bit ASCII characters in your JSON data.

It may help to also use "Msxml2.XMLHTTP.6.0" instead of "WinHttp.WinHttpRequest.5.1" as your ProgId. The former gives some indication of a better option on newer versions of Windows.

[PaulSamuelson]

"Msxml2.XMLHTTP.6.0" seems to have solved my problems, after minor adjustments.

Thanks!