How to Get Result Set from MySQL 5 Stored Procedure using Classic ASP

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