Simple MySQL connection test from your website with ASP.NET, PHP & classic ASP. Whenever you need a MySQL connectivity test from a website or server, it’s handy to have various test scripts nearby. Whether it is because you are setting up a new website or you have just installed a new server and are running your tests.

Nowadays, many websites depend on a MySQL or MariaDB database because CMS systems like WordPress, Joomla and Drupal are so popular, and Umbraco too. You’ll want your webserver to connect to MySQL fast and properly :)

Throughout the examples, we always use “mysql.example.com” as our database hostname, “example_db” as databasename and username, “password” is our password.

Connect to a MySQL Database Using ASP.NET

MySql.Data.MySqlClient example

The following C# ASP.NET script tests MySQL database connectivity with MySql.Data (MySQL’s Connector/NET). All it does is: make a MySQL connection, execute one query and print the results on the screen.

As a bonus, it also prints out the .NET Framework version using System.Environment.Version.ToString().

First, verify all necessary assemblies are loaded in your web.config file:

This may interest you:   Get Hyper-V guest serial number with PowerShell

Configure MySql.Data in your Web.config file

This assumes you are not using a system wide Connector/NET version, but your own version in the ~/bin folder.


<configuration>
	<system.web>
		<compilation debug="false">
			<assemblies>
				<add assembly="MySql.Data, Version=6.10.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
				<add assembly="MySql.Web, Version=6.10.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
				<!--
					<add assembly="MySql.Data.Entity, Version=6.10.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
				-->
			</assemblies>
		</compilation>
	</system.web>
	<!-- [...] --->
	<runtime>
		<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
			<dependentAssembly>
				<assemblyIdentity name="MySql.Data" publicKeyToken="c5687fc88969c44d"/>
				<bindingRedirect oldVersion="1.0.0.0-6.10.6.0" newVersion="6.10.6.0"/>
			</dependentAssembly>
			<dependentAssembly>
				<assemblyIdentity name="MySql.Web" publicKeyToken="c5687fc88969c44d"/>
				<bindingRedirect oldVersion="1.0.0.0-6.10.6.0" newVersion="6.10.6.0"/>
			</dependentAssembly>
			<!--
				<dependentAssembly>
					<assemblyIdentity name="MySql.Data.Entity" publicKeyToken="c5687fc88969c44d"/>
					<bindingRedirect oldVersion="1.0.0.0-6.10.6.0" newVersion="6.10.6.0"/>
				</dependentAssembly>
			-->
		</assemblyBinding>
	</runtime>
	<!-- [...] --->
</configuration>

C# MySql.Data.MySqlClient test script

<%@ Page Language="C#" %>
<%@ Import Namespace="MySql.Data" %>
<%@ Import Namespace="MySql.Data.MySqlClient" %>

<script runat="server">
protected void Page_Load(object sender, System.EventArgs e)
{
 MySQLConn();
}
void MySQLConn()
{
 string connStr = "server=mysql.example.com;" +
   "user=example_db;database=example_db;" + 
   "port=3306;password=password;pooling=true;";
 MySqlConnection conn = new MySqlConnection(connStr);
  try
  {
   Response.Write("Connecting to MySQL database...<br/>");
   conn.Open();

   string sql = "show tables";
   MySqlCommand cmd = new MySqlCommand(sql, conn);
   MySqlDataReader rdr = cmd.ExecuteReader();

   while (rdr.Read())
   {
    Response.Write(rdr[0]+ "<br/>");
   }
   rdr.Close();
  }

  catch (Exception ex)
  {
   Response.Write(ex.ToString());
  }

 conn.Close();
 Response.Write("All done!");
 Response.Write ("<br/>.NET framework version: " + System.Environment.Version.ToString());
}
</script>

You may also be interested in my MySQL Connector/ODBC C# test script to use with MySQL Connector ODBC 8.0 Unicode Driver.

PHP MySQLi connection to MySQL

The PHP example code uses MySQLi, connects to MySQL and performs a single query when the connection is made.

