Start Free Trial

asked on

CFfile upload and read - form submit

I have a project where I collect data from a digital phone system and save it on a local computer. I want the schedule a task that will open a CF page:

<form action="GetCallDataTextFile2.cfm"  
        enctype="multipart/form-data"  
        method="post"> 
    <p>Enter the complete path and filename of the file to upload: 
    <input type="file" 
            name="FiletoUpload" 
            size="45" value="d:\vbscriptwinsock\calldata_#dateformat(now(),'yyyymmdd')#.txt"> 
    </p> 
    <!--- <input     type="submit" 
            value="Upload">  --->
</form>

Open in new window

I don't need or want user interface. So, I need to submit the form because the next page gets the filename from the form scope;

<cffile action="upload" 
        destination="#request.Controlpath#phonedata\" 
        nameConflict="overwrite" 
        fileField="Form.FiletoUpload"> 
         
<---  <cfoutput> 
You uploaded #cffile.ClientFileName#.#cffile.ClientFileExt# 
            successfully to #cffile.ServerDirectory#. 
</cfoutput>   --->

<cfset theFile = cffile.serverDirectory & "\" & cffile.serverFile>

<cflocation url="index.cfm?fa=parseCallData&filename=#theFile#">

Open in new window

And the next page parses the data and updates the database.

How can I submit the form in a timely fashion without user interface and can I even do this process the way I have it laid out?

Schedule the task to run following the instructions below:
http://www.quackit.com/coldfusion/coldfusion_scheduled_tasks.cfm

ASKER

I wasn't very clear. I need to run a scheduled task on Windows XP. This machine is the one doing the data collection from the phone system. I need to upload that file to the server every 10 minutes. I can't think of another way to process the data.

Actually, I've been running this process for quite a while now. But the data was put into an SQL database by a third party software package that we are eliminating. I suppose I could use VBscript to update an MS SQL Express database. But I'm still curious about running a Coldfusion web page from Windows XP scheduled tasks.

Ok so let me see if I have this correct?

1. You want to have the XP computer call your Cold Fusion application (specifically the upload page)
2. Upload the file it has compiled from the phone system

Am I correct with the above assumption ?

If so, are you open to a powershell script to accomplish this via a regular windows scheduled task ?

ASKER

I'm open to about anything.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

It appears that I have to download/install Powershell, right?

yes if it's not present on the xp computer.

ASKER

Well, I'm not making a connection somewhere. I installed Powershell but it doesn't associate the PS1 file extension to it. What now? Windows doesn't know what to do with the file.

you need to open powershell

run powershell.exe then navigate to the directory you saved the script to and run it by typing in script.ps1 and hitting enter.

Try like this:

[b]FileName: GetCallDataTextFile2.cfm[/b]

<cfset request.Controlpath = "d:\vbscriptwinsock\myPath\">
<cffile action="read" file="d:\vbscriptwinsock\calldata_#dateformat(now(),'yyyymmdd')#.txt" variable="myStr">
<cfif !FileExists("#request.Controlpath#phonedata\calldata_#dateformat(now(),'yyyymmdd')#.txt")>
	<cffile action="write" file="#request.Controlpath#phonedata\calldata_#dateformat(now(),'yyyymmdd')#.txt" output="#myStr#">
<cfelse>
	<cffile action="write" file="#request.Controlpath#phonedata\calldata_#dateformat(now(),'yyyymmdd')#-#RandRange(1,100)#.txt" output="#myStr#">
</cfif>    
<cfset theFile = ListLast("#request.Controlpath#phonedata\calldata_#dateformat(now(),'yyyymmdd')#.txt",'_')>
<cflocation url="index.cfm?fa=parseCallData&filename=#theFile#">

Schedule

<cfschedule action="update"
task="myDailyTask" 
operation="HTTPRequest" 
url="http://#CGI.http_host#/GetCallDataTextFile2.cfm"
startdate="30-jan-20013"
starttime="00:45"
interval="daily"
>

Open in new window

Are you making any progress on this, or do you need additional help ?

ASKER

I have to collect the raw data from the phone system every 10 minutes and update the database on the server. I have that part working manually. The vb script runs continually, appending data to the daily file. I load a CF page on the local computer that deletes everything in the database for the day, reads the daily file locally , and updates the database. I can't use CF scheduled tasks because it has to run on the local computer.

The only option I can think of is creating an MSSQL database on the local computer. I can poll the SQL server from the CF scheduled task.

Ok so the powershell solution I provided (rudimentary) should work to do just that.

You can use the below link to install powershell v2 on your windows xp computer:
http://support.microsoft.com/kb/968929

We can then test the powershell solution which should work calling the cf web page and passing in the file name as a variable.

ASKER

I tried to follow the link and installed Windows Management Framework Core. What's next?

