Re: Query standards; SQL Management

Red My experience is that Java programmers rarely have a good understanding of SQL or even tuning the SQL. A good design decision (IMO anyway) we made early on in the system I am working with was to embed database logic in SPL code which could then be called from Java with CallableStatement(). This separates the database logic from the application logic, allowing you to have somebody who knows SQL and how to tune it write the database logic. Also you are able to isolate the impact of database changes to the SPL layer. What I would do is to work with the specs and come up with a bunch of business objects (could contain 1 or more tables) which can be manipulated using new_, get_, set_ and _delete methods. These methods are actually your stored procedures whic corespond roughly to insert, select, update and delete methods. If you put some (actually depending on your application, probably quite a lot) thought into the design of these procedures, you can come up with simple intuitive interfaces that can be used by the Java programmer to call these methods to operate on the business objects. Actually, had you asked me for my opinion a year or so back, I would have said that what I propose in the above paragraph was not possible, given the many complexities of an average business object. I know differently now - its possible, you just have to think about it hard enough. HTH Sujit Red Valsen <red_valsen@yahoo.com> on 01/20/2000 10:41:48 AM Please respond to Red Valsen <red_valsen@yahoo.com> To: informix-list@iiug.org cc: Subject: Query standards; SQL Management We're developing a web-based application (like everybody else) using IDS 7.3, Solaris 2.6/x86 (don't ask), and Java/JDBC with servlets. The SQL will, of course, be embedded in the Java. Aside from tutoring neophyte Java programmers in SQL, I also have to establish standards for writing and maintaining their queries. Is anyone else using a cogent set of project-level rules and/or procedures for creating and managing SQL in a development environment? I'm thinking not only of style (e.g., "always precede the columns in select clause with table alias," "distinct conditions of the predicate should appear on separate lines," "limit joins to no more than 5 tables," ad nauseum), but management tools also: how to deal with changing data structures after the SQL is already writ? Parse the query and maintain a query database which tracks queries by identifier, tables used, columns, etc? I'm obviously trying to avoid re-inventing the wheel here. Any insight into these issues from other experienced developers and DBAs would be appreciated greatly.