<?php
/**
 * PHP/mysqli example to connect to a MySQL database
 **/
function checkMySQL() {
  $error = false;
  $connerror = false;
  @$mysqli = new mysqli("mysql.example.com", "example_db", "password", "example_db");
  if ($mysqli->connect_error) {
    $connerror .= $mysqli->connect_error . " on mysql.example.com";
  } else if ($resultaat = $mysqli->query("SHOW TABLES")) {
    $error = false;
  } else {
    $error .= @$mysqli->error . " on mysql.example.com";
  }
  @mysqli_free_result($resultaat);
  @$mysqli->close();

  if ($error !== false || $connerror !== false) {
    echo("Connection error:<br/> " . $connerror . ", query error:<br/>" . $error);
    return false;
  } else {
    return true;
  }
}

if (checkMySQL()) {
    // print PHP version as bonus
    echo( "All done! PHP version: " . phpversion() );
}
?>

ASP connection to a MySQL database – VBScript

The classic ASP (VbScript) code snippet uses an ADODB connection with the MySQL ODBC 5.1 Driver to connect to a MySQL database. Then it executes a single query when the connection is made.

<%@ Language=VBScript %>
<% 
 ''
 ' ASP example to connect to a MySQL database
 ' ADODB/MySQL ODBC
 ''
Option Explicit
Dim sConn, sSQL, oRs, x
Set sConn = Server.CreateObject("ADODB.Connection")
sConn.Open "Provider=MSDASQL;" & _
  "Driver={MySQL ODBC 5.1 Driver};" & _
  "Server=mysql.example.com;" & _
  "Database=example_db;" & _
  "UID=example_db;" & _
  "PWD=password;"

If sConn.errors.count = 0 Then
  Response.write "Connected OK"
End If

sSQL = "SHOW TABLES"

' create the recordset object
Set oRs = Server.CreateObject("ADODB.Recordset")
' Open the recordset object executing the SQL statement
oRs.Open sSQL,sConn

If oRs.EOF Then 
  Response.Write("No records found.") 
Else
  Do While Not oRs.EOF
    for each x in oRs.Fields
      Response.Write(x.value & "<br />")
    next
    oRs.MoveNext
  Loop
End If

oRs.Close
Set oRs = Nothing
sConn.Close
Set sConn = nothing
%>

Using SSL with MySQL and ASP

To connect to MySQL over SSL I use Connector/ODBC version 5.3.10, with the following connection string in ASP:

' sConn.Open "DRIVER={MySQL ODBC 5.3 ANSI Driver};" &_
sConn.Open "DRIVER={MySQL ODBC 5.3 UNICODE Driver};" &_
	"Server=mysql.example.com;" & _
	"Database=example_db;" & _
	"UID=example_db;" & _
	"PWD=password;" & _
	"SSLMODE=REQUIRED;"

See the ODBC connection parameters for more information.

This may interest you:   Clean-up WordPress spam comments and meta data

Protip: SQL Server Compact: learn how to connect to an SqlCe database with ASP.

Perl DBI connection with MySQL (DBD::mysql)

Bonus: how to test a MySQL connection with Perl DBI. The DBI is the standard database interface module for Perl.

The DBI module enables your Perl applications to access multiple database types transparently. You can connect to PostgreSQL, SQLite, MySQL, MSSQL, Oracle, Informix, Sybase, ODBC and many more without having to know the different underlying interfaces of each. The API defined by DBI will work on all these database types and many more.

#!C:/Perl/bin
# Yes, ^ Windows :-)
use strict;
use warnings;
use DBI;

my ($dbh, $sth);
$dbh = DBI->connect("DBI:mysql:host=mysql.example.com;database=example_db", "example_db", "password", {PrintError => 0, RaiseError => 1});
 
my $query = "SHOW TABLES";
$sth = $dbh->prepare("$query");
$sth->execute() or die $DBI::errstr;
$sth->finish();
$dbh->disconnect ();

print "All done!"