so Powershell should not be installed:

Save the below as cfrun.ps1 be sure to wrap the file name in quotes when you save it.
e.g. "cfrun.ps1"

$now = Get-Date -Format yyMMdd-HHmm
$filename = "d:\vbscriptwinsock\calldata_" + $now + ".txt"
$url = "http://yoururl "
$parameters = "FiletoUpload=$filename"	# your POST parameters
$http_request = New-Object -ComObject Msxml2.XMLHTTP
$http_request.open('POST', $url, $false)
$http_request.setRequestHeader("Content-type","application/x-www-form-urlencoded")
$http_request.setRequestHeader("Content-length", $parameters.length)
$http_request.setRequestHeader("Connection", "close")
$http_request.send($parameters)
$http_request.statusText

Open in new window

Then load powershell e.g. start - run - powershell.exe - enter

then navigate to the path where you saved the above file and type in the name cfrun.ps1 and hit enter.

We can then look at any errors and debug / resolve this for you.

ASKER

This is the results;

PS D:\vbscriptwinsock> ".\cfrun.ps1"
.\cfrun.ps1
PS D:\vbscriptwinsock> ".

dont use the quotes when you run it from the powershell command line

ASKER

No data was moved to the server. I'm debugging now. I don't have errors on the CF side.

Did anything get returned in the powershell window ?

It should tell you if it read the page and if there was an error passing any variable etc.

A good way to debug is to change the script to write-host anything you want to verify...
e.g

$now = Get-Date -Format yyMMdd-HHmm
#putting write-host $now will let you know the value of $now
write-host $now
$filename = "d:\vbscriptwinsock\calldata_" + $now + ".txt"
write-host $filename
$url = "http://yoururl "
$parameters = "FiletoUpload=$filename"	# your POST parameters

Open in new window

Also be sure you change "http://yoururl " to match the page you are calling.

ASKER

I added the write-host lines and got exactly the same results as I posted above.

did you replace the url with your URL ?

ASKER

When I click on the url in the script editor it opens IE and I get:

File not found: /control/phonedata/index.cfm

The url looks correct to me.

ASKER

This is the only thing I see in the XP app log:

A provider, WinRMProv, has been registered in the WMI namespace, root, to use the LocalSystem account. This account is privileged and the provider may cause a security violation if it does not correctly impersonate user requests.

you need the full url:

http://www.yourdomain.com/control/phonedata/index.cfm

ASKER

I wrote:

$now = Get-Date -Format yyyyMMdd
write-host $now

and saved it as pstest.ps1. I ran it and got;

PS D:\vbscriptwinsock> "pstest.ps1"
pstest.ps1
PS D:\vbscriptwinsock>

DO NOT use the double quotes when you run:

PS D:\vbscriptwinsock> pstest.ps1

ASKER

PS D:\vbscriptwinsock> pstest.ps1
The term 'pstest.ps1' is not recognized as the name of a cmdlet, function, scri
pt file, or operable program. Check the spelling of the name, or if a path was
included, verify that the path is correct and try again.
At line:1 char:11
+ pstest.ps1 <<<<
+ CategoryInfo : ObjectNotFound: (pstest.ps1:String) [], CommandN
otFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

PS D:\vbscriptwinsock>

you need to be sure you are in the same path as the script

It is always a good idea to hit tab once you enter the name to validate you are in the right directory.

ASKER

-a--- 2/1/2013 4:46 PM 7972 CallData_20130201.txt
-a--- 2/1/2013 4:34 PM 721 cfrun.ps1
-a--- 1/30/2013 9:16 PM 2423 client.vbs
-a--- 1/31/2013 2:13 PM 1824 ClientGetCallData.vbs
-a--- 2/1/2013 4:47 PM 49 pstest.ps1
-a--- 1/30/2013 7:22 PM 620 PSuploadCallData.ps1
-a--- 1/28/2013 11:34 AM 1982 server.vbs

PS D:\vbscriptwinsock> pstest.ps1
The term 'pstest.ps1' is not recognized as the name of a cmdlet, function, scri
pt file, or operable program. Check the spelling of the name, or if a path was
included, verify that the path is correct and try again.
At line:1 char:11
+ pstest.ps1 <<<<
+ CategoryInfo : ObjectNotFound: (pstest.ps1:String) [], CommandN
otFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

Suggestion [3,General]: The command pstest.ps1 was not found, but does exist in
the current location. Windows PowerShell doesn't load commands from the current
location by default. If you trust this command, instead type ".\pstest.ps1". See
"get-help about_Command_Precedence" for more details.
PS D:\vbscriptwinsock>

PS D:\vbscriptwinsock>.\pstest.ps1

ASKER

