Trying to get INSERT FROM URL TO WORK

OliverBarrett · March 10, 2023, 10:13pm

I have a POST request that works fine in POSTMAN, but trying to get it to work in FileMaker is less simple than I expected.

Below is the CURL command in progress. I've beat my head against the wall for about 2 hours after getting the same POST to work in about five minutes in POSTMAN and another 2 minutes in Python.

Any suggestions would be welcome!

Thanks,

"--X POST" &
"--header \"API-Key: 123456\"" &
"--header \"Content-Type: application/json\"" &
"--header \"Accept: application/json\"" &
"--data {\"" &
    "content: document,\""&
    "options: {\"" &
    "resultLanguages:[\"" &
            "spa,\"" &
            "deu,\"" &
            "jpn,\"" &
         "],\"" &
        "count: 2\"" &
    "}\"" &
"}\""

xochi · March 10, 2023, 10:31pm

I think you are having issues with quoting.

If I take what you posted, and clean it up:

replace all \" characters with a regular " character
remove the leading " and trailng " &

I get this:


--X POST 
--header "API-Key: 123456" 
--header "Content-Type: application/json" 
--header "Accept: application/json" 
--data {" 
    content: document,"
    options: {" 
    resultLanguages:[" 
            spa," 
            deu," 
            jpn," 
         ]," 
        count: 2" 
    }" 
}"

Which looks to me as if you have some unwanted dangling quote characters.

One thing you can do is use the
--data @$fmvariable
syntax which may help you keep your JSON data clean

xochi · March 10, 2023, 10:38pm

I see a second issue - you don't have any whitespace between those lines, so for example:

"--X POST" &
"--header \"API-Key: 123456\""

will evaulate to a string which is:
--X POST--header "API-Key: 123456"

Notice how there's no space between the T in POST and the following --header option

OliverBarrett · March 11, 2023, 11:26am

I tried all your suggestions and now get:

{"code": "unauthorized", "message": "authentication required to access this resource"}

Appreciate your help and reply.

xochi · March 11, 2023, 2:53pm

It sounds like you are getting closer -- can you paste your updated CURL command string?

OliverBarrett · March 11, 2023, 3:11pm

Not really. I had to put the \" back into the CURL to get it to authenticate again.

This API has a python interface so we'll just go that route.

Normally, I can get the INSERT FROM URL to work, but I've always been surprised how much we're on our own (compared with how POSTMAN works) to get the CURL options to be formatted 'Just so'. The FMP CURL Options have always seemed "half done" to me.

Thanks again.

xochi · March 11, 2023, 3:34pm

Agree the FileMaker CURL options are somewhat awkward.

One idea is to build your own mini version of Postman for testing, e.g. have each CURL option separated into its own Key / Value fields, and then have a script handle all the calcultions for you, to ensure that quoting is done by machine rather than by hand.

Another idea is to use the --show-error options as described here:

Handling errors

Unsupported cURL options are ignored.

If you use the --show-error option:

When errors returned by the cURL library cause FileMaker clients to return error code 1631 via the Get(LastError) functionto indicate an unspecified connection failure, the Get(LastErrorDetail) function returns the same text that the curl command-line tool returns.

https://help.claris.com/en/pro-help/content/curl-options.html

OliverBarrett · March 11, 2023, 4:05pm

Thank you.

xochi · March 11, 2023, 11:38pm

Just for fun, here's my attempt.

each line starts off with a space character, so each key has proper whitespace
using the Quote() function, to more clearly indicate what is / isn't quoted:
put the JSON data in it's own variable for clarity:

Let(
[
  // create the JSON  languages array
  langs = "[]";
  langs = JSONSetElement(langs; 0; "spa"; JSONString);
  langs = JSONSetElement(langs; 1; "deu"; JSONString);
  langs = JSONSetElement(langs; 2; "jpn"; JSONString);

  // create the JSON options object
  opts = "{}";
  opts = JSONSetElement(opts; "resultLanguages"; langs; JSONArray);
  opts = JSONSetElement(opts; "count"; 2; JSONNumber);

 // combine the two
  json = "{}";
  json = JSONSetElement( json; "content"; "document"; JSONString);
  json = JSONSetElement( json; "options"; opts;  JSONObject);
  
 // insert the json object into the curl options string
  curl = 
    " --X POST" &
    " --header " & Quote("API-Key: 123456")  &
    " --header " & Quote("Content-Type: application/json")  &
    " --header " & Quote("Accept: application/json")  &
    " --data @$json"  
]; 

// return the result
 curl

)

Edit: fixed 3 typos.

OliverBarrett · March 12, 2023, 11:13am

That's nice. When I try to run it, though, it says not enough parameters, but doesn't specify exactly what's wrong. It's ironic that the CF editor doesn't have a debugging capability ether. LOL.

xochi · March 12, 2023, 3:00pm

Fixed typos in the first draft, but I think that won't work, since you need the $json variable separated. Try this, using these script steps:

