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.