File not found exception while reading .sql file present in grails-app/conf/sql

Question

Hi,

I have an sql query in my controller action (select * from table....where.....et al). This query is fired when the user submits a page.

It is a 50 lines code that takes in 3 parameters. For eg: select * from employee where empDate='params.empDt' and empNum=params.empNum order by params.sort asc.

In the above case the query takes in 3 parameters (params.empDt, params.empNum and params.sort) dynamically and is executed. Since it is a huge query, I am looking at externalising the query to an sql file. The file thus externalised would be called in the service and the query would be executed.

So i created the .sql file in grails-app/conf/sql/read_date.sql.

When i read this file and the run the app, using run-app, it wrks fine. I am able to read the file and execute the query.

However, when I create a war and deploy it on tomcat, the appn doesnt read the file and I get a FileNotFound Exception.

java.io.FileNotFoundException: grails-app/conf/sql/read_date.sql (No such file or directory) at java.io.FileInputStream.open(Native Method) at java.io.FileInputStream.(FileInputStream.java:106)

Inputs??

Answer 1

Well you see the thing is, the grails-app/conf structure does not exist within the war. If you use something like 7-zip to open up the war and take a look around in it, you will see that all the Classes in conf are now in Web-Inf/classes. The directory structure is only really guaranteed through development. If you created an Sql file in web-app/sql, this might solve the problem for you as the directories under web-app are respected during War generation. Web-app contains thing like javascript files that are required by the application and are then accessible via http://myhost:port/AppName/sql/my.sql. If I were you, I would database the sql though.

Hope this helps. John

Answer 2

I can't really see the point of putting the actual SQL in an external file. Just put the SQL statements in the service class, things will be fine. There is probably no increase in speed if you have the SQL statements in an external file (that needs to be read from disk every time the query needs to be executed). If you need to change the SQL Statement often (which would be a good reason to externalise the statement), you could evaluate the possibility of reading it from the database itself (like from a text field, or something)...

If you really want to use the external text file approach from a service or controller the easiest method is probably to use ServletContext.getResource to get a reference to the data packaged in your web app. Since getResource does take a context-relative URL, you can use getResource("/someSQLstatements.txt") and trust it to work, regardless of the location of your web application on the server's local filesystem OR the path it's mapped to via the servlet container. That should work well. (see also :http://www.velocityreviews.com/forums/t131134-specifying-path-for-file-to-be-read-by-servlet-with-tomcat.html)

have fun.