Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Development practices when using SQL/Interfaces
Tools to build ASP
Hi all!
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
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.:
THEN before doing any hits to the DB do a
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
Hit the big red [color red]![/color] 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 [color red]![/color] 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
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
Code:
[color blue]
SELECT *
FROM projectInformation
INNER JOIN projectTimes ON
projectInformation.projectLeaderID = projectTimes.projectID
INNER JOIN projectDetail ON
projectTimes.timeID = projectDetail.timeID
WHERE projectInformation.projectLeaderID = 1;
[/color]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.:
Code:
[color red]
SQLText = "SELECT * " &_
"FROM projectInformation " &_
"INNER JOIN projectTimes ON " &_
"projectInformation.projectLeaderID = projectTimes.projectID " &_
"INNER JOIN projectDetail ON " &_
"projectTimes.timeID = projectDetail.timeID " &_
"WHERE projectInformation.projectLeaderID = 1"
[/color]THEN before doing any hits to the DB do a
Code:
[color red]
Response.Write SQLText
Response.End
[/color]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
Code:
[color blue]
SELECT *
FROM projectInformation
INNER JOIN (projectTimes
INNER JOIN projectDetail ON projectTimes.timeID = projectDetail.timeID)
ON projectInformation.projectLeaderID = projectTimes.projectID
WHERE projectInformation.projectLeaderID = 1;
[/color]Hit the big red [color red]![/color] 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 [color red]![/color] 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