For developer or programmer that worked on classic ASP, if you want to communicate with MySQL 5 then you need to install MySQL Connector/ODBC from MySQL official site. The MySQL Connector/ODBC is the name for the family of MySQL ODBC drivers (previously called MyODBC drivers) that provide access to a MySQL database using the industry standard Open Database Connectivity (ODBC) API.
You need to specify the correct DRIVER on your database connection string. The source code below is an example of how classic ASP work with MySQL 5 stored procedure.
For a note :
Default PORT for MySQL is 3306. I'm here using 3307 because i'm installed 2 versions of MySQL on my computer that MySQL 4.0.26 on port 3306 and MySQL 5.0.45 on port 3307.
<%@ Language=VBScript%>
<%
Option Explicit
Dim strConn, objConn
Dim strDBHost, strDBPort, strDBUser, strDBPassword, strDBName, blnConnStatus
Dim strSql, objRS
Dim strCustNumber, strInvNumber, strSPCode
Dim strMessage, sServer, sPort, sUser, sPassword
strDBHost = "localhost"
strDBPort = "3307"
strDBUser = "my_db_user"
strDBPassword = "my_db_password"
strDBName = "jmnsms"
strConn = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" & strDBHost & ";PORT=" & strDBPort & ";UID=" & strDBUser & ";PWD=" & strDBPassword & ";DATABASE=" & strDBName
blnConnStatus = False
Call OpenDBConn()
Response.Write "<h3>" & strMessage & "</h3>"
If blnConnStatus = False Then
Response.End()
End If
strSql = "CALL jmnsms.MB_SelectInvoice();"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSql, objConn
If objRS.EOF Then
Response.Write "Data Not Found!"
Else
Do While NOT objRS.EOF
strCustNumber = objRS.Fields("cust_number")
strInvNumber = objRS.Fields("inv_number")
strSPCode = objRS.Fields("sp_code")
Response.Write strCustNumber & " -- " & strInvNumber & " -- " & strSPCode & "<br />"
objRS.MoveNext
Loop
End If
objRS.Close
Set objRS = Nothing
Call CloseDBConn()
'
' Procedure to Open Database Connection
'
Sub OpenDBConn()
Err.Clear()
On Error Resume Next
Set objConn = Server.CreateObject("ADODB.Connection")
If Len(Err.Description) <> 0 Then
strMessage = " " & Err.Description & " MySQL connection can't be established!"
Else
objConn.ConnectionTimeout = 120
objConn.Open strConn
If Len(Err.Description) <> 0 Then
strMessage = " " & Err.Description & " MySQL connection can't be established!"
Else
strMessage = " MySQL connection succesfull established!"
blnConnStatus = True
End If
End If
End Sub
'
' Procedure to Close Database Connection
'
Sub CloseDBConn()
objConn.Close
Set objConn = Nothing
End Sub
%>The stored procedure called MB_SelectInvoice() is look like here
CREATE DEFINER=`root`@`localhost` PROCEDURE `MB_SelectInvoice`()
BEGIN
SELECT * FROM jmnsms.t_invoice;
END