How To: Using ODBC Databases with JBuilder

How To: Using ODBC Databases with JBuilder

Level: JBuilder, SOLID Server Intermediate

Databases everywhere. No matter if you use Delphi, C++ or Java, you need connections to databases. With Borland's JBuilder, using regular ODBC databases couldn't be easier (almost). This How To will show you how to create a very simple applet, which displays the contents of a database table and allows editing. Note that I've chose to use the SOLID Server by Solid Information Technology. You can load a free 30-day evaluation version from here.

Creating a test database

After installing the SOLID Server, start it. Then fire up the SOLID SQL Editor. Connect to "SOLID" with your Admin user ID and password. Then run the following SQL statement:

/* Run the CREATE USER statement in the context of the Admin user */
CREATE USER jbuilder IDENTIFIED BY builder_j;

This will create a new test user for the purposes of this How To. Next, disconnect and re-logon with the newly created user (jbuilder). Then run the following statements, and make sure you have committed everything.

/* Run the following statements in the context of the JBuilder user */
CREATE TABLE Vinyls (
ID INTEGER NOT NULL PRIMARY KEY,
Artist CHAR(50) NOT NULL,
Title CHAR(50) NOT NULL,
Label CHAR(50),
Genre CHAR(50),
TrackCount INTEGER,
TotalLengthMin INTEGER,
RPM INTEGER,
PurchaseDate DATE,
PurchasePrice DECIMAL(6,2)
);

INSERT INTO Vinyls
(ID, Artist, Title, Label, Genre, TrackCount, TotalLengthMin, RPM,
PurchaseDate, PurchasePrice)
VALUES (1, 'Cwithe', 'Spellbound', 'M-Track', 'Goa Trance', 2, 16, 45,
'1997-10-25', 39);

INSERT INTO Vinyls
(ID, Artist, Title, Label, Genre, TrackCount, TotalLengthMin, RPM,
PurchaseDate, PurchasePrice)
VALUES (2, 'Factor', 'Factor', 'Tunnel', 'Goa Trance', 3, 19, 45,
'1997-11-10', 49);

INSERT INTO Vinyls
(ID, Artist, Title, Label, Genre, TrackCount, TotalLengthMin, RPM,
PurchaseDate, PurchasePrice)
VALUES (3, 'Sedona', 'Pulsation', 'Expansion', 'Trance', 4, 25, 33,
'1997-11-26', 39);

INSERT INTO Vinyls
(ID, Artist, Title, Label, Genre, TrackCount, TotalLengthMin, RPM,
PurchaseDate, PurchasePrice)
VALUES (4, 'Chameleon', 'Disco Volante', 'Transient', 'Goa Trance', 3, 20,
33, '1997-12-06', 39);

INSERT INTO Vinyls
(ID, Artist, Title, Label, Genre, TrackCount, TotalLengthMin, RPM,
PurchaseDate, PurchasePrice)
VALUES (5, 'Raver''s Nature', 'Stop Scrachin''', 'Fire', 'House', 3, 15,
33, '1997-12-17', 10);

INSERT INTO Vinyls
(ID, Artist, Title, Label, Genre, TrackCount, TotalLengthMin, RPM,
PurchaseDate, PurchasePrice)
VALUES (6, 'Astral Projection', 'Dancing Galaxy', 'Transient', 'Goa
Trance', 2, 15, 33, '1997-12-26', 49);

Now you need to create an ODBC alias for the database like you normally would. Open Control Panel, and from there the ODBC Data Source Administrator (double-click the "ODBC" icon). Click Add, and select the SOLID Server 2.2 (or whatever version you happend to have) and click Finish. As the Data Source Name enter "JBuilder_Test", and set Network Name to "ShMem Solid". Click OK and close the DS Administrator.

Connecting to the database

Open JBuilder, and select New... from the File menu. Create a new applet. You can enter anything to the fields the Wizard(s) prompt(s) -- these fields aren't that important. Now select the only Java file in the project, and start designing it by clicking the Desing tab at the bottom of the AppBrowser.

Click the Data Access tab, and drop a borland.jbcl.dataset.Database component on the form. Select the component (on the list as it isn't visible on the form) and start to edit the "connection" property by clicking the small button with an ellipsis on it. The "connection" dialog box opens. Click the Choose URL button. From the list of available ODBC connections, select the JBuilder_Test alias and click OK. Then test the connection, and double-check if something went wrong.

Now you have a connection to the database. Without doubt, you need a component to fetch data from the "Vinyls" table. We do this by using a borland.jbcl.dataset.QueryDataSet component. Drop one on the form.

Click the "query" property, and the "query" editor opens. Select the Database1 from the Database list. Enter the following as the value of the SQL Statement field:

SELECT * FROM Vinyls

Click Test Query. If you did everything correctly, a "Success" message apperas. Click OK to dismiss the dialog box.

Adding a grid

Next we need a component to display the data on the table. On the Component Palette, click the Controls tab, and drop a GridControl onto the form. Also add a NavigatorControl below da grid.

Select the gridControl1 component. As for the dataSet property, select the value "queryDataSet1". Note how the data on the database appears "live" on the grid. If you see the data, everything is OK! Wasn't that easy? (Also remember to set the dataSet property for the navigatorControl1 component.)

Now you can simply run the application by pressing Shift+F9, and start adding, editing or deleting data. Note that although we did create an applet, running the program under a Web browser is beyong the scope of this How To.

Conclusion

Now you should have at least somekind of idea how to connect to a ODBC database using JBuilder. Using JBuilder for this task is easy, and the solution works quite well.

Note that there is no code to download, because the code generated by JBuilder is too machine dependent. For example, all path information is given in absolute paths. I'm currently researching a solution for this problem. If you find one, mail me.

Questions? Comments? Give feedback. Or just fill in a form.



Quickly: What did you think about this article? Click the image!



Legal Issues