views:

156

answers:

1

I have given an assignment to get the feasibility for making a dll which takes some inputs such as database name(Mysql,access, sql, oracle etc.) and some more inputs to generate a query and based on that the dll should return a recordset to the application.

is it a good idea. if yes then what inputs should i consider

+3  A: 

It's completely feasible, the issues arise when you encounter different SQL grammars - take a look at how Hibernate handles this with the use of Dialects.

The popular databases - Oracle, Sybase, MS SQL Server, MySQL - have slight differences in the SQL grammar they allow. Essentually the vendors have implemented and extended ANSI SQL in different ways.

The simplest case I can think of is when the way you assign pseudonyms to column names, some databases require this:

SELECT x AS y FROM some_table

while others require:

SELECT x y FROM some_table

There's many more such examples, but the bottom line is that when writing a query abstraction layer that works across all databases you need to abstract the concept of SQL generation so that it can be tailored to each database you are going to support (as I said, Hibernate does this by allowing you to specify a dialect specific to the database you are using).

Nick Holt
thanks for the answer but i didn't understood it much.can you please explain a bit more in detail.
Meetu Choudhary
OK Thanks... For explaing. can you please also guide me hhow to go with hibernate actully what i found is its nhibernate for dotnet. could you please guide me.
Meetu Choudhary
I think your assignment wants something like the Hibernate Criteria class - you set the query attributes and get a list of the objects that match those attributes. Inside Hibernate generates the SQL query (with the appropriate dialect) and parses the result set mapping each row to an object. For your assignment you just need to generate the query and return the result set - in it's simplest form this is just string concatenation, where you're combining variables like table and columns names to create a select statement (Note: this isn't how Hibernate does it, that's a whole other story).
Nick Holt