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.

Hi, Red; I don't think you can accurately paint a picture of what a standard SQL should look like. I don't think you can try to place limitations on it at least. There may be a time when a 6 table join is required, and runs efficiently, and then you've given your poor programmer a debate on his hands as he fights with beauracracy to try to implement his sql. There are a few things that I have learned to do that have made my sql coding life easier: --Alias table names, and always precede joins with table names aliases. instead of select x,y,z from cat, dog where a=b and b=c use select c.x, c.y, d.z from cat c, dog d where c.a=d.b and c.b=d.cIt reveals a heck of a lot more information. --When using multiple tables, put commas at the front of the line. Instead of select x,y,z from cat c, dog d,horse h Use - select x,y,z from cat c ,dog d ,horse hIt's a subtle thing, but if you want to comment out the join to horse you merely have to comment that line. In the above example, you have a syntax error with a dangling comma, and it creates a bit of work. This is just a nice convention I've taught myself. Same thing with AND avoid using where a = b and c = d and e = f When : where a = b and c = d and e = f is so much more manageable, and readable, imho. --When doing inserts, always list out the destination column names. instead of insert into cat values (x,y,z)use insert into cat (size, breed, sex) values (x,y,z)If a new column is inserted into the table, your code won't die. --Capitalize all the reserved words. SELECT, FROM, WHERE, AND, UNION, INSERT, UPDATE. Makes it easier to read. --Establish naming conventions for variable names (in procedures) and --temp table names. Temp table might be preceded with tmp_ for example. Nothing more frustrating than reading through someone's code and seeing a table referenced and not knowing that it's a temp table. Hope that helps. Curtis Bennett CIBER, inc Overland Park, KS In article <388756EC.AE87770A@yahoo.com>, Red Valsen <red_valsen@yahoo.com> wrote: > 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. > > Sent via Deja.com http://www.deja.com/ Before you buy.