FM Pro + Rev (was: ODBC Drivers for Fm Pro 5.5 II)

[Peter Reid]

>At 12:00 pm +0100 23/5/02, Peter Reid wrote:
>
>>It seems that it is finding the file OK, but doesn't like it for 
>>some reason?!  Does the file have to be .html or can it be any text 
>>file? Also, does the file need to include any additional HTML, or 
>>can it be literally the following two lines:
>>
>>   JobCode: [FMP-field: JobCode]
>>   JobDesc: [FMP-field: JobDesc]
>
>Sorry, Peter. I can't help here. I've only used the xml formatting 
>with FM. I'm not sure what you need to do for the html format files.
>
>Cheers
>Dave Cragg

Thanks Dave & Tariel, I have now worked out the problem and a solution.

For those who are interested in using FM Pro as a Web database 
accessed from Rev, here's what I've learnt and tested:

1) The results format file MUST be in the FM Pro Web folder (or a 
sub-folder of it).  The Web folder is inside the folder in which FM 
Pro is installed.

2) The results format file to return all records from a table is of the form:

[FMP-record][FMP-field: fieldName1]|[FMP-field: fieldName2]|etc
[/FMP-record]

(this produces a record per line with each field separated from the 
next by a "|", nice and easy to parse within Rev!)

3) The results format file to return a single record is of the form:

[FMP-field: fieldName1]|[FMP-field: fieldName2]|etc|[FMP-CurrentRecID]

(this returns the logical rec ID as well as the fields which is used 
when updating an existing record)

4) The following handler will return all the records in a table and, 
if used with the results format file in 2) above, these will single 
record per line, fields | separated:

function FMProFullTable theTable, theLayout, theResFormat
   put "http://IPorNAMEaddr/FMPro?" into theURL
   put "-db=" & theTable & "&-lay=" & theLayout & "&-format=" & 
theResFormat & "&-findAll" \
       into theCmd
   post theCmd to URL theURL
   return urlDecode(it)
end FMProFullTable

(IPorNAMEaddr is the actual IP address in dot form, e.g. 192.168.1.1 
or the site name, e.g. www.webDB.com)

5) The following handler locates a record that matches a given 
condition and returns its field together with its logical record 
number (used with results format 3) above):

function FMProFindRec theCondition, theTable, theResFormat
   put "http://IPorNAMEaddr/FMPro?" into theURL
   put "-db=" & theTable & "&-format=" & theResFormat & "&" & 
theCondition & "&max=1&-find" \
       into theCmd
   post theCmd to URL theURL
   return urlDecode(it)
end FMProFindRec

(the condition is of the form "&fieldName=requiredValue")

6) The following handler updates the record with the specified logical rec ID:

function FMProUpdateRec theRecID, theUpdate, theTable, theResFormat
   put "http://IPorNAMEaddr/FMPro?" into theURL
   put "-db=" & theTable & "&-format=" & theResFormat & "&-recid=" & 
theRecID & theUpdate \
       & "&-edit" into theCmd
   post theCmd to URL theURL
   return urlDecode(it)
end FMProUpdateRec

(the Update is of the form "&fieldName1=newValue1&fieldName2=newValue2" etc.)

All the above have been tested with a Win XP Pro laptop running FM 
Pro 5.5 as the "server" and a Mac OS X G4 running Rev 1.1.1 as the 
"client".  Both reading and writing from/to the FM database/table 
works fine!

If anyone would like the files that I used to do this test of Rev 
working with FM Pro 5.5, then please e-mail me off-list (the complete 
set of files are only about 70Kb in total, 8Kb when zipped). Please 
note that I have NOT attempted to do this within the limits of the 
Starter Kit, though it would be easy to do so if necessary.

Thanks again to all who gave me advice.

Cheers

Peter
-- 
Peter Reid
Reid-IT Limited, Loughborough, Leics., UK
Tel: +44 (0)1509 268843 Fax: +44 (0)870 052 7576
E-mail: preid at reidit.co.uk
Web: http://www.reidit.co.uk