Web Based SQL Work Bench

Note: Please Scroll Down to See the Download Link.

About Project

The Database interface tool is designed and developed to test the database Connectivity features of JDBC drivers and their supported databases .The Environment chosen to develop this tool is ORACLE.  My-SQL .The tool  provides  in depth control of the API sequence of the driver. The DBI tool covers all methods defined in the JDBC specification. DBI  tool  provides facility  for  processing  queries .The tool provides an abstract format  and interface to submit an SQL statement and view the results. This mechanism is further simplified  with  “Load and Go” command . For instance when a “Load & Go” is used, the tool automatically takes SQL statement and view the results creates a    JDBC  statement object ,executes the statement  and displays the result  set.  During the above sequence of operation  the  tool doesn‘t   wait   for any user action or input   block .This    tool  has other additional  useful feature for  exporting  the  data in  html  or excel  format.

AIM

Enterprise  applications  are changing more rapidly and the needs of enterprise is increasing enormously, as the needs are growing  towards decision making, obtaining accurate data in time plays a  major role to withstand the fray .Ultimately the success lies in how fast can we retrieve data and manipulate the information .To send  queries and process them quite  efficiently is the task of drivers  which lead us to connect to the databases. When different companies  provide different drivers to connect to their databases ,we seldom found  a server side application to interact with any database like oracle , my-sql  offered by a database providers and also there is a need to have an user friendly interface to query the database and obtain the results from the server.

INTRODUCTION

Database Interface is an industry-standard tool for application development.   Using Database Interface, developers can interact with any back end software   (i.e. Oracle, MS-Access, My-SQL etc).The database interface can be used to build, test and debug PL/SQL packages, procedures, triggers, and functions.   Database Interface users can create and edit database objects such as tables, views, indexes, constraints, and users.  Database Interface’s SQL Editor provides an easy and efficient way to write and test scripts and queries, and its powerful data grids provide an easy way to view and edit data related to any DBMS/RDBMS tool.

The requirements of a database application developer will vary from project to project.  On a large team where DBAs manage the DDL, a developer may spend 90 of development time coding and testing SELECT queries to issue from 3GL or 4GL application code. In such an environment, a developer might be concerned only with viewing the DDL and database code. On smaller teams, a developer might be responsible for maintenance of the development schema, movement of test data between schemas, writing procedure code, populating tables from legacy sources, and more. Database Interface facilitates all of these needs.

For example if you are working with oracle, you don’t have to be a PL/SQL expert to access database objects with Database Interface. You can view the Oracle Dictionary, tables, indexes, stored procedures, and more - all through a multi-tabbed browser. Database Interface utilizes direct Oracle OCI calls for full access to the Oracle API. Advanced editing features save time and increase productivity. Code can be created from shortcuts and code templates. You can even create your own code templates.

IDENTIFICATION OF ?NEED

To clearly identify the need of this application,  it is exemplified  with one   situation. A project manager is dealing with an enterprise application, which is in the development phase. This application has got different faces such as a desktop interface, a web interface  and mobile interface. Initially all these applications are not integrated. Once these entire interfaces are working well, they are going to be integrated. In such occasion the database which is being used must be same.  But while designing these individual modules, creating a sample database for each module and perform the test, it is not possible practically. So for that reason, there must be a kind of tool, using which we can carry the database to any another location and use the database there very easily. The present application will fulfill the requirement. The current application can be used just as a brief case of database.

Existing System:

The existing system has different tools or consoles for each and differently. The user feels uneasy to switch over to a different database by learning the how to use console for that database. User has to manually copy the results from the console and paste it in a file to store results of the query.

Disadvantages:

The user feels uneasy to switch over to a different database by learning the how to use console for that database. The user can not export the data displayed by the SQL query.

Proposed System:

This entire system needs to be performed on the browser which is common to all the databases for better performance and makes it user-friendly.

The objectives of the system are as follows.

·  To connect to different kinds of databases which are located in different areas and it should be Flexible and user-friendly screens. We can run all the SQL statements from the browser and see the results on it.

·  We can switch to any database by just switching the connections

·  The results has to be displayed and those results can also be exported to .CSV files  

MODULE  DESCRIPTION:

Structure:

It gives the list of all tables which are present in the current/selected user. This module is used to browse and view the structure of an existing database table. It displays the table information such as its column names, its data types and their sizes. The user can easily understand the structure of the table with typing the “disc” command. We can write the commands at the SQL prompt, but it needs manual typing for every table. But the module allows us to see the structure of the table simply by clicking on the table name of the current user, so that we can get the information quickly about.

Browse:

The functionality of this module is display the data of an existing data object, in very attractive and convenient manner. We can display required number of records for page in a sequence manner. The data is retrieved manually using SELECT statement. But this module provides user interface to display the records without writing any select statement. 

It doesn’t allow us to display result from more than one table.

Insert:

Insert module provides to insert the records into table without writing any insert commands. It provides blank text fields with table. We simply insert the data into that text fields, when we say submit

The data will be inserted to respected table.

SQL Query:

This module provides us a text area which allows to type any type of SQL statements, since the system provides interface to only some features supported by database.  The browse module supports to retrieve data from only one table. If we want to access data from more than one table, It doesn’t support.  This module SQL Query allows us to write complex queries to retrieve data from more than one table.  As well as we can write any type of procedure, functions, triggers and so on. The result of the SQL statement will be displayed immediately after executing the statement.

Export:

This module gives the feature of exporting existing schema object(s) to an SQL file. The module has three exporting features. We select the table name from drop down menu. The data will be exported to either one of these formats like .sql, HTML table or MS-Excel sheet. If we export the schema then the schema will be imported to another user in the same database or to different database if supported.

Operations:

The operations module provides an interface to user so that he can create, alter, rename and drop the tables from a database. As well as he can alter, rename, drop and empty the column from a table

Hardware Requirements:

The selection of hardware is very important in the existence and proper working of any software. In the selection of hardware, the size and the capacity requirements are also important.

This project can be efficiently run on Pentium system with at least 256 MB RAM and Hard disk drive having 40 GB. Floppy disk drive of 1.44 MB and 14 inch Samsung color monitor suits the information system operation.(A Printer is required for hard copy output).

Pentium processor       --------  233 MHZ or above

RAM Capacity            --------              256MB

Hard Disk                             --------    40GB

Software Requirements:

One of the most difficult tasks is that, the selection of the software, once system requirement is known is determining whether a particular software package fits the requirements. After initial selection further security is needed to determine the desirability of particular software compared with other candidates. This section first summarizes the application requirement question and then suggests more detailed comparisons.

Operating System                    --------             Windows Xp

Browser                                   --------              IE, Mozilla.

Web/Application Server          --------              Tomcat

Database Server                       --------             Oracle 10g

Database Connectivity            --------              JDBC 

Other Tools & Technologies   --------              Java(JDK),Servlets,HTML

Click here to download Web Based SQL Work Bench source code