Wednesday, October 1, 2003

Easily connecting to MySQL databases from WebSphere Studio

DATABASE CONNECTIVITY

By Dan Velasco

Connecting to databases from within WebSphere Studio is surprisingly easy. As an example, I'll show you how you can quickly connect to a MySQL database. Note that there are many other database types that you can connect to using WebSphere Studio, including DB2, Informix, Microsoft SQL and Oracle. I'm going to focus on MySQL here since it's a great, free open-source choice. Note: Cloudscape is free as well, but it is limited to a single concurrent connection.

If you don't yet have or have not used MySQL, take a look at the bonus tip at the end of this article for links to help you get started.

Once you have a MySQL server up and running, either on your local computer or on a server, you just need to do to create a connection to a MySQL database within WebSphere Studio. Here's how.

Open the Data perspective

Open the Data perspective if it is not already open. You can open the Data perspective using one of the following techniques:

  • Choose Window --> Open Perspective --> Data, or
  • Click on the Open Perspective toolbar button and select Data.

If you don't see the Data perspective on the list, choose Other to find it.

Create a new connection

Right click on a blank part of the DB Servers view on the bottom-left and choose New Connection. The New Connection dialog box will appear as shown in Figure A.

FIGURE A

Establish a data connection to the MySQL database (click for larger image)

Specify connection details

In the Database Connection dialog, give the connection a name and fill out the database, user ID and password information. Select "MySQL, V3.23" as the Database vendor type. For the driver type, you can choose either MM.MySQL JDBC APP DRIVER if your MySQL server is local to your computer or MM.MySQL JDBC NET DRIVER if the database is on another server. Click Finish when done.

View the database structure

If the connection was successful, you'll be able now to view the structure of your database in the DB Servers window. To see sample data from the database, right click on the name of a table and choose Sample contents. To close or re-open the database connection, right click on the connection name and choose either Disconnect or Reconnect.

Quick tips

That's it, you've now created a database connection that you can use to examine the structure/content of a MySQL database. Here are two other quick tips.

To actually change the structure of the database, you'll need to use the Import to Folder feature. Simply right click on the connection, database or table name in the DB Servers view and choose Import to Folder as shown in Figure B. Choose a name for the Folder and click Finish. For more information about how to use this feature, check the WebSphere Studio Help.