3

To avoid creating SQL statements as strings in a class I've placed them as .sql files in the same package and read the contents to a string in the static constructor. The reason for this is the SQL is very complex due to an ERP system that the SQL is querying.

There's no problem with this method, though since the SQL reading mechanism quite simply just reads the whole file any comments within that file may cause the read to fail if they are at the end of the line, as when reading it first removes excess whitespace and removes new-lines. Full commented lines (i.e. lines beginning with -- are removed).

I could enhance the simple reading to read the file and remove commented lines etc, though I have to wonder if there is something already available that could read an SQL file and clean it up.

GarethD
  • 68,045
  • 10
  • 83
  • 123
Brett Ryan
  • 26,937
  • 30
  • 128
  • 163
  • Somehow similar: http://stackoverflow.com/questions/660609/sql-parser-library-for-java – zeller Sep 14 '12 at 08:34
  • 4
    Why do you have to clean things up at all. I see that removing line breaks will break your comments, but why not simply pass the query to the database engine as it is? That way, you'll even get meaningful line numbers in error messages. – MvG Sep 14 '12 at 08:44
  • That's a valid point MvG, though as some of these SQL statements in formatted are quite large, in some cases over 100 lines long with plenty of white-space in them due to `case` statements from `left join`s and nested `select` statements there is much to be saved by tidying. Our RDBMS (OpenEdge) also does not provide line-numbers in errors and actually includes all the white-space without new-lines in the errors making it actually harder to identify the error. Your point is valid though for other cases. – Brett Ryan Sep 14 '12 at 08:56
  • Why not use stored procedures to store your SQL instead of storing them as .sql files? Your queries will perform better and you won't have to bother parsing text files into your classes. Win-Win situation in my book. – GarethD Sep 14 '12 at 09:42
  • @GarethD, while our RDBMS is OpenEdge I don't believe my question is directly related to it. I do respect that stored procedures could solve a problem such as this in some cases though it is subjective and not part of my original question. – Brett Ryan Sep 14 '12 at 10:54
  • I would like to clarify my last comment. OpenEdge does support stored procedures, but not as SQL. OpenEdge stored procedures are actually Java code, thus any SQL must still be expressed in a string. This is then stored in the OE DB as bytecode and executed on calling. – Brett Ryan Sep 14 '12 at 12:41
  • 1) comment characters (--) can appear anywhere in the line so if you want to clear these up, probably best to use some sort of regex. 2) do you need to handle multi-line comments (/* */)? 3) While this is generally a very error prone way to approach things (string manipulation is hard to get right), you may want to validate the changes before executing by using SET NOEXEC ON. – saarp Sep 28 '12 at 18:51

1 Answers1

0

I've seen this same problem solved in a project I've worked on by storing queries in XML, and loading the XML into a custom StoredQueriesCache object at runtime. To get a query, we would call a method on the StoredQueriesCache object and just pass the query name (which is defined in the XML), and it would return the query.

Writing something like this is fairly simple. The XML would look something like this below...

<Query>
  <Name>SomeUniqueQueryName</Name>
  <SQL>
    SELECT someColumn FROM someTable WHERE somePredicate
  </SQL>
</Query>

You would have one element for every stored query. The XML would be loaded into memory at application startup from file, or depending on your needs it could be lazy loaded from file. Then your StoredQueriesCache object that holds the XML would have methods to return individual queries by name. In my experience, having comments in the query has never caused any issue since linebreaks are part of the XML node's innertext, but if you want your StoredQueriesCache methods that retrieve the queries could parse comments out.

I've found this to be the most organized way of storing queries without embedding them in code, and without using stored procedures. There should honestly be a library that does this for you; maybe I'll write one!

Jim
  • 6,753
  • 12
  • 44
  • 72
  • Nice idea, though you lose the ability for the IDE to validate the SQL and prevents you from executing the queries for testing in isolation. I currently just use embedded SQL resource files within the project which works okay. I remove line comments when loading the resource files but it doesn't work if the comment is at the end of the line, I then remove line-breaks because our SQL engine (OpenEdge) doesn't play nice with reporting errors when there are line-breaks. – Brett Ryan Oct 11 '12 at 08:35