I've been out for awhile and I'm not certain to the last few weeks of posting (sorry, we all need a break form TT once and while), but I noticed one as always common thing. In the last day I have been checking all out, the same errors with SQL statements have been coming up out of the old rusty nail holes in the floor boards.
So I'll iterate a great tip a wise old PA told me ages ago and is practiced in crucial development all over this great wide earth by PA's using DB's for data sources.
First note, I have yet to find a database that does not have a <quote> SQL Analyzer</quote> built into them. This tool is your bread and butter to alleviated SQL issues on the interface side of things. So use them!!!
So hereÆs the way to do that to improve productivity of your interface development, relief of debugging sessions and relief of your headaches. We will run through an example here using two of the most common. SQL Server M$ Access
Before you ever insert a SQL statement into your interface (ASP script, PHP script, JSP script or whatever) you should test it in SQL Server's "SQL Analyzer" or M$ Access's "SQL View"
Example situation: You have to join three tables on a unique ID of 1 Table names are projectInformation, projectTimes, projectDetail A syntax error prone task when attempts are made to build in your scripts form scratch with little of even moderate SQL skills by developers.
All right, with that said, do you start hacking away in ASP using vbscript or jscript? NO!!!
HereÆs what you should do
SQL Server: 1) Open Enterprise Manager 2) Expand to the database and select it 3) Top menu click Tools 4) Scroll and select "SQL Query Analyzer"
Analyzer opens.... Enter your statement. In our case something like this
SELECT * FROM projectInformation
INNER JOIN projectTimes ON projectInformation.projectLeaderID = projectTimes.projectID
INNER JOIN projectDetail ON projectTimes.timeID = projectDetail.timeID
WHERE projectInformation.projectLeaderID = 1;
Hit F5 and you will either get your view or get a syntax error (fix as needed) Now just add in your conversions to a string that can be read by the server e.g.:
Copy/Paste the output in the browser window in SQL Analyzer and hit F5. If it ran you are set and won't go through he** debugging typos and syntax mistakes sense you spend all your time thinking of what your SQL needs to be in a SQL syntax related tool and not the server interpreting ASP scripts.
And as I said we'll do M$ Access 1) Open the Database 2) Go to Queries 3) Click New 4) Choose Design View 5) Add the three tables (in our examples case) 6) Right click on the title "Query1" 7) Scroll to "SQL View" and select it
Two choices here. Select all the SQL generated to this point and delete it or adjust it for the join. (BTW: this is how you do full procedural SQL statement in Access) ;)
So you enter your statement with the obvious changes to write it in JetSQL over TSQL
SELECT * FROM projectInformation INNER JOIN (projectTimes INNER JOIN projectDetail ON projectTimes.timeID = projectDetail.timeID) ON projectInformation.projectLeaderID = projectTimes.projectID WHERE projectInformation.projectLeaderID = 1;
Hit the big red ! In the tool bar. It runs?!? Now do your syntax changes just as prior Response.Write It Response.End It
copy/paste back to your SQL View and hit that big red ! In the tool bar again. It runs you're good to go.
So what do you get out of this? 1) All questions that are SQL related go to the SQL related forums e.g.: Microsoft SQL Server: Programming Forum183 Microsoft: Access Queries and JET SQL Forum701 NCR: Teradata Forum328 ANSI_SQL Forum220 Oracle has twenty of them for each version errr.. So I'll post the latest Oracle: Oracle release - 9i Forum759
2) no more thinking of SQL in your interfaces, but thinking of what you should be thinking of at this stage of the sdlc; THE INTERFACE and simple syntax concerns with the language of choice
3) The most important!!!! No more headaches.
Good luck and happy programming from the little people