Visual FoxPro And FirebirdSQL

Go ahead...Think Outside The Box

I know, I know, my next post was supposed to be about connecting to FirebirdSQL database tables via an ODBC connection. I promise it is coming, but in the meantime, I had an epiphany the other day and I wanted to share it.

The elite FoxPro programmers out there will probably think this is a silly thing to write about and something that I probably should have known all along, but I really think what I learned by accident points to something bigger that everyone should take the time and think about. But before I get to the moral of the story, perhaps I should tell the story first.

At the FoxForward 2007 convention, I asked a group of seasoned FoxPro programmers about all the articles I had read on the web pro and con of using Remote Views or SQL Pass through. Without a single hesitation on anyone's part they all said in unison that SQL Pass through was the only way to go. My reason for asking the question was because I'd never seen a definitive answer to the question and frankly, I was curious to know if there was actually a preferred way of accessing remote data.

This left me with a problem. You see, I guess I'm lazy because I really like designing my screens visually and working with visual objects. Because I like to use grids in certain situations, I was having lots of problems with SQL Passthrough. I could never figure out how to use the Data Environment to open and manage my tables because every time I wanted to use a cursor that I created on the fly using passthrough in a grid, the init of the grid would happen BEFORE the query ran. I would get errors unless I performed the query in the BEFOREOPEN tables event, but when I did this, I would have to manually open and close the tables and make sure that all of the properties were set right.

Because FoxPro is so flexible, I figured no problem, just create Remote Views, parameterize them and use them in grids making sure that no data was loaded to the view until after I set the parameters and requeried the view. This worked and handled most of the situations that I ran across. However, this created a new problem because it required me to have lots and lots of predefined remote views when sometimes all I really wanted was a short list of stuff to display for the user. "Oh, well" I thought, "I guess you just have to find what works and stick with it."

But still it nagged at me. If all the programmers at FoxForward said pass through was the way to go, then why was I having so much trouble achieving what I wanted. Either I was missing something or they were just blowing smoke. I suspected that the former was the correct answer.

Anyway, fast forward to the other night when I was working once again with a grid that I wanted to set up using data generated from a query, but in this case, I just wanted to access local data run a query on it and have the results displayed in a grid. My age-old problem re-emerged because everytime the grid instantiated, it generated an error because the ControlSource wasn't found. I figured I'd deal with that later and went ahead and wrote my refresh/requery code to re-issue the SQL statement whenever the user changed something on the screen. I knew that grids will go blank if you issue the Requery command so I knew that I needed to issue the following series of commands:

1. thisform.lockscreen = .t.
2. thisform.stdgrid1.controlsource = ''
3. requery('gridview')
4. thisform.stdgrid1.controlsource = 'gridview'
5. thisform.lockscreen = .f.
6. thisform.refresh()

And while looking at this code, it suddenly dawned on me that I'd been staring the solution to my problem right in the face and never seen it before now. I could create my grid visually using the raw table, set up all of the click,dblclick and other code that I wanted to attach to the grid. Then, clear the ControlSource and make it blank. Once the form and grid was instantiated, I could issue whatever SQL passthrough commmands I wanted to create a cursor and then assign the cursor to the ControlSource of the grid!

I got even more excited after I tried it and it worked perfectly. The only issue was making sure that the fields I requested in my SQL command matched the columns I'd predefined in the grid. I wanted to share it with someone but when I turned to my wife and started to explain it, and her eyes glazed over, and I knew that I was going to have to put this on my blog even if people laughed at me about how silly I had been to not know all this stuff to begin with.

But the point about not knowing this previously is this: All this time I had been programming in Visual Foxpro and my assumption was that once I put something in a box on a property, it was written in stone. The flexibility of FoxPro gave me another way to accomplish what I wanted to do and cludgy as it seemed even to me, it worked, so why go back and figure out another, better way to do. At the same time, it is sometimes hard for programmers to admit they just figured something out, but it is more important to think about the programmers who are just starting out with Foxpro and make sure they don't have to work out all of the same problems all by themselves.

Ultimately while feeling stupid, I began to wonder how many programmers out there have done exactly the same thing: Accept that what we put in a box should always stay there? How many programmers have accepted the status quo of how they do things and never examined possible alternatives? I realize that many of us work in production mode, sling the code, send the bill, and look for a new client. But my small problem led to a huge realization and I wanted to share it because who knows who else might need the same insight.

Moral Of The Story:

Moving out of our comfort zone can sometimes lead to living in an even MORE comfortable zone.

Go ahead...Think outside the box.

Visually Create, Edit & Maintain FirebirdSQL Databases

