DATABASE HOW-TO

INDEX:

For Windows users

• Install Firebird SQL database server

• Install JDBC driver for Firebird SQL

For Linux users

• Install Firebird SQL database server

• Install JDBC driver for Firebird SQL

OS independent (JdbM)

• Install JdbM

• Create a database in Firebird SQL

• Connect to a Firebird database with JDBC

• Create Database Tables and views

• Create Queries

• Create Datagrids and Forms

• Create Reports and Graphics

• Create Macros

Install Firebird SQL database server on Windows

• Go to www.firebirdsql.org and download the zipfile for Firebird 1.5

• Extract the zipfile to a new program directory e.g. ..\programs\firebird\

• Create a directory for the databases e.g. ..\database\firebird\

• To create the entries in the registry, change to the firebird program directory and run the command

  • instreg.exe install

Note: I had to change the program path in the registry before it worked (I used E:\programs\firebird\.. and the subdirectory \firebird\ was not included in the path for some reason).

When the server is running, you should see a “fb_inet_server” or “fbserver” process running.

• To install the server as a service run:

  • instsvc.exe install (to install the fb_inet_server process)

or alternatively

• instsvc.exe install –classic (to install the fbserver process)

• To run the server without installing a service run:

  • fbserver.exe –a

and the firebird symbol will appear in the taskbar. Also you will find a “fbserver” process running

Install JDBC driver for FirebirdSQL on Windows

• Go to www.firebirdsql.org and download the zipfile for FirebirdSQL

• Extract the zipfile to a new directory e.g. ..\programs\firebirdSQL\

• Take the file firebirdsql-full.jar and copy it to your Java Runtime engine into the ..\lib\ext\.. directory

Install Firebird SQL database server on Linux

• Go to www.firebirdsql.org and download the rpm for Firebird 1.5