PS D:\vbscriptwinsock> .\pstest.ps1
File D:\vbscriptwinsock\pstest.ps1 cannot be loaded because the execution of sc
ripts is disabled on this system. Please see "get-help about_signing" for more
details.
At line:1 char:13
+ .\pstest.ps1 <<<<
+ CategoryInfo : NotSpecified: (:) [], PSSecurityException
+ FullyQualifiedErrorId : RuntimeException

PS D:\vbscriptwinsock>

Ok I keep forgetting we are starting you with PS from scratch.

so launch powershell as administrator
Start menu - windows powershell - right click run as administrator.

When it loads, run the following command:
Set-ExecutionPolicy bypass

Then we can continue from there...

ASKER

PS D:\vbscriptwinsock> Set-ExecutionPolicy bypass

Execution Policy Change
The execution policy helps protect you from scripts that you do not trust.
Changing the execution policy might expose you to the security risks described
in the about_Execution_Policies help topic. Do you want to change the execution
policy?
[Y] Yes [N] No [S] Suspend [?] Help (default is "Y"): Y
PS D:\vbscriptwinsock>
PS D:\vbscriptwinsock> ,\pstest.ps1
Missing expression after unary operator ','.
At line:1 char:2
+ , <<<< \pstest.ps1
+ CategoryInfo : ParserError: (,:String) [], ParentContainsErrorR
ecordException
+ FullyQualifiedErrorId : MissingExpressionAfterOperator

PS D:\vbscriptwinsock>

Can you paste me the full script you are running.

did you tried my solution or not

ASKER

$now = Get-Date -Format yyyyMMdd
write-host $now

I'm just trying to get PS to work.

ASKER

OK, I've moved the project to a Windows 7 64 bit machine and downloaded the 64 bit ActiveX component. I have the pstest.ps1 script running successfully. I am attempting to get the client script that polls 192.168.1.181:59003 and saves to a text file running.

Sounds great once we get this to work you can simply make it a windows scheduled task and you are good to go .

ASKER

$now = Get-Date -Format yyyyMMdd
# $filename = "d:\vbscriptwinsock\calldata_" + $now + ".txt"
$filename = "d:\vbscriptwinsock\calldata_20130201.txt"
$url = "https://dev.taxtalent.com/control/phonedata/index.cfm"
$parameters = "FiletoUpload=$filename,fa=uploadCallData" # your POST parameters
write-host $now
write-host $filename
write-host $parameters
$http_request = New-Object -ComObject Msxml2.XMLHTTP
$http_request.open('POST', $url, $false)
$http_request.setRequestHeader("Content-type","application/x-www-form-urlencoded")
$http_request.setRequestHeader("Content-length", $parameters.length)
$http_request.setRequestHeader("Connection", "close")
$http_request.send($parameters)
$http_request.statusText
write-host $http_request.statusText

PS D:\calldata> .\cfrun.ps1
20130202
d:\vbscriptwinsock\calldata_20130201.txt
FiletoUpload=d:\vbscriptwinsock\calldata_20130201.txt,fa=uploadCallData
Exception calling "send" with "1" argument(s): "The download of the specified r
esource has failed.
"
At D:\calldata\cfrun.ps1:14 char:19
+ $http_request.send <<<< ($parameters)
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ComMethodTargetInvocation

Unknown
Unknown
PS D:\calldata>

