Need help calling an API using JSON

[Eagleeyes Jim]

I've been using CF inside the firewall for years; I've never really had to do API calls.  That said, I have the below problem that I don't know how to solve and I'm hoping someone can help.  I have searched this site and StackOverFlow and while I find suggestions, I can't find an answer.

Can someone help?  I have been experimenting with CFHTTP but can't seem to figure it out.  I'd provide a sample of what I'm doing, but I think I am so far off it won't help.Here is the information I received for this need:

This API allows external systems to request a report and provides an endpoint to check for the status of the report's completion. Once completed, the contents of the report can be downloaded as a CSV file. 

The flow for using the reports API is simple: send an HTTP POST request containing a JSON-formatted payload the metadata on what kind of report to run and what kinds of columns to include and what to filter by. If successful, the response will contain the URL to poll for the status of the report that will be generated. Poll this URL until it shows a completed status and a URL where the final CSV file is located.

You can make the HTTP request using any HTTP client you want, but the simplest would be to use the "curl" command. The curl request will look like this, substituting the underlined parts for the appropriate values:

curl -i -X POST --user "YOUR_USERNAME:YOUR_PASSWORD" \
          https://myserver.com/api/reports/report_runs \
          -H 'Content-Type: application/json' \
          -d '{
          ...
        }'

Explanation: The -i flag will print the response header out, which will contain a header named Location to poll for the status of the report. The -X POST flag specifies the request method of POST which is necessary to append a payload. The --user "YOUR_USERNAME:YOUR_PASSWORD" flag specifies that the request will use basic authentication which will transmit the username and password, separated by a colon (:) character, in base 64 encoding. The -H "Content-Type: application/json" flag specifies that the payload is in JSON format, then the -d "{...}" flag specifies the actual payload. The payload is explained below. Finally, the URL is at the end of the command - ensure that the domain matches whatever domain you are using to access the application.

The payload is a JSON object structured as follows:

{
  "report": "report_name",
  "columns": [ "column_one",  "column_two"  ],

  "filters": [  { "column": "column_two", "operator": "=", "threshold": 5 }  ]

}

[haxtbh]

It would still be good to see what you have as a starting point.

I assume you are asking how to do it otherwise?

There are various examples here:

https://stackoverflow.com/questions/8932973/how-to-post-json-data-to-remote-api-using-coldfusion-cfh...

<cfset stFields =
{
  "report": "report_name",
  "columns": [ "column_one",  "column_two"  ],
  "filters": [  { "column": "column_two", "operator": "=", "threshold": 5 }  ]
}> 

<cfhttp url="http://api.url.com" username="myuser" password="mypass" method="post" result="httpResp" timeout="60"> 
	<cfhttpparam type="header" name="Content-Type" value="application/json" /> 
	<cfhttpparam type="body" value="#serializeJSON(stFields)#"> 
</cfhttp>

[Eagleeyes Jim]

Thanks for this.  I now have the following code - which still isn't working.  Any additional thoughts?

<cfset v.MyJSON = serializeJSON( {
'report': 'job_profitability',
'columns': [ 'project_id', 'customer_name', 'units_produced', 'unit_of_measure' ],
'filters': [ { 'column': 'project_id', 'operator': '=', 'threshold': 2059877 } ]
} )
>

<cfset Local.MyUserName = ToBase64("username@domain.com")>
<cfset Local.MyPassword = ToBase64("myPassword")>

<cfhttp name="CallPM" method="post" result="httpResp" timeout="60"
url="https://myserver.com/api/reports/report_runs"
username=Local.MyUserName password=Local.MyPassword >
  <cfhttpparam type="header" name="Content-Type" value="application/json /">
  <cfhttpparam type="body" value=v.MyJSON>
</cfhttp>

When I run this, I get the following info:

httpResp.StatusCode: Connection Failure. Status code unavailable.

httpResp.FileContent: Connection Failure

httpResp.errorDetail: I/O Exception: Received fatal alert: protocol_version

httpResp.MimeType: Unable to determine MIME type of file.

THANKS FOR ANY THOUGHTS!!!

[haxtbh · Accepted solution]

What version of Coldfusion are you using, along with what version of Java?

If its old, then it may not be using the right TLS Protocol supported by the remote API server. I have only see this if you have an older version of Java whish doesnt support TLS 1.2

Also

<cfhttpparam type="body" value=v.MyJSON>

Should be

<cfhttpparam type="body" value="#v.MyJSON#">

[haxtbh]

As you have to base64 the credentials as well, you may need to remove the username and password attributes and use the following cfhttpparam:

<cfhttpparam type="header" name="Authorization" value="Basic #ToBase64("username@domain.com:myPassword")#" />

[Eagleeyes Jim]

Thanks for the quick reply.  I am currently testing this on ColdFusion Version 11.  In the jre directory, it looks like java version 1.7.0.

I updated the code as suggested - it looks like this:

<cfhttp name="CallPM" method="post" result="httpResp" timeout="60"
url="https://myserver.com/api/reports/report_runs" >

<cfhttpparam type="header" name="Authorization" value="Basic #ToBase64("email@domain.com:password")#">
<cfhttpparam type="header" name="Content-Type" value="application/json /">
<cfhttpparam type="body" value=#v.MyJSON#>
</cfhttp>

Still getting the same error message.

[haxtbh]

This version of Java will be too old.

I believe TLS 1.2 is supported in 1.8 and above. You will also CF 11 HF 3 or higher.

You could try setting -Dhttps.protocols=TLSv1.2 in the JVM config first before updatign to see if it works.

[Charlie Arehart · Accepted solution]

I want to add to this: what matters is NOT what version you find to be in the CF JRE folder--unless you also confirm in the CF Admin (java and jvm page) or jvm.config that CF is POINTING to that CF JRE folder (for its java home value). If instead you or someone else modified CF to use some OTHER JVM, then the quesiton is about the version of THAT JVM.

Also, on this matter of how the JVM can impact calls out from CF to https, I will share that I did a post with more: https://coldfusion.adobe.com/2019/06/error-calling-cf-via-https-solved-updating-jvm/ . And yes, the problem with CF11 (and even sometimes CF2016) and earlier CF versions was that they often use an older JVM, which may not support some SSL/TLS versions/protocols, etc.

Just updating the JVM may be your answer, though you do need to be pay attention to what versions of CF support whate versions of Java, as I discuss in resources pointed to in that post. For instance, CF11 never supported Java 11, and never will. But you could go to Java 8 as long as you had applied CF11 update 3. And later versions of Java 8 supported later versions of TLS.

Also, as for the need to base64 the credentials, I'm not sure that is "needed". The CFHTTP tag does support use of that username and password attribute, and it implicilty is for basic auth. So if you do try a later JVM and it still doesn't work (perhaps with a new error), consider going back to what you had originally.

Let us know how it goes.

[Eagleeyes Jim]

THANK YOU!  THANK YOU!  THANK YOU!

After I upgraded the java, it worked great.  I really appreciate the help you guys provided.  Now to build it out but it is awesome it is working.  Again, can't thank you guys enough.

Here is the final code for anyone who may stumble upon this in the future:

<cfhttp name="CallAPI" method="post" timeout="60" result="APIResponse"
columns="status_url"
url="https://myserver.com/api/reports/report_runs">
<cfhttpparam type="header" name="Authorization" value="Basic #ToBase64("myemail@domain.com:myPassWord")#">
<cfhttpparam type="header" name="Content-Type" value="application/json">
<cfhttpparam type="body" value=#v.MyJSON#>
</cfhttp>