Sunday, July 30, 2006

FoxPro and the Windows API…

I’ve known for a long time that I ‘could’ leverage the Windows API (as can just about anyone) from within FoxPro, VB.Net and a score of other languages.

I just really haven’t done much with it until the past couple of weeks.

I’ve managed to pull together enough API functions to actually accomplish some things on the job that have been not only a problem, but a roadblock to progress as well!

One of the tasks, that has t take place monthly is the download of several files, some from FTP locations, and some from websites, these files are then parsed, massaged and utilized to produce the monthly supplements to the publications I’ve mentioned before.

At one time, a previous programmer had employed the object model from a mainframe connectivity product called “Extra!” to do some of this.

Unfortunately for me, and the users, somewhere else in IS the decision was made to switch to another product as the corporate standard.

Not that this is a bad thing, but, as part of the rollout each PC that had the new product installed, also had the old one removed. A very efficient, very common, practice, but it can also lead to some dependency issues. What I found was that with each of these changes, the application would cease to run on yet one more desktop.

So, I first looked for the manuals on the object model for the new product. Turns out the version in common use was no longer supported… I was however able to download a trial of the newer version, and began changing the code sections that involved communicating with, and downloading files from, the Mainframe.

In the middle of that I discover that the ‘trial’ version I’m using is being replaced by a new release… great! Now I’ll be building for the latest and greatest version!

Not so great, all of the functions did not work in exactly the same way, and required that I determine the version involved, and shift between now two separate sections of code to use the same product.

Needless to say, I got fairly frustrated.

For the most part I’ve automated the process to run on one PC from my “Automation Console” and have relieved the users of the responsibility.

Then about two weeks ago (around when I vanished from blog-land) I had a new problem.

We purchase a subscription to postal code databases. These are updated monthly and to retrieve the updates requires navigating to a website, logging in and starting the download process, individually, for each of the three files.

Now, in and of itself, this is not that big a deal, it only takes about 15 minutes in all to do so.

The bigger problem however is dependency. Several jobs, both on the Mainframe, and on the network, as well as the company’s interactions with an industry depend on the postal updates.

So, it’s either find a way to automate the process, or never be away from the office on the 1st of the month.

Necessity they say, is the mother of invention, and in this case at least it’s true!

I started doing a little research, and became very interested in a couple of Windows DLL’s called WinINet.dll and kernal32.dll.

A little more research and I found this site, which besides having a ton of free information on correctly using the Windows API from within FoxPro (well Visual FoxPro to be more specific), there’s also a membership (yes there’s a fee) option that opens a much wider group of code examples and ideas.

The best thing about it is that the code I received from them, was 100% royalty free, and I’m free to use it as I see fit. My mention of them here is one way for me to ‘give back’ a little, as their set up covers some very technical ground very nicely, and for a very reasonable price!

Now, on to the good stuff.

The problem:

I knew I could use the Internet Explorer object to navigate to the website in code, and possibly pass the username and password, but, when you do that you do not get an opportunity to pass a ‘local’ destination. As a result the “Save As” dialog box pops up, once again requiring user intervention to continue.

What I needed was a way to bypass that step and allow this process to be 100% automated.

The other issue was that the particular files I question are also “Zipped” and require “UnZipping” before the subsequent processes can utilize them.

I also needed an easy, and free, method of unpacking these files

The Solution

I employed functions from the WinINet.DLL to resolve the first issue. I’ve included a piece of sample code for those of you who aren’t completely bored with all of this, and might actually like to try something similar.

The second issue was a bit more difficult, I found a nice, free, unzip utility that runs from the command line. The problem was knowing when each process finished. A timer could be employed, but it’s fairly inaccurate, and the amount of ‘time’ would have to adhere to a ‘worst case’ scenario… Not something I like to do.

So, to solve that issue I turned again to the API kernal32.dll and a 32bit CreateProcess function that allows monitoring for the processes completion.

*---The sample for the HTTP download process---

*--WinINet FoxPro Option example

DO DECL && declare external functions

