"Stepping Through" Queries -- Debugging Tool

  • Thread starter Thread starter Smartin
  • Start date Start date
S

Smartin

This might seem a bit absurd in the context of SQL, but I'm wondering if
anyone has felt the need to "step through [running SQL] code" in a
manner similar to that which is available in some HLL development
environments?

Sometimes I think it would be enormously helpful if I could examine a
query's response as it walks through the data sets, one row at a time,
setting breakpoints, watches, or what have you, in order to isolate bugs.

Unlike VB/VBA/.Net, SQL is an "all or nothing" proposition. Either it
runs or it doesn't; if it runs it may or may not return the desired
results. In the end I am left with rewriting the code or running
isolated cases to debug issues.

FWIW I have tried Jet's SHOWPLAN.OUT functionality but have not found
any value in it. It is entirely likely I don't know what to look for in
this, but this method requires a functioning query in the first place
anyway.

Any thoughts on this?
 
Hopefully you will get repsonses from someone who is familiar with the other
tools you talk about also.

The choices available in Access are:
- Use OpenRecordset(), and walk the recordset so you can see the data record
by record, and branch as needed.
or
- Execute the query. Then use something like the Unmatched Query Wizard to
identify the records that didn't make the grade.
 
Access/Jet allows you to create complex queries as a set of
distinct subqueries. Create your subqueries, check that each
does it's simple task correctly, then assemble them together.

For example, I seldom have parameters on my base queries:
the parameters are all applied at the final step, which allows
me to check my joins and my parameters independently. The
miracle of standard RDBMS is that everything is optimised
out anyway.

This is a lot more work when doing dynamic sql, but Access
has an excellent GUI for doing this stuff as static sql instead,
and a database for storing the static SQL in.
For dynamic SQL you typically just need to dynamically assemble
your pretested views.

(david)
 
Back
Top