Ok, in the last post, I explained how to install FirebirdSQL with the basic (and perfectly acceptable) default settings. When I first decided to start working with Firebird, I was intimidated because I really didn't know that much about SQL and working with the included ISQL.exe command prompt seemed a bit cryptic to me. If you've been working with SQL commands for a while, maybe you'd feel differently, but I needed a way to visually work with the tables, triggers, and domains like I've become used to in FoxPro.

After initially trying a freely available product called Marathon, I ran across a program called EMS SQL Manager for InterBase/Firebird. EMS makes visual database editors for MySQL, SQL Server, PostgreSQL, Oracle, DB2, and DBISAM. I cannot say enough good about this product. On top of everything else the product does, the company offers a FREE Lite version of many of their products which should appeal to any FoxPro programmer out there. So far, the Lite version has done everything I need it to do, but I can see down the road when I will probably need the full version, which for businesses costs $275.00. There is also a full Studio version that includes a lot of the utilities that you can also purchase separately. EMS also publishes products such as Data Export, Data Import, Data Pump Migration Tool, Test Data Generator, DB Comparer, DB Extract, SQL Query, and Data Comparer. You can view all of their products for all of the different data systems here.

For the purposes of this article we'll be focusing on the EMS SQL Manager for Interbase/Firebird Lite. I want to take you through the process of creating a sample database that we will be using in some of the examples I'll post later. If you don't want to create the sample database manually, here's the link so you can download the sample.

The first step is to download the IBManager program. The link I have provided will connect you to Download.com and the ibmanager_lite.zip file is about 14Mb and should start to download immediately. Unzip the IbManagerLite.exe and double-click this file to start the install process.

Accept the defaults and when the installation is finished, the SQL Manager 2005 Lite for InterBase and Firebird will start. (Note: For the sake of clarity, I set the windows to be an MDI environment. To change your system to match, click Options, Environment Options, Windows and set the Environment Style to 'MDI environment'. I normally use the floating windows but it makes screenshots difficult.)

The first screen you will see is this one:

The previous screen is your "control panel" for Firebird databases and it is the starting point whether you are designing a new database, or maintaining an existing one. Any previously designed databases will be shown in the Database box in the top left-hand corner. Since this is a fresh install there are not any files, but we'll add one now.

The first thing that must be done is to register a new host. A host (server) can be defined for any computer on your network that is running FirebirdSQL server. I'm assuming that you followed our previous tutorial and installed the server on your local computer. If you didn't, simply replace 'Localhost' you see in the screenshots with the computer name of the computer where you installed the Firebird Server.

To register a new host click 'Database' on the main menu at the top and then click 'Register Host.' You should now see the screen below:

Set the protocol to TPC/IP, the Host name to localhost, leave the remaining fields set to their defaults and then click Next. This will take you to the Host Options screen below:

We should take a moment and discuss the fbclient.dll. This is the main driver that you will use for connecting to the Firebird Server through either EMS or an ODBC connection. We will discuss ODBC connections in a later post, but the most important thing to know is that each client will need to have access to this .dll in order to connect to the server. If you installed the server on your local machine, you should be able to locate the fbclient.dll in the C:\Program Files\Firebird server folders. In version 1.5 of Firebird this will be the main folder of the Firebird installation. Version 2.0 changes the folders somewhat and adds a folder under the main folder named 'Bin' and installs the fbclient.dll there.

There is an error in the screenshot above which I corrected after I made the snapshot. The correct path to the Verison 2.0 client library should be C:\Program Files\Firebird\Firebird_2_0\bin\fpclient.dll. You may choose to copy this file to some other folder to make it more accessible. For one customer, I installed it on a server folder so that clients have access to it across the network, but a better solution would be to install it in a client folder where you install your other dll, ocx or configuration files for your software in order to reduce network traffic. I haven't seen a lot of traffic being generated by this file being located on a server, but this would probably be more of an issue if you had 50 or more users on a network.

The default security database is located in the main Program Files folder where you installed the Firebird Server. You could leave this blank and EMS will still work, but you will need to make some version of this security database available to the production server so you can control access to the production database.

To simplify your life, check the box labelled 'Connect to the host automatically when connecting to any of its databases.' This prevents you from having to enter multiple logins and passwords whenever you want to access your tables.

Click Finish and the host server will be registered and you'll see the screen below:

Make sure that the current host is selected in the Databases window and then click 'Create new database.' You can also click the Create dropdown and select 'Database' to accomplish the same thing. The following window will open:

Choose the Host name where you want the database to be created and hosted. Then click Next.

