Establishing a Connection
To make a connection to a database a data source (DSN) corresponding to the database used is needed. The connection object then uses this data source to connect to the database and retrieve data. There are three types of data sources: User DSN, System DSN and File DSN. The DSN provides connectivity to the database through a specified ODBC driver. (An ODBC driver corresponding to the database used is to be installed on the web server).
File DSN is preferred over the other two as it stores the data source information in a separate file which can be ported to a different machine easily.
To open a connection to the database an instance of the Connection object is required, then subsequent manipulations can be done using the Connection object.
The first statement gets an instance of the Connection object. The open method of the Connection object is used to call the DSN. The location of the DSN could be any directory on the server provided the full path is specified. If the database requires authentication then the username and password need to be supplied.
The File DSN may alternatively be placed in a session variable so that a change in the path of the DSN is reflected in all the pages without changing the code.
conn.Execute statement executes a SQL query; if the
query were a select statement that returned records then it
is assigned to a RecordSet object and subsequent manipulations
are done on it.
conn.Close closes the connection to the database. It
is a good practice to close database connections after use.
The Execute method has two optional parameters. The
RecordsAffected parameter returns the number of records
affected by the SQL query. The
Options parameter indicates
the type of SQL statement. The constant used with the
parameter helps the ADO in interpreting the database call and
executing it efficiently. The constants used are
specify that it is a textual command,
adCMDTable to indicate a
adCMDStoredProc to indicate its a stored procedure and
adCMDUnknown if the type is unknown.
AdCMDUnknown is taken as
default if this parameter is not specified.
Connection Object methods and Properties:
|Open||Opens a new connection|
|Close||Closes the connection|
|Execute||Executes a SQL query or a stored procedure|
|BeginTrans||Begins a new transactions|
|CommitTrans||Saves any changes made and ends the transaction|
|RollbackTrans||Cancels any changes made and ends the transaction|
|OpenSchema||Provides information on database schema like tables, rows, columns etc.|
|Attributes||Controls whether to begin a new transaction when a existing one ends|
|CommandTimeout||Time to wait when executing a command before terminating the attempt and returning the error|
|ConnectionString||Information used to create a connection to the data source|
|ConnectionTimeout||Time to wait when making a connection before terminating the attempt.|
|Provider||Sets or returns the name of the provider.|