Introduction:
As we are all aware, Borland's JBuilder makes it incredibly easy to create 2-tier database applications. A couple of areas that seem to cause a great deal of questions include the following subjects: parameterized queries and Data Modules.  In the following simple example, we will walk through the steps needed to use a Data Module, and how to put queries that use parameters inside a Data Module. You will see that Borland makes it quite simple to implement this process.  In just seven (7) simple steps, we will make an application that will introduce both Data Modules, and parameterized queries. The lab is constructed using JBuilder Client/Server 1.01 however, you could also use JBuilder Professional 1.01. 

Before we get started, lets ask the obvious questions: what are parameterized queries and why would I use them, and second, what is a Data Module and why would I use it?  To answer the first question I refer to the JBuilder help to give the definition: 

"A parameterized SQL statement contains variables, also known as parameters, the values of which can vary at run time.  A parameterized query uses these variables to replace literal data values, such as those used in a WHERE clause for comparisons, that appear in an SQL statement.  These variables are called parameters.  Ordinarily, parameters stand in for data values passed to the statement.  You provide the values for the parameters before running the query.  By providing different sets of values and running the query for each set, you cause one query to return different data sets."

The Lab:

The lab is quite simple by design.  It is meant to give you an introduction to the JBuilder environment and the work-arounds for Sun's Java JDK and Borland's Data Modules. It will consist of a limited interface that includes the following JBCL (JavaBeans Component Library) components:

The application will be responsible for returning whether or not a Company exists in a database. We will pass the contents of the FieldControl to the parameter in the Data Module and the Data Module will search the database and return the result set to the calling program. 

Parameterized queries can be run with named parameters, with parameter markers, or with a master-detail relationship.  This lab only covers named parameters, while the other topics are outside the scope of this paper.  Please refer to the JBuilder help book: "Database Application Developer's Guide" for more information. 

Steps for creation:

Step 1: Setup Application

1. The first step is to create a new Application.

1. This can be accomplished by clicking the File menu and selecting the New item. This should be the first item on the File menu.
2. The gallery will be displayed next; this is a code and object repository that is used by JBuilder. The following screen should be displayed.
3. 
4. Double click on the Application icon or click the application icon and press the OK button located at the bottom of the screen. This will start the New Project Wizard.
5. The Project Wizard will be displayed.  This is where you can set up the package that will be associated with the application that is being created.
1. \JBUILDER\myprojects\ - is set up as the base directory for your application, the next directory setting is related to the package that will be generated in the final process.
2. Let us call the package advConcept1 for this project.
6. The last item on the file line is the .jpr.  This file is used by JBuilder as a general house-keeper since it maintains all of the files and JBuilder settings used during the development process.
1. Let us call the .jpr file, MyParamQuery.jpr.
2. The rest of the information on the Project Wizard is optional.  You may fill it in if you so desire, however for this project it is not needed.

7. Once completed, you may click the finish button. This action will automatically start the Application wizard.
8. The first step in actually creating an application starts with the application wizard. This wizard will walk you through the steps to generate an application. The most important part of step one is the fact that you are setting up the Class name that will be used to call your program from the Java VM (virtual machine).  
1. The Package line will already be filled in for you, it was completed in the last step.

9. Lets change the Class line to reflect what we will call the program once we have completed it using JBuilder. Replace the text that currently says Application1 with a more descriptive name like simplePQ for simple parameterized query.
1. The next option deals with the application style.  This checkbox refers to whether or not you would like to see the information you typed in on the Project Wizard included in all source code created in this project.
2. For this example, select Generate header comments.
10. Select the Next> button to continue to step two (2) of the Application wizard.
11. Part 2 of the Application Wizard. 
12. 
    1. Change the frame class to reflect the frmMainPQ for the name.  This will be the class that is called from the SimplePQ class that was set up in the prior step.
    2. Change the title for the frame to reflect Parameterized Query Example.
    3. Check the Status bar option and the Center frame on screen option. 
13. Click the finish button. 
14. The AppBrowser should appear as the following.
15. 

Step 2: Setup a Data Module

1. Click the File - New.
2. In the gallery, double click on the Data Module or select Data Module and press the OK button.
3. 
1. A new Dialog will be displayed asking for a Data Module Class Name.
2. 
3. Change the name area to reflect pqDM for the name.
4. Press OK button.
5. The AppBrowser should appear as the following:
6. 

Step 3: Configuring the Data Module

1. Click on the pqDM module in the navigational pane as above.
2. 
3. Select the Design Tab under the content pane.
4. The AppBrowser should appear as below.
5. 
6. Select the Data Access tab on the JBCL component pallet.
7. 
1. Select the Database icon - first icon.
1. Drop icon into the Structure pane.
1. The component should show under the Database tree.
2. Select a QueryDataSet icon - fourth icon.
1. Drop icon into the Structure pane.
1. The component should show under the Database tree.
3. Select a ParameterRow icon - eighth icon.
1. Drop icon into the Structure pane.
1. The component should show under the Database tree.
8. Select the Database component in the structure pane.
9. 
1. Go to the Inspector, it should be representing the Database object.
2. 
3. Click on the connection item.
1. This will give us the connection dialog.
4. The connection dialog should appear as below:
5. 

