Configuring the Server-Side Database Connection

Server-Side Connections for ASP

When using ASP, there are two common methods used to create the server-side connection. The first method involves using an OLE DB provider such as the Microsoft Jet 4.0 OLE DB Provider, used to connect to Access databases. The other method involves making a connection with an ODBC DSN. When using an ODBC DSN, the DSN must be configured on the machine where the pages will be published. You can then simply specify the ODBC DSN name when configuring the connection.  Refer to Configuring the Design-Side Database Connection for more details about configuring a DSN.

Parameter	Description
Date Format	This property specifies the format of date types expected in the database.  This is part of the database configuration.
Boolean Format	This property specifies the format of boolean types expected in the database.  This is part of the database configuration.
Use LIMIT/TOP	This option specifies whether or not the LIMIT or TOP clauses should be used to limit amount of data returned from SQL queries.  You should use this option when you have large tables and your queries typically return a large number of rows.
Same as Design	Use the same Connection Settings specified in the Design Tab.  Your server database connections will use the same database connection configuration you specified for design-side connections.
Use ODBC Data Source Name	This option should be selected if the connection to the database will be through ODBC. Using the list box field, you can select one of the ODBC DSN's that is currently registered on the machine. Alternatively, you can create a new ODBC DSN by clicking on the New... button.
Use Connection String	This option specifies a connection string that contains the details necessary to make a connection to the database. Some of the details included in the connection string are: database driver to be used, location of the database file, and the username and password needed to connect to the database. If you are an experienced user, you can simply type in the proper connection string into the provided field. However, most people would find it necessary to click the Build button and use the Data Link Properties windows to create the connection string.
Login	The user account to be used to access the database.
Password	The password corresponding to the Login name.

The process of creating a connection based on an OLE DB provider varies based on the type of the database as well as the provider being used. However, the common denominator is that the process ends with the creation of a connection string. Basically, a connection string is a group of attribute-value pairs which specify the parameters for the connection string such as the name of the provider and the location of a database.

For example, the connection string for Microsoft Access is usually of the form:

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;
Data Source=C:\CodeChargeStudio\Projects\TaskMan\Intranet.mdb;
Persist Security Info=False

In the sample connection string, notice that the location of the database file is specified using a file system path. The implication here is that if you are publishing the site to a server, you need to know the path to the Microsoft Access database files.

Note that the path cannot be specified as an URL or a relative path (e.g., ../database.mdb). When the Data Link Properties dialog is used to create the connection string, the path to the database file is entered under the Connection tab.

In certain cases when an ASP site is published to an external server, such as a commercial ISP host, it is not always possible to know the file system path to the Microsoft Access database. In such cases, you can use of the Server.MapPath() ASP function to automatically retrieve the path to the file. In this case, the function call is embedded directly into the connection string:

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;
Data Source=" & Server.MapPath(Intranet.mdb) & ";
Persist Security Info=False

If you use the Server.MapPath() function, make sure that the Access database file resides in the same location as the Common.asp file. Also note that the Server.MapPath() function can only be used for the server-side connection, never the design-time connection.

When connecting to databases other than Microsoft Access, the process of creating the server-side connection is essentially the same, with variances based on the parameters required to create the connection string. For example, when using a Microsoft SQL Server or Oracle database, you can either use an ODBC DSN or an OLE DB Provider. The choice, of course, depends on whether you have the ODBC driver or OLE DB provider available on the machine where the pages will be published. Additionally, under certain conditions, some drivers yield better performance than others. In these cases, you would need to refer to the documentation that comes with the driver for details on its performance.

The following is a typical configuration for a Microsoft SQL server database using the Microsoft OLE DB Provider for SQL Server. When connecting to SQL Server and Oracle databases, among others, you must always specify a username and password that has access to the tables used within the project. Some user accounts have access to the database, but not access to the required tables within the database. Note that if you intend to use a MySQL database for a published ASP site, you must make the connection using an ODBC DSN created using the MyODBC driver.

Server-Side Connections for PHP

Server side connections in PHP vary depending on the database being connected to. MySQL is the most common database used with PHP and PHP has the capability of connecting directly to a MySQL database without going through an intermediate driver or provider.

The following are the settings that need to be made for the server-side connection to the MySQL database:

Parameter	Description
Use LIMIT/TOP	This option specifies if the LIMIT or TOP clause should be used to limit the size of SQL result sets.  You should select this option when you have large tables and you anticipate that your SQL queries may return a large amount of data.
PHP Database Library	Select the library for the database.
Database or ODBC connection name	Enter the name of the MySQL database.
Host	Enter the IP address or hostname of the machine where the MySQL server is located.
Port	Leave blank, unless using a port other than the default (3306).
Login	The user account to be used to access the database.
Password	The password corresponding to the Login name.
Persistent Connection	Select this option so that opened connections are reused whenever available.
Use ODBC Cursor	Select this option to use the ODBC cursor when creating the connection to the database, it maybe necessary for some ODBC drivers. For more information please see the PHP help: odbc_connect function.

