Run Ad-hoc SQL (DML) Queries

Quite a lot of applications use an SQL programmable database and IDEs that

programmers use should naturally feature a way to interact with those databases.

But, when you use an IDE, you need to switch between different views and

perspectives when working with your code or the database. Granted that your

database would feature a front-end application that lets you perform queries and

work with the database. But that application would be too database specific and

your IDE is 'heavy.' Both, therefore, are not suitable for performing quick

checks and minor operations on the database, and a demonstration of what a piece

of code can do.

DB-Visual Architect (DB-VA), which is a part of the Visual Paradigm

Enterprise and Professional suites, is a light-weight tool that lets Java, VC++

and Perl developers work on different bits of database or programmatic code

without needing to create classes or complicated code files for them to run.

You can download a 30-day evaluation copy of the program or get their free

for non-commercial use community edition. DB-VA supports a wide variety of

databases including Oracle, DB2, Sybase, MS SQL Server, MySQL and PostgreSQL. It

can integrate with IDEs such as Eclipse, NetBeans, JBuilder and BEA WebLogic

Workshop.

Step 1: Setting it up



Setup of the suite is straight forward and easy. It installs all the tools
giving you options such as installing the suite as standalone, as a plug-in or

as an extension to your IDE. After this you will be prompted to acquire the

license for the tools that you use in the suite when you run them for the first

time. The suite provides a Product Selector tool that can be used to connect

license servers for downloading trial or complete licenses. You will have to

activate licenses individually for all the tools when you run them for the first

time.

This dialog box is the interface for providing all the configuration settings for connecting to a particular database type

Simply follow the instructions on screen to acquire and install them. We used

MySQL 5.0 to work with this tool. For connectivity drivers we used MySQL

Connector/J that can be downloaded from http://dev.mysql.com/downloads/connector/j/3.1.html.

You need to unzip that file to any location on your hard disk for setup.

Step 2: Connecting to a database



Launch the DB-VA tool and click on 'Database' tab, and on the Database
Configuration button. Next, you need to check the 'MySQL' checkbox and

select 'Java' from the list of languages on the left side pane. Now, you can

proceed to the Database Settings page to configure the connection properties.

Here, you will need to select the Connector/J JAR Archive by navigating to it

from the Driver File field. You will also need to edit the 'Driver Class'

value to read 'org.git.mysql.Driver' while keeping its default dialect.

Provide the values for hostname, port number (default of 3306 for MySQL),

username, password and database name before using the 'Test Connection'

button to verify your settings. Click on OK to commit and exit the screen. On

the right hand side pane, the Database Settings page will be enabled where you

need to provide the connection properties.



Navigate to the Connector/J JAR archive and select it for populating the Driver

You can easily create Scrapbook entries of code in either Java or SQL by choosing requisite option from this menu

File field. Change the driver class to 'org.gjt.mm.mysql.Driver' and keep

the dialect to its default value, i.e. 'org.hibernate.dialect.MySQLDialect'.

Next we need to provide values for the connection string viz. hostname, port

number (default for MYSQL is 3306), and the database name ('test' in our

case). To check settings click on the 'Test Connection' button. If you

provided right values for hostname, user and password in connection string you

will see 'Connect Successful' message in a dialog box.

Step 3: Executing queries



Once you are connected to the database, the 'Database' tab displays the
current tables. Right click on a particular table and choose an SQL or Java

query from the menu. For eg, select 'Java>Select' for any table to

automatically generate Java code. Similarly, you can choose SQL queries which

include SELECT, INSERT, UPDATE, and DELETE commands.

DB-Visual Architect also lets you run ad-hoc blocks of host language code

without having to fire up your IDE and write a big bunch of code. It supports an

impressive list of databases and lets you reverse engineer database tables into

UML models.

'Scrapbooks' lets you easily check how a particular code block will perform by running just that block of code without needing to create a bunch of classes

It comes pretty handy for quickly creating tables in your database and

running ad-hoc blocks of host language code to check there correctness. Go ahead

with the tool in case you are in need of last minute modifications of code

particularly in cases involving database access.Other than this tool if you go

for the entire suite you will find some more useful tools than just the

DB-Visual Architect we have demonstrated here. These tools include a Business

Process Visual Architect for designing business processes, a UML modeling tool

and also a SDE (Smart Development Environment) that can plug-in into any IDE of

your choice and be used for the complete modeling-development and deployment

cycle. So just in case you are looking for a one stop solution for synchronizing

your UML designs and code spread across different tools you will find the entire

suite handy.