NOTE: if you have not set up a database with either a BDE (Borland Database Engine) alias or an ODBC (Open DataBase Connectivity) DSN (Data Source Name), please press the following link for setting up a DATABASE for this application. 

6. Press the Choose URL button.
1. This will display a Choose a Connection URL dialog.
2. 
3. At this point you have two options.
1. Use ODBC Drivers or use Borland's DataGateway Product.
7. For the ODBC Drivers, press the Show data sources button, the following dialog will appear. For using DataGateway proceed to step viii.
1. This will show a different Connection Dialog however the principles are the same.
2. If you have not already pressed the Show data sources button, do so now, it will return a list of ODBC data sources currently setup on the machine.
1. If you have not set up a specific data source for this lab or you don't have one that you can use, please refer to the DATABASE setup link. 
3. Select the Data Source to use for this project and press the OK button.
8. For Borland's DataGateway product follow these steps, if you need ODBC follow the steps listed in step vii.
1. Press the local button in the DataGateway box.
2. Select the jdbc:BorlandBridge:advConcept line.
1. If you do not have a line that represents the above, you must define a BDE (Borland Database Engine) alias for the database.
3. Press the OK button.
9. The connection dialog should have the Data source or BDE Alias in the Connection URL edit box.
10. 
1. Input the Username field.
1. For Interbase databases remember that it is SYSDBA (upper case) if you have not setup a user name already.
2. Input the Password field.
1. For Interbase databases remember that the password is masterkey in lower case.
3. Press the test connection button.
1. Success should appear under the button as above.
4. Press the OK button.
10. Select the ParameterRow1 component in the structure pane.
11. 
1. Click the plus (+) symbol to the left of the parameterRow1 item.
1. This should show a new column.
2. Select the new column in the structure pane as shown from above.
2. Go to the Inspector, it should be representing the parameterRow1 object.
3. 
1. Click on the dataType.
1. Select String data type.
2. Change the columnName item to reflect currentCustomer - watch case.
1. Remember to hit return or enter after changing the name, changes only happen after a carriage return.
12. Select the QueryDataSet component in the Structure pane.
13. 
14. Go to the Inspector, it should be representing the QueryDataSet object.
15. 
1. Click on the query option.
1. This will activate the query dialog.

2. Inside the query dialog select database drop-down listbox.
1. The only database that should appear is the database1.
1. The SQL Builder button (Client/Server package only) and the Browse tables button should become activated.
2. You can use the Browse tables to build the query or you could use the SQL Builder to build the SQL statement.  You can also add text to the SQL Statement area; this is how we will build the query for this exercise.
1. Note: If you use the SQL Builder, realize that it will appear on the task bar not on top of the desktop. Click on the icon in the task bar and activate SQL Builder.
3. In the SQL area type the following.
1. Select * from Customer where Company = :currentCustomer.
2. As shown above.
4. Click on the Parameter tab.
1. Select the parameterRow1 from the drop-down listbox in the pane.

5. Click on the Query tab.
6. 
1. Press the Test query button.
2. Success should be display under the test button.
3. Press the OK button.

Step 4: Getting the frame to recognize the Data Module

1. Click on the source tab under the content pane.
2. 
3. Go to the class definition and the class constructor.
4. 
1. Add the following line.
1. pqDM myDM1 = pqDM.getDataModule();
5. Now press the Save all button to continue.
6. Press the rebuild icon on the speed bar on the main window.
1. Return to the source code by clicking on the source tab. 
7. Copy the line that was added in step 4.d.i.1 
1. Add this line to the first line under the class definition.
2. Comment out the old line.

8. Select the design tab 
9. You should see the following text in the structure pane:
1. Parsing text... please wait.
2. 
3. The structure pane should show the myDM1 under the Data Access tree.
1. NOTE: This process will need to be done every time a change is made to the Data Module. This is because the Data Module class is already loaded and needs to be reevaluated. This process will not always work, if the changes are not reflected in the dataset property, exit JBuilder, then restart JBuilder. This process will remove all references to the Data Module and when the project is loaded the changes will be reflected.

Step 5: Defining the User Interface

1. One (1) fieldControl.
2. One (1) textControl.
1. Change the text property to read Company Name.
3. One (1) buttonControl.
1. Change the label for the button control to read Find Company.
4. One (1) checkboxPanel.
1. Add two (2) labels.
1. Click the labels property.
1. The picklistitemeditor will appear.
2. Press the add button two (2) times.
3. Change the items to read. 
1. Record Found
1. Press return. 
2. Record NOT found
1. Press return.
2. Click on the Grouped property.
1. This will change the items in the checkboxPanel to a radiobuttons.
1. Radiobuttons are mutually Exclusive.
3. Set the selected index to 1 for Record NOT found.
4. Set the orientation to horizontal.
5. The interface should look like below.

