Whenever you connect to a website that uses Active Server Pages (ASP),
IIS will immediately compile the global.asa to retrieve any global
variables that need to be used. The easiest way to connect to SQL Server through ASP is by using a global.asa file.
You can also connect in each file if you wish to enable things like connection pooling. For purposes of this article, we will only
discuss session connections which would only be used on a small development servers and is only to get you started. More advanced
connection pooling options will be discussed in a later article.
Two main types of global variables that can be used in the global.asa file are Application and Session.
Application variables are shared throughout the entire application and are shared across for anyone connecting to your website.
These should be used sparingly because of this. Session variables are a little more flexible. Each connection to your website can have its
own session and it's not shared. The downside to this of course, is that each user visiting your site will use its own SQL connection, and you will need to close
each connection in your code.
When you create a ASP project through Visual Interdev, it will create a sample global.asa file for you. If you're starting from scratch though, the first line should begin with :
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
This will set where the queries will process. If we use the Session
object, then you're next section of code will
call the object and set the global variables:
Sub Session_OnStart
Session("dbaconn_ConnectionTimeout") = 30
Session("dbaconn_CommandTimeout") = 60
Session("dbaconn_CursorLocation") = 2
Session("dbaconn_RuntimePassword") = "password"
Session("dbaconn_RuntimeUserName") = "username"
Session("dbaconn_ConnectionString") = "Driver=SQL Server;
User Id=sa;PASSWORD="&Session("dbaconn_RuntimePassword")&";
SERVER="&Session("DefaultServer")&";UID=sa;database=Northwind"
Session("Datapath") = "d:\mssql7\data\"
End Sub
|
The Session("dbaconn_ConnectionTimeout") variable will set the
maximum amount of time that you will allow a user to connect.
The Session("dbaconn_CommandTimeout") in contrast will set
how long you will allow a query to run.
You can set cursor location at the global level with the Session("dbaconn_CursorLocation")
variable. Cursor location can also be set at the query level.
The Session("dbaconn_RuntimePassword") and Session
("dbaconn_RuntimeUserName") variables are optional variables
which we will use in our connection string.
We did this so that we can change the password and
username dynamicly through a website. We will cover
changing this in another article. It is not
recommended that you use the SA username for this due
to security issues.
Session("dbaconn_ConnectionString") is required to connect to SQL Server. The example provided is a DSNless connection. You will call
this variable in all sql queries that you run throughout your website.
You can hardcode the username and password in this section. We chose to in our connection string make the
username and password dynamicly assigned.
If you get nothing else from this article, you must learn how to call a query dynamicly. To query
dynamicly, you must concatenate the query opening with "& and close with &". For example : "&Session("dbaconn_RuntimePassword")&"
The Session("Datapath") variable is an example of a user defined variable. If you wish to expose a variable
to the rest of the website.
The last step is to close the global.asa file with :
</SCRIPT>
The global.asa file provides you an easy way to connect to SQL Server
dynamicly without placing a connection string on the top of all files.
In the above example, you established a connection to SQL Server through the global.asa file.
In future articles we will cover how to query and run DBA commands from a website.
