|CodeCharge Studio||All ASP PHP Perl ColdFusion Java C#.NET VB.NET|
There are two types of connections you can configure to use with CodeCharge Studio: design-side and server-side. Design-side refers to connections that you use with the IDE (i.e. CodeCharge Studio) to develop your application. Server-side refers to connections that your application uses after your application is published to the server. Within your published application, code is generated by CodeCharge Studio to open and maintain the database connection, and retrieve and update data. Therefore, the connection options available to use within your application will differ depending on the target platform (server) for your application. The connection may be different from the design-side connection especially if you are publishing your web application to a remote web server. Before defining the server-side connection you should know some details about how the server is configured, such as which connectors (ODBC, OLEDB, .NET) are installed on the server, the type and location of the database, etc.
Note that the process of configuring the server-side database connection is different for different target platforms (programming languages).
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.
|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 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:
|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.
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:
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:
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.
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:
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.
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.
|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:
|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:
In the IDE, CodeCharge Studio uses non-managed (non-.Net) ODBC drivers, or OLEDB providers. These are the only type of connections you can select from when deciding what to use for a design-side connection. These may, or may not be installed on your Windows platform. For instance, if you want to use the MyODBC (MYSQL) ODBC driver, you will need to install the driver from www.mysql.com.
Your sever-side connection can use either managed, or unmanaged providers. If you select the Same as Design, CodeCharge Studio will generate the code necessary to use the non-managed connectors, but if you do not select this option, you need to use a managed provider, and you will need to ensure that the provider is installed on your target server. For example, if you choose to use an ODBC connection, you have to download and the install the Microsoft ODBC .Net Data Provider. This is not installed by default with the .Net framework SDK. If you attempt to publish a project, and 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.
The server-side connection can be configured to use several different types of .Net providers. The 'system' providers refer to connectors that are normally already installed on your server. If the connector you select doesn't work, make sure that the provider is installed on the server, and the machine.config file on the server is properly configured. You can find more details about the system providers at www.msdn.microsoft.com.
Apart from the .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 the OLE DB or ADO drivers since it taps into the 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 that you not use the OleDB provider since this may cause serious problems. Please use the ODP .Net data provider instead.
In the event that you use a native vendor provider, refer to their documentation for instructions on how to configure the connection. Note that if your CodeCharge Studio project is pre-compiled before publishing, the binaries for the provider may have to be present and properly registered in the machine where the compilation takes place (i.e. where you are running CodeCharge Studio) even though you may not be using that type of connection for designing your application.
|Use custom provider||Select this option to use a vendor-supplied provider.|
|Name||This is a description of the connection. This can be any text you want to supply.|
|Invariant||This is a unique name assigned to the provider in your machine.config or web.config file. If you know the provider is installed on the server, and you want to use the installed provider, enter the 'invariant' name as it appears in the server's machine.config file and do not enter a 'type' string. Adding a 'type' string means that CodeCharge Studio will add an entry in your web.config file to define another provider. If you provide a 'type' string, you must use an 'invariant' name that doesn't conflict with the installed provider.|
|Type||Do not define this if you know the provider is installed on the server, and you want to use that provider. If you know the provider is NOT installed on the server, enter a unique string for the 'invariant' name (this can be anything but it must be unique for all the providers on the server), and define a 'type' string. You can find an example of a 'type' string in your machine.config file. A 'type' string is similar to an assembly name. It uniquely identifies the assembly for the provider you want to use. When you provide a 'type' string, CodeCharge Studio adds a DbProviderFactories entry to your web.config file, therefore it is important that you: 1) use a unique 'invariant' name so it doesn't conflict with the 'invariant' names of other providers, 2) specify a correct 'type' string, 3) make sure the assemblies for the provider are published with your application (e.g. placing them in your /bin folder). Refer to your vendor documentation and www.msdn.microsoft.com for more details.|