[Success Story] Managing Website Content (MySQL) with Servoy

by David Workman

I recently (April 2003) implemented a website with high degree of dynamic content. The tools I used were Lasso Professional 6 (www.blueworld.com) and MySQL (www.mysql.com). The last step of the process was to develop an application to easily manage the content. I needed something with the following:

• Access from different locations—Maryland, Pennsylvania and New Jersey

• Rich user interface

• Easy to use for non-technical people

• Ability to implement advanced functionality

• Integration with FileMaker Pro client/server applications

• Efficient and fast to create

• Easy to maintain

Access from different locations was an absolute requirement. After that, it was starting to look like a wish list...

Options

One option was to write a web application with Lasso Professional 6. As easy as Lasso Professional 6 is for web development and even utilizing an organized templating system with a high degree of code reusability (my preference is the Corral method—www.corralmethod.org), it takes time to put together pages to build an application to manage web content. You can't get away from working at the code level to add functionality and make changes, and code level work has a certain inherent time cost. My web coding skills are not shabby, but comparable functionality in FileMaker takes me much less time to accomplish. I like FileMaker type development speeds!

Why not use FileMaker Pro then? I am convinced FileMaker can do almost anything with enough effort, the right plug-in and a heightened sense of accomplishment for putting together solutions worthy of an episode of "Junk Yard Wars". In practical terms, whichever method you use to connect to a SQL data source (SQL or URL plugin) also demands a certain time investment in scripting that is over and above normal FileMaker functionality. In any event, the best you can accomplish with FileMaker is a static mirror of your MySQL data. This results in a different user experience than what FileMaker users are typically accustomed to.

Both of these options have their pros and cons. Neither was able to completely deliver my list of capabilities.

Enter Servoy.

Solution

My Experience

I arrived at a point where the site was ready to go up, but I didn't have content. I had no way for users to put the content in except to email content to me. With my back against the wall, I cranked up my beta version of Servoy after work one evening to see what it could do for me. Six hours later I went home with a huge grin on my face. Upon arrival at the office the next morning I bought a Servoy starter pack, told my users where to log on to open the application, answered about 15 minutes worth of questions and sat back and twiddled my thumbs. Four other people in different locations poured in the data, looked at the results in their browsers, and made changes as needed all via Servoy. As a result, our site went live later that afternoon(!) several days ahead of schedule and without me ever touching the content.

Whew! Try accomplishing that with a web or FileMaker program.

Key Steps

Note that I used Servoy build 1.01 for this so some specifics may be different with later versions.

1. Configuration and Setup

My first step was to figure out Servoy enough to get a simple application created and deployed. Servoy comes in three flavors: Developer, Client and Server. Unlike FileMaker Pro, Servoy Client does not allow you to create applications with it so you need to buy at least one copy of Servoy Developer. With Servoy Developer, you also get a single-user copy of Servoy Server and Servoy Client to test with.

When you install Servoy Developer, it installs an open source database called Firebird to store all the information about the applications you create. This storage bin is called the "Repository" and everything you create is an object that is stored in the Repository. Layouts, buttons, methods, relationships—they are all are stored there. You can use any SQL database as your Repository, but in my first pass I kept to the default setup.

Next, you have to give Servoy at least one SQL data source to work with. Again by default you can use the Firebird installation to create a new database and tables for your data. In my case, I wanted Servoy to use the MySQL 3.23.39 installation at my ISP where all my website tables resided.

You can set up a new data source under "Application Preferences." Go to the tab "DB Servers", select "Auto Setup Server: MySQL" and fill in the necessary fields:

• Server name: whatever name you want to call this connection.

• User name: MySQL user name to connect with.

• Password: MySQL password to connect with.

• Database Server URL: this needs to be in the form of "jdbc:mysql://<hostname>/<database_name>".

• Driver: this if filled in for you and is "org.gjt.mm.mysql.Driver".

The key here is that you will need your ISP to create a user in MySQL that allows access from your location. Most ISPs will initially give you a username and password that has local access to the ISP only. One way to do this is to use the grant statement:

grant all on databaseName.* to databaseName@198.6.1.1 identified by "password";

With my Repository and data source all set, I was ready to create an application.

2. Creating an Application

As a FileMaker developer, I was soon in my element with Servoy. You create layouts ("forms" in Servoy) in layout mode ("Designer" in Servoy). Forms contain all the usual suspects like fields, labels, buttons, parts, and drawing objects. You can set the form to display in record, list or table view. When you exit design mode, the form is "live" and ready to interact with. If you want to print a form, enter preview mode to see what it looks like and print. So far so good!

As I started to throw objects all over the place, I encountered my first deviation from FileMaker. Each object is now an object. For real. Not something you just call an "object" but something that behaves like an object. Objects have properties and Servoy displays those properties in a pane on the right side. As you select an object, the properties associated with that object are presented to you for modification. Note that everything in Servoy is an object—forms, parts, labels, fields, relations, portals, etc. Very tidy, simple to use, and as I have discovered more and more—very powerful and flexible.

After creating forms for all of my tables, the next step is to give users a basic GUI for navigation and normal record functions. Like FileMaker Pro, you can forgo this step and use the menu controls to get around with but my users haven't used a default menu in years.

It didn't take long for me to realize that Servoy surpasses FileMaker Pro in every regard when it comes to implementing a GUI. As a basic plan, I created my own custom "controller" (panel on the left side) which I used for every form. This keeps the GUI looking uniform across the solution and I don't have to copy and paste all the buttons on this controller onto each new layout. I just assign the custom controller to the new form and everything is there.

