Saving Webservice call to CSV file is Intermittent

Report · Nov 03, 2011

I created the following code to call a Webservice Feed using POST and save the data to a .csv file..

- I have the CF Scheduler set to run this page four times a day

- I notice that sometimes the file gets updated, sometimes it does not..?

- If I run the page manually, it works only 1 out of 4 times? (automatically overwriting the old csv)

Any ideas on what to do to make it work every time?

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Web Service Call</title>
</head>

<cfset theURL = "https://webservice.php">

<cfhttp
        method="post"
        url="#theURL#"
        port="443"
        throwonerror="True"
        resolveurl="yes"
       delimiter=","
      textqualifier=""
        path="C:\ABS"
        file="AC_CHG.csv">

Report · Nov 03, 2011

Does your application capture an error event when your process fails? That will probably be the single most useful piece of information to troubleshoot your problem. I would recommend surrounding your CFHTTP and CFFILE code with try/catch blocks to capture any errors that are generated by the application and send the CFCATCH scope to you via email (if you are not already doing this). Hopefully the system generates an error message that makes sense and points you in the right direction. It could be a number of different items, though the fact that it only happens occasionally is a little suspicious.

Do you know if your target URL that you are using for CFHTTP is hosted on a load balanced server set up? Sometimes differences in certificates or server settings (or even code) between load balanced servers can result in intermittant issues like what you are describing. CFHTTP and SSL also have a long history of mutual dislike and can make troubleshooting an SSL connection problem tricky. Or it could be a good old fasioned wite-to-file-system problem on your server. The presence (or lack of) a CF error will go a long way to help narrow the possibilities down.

Report · Nov 18, 2011

The code below works every time, but does not automatically save the File? (It just displays the comma-delimited results to the screen)

Question: What can I do to make it automatically save the CSV text file to "c\mba\AC_CHG.csv" ?

-----------------------------------------------------------------------

<p>Key:

CompID:

Table:

</p>

</cfform>

---------------------------------------------------------------------

Report · Nov 18, 2011

The code that handles saving the file is a PHP file, not a ColdFusion file. Despite the fact that you are using cfform to construct your form interface, once the data is submitted to the PHP action page, all processing (incuding file saving) is done using your PHP application server and NOT Coldfusion. If you are experiencing problems with the behavior of the PHP script, I recommend posting on a PHP-oriented message board (stackOverflow for instance). Unless you intend on re-writing your PHP code into ColdFusion code, this forum may not give you the answer you need to solve your mystery.

Hope that helps,

- Michael

Report · Nov 18, 2011

Michael, thanks for the reply..

What I'm trying to do is very simple & common, but just not working..

1) Call a webservice url..

2) Post to it three variables: webServiceKey, companyId, queryTableName

3) Get back a Comma-Delimited Text file

When I run the page in my previous post, it works fine:

1) It displays a form with those three fields (w/defaulted values)

2) I click submit button

3) The Comma-Delimited text comes back & displays on my browser window screen

I just need this page to run automatically (without clicking the submit button) , and then save the output to c:\mba\AC_CHG.csv

ps: I'll be running this page from a Scheduled task in CF..

Report · Nov 18, 2011

Ah, I misread your original problem.

Sending data to remote web services can be accomplished programatically using <cfinvoke> (if the web service is SOAP based) or using <cfhttp> if the web service is HTTP/Restful based.

Using one of the two tags above, you can send data to the web service and then write the response to a file using <cffile> (make sure your CF server has the propper permissions set to be able to write to your directory structure otherwise you might get a permission error).

Since you are currently sending data via HTTP POST I would recommend looking into how to use the <cfhttp> tag. It should allow you to accomplish what you are looking to do.

Report · Nov 18, 2011

Which brings me back to my original post:

1) That code (using cfhttp) worked fine for months, then just stopped working or works intermittently..?

2) Keep getting this error.. have tried all kinds of things listed on the forums, compression, ssl, etc, no good?

3) How would I add your cftry & cfcatch to see more error details?

4) When I use the cfform code above, it works everytime, but I do not know how to save the file to the server.

Note: Using CF 9,0,0,251028 Standard

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Web Service Call</title>
</head>

<cfset theURL = "https://webservice.php">

<cfhttp
        method="post"
        url="#theURL#"
        port="443"
        throwonerror="True"
        resolveurl="yes"
       delimiter=","
      textqualifier=""
        path="C:\ABS"
        file="AC_CHG.csv">

Report · Nov 18, 2011

Ah, I know that error message well. My suspicion is that you need to import all of the SSL certificates in the certificate chain for your target site's SSL certificate. Sometimes importing just the target site's SSL cert into the CF keychain is not enough. If you have already made the neccessary adjustments to compensate for compression, getting those parent certificates installed would be the next step.

If you surround your CFHTTP call with a try/catch block and email yourself a dump of the CFCATCH scope when it errors out on you, you should be able to get some additional details about what went wrong (HTTP status code is generally pretty useful here).

You can find detailed instructions on how to do that in this forum on through google. There is even a really slick CF administrator plug-in that gives you a visual interface for adding SSL certs.

Report · Nov 20, 2011

In a post about cfhttp at The Talking Tree website, one of the comments mentions the same Connection-Failure error message. A possible solution is as follows:

1) create a new directory, C:\abs;

2) add the Accept-Encoding and TE headers to the cfhttp post.

<cfhttp

method="post"

url="#theURL#"

port="443"

throwonerror="True"

resolveurl="yes"

delimiter=","

textqualifier=""

path="C:\abs"

file="AC_CHG.csv">

</cfhttp>

Report · Nov 21, 2011

BK, Thanks for the post..

But unfortunately I already tried the compressed headers thingy and still doesn't work..

I'm ready to give up completely on using CFHTTP if I can figure out how to just use the CFFORM code below that works manually: (or any other code that will call the url, pass the parameters, and save to CSV file?)

--------------------------------------------------------------------- --

<p>Key:

CompID:

Table:

</p>

</cfform>

---------------------------------------------------------------------

Report · Nov 21, 2011

Surround your code with a try catch block, generate the error again and capture a dump of the FULL cfcatch scope and paste it here. Make sure you turn robust debugging on. My guess is still that you don't have all the certificates imported correctly into the cf keystore. The HTTP status code from the dump will tell you a lot about why cfhttp is failing.

Report · Nov 22, 2011

jlig wrote:
BK, Thanks for the post..
But unfortunately I already tried the compressed headers thingy and still doesn't work..
I'm ready to give up completely on using CFHTTP if I can figure out how to just use the CFFORM code below that works manually: (or any other code that will call the url, pass the parameters, and save to CSV file?)

And what if you cfhttp without the port attribute (and within try-catch block, as Insuractive advises)?

Adobe Community

Saving Webservice call to CSV file is Intermittent