asked
writing a generalised sql query
Hi, We have a grails-groovy based application and initially we developed for MySQL as the database. We now want to extend support for Oracle and MSSQL. The will need changes to some of the queries which are not written in HQL. What are my options here? I think since we are already using Hibernate that would be the way to go. By for many queries written for MySQL only, we will need to create new domain classes to map it to backend tables. I am fine with that if that is a solution which will make queries totally independent of backend database technology we use. Regards, Priyank
Try Toad data modeler,
http://www.quest.com/toad-data-modeler/ I believe that there is a free version, but the full version is well worth the cost. You should be able to reverse engineer your MySQL database, then automatically create MSSQL and Oracle SQL scripts to create the database and objects.
Hibernate and nHibernate as well as other ORM tools are for making generic calls to databases through the code. That would be the way to go. Just understand that every RDBMS is different. Generic queries will run, but they may not run well. Don't be surprised when something that works fine in MySQL tanks in Oracle or SQL Server. Also, you need to be very careful of some of the problems generated by inappropriate coding choices in ORM tools like [N+1 and others][1]. [1]:
http://www.google.com/url?sa=t&rct=j&q=&esrc=s&frm=1&source=web&cd=5&ved=0CEYQFjAE&url=http%3A%2F%2Fweblogs.asp.net%2Fricardoperes%2Farchive%2F2011%2F09%2F18%2Fnhibernate-pitfalls-select-n-1.aspx&ei=vNYKT5TnE4PY0QHQrvzVBA&usg=AFQjCNHFXy6ObNlKgfje3cGYkQ_GY5dQng&sig2=27rZQiA1zm9sIoEsPfpaAQ
