GuruGraffiti Writing On The Walls Of The Internet
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 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 FilesFirebird 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 FilesFirebirdFirebird_2_0binfpclient.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 IBManager 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!