Enter a fully qualified database name. You can click the disk icon at the end of the field to open a window to browse your drive and/or create a new folder. It is important to note that if the database will reside on a computer other than the local host, you will need to preceed the absolute path to database with the name of the server. For example, if you are creating a database on a server named 'MYSERVER' and the absolute path is D:\MYDATA then then the fully qualified path will be 'MYSERVER:D:\MYDATA' without the quotes. The default file name extension is .gdb, but the Firebird convention is usually .fdb. Any extension will work so choose what works for you.

The default login is 'SYSDBA' and the password is 'masterkey'.

In this example, I've changed the page size to 8192 even though the default is 4K. In some experiments I've been running, the connection speed has increased significantly. If the tables you plan on creating have large numbers of fields this can be a quick and easy way to improve performance and reduce network traffic. Experiment yourself to find a page size that works best for your application and situation.

I selected ASCII as the Charset, but the server is capable of handling any one of several. Pick what works for you and the app you want to build. I've used Unicode and ASCII and one big difference is that Unicode needs an extra byte on fields to handle extended characters. For example, with Unicode, you cannot define a character field with only a 1 byte length. The definition will require at least 2 bytes.

Check the Single database file and everything will be stored in one database file. This works well for me so far, but if anyone can contribute thoughts on this, please do.

Click Next to see the next screen.

You should see the details that you've selected on previous screens displayed for your confirmation. Make sure the Register the database after creating checkbox is selected.

Clicking Finish will cause the program to create your database and register it with the correct host. You will be returned to the main control screen and you should see your database listed in the Databases treeview under the host you defined earlier. Right click on the icon for your database and then left click on Database Registration Info to see the next window.

Click Connection in the Options list and you should see all of the connection details to the database your created. Click Test Connect and you should be rewarded with an information pop-up that says 'Connected!'

Click the Ok button to return to the Connection screen. Click Options to see several other configuration choices.

Click System Objects to select which system objects will be displayed in the DB Explorer.

For the sake of this tutorial all items have been unchecked to keep the DB Explorer window uncluttered. However, I would advise you to take the time to go back and turn each of these options on individually so you can see the different items that are created behind the scenes. It will help you understand better at that time how everything relates. I do not advise allowing the editing of triggers for system tables and views. Frankly, the idea scares me, but if you are a SQL expert you can certainly give it a whirl. Click Data Options to view additional data settings.

The only setting I changed from the defaults here is the Get record count beforehand. When working in the design mode, it is sometimes helpful to know how many records are in a particular table. It can also be interesting information to watch in a production database. Click Ok to save your settings and return to the main control panel.

Double-clicking on your defined database will expand the treeview to show you all of the different parts of your database. For now, we won't worry about any item in this list except the Tables folder. Right click on the Tables Folder and left click on the New Table to start creating a new table. As you can see above, we've started creating a new table named 'BLOGGERS' and have entered a note in the description field to give anyone a clue as to what will be stored in this table. Click the Fields tab and then right click in the blank white area and left click New Field. The following window will appear.

As you can see you can define almost any attribute for a field at the time of creation. Here we are creating a field named 'PK_UNIQUEID' and we are defining it as a Primary Key. For this tutorial just select a Standard data type and in the Type dropdown you'll see all of the different types of fields you have available to you in Firebird. It is beyond the scope of this discussion to describe all of the field types, but for this first field we'll select INTEGER. Next we'll select the Autoincrement tab to create a Generator and a Insert Trigger.

A Generator is place to store the next incremental number and the Insert Trigger will do the heavy lifting of making sure that any new record will receive a unique identifier. Let's click the Trigger tab.

You'll see that Firebird will automatically create the SQL code necessary to update a new record with the next Generator value if the PK field is found to be NULL. The next screen shows the table as it should look after you've created all of the fields that you want in the table.

Let's go back and edit the trigger for the PK_UNIQUEID. As you may have noticed in the previous screen, we created a field called 'DATEADDED.' Ideally, I'd like this field to be updated with a date whenever a new record is added to the table. The following screen shows the code to do this is very easy to add to the PK trigger.

When you are satisfied that all of your changes have been made, click 'Compile' and the system will generate the necessary SQL code and attempt to compile it. If everything goes well, then you can click 'Commit' and the changes will be written to the database. If there is a problem, you can Rollback the change and fix the problem. This is a great feature because it has helped me identify many mistakes BEFORE they make it to the database. In this case, the table should be created and will appear in the Databases Treeview as in the screen below.

Double-clicking on the table icon on the left hand side will open the table and you can then click the 'Data' table and begin entering data. In this case, I've entered five records of different blogs and references to each one.

In our next tutorial, we'll install the ODBC drivers for Firebird and then create both DSN and DSN-Less connections and use them from inside FoxPro.