Set Variable $jsonData =
Let(
[
  // create the JSON  languages array
  langs = "[]";
  langs = JSONSetElement(langs; 0; "spa"; JSONString);
  langs = JSONSetElement(langs; 1; "deu"; JSONString);
  langs = JSONSetElement(langs; 2; "jpn"; JSONString);

  // create the JSON options object
  opts = "{}";
  opts = JSONSetElement(opts; "resultLanguages"; langs; JSONArray);
  opts = JSONSetElement(opts; "count"; 2; JSONNumber);

 // combine the two
  json = "{}";
  json = JSONSetElement( json; "content"; "document"; JSONString);
  json = JSONSetElement( json; "options"; opts;  JSONObject)
];
  // return the result
  json
)  

Set Variable $curlOptions =
 // use the @$variable notation, reference the $jsonData variable into the curl options
    " --X POST" &
    " --show-error" &
    " --header " & Quote("API-Key: 123456")  &
    " --header " & Quote("Content-Type: application/json")  &
    " --header " & Quote("Accept: application/json")  &
    " --data @$jsonData"  

Insert from URL [ With dialog: On ; Target: $results ; 
"https://example.com/API" ; Verify SSL Certificates ; 
cURL options: $curlOptions ]

// the $results variable should hold the data, but if not...

// check for errors
If (Get(LastError) = 1631)
  Set Variable $errors = Get(LastErrorDetail)
End if

rivet · March 12, 2023, 8:54pm

To avoid troubleshooting escaped quotes, I like to use the Insert Text step. There I can paste my static text with some merge tags for substitution;


# prep
Set Variable [ $url ; Value: "https://example.com/API" ] 
Set Variable [ $api_key ; Value: "123456" ] 
Insert Text [ Select ; Target: $data ; “{"content":"document","options":{"count":2,"resultLanguages":["spa","deu","jpn"]}}” ] 
Insert Text [ Select ; Target: $curl ; “--X POST  --show-error  --header "API-Key: *|api_key|*"  --header "Content-Type: application/json"  --header "Accept: application/json"  --data @*|data|*” ] 
# 
#  merge
Set Variable [ $curl_options ; Value: Substitute ( $curl ; [ "*|data|*" ; $data ];[ "*|api_key|*" ; $api_key ]) ] 
# 
#  run
Insert from URL [ Select ; With dialog: Off ; Target: $results ; $url ; cURL options: $curl_options ]

xochi · March 12, 2023, 10:33pm

@rivet that's a great tip, I didn't know that was possible.

The Insert Text script step:

allows you to use multi-line string constant with quote characters
will put the value into a $variable
has a built-in tiny editor for editing the text block

It looks like this in use:

OliverBarrett · March 13, 2023, 4:35pm

Just a follow up that, unfortunately, the code you posted did not work and resulted in an authorization failure. It seems the \" may really be needed. Since I don't control the service side of this API, I'm unable to figure out why it's so picky.

Thanks again for your help.

menno · March 14, 2023, 6:33pm

Manual quoting is always tricky and should best be avoided by using Quote() as suggested by @xochi

I also noticed that the payload you created is not valid JSON. Try the following formula in a set variable, it creates all variables you'd for the insert-from-url. You just need to fill in the url and the api-key in there:

Let ( [ 
	$url = "https://webservice.example.com/" ; 
	apikey = 12345 ; 
	$payload = 
		JSONSetElement ( "" ; 
			[ "content" ; "document" ; 1 ] ; 
			[ "options.count" ; 2 ; 2 ] ; 
			[ "options.resultLanguages[0]" ; "spa" ; 1 ] ; 
			[ "options.resultLanguages[1]" ; "deu" ; 1 ] ; 
			[ "options.resultLanguages[2]" ; "jpn" ; 1 ] ) ; 
	$options = 
		Substitute ( 
			List ( 
				"-X POST" ; 
				"-H " & Quote ( "API-Key: {apikey}" ) ; 
				"-H " & Quote ( "Content-Type: application/json" ) ; 
				"-H " & Quote ( "Accept: application/json" ) ; 
				"-d @$payload" ) ; 
			[ "{apikey}" ; apikey ] )
] ; 
	"" 
)

Then use $url and $options in your IFU scriptstep. The reason for the list() in the $options is that FileMaker will convert the ¶-separated $options into space-separated by itself.

Compare the payload for the correct syntax with the one you succesfully used in Postman, before you use it

HTH

Topic		Replies	Views
Getting INSERT FROM URL To parse JSON Data? Questions community	12	267	October 10, 2023
How to obtain both Header and Body from Insert from URL Questions api	3	217	March 29, 2023
Base64Decode Question Questions community	6	399	June 12, 2021
ChatGPT and FileMaker chatgtp	0	233	May 5, 2023
Problem: Timestamp in JDBC vs In FileMaker Itself Questions community	20	227	August 3, 2023

Trying to get INSERT FROM URL TO WORK

Handling errors

Related Topics