ASP + FileMaker Pro: An Example

dEAk · January 29, 2002

Well, after trawling various forums looking for a way to get my webpages pulling data out of FMP and finding a few examples (non of which worked) and crap support from Apple, here's how I finally got it to work.

A word to the wise, though... FMP is a desktop application and has a very poor performance supporting concurrent users. Infact even basic SELECT queries are unacceptably slow, even for a single user. You cannot run a commercial website with it, especially as its ODBC driver only supports Level 1 queries (no nice JOINS et al for you, buddy). In my code I've included a Timer function so you can see for yourself how quickly it pulls out the records.

In terms a comparison FileMaker Pro 5.5 was retrieving 500 records in around 6 seconds, SQL Server 2000 pulled the same records in just over 1 second. If you were to the apply indexing and use Stored Procedures it would easily be under 1 second for SQL Server. The other thing to bare in mind is that SQL Server will happily offer this performance with hundreds concurrent users. Another fact to bare in mind... an INSERT query in FileMaker seemed to add only 1 record per second!

If you can't afford SQL Server I would advise Microsoft Access, as it's Jet Driver will support 70+ concurrent users without a noticeable loss of performance. Combined with using Indexing and Views, it would be ok for a small site. I am no expert on FileMaker products, perhaps the Server product offers acceptable performance for this kind of application?

Anyway set up your environment:

1. In FileMaker, make sure Remote Access Companion is ticked in your Preferences (Edit, Preferences, Application).

2. Next make sure Multi-User and Remote Access Companion are enabled in Sharing (File, Sharing).

3. On your local machine open Administrative Tools, ODBC Data Sources. Create a System DSN. Use the FileMaker Pro ODBC driver and tick Use Remote Connection and enter 127.0.0.1

4. Make sure the Database you want to play with is open.

5. You may need to restart IIS for it to pick of the new DSN.

Next the code... the structure of FileMaker databases is a bit different to what I'm used to. Each database seems to be made up of just one table (which is named the same as your database).

<%

OPTION EXPLICIT

Dim strSQL, objRS, objDBConn, strMsg, varStart, varEnd, varHowLong

strMsg = ""

varStart = Timer

Set objDBConn = Server.CreateObject("ADODB.Connection")

objDBConn.Open "yourdsnname"

strSQL = "SELECT * FROM yourtablename"

SET objRS = Server.CreateObject("ADODB.Recordset")

objRS.Open strSQL, objDBConn

Set objRS = objDBconn.Execute(strSQL)

If (objRS.EoF) Then

strMsg = "No records found."

Response.Write strMsg

Else

Do While Not objRS.EoF

Response.Write 'objRS(" THE NAME OF YOUR RECORD ")

objRS.MoveNext

Loop

End If

objRS.Close

Set objRS = Nothing

objDBConn.Close

Set objDBConn = Nothing

varEnd = Timer

varHowLong = varEnd - varStart

Response.Write "<BR><BR>Query Timer: " & varHowLong

%>

Good luck... hopefully you Cold Fusion users can work from this.

/dEAk

Garry Claridge · January 29, 2002

Try this:

http://yourIP tongue.gif" border="0 ort/FMPro?-dbname=yourtable.fp5-lay=yourlayout&-format=myformat.html&-findall

The 'myformat.html' can contain:

<html>

<body>

[FMP-Record]

[FMP-Field:myfield]<br>

[/FMP-Record]

</body></html>

Check what the speed is like!

I too use ASP and SQL and Access. However, developing in CDML is much faster and easier. It is not the best for all situations, however it is fine for most. It is also very cost-effective.

All the best.

Garry

dEAk · January 29, 2002

I don't mean to be down on what you're saying, but CDML is pointless.

Why would you want to use a proprietary language if you can use ASP, Cold Fusion, PHP, Perl or whatever, for the same thing? Don't waste time learning CDML when you could teach yourself a skill that is re-useable. Not only will your applications run fast, they will be easily extensible and portable to other databases or applications. If you're a consultant or developer you will have a very nice addition to your resume.

Why build a website using a proprietary language? It's crazy. Suppose the underlying database changed... you're screwed. You may think: "Oh it will always be FileMaker", but if you're developing web applications yourself then you will quickly realize how limited you are with CDML. The client I am doing work for is now appreciating that FileMaker Pro was not the best choice for them in terms of that all-important buzzword, extensibility. They saved money by not choosing Oracle or SQL Server initially, but now they're paying for this short-sighted approach. Fine by me, I get paid to fix problems just like this.

Another angle is this... Suppose you want to re-use/re-sell your wonderful new web application you've written but your new prospect has a different database? If you'd started in ASP or ColdFusion you would have would have a very portable application that you can re-sell.

Suppose the client wants to change the look and feel of their website? With ASP and XML/XSL you can change the layout/design without even touching database or logic levels of the application. No messy HTML embedded logic.

Do any large commecial websites use CDML? Is it scaleable? Is it fast?

/dEAk

Vaughan · January 29, 2002

Err, it depends on what you mean by "proprietary." ASP, Cold Fusion are proprietary from the point of view that thay have been created privately by a company. Your argument doesn't make sense. ASP, CF and CDML all nead some sort of middleware that you've got to buy and is not freely distributable. That's what I think of when I hear the term proprietary. Perl and PHP are proprietary too but the middleware might just be freely available.

Now if you said "CDML has reached it's use-by date" then you'd proably get a lot of agreement. However CDML still stands as a quick and (relatively) easy method of getting interaction between FMP and the WWW, and it's in the same box as the database so it's cheap.

