FilePro: Dead End Technology, rapidly approaching a brick wall

Disclaimer

This page in no way represents the opinions of ANYONE except the maintainer of this web page. Well, actually, it might. But it certainly does not reflect the opinion of my employer, nor my co-workers. I know for a fact that it doesn't represent the opinion of Sue Rubin or of FilePro Technologies or of STN. I may be the only person on Earth who dislikes FilePro. If so, you may happily ignore this page, and bask in my foolish rant.

Furthermore, Perl should not be taken as an example of an ideal programming language. It's quick, dirty, and can be highly arcane. A person is more than capable of writing thoroughly obfuscated and non-supportable code in Perl. It is, however, relatively OS-agnostic, and is well documented.

How does FilePro compare to Perl?

In a filepro "file" (which is equivalent to a DB's table) fields can be named, but cannot be referenced by name--only by column number, basically[1]. So, in order to do something sane, like get the value of the "employeeName" field from the record where employeeNum = 33, and assign it to a variable named en, one would do this with an SQL database and perl, and DBI:

		$result = $db->prepare("select employeeName from Employees where employeeNum = 33");
		$result->execute();
		$en = @row[0];

Note that this would easily allow for fetching dozens of records and sticking them into an array--with very little code modification. To do this in filepro, I must first declare an index on the Employee table, based on the field which holds employeeNum. We'll call that index "A". Remember, I only get 16 automatic indexes (which are updated every time the records are changed), and 10 demand indexes (which are static snapshots of data). And, we'll need to know that employeeName is held in the 2nd field:

	::aa(3,.0) = "33":
	::lookup emp = Employee i=A k=aa -nx:
	::en(33,*) = emp(2);

Not bad, eh? Sure, it was more work than Perl, but we did it in three lines of code, didn't we? Mostly. We didn't include the steps necessary to define the index--which means that if you need to be looking up to a table based on 17 different criteria, you can't have real-time views of the DB on all of them.

Let's take our example above, and flesh it out a little, so that we'll find all records of people named "Brent." We don't know if the name is properly capitalized or not:

	#!/usr/bin/perl

	use DBI;

	$db = DBI->connect("DBI:mysql:database=COMPANY;user=root");
	$result = $db->prepare("select firstname, employeeNum from Employee where firstname like '%rent%'");
	$result->execute();
	while (@row = $result->fetchrow_array()) { print "$row[0], $row[1] \n"; }

That's the entire script, mind you. It will grab any number of rows which contain those values, and print them out.
Now, let's do this in filepro. First, create an output format, and in it, specify the values that correspond to the field numbers for employee's first name, and their employeenumber. That'll allow us to print things. Now, if you're associating your code with the Employee table, it's not bad. Simply create a selection set that specifies:

	:2:co:rent
	:::
	:::
	:::
	:::
	:::
	:::
	:::
	:::
	:::
	:::

There is a curses-like tool for doing this.
Now, run this handy-dandy command, after remembering to set your report to display to video:

	/appl/fp/dreport Employee -fp NAMEOFYOURPROCESSINGTABLE -S NAMEOFTHESELECTIONSETFILE

How could that not be easier?. I mean--there's only, what 2 files to keep track of, each modified with separate editors, instead of my 8 lines of code. If you're intending, however, to do lookups from multiple tables, think again. It becomes drastically more complex.

For the real power for filepro, let's look and check to see if we've got a value between 23 and 26 in a numeric field:

#!/usr/bin/perl
if (($aa > 22) && ($aa < 27)) { print "found a value between 23 and 26\n"; }

#filepro
:aa ge "23" and aa le "26":show "@found a value between 23 and 26":

That's not bad at all! Just don't forget the quotes around "26", or it won't check for the literal 26, it'll compare to the value in FIELD 26. How utterly *bleep*ing intuitive.

Let's check for the presence of the word "Red" -- note the special case sensitivity:

if ($aa =~ /Red/) { print "found Red\n"; } #filepro Darn. Can't be done easily. I have to first do a co to see if the word red appears anywhere, then figure out a way to strip that out, and see if it starts with a big R or little r. I've looked at the raw data that filePro stores, and it's stored with case-sensitivity, IIRC. But I don't have a case-sensitive comparison operator. Ugh.

Let's look now for either pig or pin, in any case:

#perl
if ($aa =~ /pi[gn]/i) { print "found a pig or pin\n"; }

#filepro
:aa co "pin" or aa co "pig": show "@I found a pig or pin":

Again, what we have here is an issue of scaling: Regular expressions are insanely flexible, and scale very well. Due to its lack of case-sensitive data, filePro ends up being simply incapable of some of these things. Now, if your data is such that SOUP is always the same thing as Soup and SoUp, you're fine.

Now, let's grab all the lines which start with "fee":

#perl
if ($aa =~ /^fee/) {print "started with fee\n";}

#filepro
:aa eq "fee":show "found fee in first 3 characters":

See, here, we just check aa versus a 3-character string. It will match just fine, because apparently, it will only check against the first three characters of the string. This is as opposed to the co operator, which would find 'fee' anywhere in the string.

Okay, let's see if filepro can determine if the string has only the word "fee" in it.

#perl
if ($aa =~ /^fee$/) {print "contains only fee\n";}

#filepro
:aa eq "fee":goto chk:
::end:
chk::ll(4,.):
:len(aa) eq "3":show "@yep, aa only contains fee":

Simple as apple pie, right? It's not like the inability to run two commands in a single line complicates things at all.

Now let's really push things: The LAST three characters of a string must be "bar":

#perl
if ($aa =~ /bar$/) { print "ended with bar\n";}

#filepro
::zz = aa{"":
:mid(zz,len(zz)-"2","3") eq "bar":show "@found BAR at the end of the field":

There we go. It's only about 2x the code. Thanks to the guys at hvcomputer.com for that tip. note that hvcomputer.com is owned by Ken Brody--the dude who is the main programmer on filePro. heh. Still, look at that code--we have to create another string, we have to to assign part of the other string to it, and then we have to do our comparison. When you want to make a function that does a compare like that, things are going to get even more complex.

[1] Well, you can define text names for columns, but it's an extra step. None of the filePro code I was ever exposed to actually did this.

Note that there is a rebuttal of this page, wherein they clearly spell out that I am not an expert in filePro. Nope, I'm not. But I can tell you this: I tried for a damn year to learn filePro, and never made heads or tails of it. I taught myself perl in a couple of weeks, so I was far enough along that my code did what I needed. I was producing functional python code within a few days of getting the O'Reilly book. There's a LOT I don't know about the filePro language. I blame this on the lack of any decent documentation. As I said, there is a SINGLE book available on filepro, and it took me 25 minutes just now to dig through fptech's on-line manual, trying to find the syntax of the "co" comparator.

Mind you, this is likely to continue the code arms race with fptech/hvcomputer.com. Which is good, because it forces me to re-examine my perl every now and then. And perhaps, when time is copious, I really will get around to writing a translator for filePro code...something to turn it into PHP, so it's supportable, and to translate the tables into MySQL, so the data is actually searchable. On the other hand, there is sometimes a great value in starting with a clean slate, and building a system from the ground up.

return to the Index page