Apart from MySQL, PHP can also used in conjunction with other databases such as Microsoft Access with an ODBC DSN as well as Oracle and Microsoft SQL server. When using a database such as Oracle or Microsoft SQL server, you should first ensure that the server has been compiled with support for the database or the extension for the database is enabled within the PHP.ini configuration file. If the server does not have the necessary extension, an error will be generated when you attempt to view the page. The error usually states that you attempted to use a undeclared function, which is indication that the server does not have the required extension.

The configuration for other databases in PHP is similar to the configuration for a MySQL database. However, the PHP Database Library field should be adjusted to indicate the type of the connected database. In the event that you want to use an ODBC DSN to connect to a database such as Microsoft Access, the PHP Database Library field should be set to ODBC. In this case a new field called Database appears so that you can select the type of connected database.

Server-Side Connections for PERL

Just like all other languages, the design-time connection when using PERL can be made using an OLE DB provider and driver or through an ODBC DSN. However, the server-side connection for the PERL language requires that you install additional packages that implement the database connectivity.

Depending on the PERL distribution you are using, there are different ways of installing packages. There are also a number of publicly accessible sites where the packages can be downloaded from. These include:

• http://www.indigostar.com/

  If you have the IndigoStar distribution of PERL, you can use the GUI package manager to automatically download and install packages. For the ActivateState distribution, you can run the PPM.bat file from a command prompt and use it to browse, download and install packages. For other distributions, please consult the documentation that accompanies the software.

  The packages required for PERL database connectivity are:

  • The database Interface called DBI.
  • A database driver for the specific database you are using. The name of the drivers is usually of the form DBD:database_name, where database_name is the name of the database concerned.

  For instance, if your database of choice is MySQL, you would need to have the DBI package as well as DBD:MySQL. Similarly, to connect to an ODBC data source, you would need the DBD:ODBC package in addition to the DBI package. Note that the DBI package needs to be installed only once, after which you can incrementally install the DBD packages for the databases you use.

  
• http://www.perl.com/
• http://www.activestate.com/
• http://dada.perl.it/

Within CodeCharge Studio, setting up the Server side database connection is pretty straight forward. To begin with, note that the design-time connection does not use the DBI or DBD packages but rather can be configured to connect directly to the database using OLE DB or ODBC. It is only the server-side connection that uses DBI.

The connection string used for the server-side connection is of the form: DBI:database_driver_name:database_name

For example,

• for a MySQL database: DBI:MySQL:MySQLDBName
• For an ODBC DSN: DBI:ODBC:DSNName

Server-Side Connections for ColdFusion

When using the ColdFusion application engine, and language, all server-side connections must be made through an ODBC DSN. The design-time connection used within CodeCharge Studio can be configured using an OLE DB provider or an ODBC DSN but the server-side connection must use an ODBC DSN. The Server tab of the connection properties window is used to configure the ODBC DSN connection.

Depending on the database you are using, you might also have to specify the username and password to be used to login to the database once the connection is made by the ODBC driver. This is usually not required when using a Microsoft Access database, but other databases such as Oracle and MS SQL Server require authentication details. Users of ColdFusion MX server should also register the ODBC DSN using the Data Sources option of the ColdFusion Administrator.

Server-Side Connections for JSP or Java Servlets

When publishing in JSP or Java Servlets, the server-side connection differs significantly from the other languages. You have to explicitly specify the driver to be used for the connection as well as enter a URL for the connected database.

Parameter	Description
JDBC Driver:	The name of the driver to be used, (e.g., sun.jdbc.odbc.JdbcOdbcDriver)
Database URL:	The URL pointing to the database to be connected (e.g., jdbc.odbc:intranet)

Note that you are not restricted to using JDBC drivers only. You can use any valid driver provided that it is installed and properly configured on your system. If you are not familiar with Java database drivers and URL's, you can refer to the Java documentation for more detailed information.

Note that, in addition the following optional parameters can also be used as required:

Parameter	Description
Use LIMIT/TOP	This option specifies if the LIMIT or TOP clause should be used to limit the size of SQL result sets.
User Name	The username to be used to login to the database.
User Password	The password corresponding to the Login user name.
Max Number of Connections	The maximum number of connections to be allowed to the database.
Connection Timeout	The duration of time after which an idle or unresponsive connection will be terminated.
Database properties	Some drivers allow other properties to be specified.

Also you can use the JDBC DataSource extension:

1. Set the Use Data Source Extension property of the Project to Yes.   This option is in your Project settings, under the Server/Script category of settings.
2. Based on the used application server specify the value for the JNDI Name field.
3. Optionally specify the User Name and User Password.

• System.Data.OleDb - .Net Framework Data Provider for OleDb
• System.Data.SqlClient - .Net Framework Data Provider for SQL Server
• System.Data.ODBC - .Net Framework Data Provider for ODBC
• System.Data.OracleClient - .Net Framework Data Provider for Oracle