Step 6: Add the code

1. Now double click on the buttonControl1.
1. This will take you inside to the snippet that must be performed when the button is clicked.
2. 

Comments    = commented lines.

Actual Code = Actual code lines.

1. Add the following code.

1. Remember All database access must be put inside a try...catch block.
1. Catch the general exception (Exception x).

1. The last thing that must be completed is to add the classes to support database objects in the frame class.
1. Click the pqDM.java file name and click the source tab.
2. Select the two (2) import statements that deal with databases.
1. They should be the last ones of the import statements, see below.
3. Copy them into the clipboard (ctrl-c).
4. Click the frmMainPQ.java file name.
5. Copy them into the import statements.
2. 
1. Paste (ctrl-v) in the list of imports.
1. import borland.jbcl.dataset.*;
2. import borland.sql.dataset.*;

Step 7: Compile and Run 

Compile and Run 

1. For a company, insert Max and press return or enter.
2. Press the Find Company Button.
1. It should return found.
3. Test other options.

 
 
LAB COMPLETE!

Database Setup
Setup BDE and or ODBC 

Setup BDE: 

1. Start by clicking the Start button on the task bar
1. On the start menu select the Settings item to show a child menu. The first menu item on the child menu is the Control Panel.
2. Select the Control Panel option and the following window will be displayed.
3. 
4. Double click on the BDE administrator icon.
5. This will display the following window:
2. Configuration of the BDE Administrator
3. 
1. Select the Object menu item.
2. Select the New menu item to display the following interface:
3. New Database Alias Dialog
4. 
5. Click the drop-down listbox and choose the INTERBASE database type.
6. Then click the OK button
4. The following is a representation of what it will look like:
1. The Database pane will be displaying the INTERBASE in highlight.
2. Change the name from INTERBASE to advConcept and press return/enter.
3. 
4. Next change the Server Name to reflect where the database is located.
1. NOTE: This lab has been setup with the knowledge that you have purchased JBuilder Professional or JBuilder Client/Server. Both products come with either Local Interbase for the professional version and Interbase Server for 95 or NT with the client/server addition.
2. Both databases ship with an example database.  Click the Server Name item in the options pane (right side) and ... (ellipsis) will be shown. Change the directory to where the example database is located.
3. Select the Employee.gdb file and press the open button.
4. Next change the User Name from MYNAME to SYSDBA.
5. Once the changes have been made the BDE interface should look as below:


5. The last operation that must be completed is to apply the changes.
1. Two options are available.
1. Right mouse click anywhere a context sensitive menu will be displayed. Select the Apply option, you will be asked to confirm changes.
2. Press OK
3. or
4. Select the Object menu item and select the apply option. It will display a confirmation dialog.
5. Press OK
2. To check the setup, double click on the advConcept item in the database pane.
1. The following dialog should appear.
2. 
3. Type in masterkey as the password and press the OK
4. 
5. If everything was successful then the advConcept name should have an icon to the left of it with a yellow-green box around it. This represents connection.
6. If successful, right mouse click on the advConcept and select the close option. The icon to the left of the advConcept should return to normal.

Setup ODBC: 

 
1. Start by clicking the Start button on the task bar
1. On the start menu select the Settings item to show a child menu. The first menu item on the child menu is the Control Panel.
2. Select the Control Panel option and the following window will be displayed:
3. 
4. Double click the 32bit ODBC icon.
1. The following window will be displayed:
5. Configuring the ODBC Data Source Administrator
1. Click the Add button.
2. 
3. The Create New Data Source will be displayed.
1. Select the InterBase 4.x Driver by Visigenic (*.gdb) inside the listbox as shown below.

4. Select the Finish button.
2. InterBase ODBC Configuration dialog will be displayed next.
1. Fill in the Data Source Name with AdvConceptODBC.
2. Fill in the Description with Advanced Concept Lab.
3. 
4. In the Database edit box type in the directory where the Interbase example database is located.
1. NOTE: This lab has been setup with the knowledge that you have purchased JBuilder Professional or JBuilder Client/Server. Both products come with either Local Interbase for the professional version and Interbase Server for 95 or NT with the client/server addition.
1. Both databases ship with an example database.  Fill in the Database area with a directory name such as: 
1. D:\Program Files\Borland\InterBase\EXAMPLES\employee.gdb
2. The database we should use is the Employee.gdb file.
3. Next change the Username to SYSDBA.
2. Press the OK button, the program will return to the main Administration screen.
3. From the Administrator screen, press the Configure button.
4. 
5. This will display the Interbase ODBC Configuration screen, with the fields filled in for you, except for the password.
1. Type in masterkey as the password, you will notice that the test button becomes active.
2. Press the Test Button.
3. 
4. The following confirmation dialog will be displayed:
5. 
6. Press the OK button.
6. If the database was found and was able to be connected to, the following dialog should appear:
7. 
1. If you do not see the above dialog, most likely the path name is incorrect.
1. Correct path name and try again.