Run Ad-hoc SQL (DML) Queries

Write, run and test Data Manipulation Language (DML) code using a light-weight tool called DB-Visual Architect, as standalone or integrated with your favorite IDE

Sunday, September 17, 2006

Print

Comment

Email

Digg

Del.icio.us

Reddit

Twitter

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.

Page(s)   1