Creating Records - Simultaneous Connections

[Singlequanta]

We recently launched a solution that incorporates a "Survey" module. This filemaker file contains two tables: questions and answers. Each has their own respective indexing and there is a relationship between Q & A based on Q's index.

In the survey, there are 20 questions. Ergo, 20 answers to post to the database upon completion.

Here is the dilema (and it's a good one):

When posting the records we are using a simple While loop as such:

while (x < 20) {

// the posting routine goes here

$post -> doQuery;

$x++

}

Now we have used both FM-and-PHP and FX.PHP and the results are identical....

The loop creates the 20 records just fine. No problem at all. BUT.... During this process it consumes 20 tcp/ip connections on the server.

If 5 users simulataneously connect to the system and post their survey answers, all 100 tcp/ip sockets are consumed. The 6th user must wait until the tciip times out for the first connection.

Add to this the problem that on windows 2003 the minimum you can set the TCP_WAIT timeout value to is 30 seconds.

Major problem.

Does anyone know a way around this? Surely I can't have been a bad programmer in writing a for-next loop that creates 20 records in the database....

I have written to the guys at FMWEbschool but alas i haven't had a reply in three days.

[Garry Claridge]

Can you create a new record for each question as it is answered?

Good Luck.

Garry

[Singlequanta]

We could, but that doesn't alleviate the problem. Not onless each user is forced to wait for 30 seconds between questions..... If there are 20 questions to answer then the user would be there for a ten minutes...

[Garry Claridge]

You have something serioulsy wrong if there is a 30 sec wait!

Good Luck.

Garry

[andygaunt]

Could you not pass your values to a global in FileMaker and then use a script to parse through the global and generate your new records?

This way you would only use one connection.

[Singlequanta]

Yes there is something seriously wrong!! LOL. Well it's not a laughing matter.

Yes Andy, we could post all of the data to a global field chunked together and then parse it into individual records. We most likely will do this anyway.

We have escalated this matter to FileMaker Australia who, after mucking around with it, have sent the problem to FileMaker US.

Programatically, there is nothing wrong with the PHP code we created. In fact, if we substitute MySQL for FM, it works beautifully. In general, there are substantial performance increases using MySQL over FileMaker. For example, in FileMaker, creating 1000 records takes 12 seconds where in MySQL it took 1.8 seconds.

This however isn't a debate between the two.

Part of the reason why MySQL works and FileMaker doesn't (with this scenarios) is because of the way in which both approaches ultimately communicate with the host. FM goes via a PHP class which opens a socket on the FM Servers web port and communicates via http/xml whereas MySQL doesn't have the overhead or the middleman php class. (OK it does but not nearly to the extent necessary)

The real issue I believe, is that minimum 30 second tcp_wait value that exists in Windows2003 server, and the fact that the socket is left in a TCP_Wait state after closing the connection. On this, I don't know if it's a FIleMaker or Windoze problem.

I would love to try this on a Mac Server to see if this is in fact the problem.

In the meantime, I still need a work around. I would like to experiment with ODBC connections from PHP to FileMaker but can't find anyone who has done this.