*--Make sure WinINet is available
hOpen = InternetOpen ("w32vfp=110", ;
IF hOpen = 0
? "GetLastError:", GetLastError()
? "WinInet is not available on this computer"

LOCAL lcUrlBase,lcFileName, lcPathDst, lcFileDst, ;
lnCaSize, lnUaSize, lnMxSize, lcUserName, lcPWord

lcUserName = "Your User Name"
lcPWord = "password"
*--Basic URL
lcUrlBase = ""
*-- remote file with UserName and password
lcFileName = "download.asp?file=114&login=" + ;
lcUserName + ;
"&password=" + lcPWord
*-- assign a destination directory
lcPathDst = "F:\DownLoads\"
*--&& assign a destination FileName
lnCaSize = GetPostalData(lcUrlBase,lcFileName, ;
lcPathDst, lcFileDst, ;
"DownLoading Canadian Postal " + ;
"Codes File. . .", 20)

*--release Session handle and library
= InternetCloseHandle (hOpen)

*--Now Extract Downloaded File
=Run32Bit("F:\UnzipUtility\7za e F:\Downloads\" + ;
" -of:\Railinc Postal*.* -y")



FUNCTION GetPostalData (lcBaseUrl, lcFile, lcDstPath, ;
lcDstFile, lcProgMess, lnProgMax)

* get a handle of the remote file
hFile = InternetOpenUrl (hOpen,;
lcBaseUrl + lcFile, "", 0,;

IF hFile <> 0
* even if there is no such file, the most evidence
* you will get is an ASCII file in response
* (404 error page)
lnFileSize = 0
lnProgVal = 0
lnByteCount = http2local (hFile, ;
lcDstPath + lcDstFile, ;
lcProgMess, lnProgMax)
= InternetCloseHandle (hFile)
? "Unable to open source file"
? "GetLastError:", GetLastError()

RETURN lnByteCount


FUNCTION http2local (hSource, lcTarget, lcMess, lnMax)
* reads data from a remote file
#DEFINE TransferBuffer 4096

*-- Progress Status Bar ------------
*---Note, if you have your own insert it here
* If you'd like this one, drop me an email
*--If you don't want one, comment this out
DO FORM cpsProgstat NAME wlStatBar
wlStatBar.Label1.CAPTION = lcMess

*--as we don't know the exact file size, we'll
* use what was passed or, if nothing default
* to 40K and reset as required
IF VARTYPE(lnMax) = 'N'
wlStatBar.ocxProgressBar.MAX = lnMax
wlStatBar.ocxProgressBar.MAX = 40
*-------end progress/stat init

* create the target file
hTarget = FCREATE (lcTarget)
IF (hTarget = -1)
?? "invalid target file name"

LOCAL lnTotalBytesRead, lnBytesRead
lnTotalBytesRead = 0

lcBuffer = REPLI (CHR(0), TransferBuffer)
lnBytesRead = 0

IF InternetReadFile (hSource, @lcBuffer,;
TransferBuffer, @lnBytesRead) = 1

= FWRITE (hTarget, lcBuffer, lnBytesRead)
IF lnBytesRead = 0

lnTotalBytesRead = lnTotalBytesRead + lnBytesRead
IF MOD(lnTotalBytesRead,1000) = 0
lnProgVal = lnProgVal+1
*--Reset bar if required
* Comment this section out if not using
* a progress bar
IF lnProgVal > wlStatBar.ocxProgressBar.MAX
lnProgVal = 0
wlStatBar.ocxProgressBar.VALUE = lnProgVal
*-- end progress bar

= FCLOSE (hTarget)

*-- More Satus/Progress Bar updates
wlStatBar.Label1.CAPTION=LTRIM(STR(lnTotalBytesRead))+ ;
" bytes Ok"

RETURN lnTotalBytesRead


DECLARE INTEGER GetLastError IN kernel32

DECLARE INTEGER InternetOpen IN wininet;
STRING sAgent, INTEGER lAccessTypem,;
STRING sProxyName, ;
STRING ProxyBypass, STRING lFlags

DECLARE INTEGER InternetCloseHandle IN wininet ;

DECLARE INTEGER InternetOpenUrl IN wininet;
INTEGER hInternet, STRING lpszUrl, ;
STRING lpszHeaders,;
INTEGER dwHeadersLength, ;
INTEGER dwFlags,;
INTEGER dwContext

DECLARE INTEGER InternetReadFile IN wininet;
INTEGER hFile, STRING @lpBuffer,;
INTEGER dwNumberOfBytesToRead,;
INTEGER @lpdwNumberOfBytesRead

INTEGER InternetQueryDataAvailable IN wininet;
INTEGER @ lpdwBytesAvailable,;
INTEGER dwFlags,;
INTEGER dwContext

Last, but certainly not least the 32bit comand line process to allow us to know when each process completes, and return control to VFP when it does.


* Function Run32Bit
PARAMETERS pcFile2Run, plShowDone

*-- Return code from WaitForSingleObject() if
* it timed out.

*-- This controls how long, in milli secconds,
* WaitForSingleObject()
* waits before it times out. Change this to
* suit your preferences.

*--Declare the required kernal32 items
DECLARE INTEGER CreateProcess IN kernel32.DLL ;
INTEGER lpApplicationName, ;
STRING lpCommandLine, ;
INTEGER lpProcessAttributes, ;
INTEGER lpThreadAttributes, ;
INTEGER bInheritHandles, ;
INTEGER dwCreationFlags, ;
INTEGER lpEnvironment, ;
INTEGER lpCurrentDirectory, ;
STRING @lpStartupInfo, ;
STRING @lpProcessInformation

DECLARE INTEGER WaitForSingleObject IN kernel32.DLL ;
INTEGER hHandle, INTEGER dwMilliseconds

DECLARE INTEGER CloseHandle IN kernel32.DLL ;

DECLARE INTEGER GetLastError IN kernel32.DLL

*-- STARTUPINFO is 68 bytes, of which we need to
* initially populate the 'cb' or Count of Bytes
* member with the overall length of the structure.
* The remainder should be 0-filled
START = long2str(68) + REPLICATE(CHR(0), 64)

*-- PROCESS_INFORMATION structure is 4 longs,
* or 4*4 bytes = 16 bytes, which we'll fill with nulls.
process_info = REPLICATE(CHR(0), 16)

*-- Start the program that was passed in
* (EXE name must be null-terminated)
File2Run = pcFile2Run + CHR(0)

*-- Call CreateProcess, obtain a process handle.
* Treat the application to run as the
* 'command line' argument, accept all other
* defaults. Important to pass the start and
* process_info by reference.
RetCode = CreateProcess(0, File2Run, 0, 0, 1, ;
NORMAL_PRIORITY_CLASS, 0, 0, @START, @process_info)

*-- Unable to run, exit now.
IF RetCode = 0
=MESSAGEBOX("Error occurred. Error code: ", ;
*-- Extract the process handle from the
hProcess = str2long(SUBSTR(process_info, 1, 4))

*-- Use timeout of TIMEOUT_INTERVAL msec
* so the display will be updated.
* Otherwise, the VFP window never repaints until
* the loop is exited.
IF WaitForSingleObject(hProcess, ;

*-- Show a message box when we're done,
* If desired
IF plShowDone
=MESSAGEBOX ("Process completed")

* Close the process handle afterwards.
RetCode = CloseHandle(hProcess)


FUNCTION long2str
* Passed : 32-bit non-negative numeric value (pnLongVal)
* Returns : ASCII character representation of passed
* value in low-high format (lnretstr)
* Example :
* m.longval = 999999
* lnLongStr = long2str(m.long)


PRIVATE i, lnretstr

lnretstr = ""
FOR i = 24 TO 0 STEP -8
lnretstr = CHR(INT(pnLongVal/(2^i))) + lnretstr
pnLongVal = MOD(pnLongVal, (2^i))
RETURN lnretstr

FUNCTION str2long
* Passed: 4-byte character string (lnLongStr)
* in low-high ASCII format
* returns: long integer value
* example:
* lcLongStr = "1111"
* lnLongVal = str2long(lcLongStr)


PRIVATE i, lnRetVal

lnRetVal = 0
FOR i = 0 TO 24 STEP 8
lnRetVal = lnRetVal + (ASC(lcLongStr) * (2^i))
lcLongStr = RIGHT(lcLongStr, LEN(lcLongStr) - 1)

Well, I hope that helps you with a problem you might have in the future. Again, if you really want to get a 'handle' on integrating API funtions into your VFP applications, check out News2News!! They've got the goods!!

Technorati Tags: - - -
-IceRocket Tags: - - -


No_Newz said...

This is were I thank my lucky stars I don't have these kinds of problems. Of course if I did, I'd just call for your assistance. :D
Lois Lane

Spirit Of Owl said...

Blimey! With VB and VC++ using the Windows API is requisite if you want to get anything done at all, but I've never had to get around these kind of problems! :)

Bill said...

Lois - I be happy to help! I don't see these as problems though... It's just what I get paid to do, NOT having this to do, now *that* would be a problem!!

Spirit - God to see you! I'm getting more, and more interested in what's available inside the API.. amazing stuff 'eh?

Craig Bailey said...

Hi Bill,

I agree that the Win32 site is awesome. There's a few other VFP tools you might be interested in too:
(the VFP ones have the little Fox logo next to them)

Also, depending on whether you need to show download progress you can use the MS XML object to do downloads (takes about 7 lines of code). Here's a link to an example of where I have used it for downloading managed funds data:


Bill said...

Craig - I actually wrote a little 'progress bar' that does that... but I'll definitely check out the XML object.

Thanks for the links!