Accessing FP data from external programs (MS Assess)

[Brian K. White]

----- Original Message ----- 
From: "Phil Hausman" <phausma1 at nycap.rr.com>
To: "Filepro List" <filepro-list at seaslug.org>
Sent: Saturday, November 12, 2005 4:24 PM
Subject: Accessing FP data from external programs (MS Assess)


> Good afternoon,
> As many have expressed, I'd like to be able to use another development 
> tool to access filePro data, but it looks like ODBC in that direction is 
> way off in the future, if at all.   Since many development tools include 
> the functionality to issue a system command, is it feasible to issue a 
> system command to, for example, run dreport with a prc table that accepts 
> a parameter (ie account number), reads that record, and returns something 
> (ie name) to the command line.   filePro is on Unix.    Does anyone 
> currently do this, or something similar?    Any help greatly appreciated.

I wrote a simple rreport table that does exactly that, that runs from a cgi 
script actually.
You issue a url containing a simple, crude really, query parameters and it 
returns the matching records in csv form.
It works as far it goes but the people on the other end who were going to be 
getting data via the cgi never finished the project so it never got used 
much.
As it stands, it runs out of a single file and you get to ask for records 
from that file by a simple selection sentence (24 gt @td+7, 1 eq 11324, 
etc...)
It always seemed like it would be a straightforward progression of further 
enhancements from there to allow you to specify what file to get the records 
from, specify what fields to to export, allow more complex selection 
criteria, allow writing as well as reading. I envisioned it eventually 
having transactions being sent from one box to another this way.
I consider it a skeleton starting point for something useful.
By now, I think Mark has recently finished a complete transaction system 
based on xml and his onegate product. I haven't looked at it myself but it 
sounds like nothing less than a bonafied milestone for fp functionality.

This is the report command line and the two process tables involved.
I chopped the csvq table down to represent a file that only has 10 fields 
per record. I generated the repetative parts of that table by a little one 
line shell loop since the real file had over a hundred fields being 
exported. (the file has several hundred but you don't necessarily have to 
export every field just because it's there) JHexport could do it for you 
too.
The export table is actually dead simple, most of it is just 3 copies of the 
same block of code to optionally generate header lines showing the field 
names, lengths and edits.

The query parameters are passed via eviroment variables:
FLD accepts either a field number or @rn
OPR accepts any fp comparison operator eq, gt, co, etc...
VAL accepts a static value, a filepro system field name like @td, and simple 
expressions like @td+7

report command line:
FLD=<field> ; export FLD
OPR=<operator> ; export OPR
VAL=<value> ; export VAL
OUT=<output filename>
rreport somefile -fp csvq -a -v varsel -y "" -u -r $OUT >/dev/null 2>&1

prc.varsel:
http://nj.aljex.com/csvq/prc_varsel.html

prc.csvq:
http://nj.aljex.com/csvq/prc_csvq.html

The real copy of this is run from a cgi script that sets fp variables for 
skipping locked records and/or readonly reading all records, various things 
to deal with the fact that fp is essentially not non-interactive, like 
PFMBTO etc..., checking a password before running, modifying it's behaviour 
according to some options added to the query string, and catting the output 
file back to the user. But I didn't want to clutter this with cgi related 
stuff. Just show the simple process of being able to request simple records 
according to non-static selection criteria. This could be a system command 
in your other db easily if fp is running on the same box. If not, well then 
the cgi wrapper is a possible solution.

Brian K. White  --  brian at aljex.com  --  http://www.aljex.com/bkw/
+++++[>+++[>+++++>+++++++<<-]<-]>>+.>.+++++.+++++++.-.[>+<---]>++.
filePro  BBx    Linux  SCO  FreeBSD    #callahans  Satriani  Filk!