GET EMS SQL MANAGER FOR FIREBIRD! It will save you lots of tears, trouble and time!

Get It Today!

PHT 

Installing FirebirdSQL

I described in a previous post some of the work we've done with FirebirdSQL and now it is time to get to work. FirebirdSQL is a relational database that runs on Linux, Windows and many different Unix platforms. It was originally developed in 1981 and has existed under various names and incarnations since that time. Before being released as open source, it was a commercial product published by Borland as InterBase. On the FirebirdSQL (http://www.firebirdsql.org) website you will still find many references to InterBase and even some links into the existing InterBase community. As Firebird moves forward, it seems to be moving further away from its InterBase history so you will have to be sure that any freely available tools you choose specifically support the Firebird release you will be using.

The first decision you will need to make is which version of the product do you want to use. The download page lists three different versions: Firebird 1.0.3, Firebird 1.5.4, and Firebird 2.0.1. When we did our first project with Firebird, version 2.0 was still in beta release so we choose version 1.5.4. Our next project will probably be in version 2.0 assuming that it continues to be a stable release. The platform you are working on may also have an impact on which version you choose to download. From version 1.5 forward, you will also be given a choice to install either the Classic or the Superserver architecture. It is probably a good idea to read the technical notes to get an idea of the pros and cons of each architecture before you install.

The most important thing to note is this: The Classic Architecture allows programs to open a database file directly and spawns a seperate task per user connection. While this may affect scalability, there are some cases where this might be useful and/or faster. The Super Server provides a server process and all SQL requests are handled via the server using a socket. This version uses multiple lightweight threads to process all requests.

So far, we've used only the Super Server architecture and if scalability is the most important concern you have, you should choose this option when installing the program. We find the Super Server to be incredibly fast and it uses very little memory. On the production server we currently have in place, we have seen that with an average of between 50 and 100 connections, the server uses between 100Mb and 150Mb of memory consistently.

When you select a version, click on the link and you'll be transferred to SourceForge where you can download in the installation program. The .exe for Version 2.0 is about 4.1Mb and includes all of the code necessary for either Classic or Super Server Architecture. Once the download is complete you can either install the program on your local computer or any computer you want to designate as the "firebird server". For testing purposes, you may want to install the program on your local computer, just remember that the ODBC connection requires a server name as part of the connection string and if the server is on your local computer this name MUST be 'localhost'.

Start the install process for either 1.5 or 2.0 versions and you'll find that they are very similar with the first step on both being to select a language. Version 1.5 includes Dutch, English, French, and Portuguese. Version 2.0 includes all of these plus Bosnian, Italian, and Spanish. Accept the agreement and read any installation notes, yeah, right . Select a destination path where you want the program to be installed. Notice that in Version 1.5, the server uses 1.4Mb of space and in Version 2.0, it uses 2.0Mb of space.

The next screen will ask you to select the components that you will need. My personal recommendation is to select the Super Server Binary and all the developer and server components. Once you select all of the additional components the disk usages jumps to a whopping 9.2Mb for Version 1.5 and 14.7Mb for Version 2.0.

Give the system a name for the Start Menu and then you will be asked to set several individual options, such as

• Use The Guardian to control the server: The Guardian is a separate software utility that runs in parallel to the Firebird Server and is designed to restart the Firebird server if it ever fails. If you install the Firebird server as a service it may not be necessary to install the Guardian, however, I've installed it anyway and it only uses about 3Mb of memory and very few resources. If memory is a premium on your server, you may consider installing Firebird as a service and then leaving this off.
• Run Firebird as an Application or Server: This is a choice that is strictly up to the developer and is probably more of a development type choice. I can't imagine a production server where you didn't run the database server as a service, but I have installed Firebird as an application on my development laptop. This one is your call.
• Start Firebird automatically everytime you boot: Again, this is a developer call, but as in the previous setting, I can't imagine a production server where you wouldn't want this to happen automatically.
• Copy Firebird Client Library to <system> directory: Not defaulted, but might be useful if you were using the client driver rather than ODBC which is what we expect to use with FoxPro.
• Generate client library as GDS32.DLL for legacy app support: Defaulted to do so, haven't needed the GDS32.DLL version because I don't have any legacy apps, but leave it checked just in case you need it later I guess.
• Install Control Panel Applet?: Defaulted, but it does install a cute little applet in the Control Panel that you can open and quickly start and stop the Firebird server as well as change some of these setup apps even after the server is installed.

You'll be given a screen to verify your choices and then you can click 'Install' and the files are installed to their respective locations. The last choice is to start the Firebird server. If you confirm this, the server will start and any user on your network and/or yourself can begin accessing Firebird database files.

