Inserting records into FileMaker Pro database?

[ERlc_the_red]

I'm a database developer/DBA (Oracle and some SQL server) and, being the new guy at our company, last week I was given the task of figuring out how to write to the FileMaker Pro database for the web guys here. They're using ASP, and so I figured out how to write to FileMaker Pro with FileMaker's ODBC driver, and can insert records successfully through VB Script, but determined that you can't connect to the database (with ADO) unless the FileMaker Pro client is up and running as an application!! Sounds awful already.

The web guys understandably do not want an open application on their web server.

So, I decided to make the FileMaker Pro run as a service (using SVRANY.exe and INSTSVR.exe, and making the proper registry entries). Now it runs as a service in the background. However, when run as a service, the same code does not write to the database. I am logged in as the local admin, and the service is started as this local admin. Sharing of the FileMaker Pro files is set correctly. When run, the ADO connection object's error collection holds the following two items after opening a connection:

> errors:

[FileMaker][ODBC FileMaker Pro driver]Optional feature not implemented.

[FileMaker][ODBC FileMaker Pro driver]Driver's SQLSetConnectAttr failed.

Now I know a successful instance of FileMaker Pro is running in the background because when I launch FileMaker Pro from the start menu and attempt to connect to the particular flat file it barks at me telling me someone else is already connected.

Here is the VBA code which produced the above two errors in the immediate window:

> code:

Public Function TestThis()

Dim strCnn As String

Dim strSQL As String

Dim objCnn As ADODB.Connection

Dim intBound As Integer

Dim intInc As Integer

strCnn = "DSN=FileMakerTestA" 'dsn used - FileMaker Pro ODBC driver

strSQL = "INSERT INTO Test2003 (TestField1) VALUES ('please insert')"

Set objCnn = New ADODB.Connection

objCnn.ConnectionString = strCnn

objCnn.Errors.Clear

objCnn.Open

'checking for internal errors

intBound = objCnn.Errors.Count - 1

If intBound >= 0 Then

'errors found, print them out

For intInc = 0 To intBound

Debug.Print objCnn.Errors.Item(intInc).Description

Next

Else

'no errors found

objCnn.Execute strSQL

End If

objCnn.Close

Set objCnn = Nothing

End Function

Any help is greatly appreciated. Basically the final solution I need is to be able to write to the database without having the DARN FileMaker Pro application open. We have the FileMaker Server, but it is my understanding that we need the client to update the data in the flat files.

THANKS IN ADVANCE! -e

[Kurt Knippel]

Hmm, I would first ask why use Filemaker?

Are you using Filemaker internally and needing to have that information on the web? If not, there are many other and far superior web based database solutions, such as MySQL.

Anything involving Filemaker and the web, requires that the Filemaker Pro or Filemaker Pro Unlimited client be opened so that it can accept the ODBC/JDBC calls. Filemaker Server has no capability (at this time) for external access from anything other than Filemaker Pro clients.

However it is not necessary for Filemaker to be running on the web server itself, most often I recommend that people put Filemaker Unlimited on its own machine (any old workstation, dedicated to this pupose and secured along side the web server).

[ERlc_the_red]

FM is not the right tool, but the printing department where I work uses the application (about half of their computers are Macs). They've requested automation of print requests into their FM Pro database from one of our intranet web pages. Right now, when the web page is submitted, it is processed and an email is routed to their department containing the relevant print request information. They have to then hand enter in the information into filemaker.

I would like to recommend that we in-source the application, create an Oracle or SQL Server back-end, and avoid FM Pro totally. BUT, we're a not-for-profit with limited resources, that department already has financial and operations reports written in FM Pro, people trained to use the third party vendor app, and so on. (SO, stick the new guy on this - he'll have fun figuring out how to write to that database).

I believe in bringing the functionality in-house but they can't at this point. In my opinion, it wouldn't take much to write a scalable solution using industry-standard development tools, but anything is too much in the short run.

I was hoping to find someone else who had gone down the same road.

-e

[Anatoli]

RE: I was hoping to find someone else who had gone down the same road.

No offence, but "to write a scalable solution using industry-standard development tools" equals "expensive". We here are masters of FM and we do not like "industry-standard development tools" at all. There are of course some minor exemptions.

FM = inexpensive development, fast solution, good server, web connectivity, parallel communication with SQL databases via Lasso etc.

[ERlc_the_red]

I see what you mean. There are some free solutions such as PHP, MySQL, Java, and PostGreSQL, but the company that I work for already has developed in Java/Oracle or ASP/SQL Server. The web site is not HUGE and we're not a really large company (5000 employees) but we're not small. The printing department is small and I was asked to help them out. Being a not-for-profit organization, the company can't afford to get tied to yet another 3rd party app.