Most importantly, CDML not vaporware.

Anatoli · January 29, 2002

RE: Why would you want to use a proprietary language if you can use ASP, Cold Fusion, PHP, Perl or whatever, for the same thing? Don't waste time learning CDML when you could teach yourself a skill that is re-useable. Not only will your applications run fast, they will be easily extensible and portable to other databases or applications. If you're a consultant or developer you will have a very nice addition to your resume.

That made me laugh. I am beating all that SQL and ASP guys on price, on speed of development, on speed of finished applications, on lowest support charge and all price/performance aspects.

I will not argue about SQL powering bank transactions. Fine, that is not our market.

But your testing dear dEAk are just joke. You used your (for me obsolete) methods, that is fine. My result is:

Served 6 requests per second with search and sort.

To search 30 MB database full of press releases for word "mode" (will find also modem, models etc.) and sort and display 2600 records from 4 000 is less than 1 seconds.

Oh well, I am glad you are not in our camp, less competition here smile.gif" border="0

dEAk · January 30, 2002

Hey no need to get insulting. You said yourself you are not in my market, and that's just fine. Typically companies I work for wouldn't have something like FileMaker, this is the first time I've encountered it. I am more used to big ERP systems.

If you're making a living out of FileMaker consultancy work, all power to you. It's a small market. I am technology agnostic; I use the best tool for the job. If that's ASP, Java or Perl I don't care. If it's an Oracle database or an AS/400, I don't care.

The points I was talking about were extensibility and portability, two essential elements for ANY business. Even small businesses and SMEs. If you build an application in a closed proprietary language you are opening yourself up to problems and additional cost in the future. With the clients I work for (mostly B2B), closed proprietary systems are dead in the water. You and your proposal wouldn't even make it through the door.

I find the figures for search times you quote interesting, but can you back them up? Are these through a web-interface using CDML? What FileMaker product? As I said though, the slow search and update times were only part of my concern. FileMaker, for this project, is part of a much larger web presence and CDML doesn't offer even 1% of the functionality required.

In terms of price, you probably would beat me every time based on the rate that you quote... but I've never won a job by being the cheapest.

/dEAk

P.S. Can't remember the last time I worked with a 30MB database.

[ January 30, 2002: Message edited by: dEAk ]

Anatoli · January 30, 2002

I was not trying to insult you; on the contrary, you insulted FileMaker because of bad testing procedure.

I know that you are 1 or 10 levels above me in your skills.

You are right in the “best tool for the job” and that is precisely what I am doing.

You still didn’t get near the power of FM. Look at the page http://www.filemaker.cz/pages/novinky/novinky.html

On right side for companies, which are using FM. Some of them are using something from me.

Sometimes the do use things like SAP and Oracle. But smaller workgroups cannot use that for their specific needs. So they use FileMaker. Search FMI site for some success stories.

Also you will found strongest criticism of FileMaker from me on this forum. Sometimes people don’t like me because of that smile.gif" border="0

I can back up any claim I've made here. If you will be here, I can show you that. The CDML is direct interface between web page and FM, in my case standard Unlimited.

Also, when Adobe discussion forum for GoLive collapsed last year that was because it was served from badly written application in Oracle. The only GoLive forum for 2 months was John May from Point in Space running on FileMaker/Lasso combo.

And you should be blamed for sentences like “As I said though, the slow search and update times were only part of my concern. FileMaker, for this project, is part of a much larger web presence and CDML doesn't offer even 1% of the functionality required.”

You even didn’t scratch the surface of CDML and FM combo and you are declaring such laughable statements! We do host on single FM software on obsolete NT server:

Bertelsmann/Springer Technical weekly magazine.

B2B ordering system for Czech chemical company.

PRnet press portal.

Web Catalogue for largest Czech Company with gifts items. Their UK partners are 3 years behind in this.

The same company for special offers.

Discussion forum for Bentley Systems is in final stage of development.

Discussion forum for FileMaker Cz.

Another 2 Discussion forums are programmed.

And money, well if depends on how much is one bringing home. I will not compete with you, because of low local economy performance, but my customers are happy and I am bringing home 3 time more, than ASP and SQL average guys and I am cheaper, than their offers. It is productivity, what counts! smile.gif" border="0

You can see that this is another planet with another ways how to do job and profit.

scratchmalogicalwax · January 31, 2002

Calm down chaps! !!!

Horses for courses simple as that...if you are worried about using CGI / Middleware that limits the data sources you can use but don't fancy learning many languages go for Lasso.

(some interesting white papers here regarding performance compared to ASP and CF using SQL server )

http://www.blueworld.com

Also the new Lasso V will come with built-in mySQL runs on the best OS ever and has all the benefits offered by an App running on UNIX based achitecture (I'm a biased Mac head can't you tell!!! it's nice to have something these enterprise boys a jealous of!!)) the Lasso app has also now been split from the web server to allow better performance, scalability etc

BUT

you can still use it with FMPro if you don't want to spend TENS of Thousands of pounds / dollars on enterprise database software to publish your mini site...or simply can't be bothered with mySQL

My horse is still filemaker (with Lasso).....for now

laugh.gif" border="0

Sign In

ASP + FileMaker Pro: An Example

Recommended Posts

dEAk

Garry Claridge

dEAk

Vaughan

Anatoli

dEAk

Anatoli

scratchmalogicalwax

Create an account or sign in to comment

Create an account

Sign in

Browse

Site Support

Forums

Blogs

Marketplace

Activity

Important Information