Hi there,

I am new here.. As part of my final year project at uni I am to create a SQL tool that could be used to access different DBMSs. One possible development is making it configurable , so it could look like psql, for PostgreSQL users even if they were accessing an Oracle DB or like SQL*Plus, for Oracle users even if they were accessing mysql. The system will be developed in Java

I am familiar with Postgres, but not with any other databases. I was wondering if anyone could give me some tips on where to start.. Im not looking for anyone to do the project and thinking for me.. just some tips.. like if there is any existing sql tools like this etc.. I am not sure if I need to learn all the other databases to complete this project.

Any help will be appreciated..
Thanks
J

There are a million and a half of these, everything from command-line to GUI to everything in between.

Most of them use a single interface, e.g. JDBC or ODBC. You'd probably be interested in JDBC, but look at JDO and some other stuff too. There are a *lot* of libraries that build on the vanilla JDBC interface.

As to learning the other DBMSs: yes, but you can pick how much you want to learn. A SQL DBMS (note that there are non-SQL DBMSs out there) will have all sorts of functionality. By order of more standard to less, here's a brief list:

• Simple SELECT queries with column list, FROM, joins and WHERE.
• Subqueries via IN, EXISTS. UNION support.
• INSERT/UPDATE/DELETE, which together with SELECT is DML.
• Basic transactional support, COMMIT, ROLLBACK
• Subqueries in the FROM clause or in columns, or via WITH. MINUS.
• CREATE/ALTER/DROP TABLE, aka DDL.
• Foreign key constraints, check clauses.
• Stored procedures, user-defined functions, triggered stored procedures.
• Reading the schema.
• Dumping the schema as SQL.
• Permissions.
• ALTER DATABASE, ALTER SESSION... by this point you're recreating the DBMS's native tools.

This is just off the top of my head, but you get the point: you just have to decide how much stuff you're willing to do. I'm guessing you'd be quite happy to get the first two levels working.

There are also idiosyncrasies for every DBMS. Oracle, for instance, considers a zero-length string to be NULL. I happen to like that behavior, but it definitely isn't standards conformant, and I'm occasionally bit by the fact that '' = '' is unknown. MySQL (at least back when I used it) would happily let you put text in a number field, so a client couldn't be certain that selecting data from a number field meant it would get numbers. No one can agree how to quote symbols or sometimes even strings.

So, suggestions: pick a few DBMSs you'll actually test, 2, maybe 3. Look at possible interfaces, and libraries that abstract the interfaces. Learn what the different classes of JDBC drivers are. Do some tests and learn how to just connect and loop through rows from a "SELECT * FROM FOO" type query.

Thanks for the reply mate..that should definitely get me started on something..