Can we test a simple form first (without ssl http://) to see if we get the expected behavior / response ?

Maybe a simple form that reads the two form values and returns would help to let us know if this is working as expected.

I will set up a quick CF server on a box here and test this for you as well.

ASKER

My "framework" needs to run through index.cfm and I need to figure out how to bypass my login security. Maybe I can go through another "public" site. The one I'm working on isn't "public".

I'm thinking I need to setup an express SQL server and just dump the data into that on the local PC. That way the existing SQL polling task can just continue to do what it's doing.

Ok so it sounds like you have an application framework etc, session - authentication.

Maybe just running a simple ftp upload might be helpful, we may end up creating a more complex solution for a simple problem.

What flexibility do you have where the windows 7 machine is concerned ?

I think at this stage if you are thinking of going with sql express why not just put a developers versions of CF on it and create a scheduled task in CF to do this ?

ASKER

Then I can access my SQL server and just update it?

There would be a lot of options in that instance, including simply crafting a local cold fusion page to handle the upload etc.

ASKER

I have a CF page called GetCallDataTestFile.cfm;

<cfset attributes.suppresslayout2 = "true">

<cffile action="write" addnewline="yes" file="pstesttext.txt" output="#now()#">

I have a PS script name cfrun.ps1;

$now = Get-Date -Format yyyyMMdd
# $filename = "d:\vbscriptwinsock\calldata_" + $now + ".txt"
$filename = "d:\vbscriptwinsock\calldata_20130201.txt"
$url = "https://dev.taxtalent.com/control/phonedata/GetCallDataTest.cfm"
$parameters = "FiletoUpload=$filename,fa=uploadCallData" # your POST parameters
write-host $now
write-host $filename
write-host $parameters
$http_request = New-Object -ComObject Msxml2.XMLHTTP
$http_request.open('POST', $url, $false)
$http_request.setRequestHeader("Content-type","application/x-www-form-urlencoded")
$http_request.setRequestHeader("Content-length", $parameters.length)
$http_request.setRequestHeader("Connection", "close")
$http_request.send($parameters)
$http_request.statusText
write-host $http_request.statusText

The results of running cfrun.ps1 is a text file in the CF temp directory with;

{ts '2013-02-02 16:44:38'}

ASKER

So, now I need to FTP the text file to the server before the http request.

do you need an ftp solution to simply ftp the file up to the server ?

That might be the easiest solution..

ASKER

I've completely reverted to VBscript and everything is running fairly well. I just have to figure out a better way to handle the timing of creating/appending the text file and uploading the file to the server.

you can script this easily and run a scheduled job.

you can use a command line batch script to mput the file on the ftp sever, also you can script appending the text file and make it all one process.

I can write a simple powershell to get the info from the source and append the text file then upload via ftp to your ftp server.

I think that would accomplish what we started out to do here ...

ASKER

The only trouble I'm having now is passing the filename and office in the http request.

Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP.3.0")
dim parameters
parameters = "filename="& Replace(OutputFile, "$DATE$", varDate) & ", office=tul"
'wscript.Echo parameters
parameterslen = len(parameters)
'wscript.echo parameterslen
oXMLHTTP.Open "GET", "https://www.tt.com/control/tasks/updateCallData.cfm", False
oXMLHTTP.setRequestHeader "Content-type","application/x-www-form-urlencoded"
oXMLHTTP.setRequestHeader "Content-length", parameterslen
oXMLHTTP.setRequestHeader "Connection", "close"

oXMLHTTP.Send parameters

If oXMLHTTP.Status = 200 Then
	Set oStream = CreateObject("ADODB.Stream")
	oStream.Open
	oStream.Type = 1
	oStream.Write oXMLHTTP.responseBody
	oStream.SaveToFile "d:\vbscriptwinsock\getcalldatatestfile.txt", 2
	oStream.Close
End If

Open in new window

Try using

Parameters= "filename=value&office=value"

oXMLHTTP.Send parameters

Here is also a really good example:
http://www.example-code.com/vbscript/http_post_form.asp

ASKER

Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP.3.0")
dim parameters
parameters = "?filename="& Replace(OutputFile, "$DATE$", varDate) & "&office=tul" 
'wscript.Echo parameters
parameterslen = len(parameters)
'wscript.echo parameterslen
oXMLHTTP.Open "POST", "https://www.taxtalent.net/control/tasks/updateCallData.cfm", False
oXMLHTTP.setRequestHeader "Content-type","application/x-www-form-urlencoded"
oXMLHTTP.setRequestHeader "Content-length", parameterslen
oXMLHTTP.setRequestHeader "Connection", "close"

oXMLHTTP.Send parameters

If oXMLHTTP.Status = 200 Then
	Set oStream = CreateObject("ADODB.Stream")
	oStream.Open
	oStream.Type = 1
	oStream.Write oXMLHTTP.responseBody
	oStream.SaveToFile "d:\vbscriptwinsock\getcalldatatestfile.txt", 2
	oStream.Close
End If

Open in new window

Even tried putting ? in the parameters. Results the same;

<tr>
        <td bgcolor="#4646EE">
            <font style="COLOR: white; FONT: 11pt/13pt verdana" color="white">
            Error Occurred While Processing Request
            </font>
        </td>
    </tr>
    <tr>
        <td>
            <font style="COLOR: black; FONT: 8pt/11pt verdana">
    

    <table width="500" cellpadding="0" cellspacing="0" border="0">
    <tr>
        <td id="tableProps2" align="left" valign="middle" width="500">
            <h1 id="textSection1" style="COLOR: black; FONT: 13pt/15pt verdana">
            Element FILENAME is undefined in URL.
            </h1>
        </td>
    </tr>

Open in new window

ASKER

I'm sending myself an email from the server with a dump of #URL#. This is one of the last resuslts. I don't unserstand the dash in the filename parameter.

struct
filename-calldata_20130206.txt [empty string]
office tul

so here is a suggestion, define the filename variable first then simply pass it to your parameters:

e.g.

Not a vbscript person so you have to tweak

dim filevar
filevar = Replace(OutputFile, "$DATE$", varDate)
Parameters= "filename=filevar&office=value"

ASKER

Had to convert to VBscript but a good start