That's it. The server is installed and is operating. Check it with the Control Panel app to verify it is running, but other than that, you are off and running. There is a firebird.conf file that has some additional settings that you can customize if you choose, but basically you can build a complete SQL app right now and get it working with no other setup. Simply Amazing!

Next up for FoxPro programmers, we'll talk about installing and using a tool to create, manage and edit databases and we'll create a test database to play with in later discussions. After that we'll discuss installing the ODBC drivers and create a DSN and a DSN-Less connection to the database we create.

Questions, comments, corrections are welcome and appreciated.

PHT

My First FirebirdSQL Project

In 2006, I was approached by my client using the AgentTrax Sales Management system that I began working on in 2000, because they wanted a web-based front-end to allow their agents (at the time numbering around 8,000 active agents) to contract online as well as to have a place for active agents to enter their sales production and distribute that workload out to the agents making the sales. Having sold a pre-packaged app using FirebirdSQL as the database, we had a little bit of experience with FirebirdSQL so it was an easy step to decide to use it as our database as well. After all, it was open-source, well supported and documented and it was screaming fast.

I hired a programmer (Robbie Clark) with some experience in Java and between my experience in database development and his experience in web programming, we started our build of AgentTrax.Net (The name refers to our web domain as we did not utilize .NET technology in the development). Sixty (60!) days later, we unveiled AgentTrax.Net as a live web app for the client and in the first year, the client hired over 12,000 agents and the agents have entered over 200,000 insurance applications into AgentTrax.

We built a complete interface between our web product and the original back-office product and we renamed the original program, AgentTrax BackOffice. Data is entered transparently between the backoffice (using native Foxpro tables and dbc) and the webapp (using Java Server & FirebirdSQL) with our synchronization system. Using Remote Views, BackOffice users edit and correct applications entered by agents from the web and then post the sales to the sales management and reporting system.

The AgentTrax.Net web application includes a contracting system that allows potential employees to entered all of their resume information using various forms and then select the insurance companies for which they wish to sell. Once they click the 'Hire Me' button, the server merges the information they entered with standard PDF contracts we've created and then emails the finished contracts directly to them. The users of the AgentTrax BackOffice product then access the information entered by the potential employee in real time and once all of the hiring requirements are met, the newly hired agent information can be populated directly to the backoffice system without additional data entry. Once an agent has been hired, he/she can register online to access the production side of the site and begin entering their sales information.

Long term, we plan to upgrade the backoffice portion of this app to natively access the same FirebirdSQL database used by the web-based portion of the system. But it appears that this will end up being a evolutionary process rather than a revolutionary process. Our users continue to ask for features and all new features are being created using the FirebirdSQL whenever possible, so probably before we can do a complete re-write the program will naturally migrate in this direction over the next couple of years.

Stay tuned. I'll try to update our progress on this project as we move forward on it.

Lingering After Effects of FoxForward 2007

Having just returned from FoxForward 2007, I can say that the recovery process may never be over. I saw more things that I want to implement than I have time to implement, but I'm making a solemn promise to try get them all done. FoxForward 2007 for me was simply one of the best experiences and I highly recommend the conference to anyone wanting to learn more about VFP as well as many other technologies as well.

Meeting people from all over with different backgrounds and experiences always can help broaden your horizons and when each person you meet is willing to offer the knowledge they have, the results are amazing. The FoxPro community has always had a camaraderie that is rarely found among practitioners of other programming languages and I have benefited greatly over the years from my association with other FoxPro code-slingers.

The sessions led by Ed Leafe, Dave Bernard, Michael Babcock, Stein Goering, Brian Marquis, Tod McKenna, Kevin Ragsdale, Alan Stevens, David Stevenson, and all the others left my head spinning and my fingers itching to get back on my keyboard and start pounding away and see if I could make all of the great ideas I saw work in my library. But more importantly, in-between each meeting and at each meal, there were plenty of opportunities to meet other people and find out about what they do, how they use FoxPro and to share ideas with each other.

For two and one-half days, I lived, ate, and breathed FoxPro and it was wonderful. If you are reading this blog right now, it is because I discovered at the conference that there was not a lot of information available about using FirebirdSQL with FoxPro. Having just spent the last year developing with Firebird and FoxPro, and having been encouraged by several of the people I spoke with to start a blog on the subject, my first order of business upon returning was to get this blog off the ground.

My next post will contain more information about that project and just how we found Firebird and got it implemented, but I just wanted to take a moment and thank Kevin Cully, his family, and the sponsors for putting on a great conference. The friendships and the learning will be with me for a long, long time!

PHT