| Author |
Topic |
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
 Posted - 2007-03-06 : 11:54:09
|
| I had to debug a sproc today, and after about 20 minutes I found the problem was a piece of code that had been inadvertently left in after the last debugging/testing session.So that got me to wondering...doctors have to count the sponges they put into patients during surgery, and then count them again as they are removed to make sure none are left behind. What kind of similar tricks or devices can SQL developers use to mark code for removal after testing and debugging? I'd like to hear a variety of ideas from developers on how they handle this problem.STAR SCHEMAS ARE NOT DATA WAREHOUSES! |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-03-06 : 12:14:40
|
| I wish sql would have incorporated something like #debug directive specifically for this purpose. Actually there are no build modes like Debug and Release for the back-end as they are for the front-end.So, I guess the responsibility lies with the developers to keep track of the stuff added.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
codism
Starting Member
11 Posts |
Posted - 2007-03-06 : 12:18:36
|
| A source control system would helps a lot. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-06 : 15:01:23
|
"A source control system would helps a lot."Unless I'm missing something I don't see that "counting any sponges"!We use/**DEBUGSELECT * FROM MyTable WHERE MyPK = '1234'DEBUG**/ and we comment this IN as follows:/**DEBUG*/SELECT * FROM MyTable WHERE MyPK = '1234'/*DEBUG**/ (note the single and double "*"), which means we can search for /**DEBUG*/&/*DEBUG**/ which should not exist in installation scripts.For things that must not get into the "wild", or need specific action when installing, we also do:SELECT A, B, CFROM MyTableWHERE A = @Param1zzz Set this to '123' on DEV and '456' on LIVE AND B = '123' -- Some comment about this being for debugging only! obviously the "zzz" will prevent this running. We comment it out when we install/test the Sproc, but if we are editing it we try to remember to comment it back out again.When we build the "release" scripts we search for "zzz". Those that are like the one above we change to '456' for the "Production" environment. Those that are like:--zzz Some comment(i.e. got accidentally saved commented-out) we take the appropriate action AND change the master copy to remove the comment.Its all String and Gum, but it gives us a slightly better fighting chance - I think!Kristen |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-03-06 : 15:23:14
|
quote:
Originally posted by Kristen
"A source control system would helps a lot."Unless I'm missing something I don't see that "counting any sponges"!We use/**DEBUGSELECT * FROM MyTable WHERE MyPK = '1234'DEBUG**/ and we comment this IN as follows:/**DEBUG*/SELECT * FROM MyTable WHERE MyPK = '1234'/*DEBUG**/ (note the single and double "*"), which means we can search for /**DEBUG*/&/*DEBUG**/ which should not exist in installation scripts.
Very nice, I like it. I may try to start using that.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-03-06 : 16:44:22
|
I like the /**DEBUG*/SELECT * FROM MyTable WHERE MyPK = '1234'/*DEBUG**/ thing, but I'm not sure I understand what you do with the ZZZ stuff.I also like brett's idea of "interrogating" the sprocs. But I'm thinking it would be best to use a short (quick and easy to type) code that could be searched for directly in the syscomments table.STAR SCHEMAS ARE NOT DATA WAREHOUSES! |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-03-06 : 17:06:16
|
| Does my sig help?DavidMProduction is just another testing cycle |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-06 : 17:16:52
|
| "I'm not sure I understand what you do with the ZZZ stuff."Its for "ToDo" stuff. The "ZZZ" is a syntax error, so you run the SProc, it fails, you look into why, see the ZZZ and make whatever change is necessary - usually, for us, its something like "Do this on DEV, but this different thing on Live"So, on Dev, you comment out the ZZZ because its annoying you ... and you save it in that format, so its useless because its not longer causing a Syntax Error.As part of our "roll-out to production" procedure we make a single script of ALL Sproc SQL files that are "modified" in this version.I reckon that's the same as "Check out of version control all the modified files"Anyway, whether you've concatenated them into a single script, or you have checked them out into a Working-Directory, search for "zzz" - and that are on a line by themselves: do whatever the comment says. Any that are commented out - Tut! Tut! the file was accidentally saved with it commented out so: a) "do whatever it says" and b) find the original source code file and un-comment-out the ZZZ for the next person who uses that file.Dunno if that is clearer though?Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-06 : 17:18:12
|
P.S. I'm sure not lost on you folk, but:/**DEBUG*/SELECT * FROM MyTable WHERE MyPK = '1234'/*DEBUG**/ is designed so that the START and END "Debug" comments are non-symmetrical so, with a longer block of Debug Code, you can tell which is the Start and which the End whether commented-In or commented-OutKristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-07 : 11:16:33
|
| "how do you find where the debug code is?"Well, in theory, it shouldn't have been left in any code that was checked-in to Version Control - so I'm looking for the exceptions where it has accidentally been left in.As part of our roll-out procedure we search for/**DEBUG*//*DEBUG**/--zzz(Programmer's Editor, wildcard/multiple search phrases in one pass, all files in the Version Control which are "current" for the roll-out)if any of these are found the original file is edited to put them back to "disabled" state (the "--zzz" is a bit different, because its "normal" state is just "zzz" - so that it forces a syntax error).Then we build a single script-file of all Sprocs etc. in the release. THEN we search that for "zzz" and take the appropriate action (which is going to be setting something as appropriate for PRODUCTION or QA or whatever)Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-07 : 19:16:36
|
You mean someone could have activated the debug code, run on DEV, disabled the debug code, and then stored the Sproc in the Version Control system?Yup, that's true.Personally I don't care too much - if we have debug code running on DEV that's fine - not so good if its on QA or Production (in our case ...)  I suppose you could search SYSCOMMENTS for the "Debug markers" ?Kristen |
|
|
|
|
Left a sponge in the patient....