CFHTP Get issue - CF2021 U13

[cld007]

Hi,

I need to retrieve mailing results from the third party we use to send our newsletter.  Results can be exported to a .CSV file from their site but this is limited to 500 records. 

I already know what the output will look like (see partial output below from their site). They also have an API we can use but it is also limited to 500 records.

Env: CF2021 Enterprise update 13 on Windows Server 16

Response Body

{
  "TotalCount": 1084,
  "Bounces": [
    {
      "RecordType": "Bounce",
      "ID": 3252889833,
      "Type": "HardBounce",
      "TypeCode": 1,
      "Name": "Hard bounce",
      "Tag": "",
      "MessageID": "11417f42-a012-4cc0-9a2e-22fa35436c85",.....

My code below (filters are hard coded for now):

<cfhttp url="https://api.thirdParty.com/bounces?count=500&offset=0&type=HardBounce&todate=2024-10-03&fromdate=2024-10-03" 
method="get" 
name="test1"
result="results"
> 
<cfhttpparam type="header" name="third-party-Server-Token" value="blahblahblah">
<cfhttpparam type="header" name="Accept" value="application/json">
</cfhttp>

The error messages I get:

Diagnostics

The column name {TotalCount:1084 is invalid. Column names must be valid variable names. They must start with a letter and can only include letters, numbers, and underscores. <br>The error occurred on line 21.

Message

The column name {TotalCount:1084 is invalid.

Detail	Column names must be valid variable names. They must start with a letter and can only include letters, numbers, and underscores.
ErrNumber	0
Message	The column name {TotalCount:1084 is invalid.

I can easily tell that it doesn't like the very first thing it finds in the result (Total number of records) but shouldn't it not care about the content of the result and let the next line of code in my pgm take care of it...

CFHTTP is followed by a deserializeJSON(results.filecontent) but it never gets there as it cannot go passed the CFHTTP.

This is the first time I use CFHTTP along with deserialzeJSON()... but the current issue is with the CFHTTP.

Any help would be much appreciated.
Thanks,
Claude

[Charlie Arehart · Accepted solution]

Simple answer: remove the NAME attribute used on your cfhttp. 

Explanation: that's causing cfhttp to think it's retrieving a csv file, which it would turn into a cf query for you (having that NAME). Then it's also presuming the first line of the file retrieved holds the names of COLUMNS to use for that query, whose rows it would expect in the subsequent lines.

It's indeed a little-known feature, and we could argue the error message could be made more clear. Anyway, the content you're retrieving definitely has neither of those, so just remove the NAME.

Let us know how it goes. 

[cld007 · Accepted solution]

Amazing what a NAME can do! And I named it in case I would have to refence it later on...

Thank you very much Charlie. 

[Charlie Arehart]

Great to hear and glad to help...and for the older folks here, "I love it when a plan comes together". 🙂 Thanks for marking the "answer".