All I want is to be able to write to the database without having the FileMaker Pro open as an application (I can already write to it with ASP when the client is open as an application - that was easy). For obvious reasons we can't have some application sitting open on the desktop waiting for a request to write data into the database. I need to write to it with FileMaker Pro client running in the backgound or not running at all.

I appreciate the time that you have spent reading my posts. I want FileMaker Pro to work for them. Like you said "We here are masters of FM..." so I came here knowing that I had a good chance someone else had set up FM as a service and figured out a way to write to the database, or some other such optimal solution.

Thanks!

[_ian]

You won't be able to insert records into FileMaker without the file being open. I've got no experience with SVRANY so am no help at all there.

How about a different approach to the problem? Here are a couple: Instead of emailing the data to the FileMaker user, why not have the ASP system create an XML document that the user can then import? Alternatively, is there a SQL Server or Access database hanging around on that Web Server? Write the data to a table on that, then users can have a FileMaker script that checks for new records and imports them into FileMaker.

Ian

[Kurt Knippel]

Eric,

In this case Filemaker is the right tool, because it is in use in your organization. You initial post made it seem like Filemaker was not actually used within the organization.

The way that Filemaker works is that you run Filemaker Unlimited $999 (allowing an unlimited number of IP connections, normal Filemaker Pro is limited to to something like 10 in a 12 hour period) on a machine sitting in the corner (UNTOUCHED), the web form sends commands to this machine, which in turn writes the information to the Filemaker Server. At this time any other solutions require hand entry or import of some file format.

What is you objection to having this application running on a spare machine?

[ERlc_the_red]

Ian,

That is the very solution we have discussed. We're probably going with the XML solution since this will modularize the web page. Then we can write any data service to write to any database, be it FileMaker, Oracle, or SQL Server, if we ever decide to in-source the functionality of the 3rd party vendor application (FileMaker).

Kurt,

I hope I was not misleading. One of the departments here does use it. The web guys have come to me asking me how their ASP can insert data into the FileMaker Pro database (since I am on the database team).

We have had success writing to the database WITH the FM Pro client open from ASP, BUT they do not want the administrative responsibility of having some client application open in the FOREGROUND on a machine somewhere. If it were a service then maintenance could be performed, users log in and out, everything except shutting the computer off (and if it were a service, we could have it start up automatically on reboot). But it seems it cannot run as a service, without some more information.

I was hoping someone here would know something about the internal workings of FileMaker to be able to point out the difference between it running as an application in the foreground, and setting it up as a service (with SVRANY.exe and INSTSVR.exe, and making the proper registry entries). Maybe there are some files, variables, etc. that are toggled.

Again, thank you for all of your time and effort!

Eric

[Kurt Knippel]

Hmm, I am sure that I recall some people doing this, but I do not rememeber the results.

However that said, you may be misunderstanding what Filemaker Unlimited does and how it works. Filemaker Unlimited is a client version of Filemaker, it runs as an app, connects to the Filemaker Server and opens the databases. It then allows IP connections (typically from a website) to connect to it via the WebCompanion plugin. You can then use the CDML and/or XML languages to manipulate the database. The communication and translation between Filemaker Server and CDML/XML is what WebCompanion does.

So while this app is a CRITICAL part of the communications, it is nothing more than a translator. You could crash or force quit this app, every minute and it would not cause any problems (aside from breaking the communications link while it is restarting). It does not have or need any more access than any other client machine in your enterprise. You do not have any problems with them having Filemaker open all day long, do you?

I suggest securing the physical machine along with the other servers, only so that someone does not accidentally mess around with it and quit the app or lock up the machine. It also allow your IT department to restart it or shut it down if necessary. Finally if something happens and the harddrive burns up or the RAM goes bad and the machine needs to be replaced, just setup any other machine with the same IP and run Filemaker Unlimited, everything is back up. The actual machine is only there to provide an IP to Filemaker Unlimited and give it the RAM to run in.

Eventually I suspect that Filemaker will integrate this functionality into Filemaker Server. To a certain extent I do not like this concept, because then you have to make your Filemaker Server accessible to the web. With Filemaker Unlimited only this particular client app needs to be open to the web, and you can much more easily restrict what it can and cannot do.

[ERlc_the_red]

Kurt,