• Install the rpm (usually in the /opt/ directory

• Create a directory for the databases e.g. ../var/firebird/

• Follow the instructions in the readme file to activate your account

Install JDBC driver for FirebirdSQL on Linux

• Go to www.firebirdsql.org and download the zipfile for FirebirdSQL

• Extract the zipfile to a new directory e.g. /opt/firebirdSQL

• Take the file firebirdsql-full.jar and copy it to your Java Runtime engine into the ../lib/ext/.. directory

Install JdbM

• Go to www.ahrauch.com and download the zipfile for JdbM

• Extract the zipfile to a new directory e.g. ..\programs\JdbM\

• You can now either copy the JdbM.jar file to your Java Runtime engine into the ..\lib\ext\.. directory

or

• Modify the CLASSPATH and PATH environment variables to point to the newly created directories for FirebirdSQL and JdbM. You should then be able to run JdbM from the program directory where you have unzipped the file.

or

• Run JdbM from a commandline that includes the correct CLASSPATHes and type

  • java –classpath PathToFirebirdSQL;PathToJdbM;. –jar JdbM.jar (don’t forget the dot at the end)

In LINUX you separate the path entries by a colon

• java –classpath PathToFirebirdSQL:PathToJdbM:. –jar JdbM.jar (don’t forget the dot at the end)

Further help can be obtained from the helpfile at www.ahrauch.com in the support section.

Create a database in Firebird SQL

• Go to the directory where you have installed Firebird.

• Make sure the server is running

• Start a DOS session and change to the Firebird directory

• Run the command

  • isql –u sysdba –p masterkey

This wil log you in as the system adminstator.

• Now you will get the isql prompt “SQL>” at the command line

• Type the command

  • create database ‘PathToDatabase\myfirst.fdb’;

Note that with the semicolon you will always commit a command in isql.

• Type

  • exit;

to quit the isql program

When you go to the PathToDatabase you should find the database myfirst.fdb now. Everything else can be done from JdbM in a more comfortable way.

Connect JdbM to a Firebird database, create tables, queries and forms

Step 1: Start JdbM and create a database session

• Start JdbM

• Choose File/New and you will get a new Database session tree named default.jdb

• Go to the Session node and click on the gear symbol; this will open the properties of the database session

• Give the session a display name e.g. “MyFirst”

• Then you must specify the driver class name of the JDBC driver which is

  • org.firebirdsql.jdbc.FBDriver

• The you must specify the URL of an existing database. The syntax is

jdbc:firebirdsql:Servername/Portname:PathToDatabase

For local databases the Servername is “localhost”. In our example this would be

• jdbc:firebirdsql:localhost/3050:PathToDatabase/myfirst.fdb

• Then you mus specify a username and a password which is from factoryside for the administrator level

  • sysdba as username

  • masterkey as password

NOTE: When you are more familiar with Firebird you should change these settings to avoid security problems.

• For Left, Top, Width and Height you can select appropriate values

• For Auto Connect you stay with “No” for the beginning. When everything works fine you would change to “yes”

• For “Update Mode” you select “Primary Index” since Firebird offers this possiblity

• For Compress BLOBS you should select “Yes” for a new/empty database and “No” for existing databases

• Press OK to save the changes and select File/SaveAs to save the project file.

Step 2: Open the database session and create a database table

• Now you click on the MyFirst node and press the “folder open” button in the tree view.

• A small window should appear that lists the database objects and states that the database has been opened in the last row.

• If any errors occurred, you will see the errors in the window.
  Common problems are:

  • Server not running – check if the “fb_inet_server” process is running

  • Driver class not found because of missing or wrong entry in CLASSPATH or PATH – check the java commandline where you started from.

  • Incorrectly specified driver classname – see chapter above

  • Incorrectly specified database URL – check network connection with “ping ServerName” if you have connection to the database server; check path to the database on the server and access rights to the directory and database file on the server; check port if used by other processes;

• Select File/Save from the menu to save the project file.

Step 3: Create and examine a database table

• Go to the “Tables” node in the tree-view and press the “File New” button

• Go to the newly generated subnode named “table 0” and press the gear button

• In the dialog box, give the node a display name like “Books”

• In the field table you enter the name of the new table “books”

• Go to the SQL commands tab and enter a valid table creation script like the following

  • CREATE TABLE books (IDBOOK CHAR(20) NOT NULL, TITLE VARCHAR(64), SUBTITLE VARCHAR(128), AUTHOR VARCHAR(255), ABSTRACT VARCHAR(255), PRIMARY KEY (IDTEST))

  • Press the Exec SQL button

  • When this has happened without error, you can close the dialog

• To see the result of your work, press the gear button again, go to the Tab “Table Info” and press “Info”.
  Now you should see the structure of the table you have just created.

• Close the dialog.

• Now you have created a table named “books” in your database, you can open the table by pressing the button “open folder” and you will see the empty table with the fields.

• Select File/Save from the menu to save the project file.

Step 4: Create a query and a datagrid to enter data

• Select the “Query” node and press the “file new” button

• Select the new subnode named “Query 0” and press the gear button to edit the properties

• Select a display name e.g. “QBooks”

• In the field at the bottom enter a valid SELECT statement

  • SELECT * FROM books

  • In the field “Auto field” enter our primary field “IDBOOK”
    (this will ensure that you get this field filled with unique values every time you append a dataset)

  • Alternatively you can also first select or enter the table name “books”, then press the “data fields” button and select “IDBOOK” from the drop down list that has been created in the Auto Field section.

  • Close the dialog box by pressing “OK”

• Go to the “Forms” Node and press the “file new” button

• Select the new subnode named “Form 0” and press the gear button to edit the properties

• Select a display name e.g. “My Books”

• Select a geometry for the window you like or leave the default values

• In the drop-down list named “Data Source” you select your query named “QBooks”

• In the field “Form Style” you leave the selected item “Table”

• At last you press the button “Create field list” and close the Dialog with OK

• Now you should see several subnodes under the Formnode “QBooks”. These are the fields you just created.

• Edit the field named “ABSTRACT” and modify what you think that needs to be modified.

  • Change the field width to 300

  • Change the background color to “light green” by selecting the background field and pressing the “color” button

  • Change the font style to “italic”

  • Change the lable name to “Short description”

  • Press OK to close the field dialog

• Now select the form node “My Books” again and press the “open folder” button to open the form.

• An empty table should appear with a navigator on the bottom.

• Press the “Plus” (+) button to append an empty dataset.

• You will notice that one field is alredy filled out: The primary field “IDBOOK” that was defined in the Query definition to be the “Auto Field”

• You can now enter data in the fields and press the Exclamation button (!) to commit the changes in the database.

• Every time you press a (+) you will get a new empty dataset that has already an ID

• With (-) you can delete datasets

• And with the (< >) buttons you can navigate.

• Close the form and press the gear button again. When you now chose “Form” as the field style, quit the dialog and open the form again, you will now find that the form is displayed as in a data mask style now.

• Select File/Save from the menu to save the project file.

Step 5: Create a database with a main form and an attached table (subform)

Let’s imagine we want to enhance our first database.

Table books could for example hold the description of a book and a second table, related to the books table, could hold a short description of every chapter in the book. Thus we would have a parent / child relationship between book and chapter that can best be expressed in a parent/child form.

What do we need to acomplish this task:

• A new table called “chapters” containing the following fields:

  • IDBOOK (references to the IDBOOK)

  • IDCHAPT (unique key for the chapters)

  • TITLE

  • ABSTRACT

How this is done is already explained in Step 4. Just to give you a little help here is the script that creates the database table:

• CREATE TABLE chapters (IDBOOK CHAR(20), IDCHAPT CHAR(20) NOT NULL, TITLE VARCHAR(64), ABSTRACT VARCHAR(255), PRIMARY KEY (IDCHAPT))

• A query called “QChapters” that contains the fields of the new table “chapters” with IDCHAPT as Auto field

This is also done analogous to Step 4 BUT the query contains a JdbM special command $MASTER and looks like this

• SELECT * FROM chapters WHERE (IDBOOK = $MASTER)

This will give us the neccesary “join” between the master form “My books” and the subform “Chapters”

• A form formatted as “data grid” that contains the query “QChapters” as data source

• Then we modify the original Form “My Books”

  • Go to the Node “My Books” and press the “File New” button

  • You get a new, empty field that you select and open with the “gear” button

  • Set the Display Name to “Embedded Chapters”

  • Select “Embedded Object” as field style

  • Select “QChapters” as data source and data field

  • Set the field geometry to (10, 200, 600, 250)

  • Close the field dialog

  • Open the form node “My Books”

  • Set “Master Field” to “IDBOOK” – NOTE: this matches the $MASTER expression in the QChapters query

  • Of course the “Form Style” must be “Form”

  • Set the form geometry to (10,10, 800, 600) to cover the new embedded table

  • Close the form dialog by pressing OK and open the form “My Books” by pressing “Open folder”

  • You should now see the combined form with 2 navigators.

  • When you add a dataset to the embedded table it should automatically get a new ID and the master ID of the actual dataset in the main form.

  • Now you can start making it look prettier: Change the field widths, background colours or reorder the fields using the query

  • TIP: When you have primary indices, give them always a name that starts with ID; Format those fields with a gray background and make them “Read Only” so the user can not confuse the database.

• Select File/Save from the menu to save the project file.

The contents of this tutorial project you can find in the project file “tutorial.jdb” that you can download from www.ahrauch.com

Happy computing!

a.rauch@ahrauch.com