Configuring the Server-Side Database Connection

The server connection is the database connection used by the generated pages to retrieve and update data. This connection can be different from the design connection used by the CodeCharge Studio, especially if you are publishing your web application to a remote web server.

Note that the process of configuring the server-side database connection is different for different programming languages.

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.

Parameter Description

Date Format This property specifies the format of the date as it appears in the database.

Boolean Format This property specifies the format of the boolean as it appears in the database.

Use LIMIT/TOP This option specifies if the LIMIT or TOP clause should be used to limit the size of SQL result sets.

Same as Design Use the same Connection Settings specified in the Design Tab.

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.

Parameter	Description
Date Format	This property specifies the format of the date as it appears in the database.
Boolean Format	This property specifies the format of the boolean as it appears in the database.
Use LIMIT/TOP	This option specifies if the LIMIT or TOP clause should be used to limit the size of SQL result sets.
Same as Design	Use the same Connection Settings specified in the Design Tab.
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 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 a 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 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.

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.

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.
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 is 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/packages/perl5_6/
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/CPAN-local/README.html
http://www.activestate.com//PPMPackages/5.6
http://dada.perl.it/PPM

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 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)

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 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.

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:

Set the Use Data Source Extension property of the Project to Yes.
Based on the used application server specify the value for the JNDI Name field.
Optionally specify the User Name and User Password.

Server-Side Connections for .Net

When publishing .Net code in C# or VB.Net, you can use the DSN and DSN-less connections similar to those used when publishing in ASP. Note that if you choose to use an ODBC connection, you have to download the install the Microsoft ODBC .Net Data Provider. This is not installed by the default .Net framework SDK. If you attempt to publish a project using a framework when the ODBC .Net data provider is not installed, you will receive a compilation error stating that Microsoft.Data.Odbc.dll could not be found. This is one of the files that comes with the Microsoft ODBC .Net data provider.

Apart from the ODBC .Net data providers, other database vendors may introduce other drivers beyond those that come with the .Net framework SDK. For instance, Oracle introduced the ODP .Net data provider that provides native support for connecting to Oracle databases. The ODP .Net provider offers better performance than other OLE DB or ADO drivers since it taps into native Oracle API without using an automation layer such as that used by OLE DB or ADO.

Note: While working with Oracle it is strongly recommended not to use the OleDB provider which may cause serious problems. Please use the ODP .Net data provider instead.

In the event that you use one of these native vendor provided drivers, refer to their documentation for instructions on how to configure the connection. Remember that when the project is published and compiled, the binaries for the driver have to be present and properly registered in the machine where the compilation takes place.