I will look into FileMaker Pro Unlimited more deeply. I am a little uncomfortable with having an app in the foreground open all the time, and I know the web guys completely baulked at the idea. However, while I process what you've given me and what is in the documentation concerning FileMaker Pro Unlimited and the Web Companion, I'm going to research any system variables, files, etc. differences between running FM as a service and as an app.

I think securing a machine at a not-for-profit company is like an act of god here. I am a new guy so it is even harder to convince someone of the need for a dedicated machine. What you've described sounds "close" to what we need except having it's own machine and having the app up and running on it full time AND we can accomplish our inserts with the Filemaker client open with no problem with ASP code now anyway.

All said, it is worth the time to look into it, and AGAIN I appreciate your time and suggestions. They need to pay you for this. All have been helpful.

-e

[Kurt Knippel]

Does anyone in your organization have a problem with Filemaker Pro being open and in use all day on someones workstation? If so, then maybe you have bigger problems, if not then Filemaker Unlimited should not be an issue either. It is just another type of client application.

Does anyone in your organization have a spare computer lying around? Do you have a location where the computer can rest undisturbed? Since it is going to be running just another type of client application, it just needs to sit somewhere where not any Tom, Dick or Harry can come by and shut if off, or try to use it for something else, or knock it over, etc. The janitorial staff can be particularly hard on computers. It does not need to be "secured" like a server, it only has as much access as any other copy of Filemaker Pro (literally the ONLY difference is the number of IP connections allowed).

You have to look at it this way, it really has nothing to do with your servers and nothing to do with you web guys, any more than having a user with a web browser or with an ODBC driver affects them.

I think that your depth of knowledge in Oracle and other such technogies is causing you to over-think this a little bit. Both Filemaker Server and Filemaker Unlimited are incredibly simple compared to other database engines and web services. They are almost as powerful, but far simpler.

Remember that Filemaker whole system is designed to work "off-the-shelf" by anyone, without an IT support.

[ERlc_the_red]

There are people here that do have a problem with it being open on the desktop. And one of the first suggestions I made was having some computer sit open with filemaker on it. But even I think there is an issue with having something in the foreground dedicated to one application. When our network guys push out update packages for security to the network computers, sometimes those updates require re-boots (as everyone is familiar with). If we had one dedicated machine solely for the FM Pro app residing open in the foreground, then someone is going to have to remember to DameWare into that single computer and launch that app, or we could set it up as a service and Allow it to "Interact with Desktop". Well, it basically comes down to the 'non-standard' set up with the FileMaker client being open in the foreground, because everything else could be made the same EXCEPT for that (and the dedicated machine).

Thank you again!

[Vaughan]

It's easy to set the computer up so that it automatically logs-in as a user, the Web Security databases automatically open up, and a script then opens the necessary files that needs web sharing. (There may be a way to lock the screen after all this has happened, I don't know.)

If you can make FMP launch itself again after a re-boot then you have no worries at all.

I've done it on NT4, and am doing it now on Mac OS X 10.1.

For the amount of money you save compared with SQL or some other setup, they can learn to live with a real live application running in the foreground.

[ERlc_the_red]

Yes, agreed!

And FileMaker has been around longer than I realized. Why make changes now, to engineer the client application to be able to run as a service. FM has been doing great AND is extremely cheap (far cheaper than Oracle) AND supports the Mac OS.

I've done some performance tests, however, and FM didn't fare too well (against Oracle, SQL Server, and MySQL). I didn't test it against an Access database (but Access databases, in my opinion, are only good for favorite beer lists and CD collections).

[Anatoli]

In my test (200k+ records, find group of 200+ records) FM is on PC faster than MySQL when outputting the result to Lasso page. That is the main job in my cart solution.

In more complicated queries the MySQL was faster 2-10 times, than FM. But I never used anyway those SQL queries.

[Rajat]

Hi,

It seems that you have done performance test for FileMaker Applications.Well I have to perform a performance test on a Filemaker Pro 6 Application. It would be great if you could let me know the tool and the protocol to record the script.

Thanks and Regards,

Rajat

Email:[email protected]

[Anatoli]

FM file with 2 022 990 records 733 MB

Index unindexed field: 4 min 20 sec.

Search on unindexed: 1 min 40 sec

Search on indexed: less than 1 second

Sort 100: less than 1 second

Sort 1000: less than 1 second

Sort 10 000: less than 1 second

Sort 100 000: 4 seconds

Sort 1 000 000: 44 seconds

Sort all of 2 022 990: 95 seconds

If you have good design without calculation across many records, this will be your speed on W2K, FM 5.5 IDE disk P4 2.6GHz.

Use bad design and everything will be hours instead of seconds.