FIGURE 1: A basic form with a custom controller.

FIGURE 2: Table view.

Thinking of the many hours I have spent maintaining FileMaker tabs, I looked longingly at the tab panel object. However, I was on a mission to get some sleep that night so it was off to the last step: figuring out how to get some basic scripts knocked out.

Like me, you probably have a love/hate relationship with FileMaker Pro's ScriptMaker. From the joy of easily creating scripts that do all sorts of neat stuff to the pain of managing hundreds of scripts—that is the ScriptMaker experience.

I can emphatically say that to the FileMaker Pro developer, the Servoy Method Editor is a dream come true. It gives you an organized view of your "methods", you can easily copy and paste methods, it comes with a real debugger, the editor can stay open alongside your forms, it presents all your JavaScript functions in an organized manner... oops, did I say "JavaScript"?

Yes, Servoy uses standard JavaScript as its scripting language. If JavaScript is out of your comfort zone, let me assure you that Servoy's brilliant method editor makes the JavaScript learning curve practically disappear. Your available functions are intuitively organized and presented with correct syntax. If you are not comfortable with using JavaScript functions, there is a FMP plugin that duplicates most of the FileMaker calculations using the FileMaker calculation syntax. I didn't know JavaScript and in short order I had all kinds of useful scripts attached to my buttons on my custom controller. Hands down, it is MUCH easier to use than ScriptMaker.

Now I have an application—albeit a basic one—but I have forms to see every table in both list and record view. I have a common navigation controller on the left side with all the necessary navigation and record keeping duties taken care of. I am ready to deploy.

3. Deploying an Application

I have users in New Jersey, Pennsylvania and Maryland, this could be tough... As it turns out, this is the easiest part. Start up server on your computer and tell your users to open a browser window and type in "yourIPAddress:8080". Java Web Start technology takes care of the rest and before you know it, Servoy client is running on their computer. Nothing else, that is it.

Results

I rapidly created an application with a data source at my ISP; it was accessible to users in widespread locations; it took little time to implement and install; and it necessitated virtually no user training. All in one night after work starting with just my FileMaker Pro and Lasso experience.

FIGURE 3: Final touch—add a splash screen with simple directions. Uhm, looks like the website...

4. Notes and Tips

1. Managing Key Fields

When using MySQL as a data source, Servoy needs to manage the key field. In other words, set Servoy to increment the key field, not MySQL. I'm not clear on the technicalities of why this is the case but that's the way it is for now. I believe that there are other databases that do not have this restriction when working with Servoy.

Consequently, you need to be careful about adding or deleting records with another mechanism other than Servoy; i.e., a web browser. To correctly increment key fields, I create separate tables for all data entered via the web and then in Servoy transfer this data from the web input table to the corresponding "operational" table.

2. OS X caveats

Servoy Client on Mac OS 10.2.x has a few issues. Java 1.4 is still not a 100% compliant citizen on the Mac and it gets a additional performance hit for running on OS X. Besides speed, most of the problems have to do with display issues. Menus grey out, buttons lose border properties or disappear altogether, and fonts get substituted. In my testing, none of these annoyances occur on Windows XP client.

Having said this, I have Servoy client running fine on 400mhz G3 iMacs. Servoy developer also runs great on OS X and that is what I use to develop with. The trick for now is to keep your interface on the simple side and wait for Apple to update Java.

3. Developer Setup

I have Servoy Server running on computer running OS X and I develop on a separate computer. I have the repository of Servoy Developer on my laptop pointing to the Firebird repository on the server machine. This way I don't have to export and then import the application to transfer it. Just need to restart Servoy Server after you make changes. Unlike FileMaker Server, this is accomplished in about 30 seconds.

You can also create a new "Release" of your application to make significant changes. Once you are ready to deploy the new release, flip the switch on the server and the users see the new version. Since in Servoy, structure is kept separate from your data, and your application is stored in a database—if you don't like the new release you can flip the switch right back to the previous version.

4. Connecting to LassoMySQL

Lasso Professional 6 comes with its own MySQL installation (LassoMySQL) which you administer with the web based Lasso Admin application. The default installation of LassoMySQL runs on port 14551, has a master user name of "root" and no password (I suggest changing this). When you make a DB server connection from Servoy to LassoMySQL, the DB URL line needs to be in this form:

jdbc:mysql://localhost:14551/<database name>

You cannot replace "localhost" with a remote IP address with this user name. If you want to run Servoy on a different machine than Lasso Professional 6, you will need to issue a "grant" statement to add a user with access outside to the local domain. You can do this with the LassoMySQL command line tool located in the directory "../LassoProfessional6/Tools/LassoMySQLclient".

Note that you can connect directly to the databases that Lasso Professional 6 uses to store server settings. I find it useful to have Servoy monitor the Lasso "Events" and "Email" queue this way. I suspect it wouldn't take much to write a Servoy application to entirely replace the Lasso Admin application.

5. Summary

I like Servoy because it's extremely fast to develop in, simple to maintain and manage, and easy to train users on. My users like the results because it feels like a familiar FileMaker application where they can get what they need done with a minimum of effort. Without a doubt, using Servoy to build an application to manage my website content has saved me hours and hours of work (if not days). In every way Servoy is a true rapid application development tool!

You can view the results of these efforts at http://www.bossstaffing.com/.