MySQL Database Connection

Products:   
Areas: ASP, ASP.NET(C#), ColdFusion, Java Servlets, JSP, Perl, Perl 

When using a MySQL database, there are a number of important distinctions between how the Design time and Server side connections are made.

The Design Time Connection
A design time connection to a MySQL database must be made by using an ODBC DSN created using the MyODBC driver. This is because It is not possible to connect directly to MySQL at design time as can be done with an Access database using OLE DB. As such, if you intend to connect to MySQL at design time, you need to first download and install the MyODBC driver which can be obtained from http://www.mysql.com/downloads/api-myodbc.html.

After installing the MySQL driver, proceed to setup a DSN (Data Source Name) to point to the MySQL database. The screenshot below shows the window used to setup the DSN.

The fields that need to be configured are:

  • Windows DSN Name: Specify a name of your choice to be used as the Data Source Name
  • MySQL host: This is the IP address of the machine where the MySQL server is located. For a local database, you can enter localhost
  • MySQL database Name: The name of the database within the MySQL server
  • User: The user account to be used to access the database
  • Password: The password corresponding to the User name.
  • Port: By default, MySQL listens to port 3306. If a different port number is used, enter it here.

The rest of the fields in the DSN configuration windows can be left blank unless you have a specific reason for configuring them. Once the DSN has been setup using the MyODBC driver, you can now use it for the design time connection within CodeCharge Studio.

  • Connection Name: Specify a name of your choice to identify the connection within CodeCharge Studio.
  • Database: Select MySQL
  • Use ODBC Data Source Name: With the radiobox checked, proceed to select the DSN you previously created using the MyODBC driver
  • User Name: The user account to be used to access the database
  • Password: The password corresponding to the User name.

The Server Connection
Once you have setup the design time connection, you can proceed to create forms and fields based on the database tables. If you wish to, you can still use the ODBC DSN for the Server side connection but the norm is to connect directly to the MySQL database. MySQL is usually used with the PHP language which has the capability to connect directly to the MySQL database without using an ODBC DSN. The following are the settings that need to be made for the Server side connection to the MySQL database:

  • PHP Database Library: Select MySQL
  • 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

Note that the above settings apply when using the PHP language. In the rare event that the MySQL database is used with another language such as ASP, C#, VB.Net or Cold Fusion, the Server side connection would have to be made using the ODBC DSN. For Perl, a DBI driver has to be used and for Java or JSP, you can use the ODBC DSN via the jdbc.odbc driver or using a driver for MySQL if one is available.



Viewed 98142 times.   Last updated: 12/30/2002 6:10:12 PM