Wednesday, September 12, 2007

GUI Editor for MySQL 5

Most of PHP/MySQL programmers will use phpMyAdmin as an administration tools when managing MySQL database. I'm one of them but i get a difficulties when working with MySQL 5 routines (stored procedure and stored function). So, i'm looking an alternate GUI for managing MySQL database.

If you have a difficulties when create/edit/drop stored procedure when working with MySQL 5 then you can try to use GUI Tools from MySQL. You can try to use MySQL GUI Tools from

The MySQL GUI Tools Bundle for 5.0 includes the following products :
  1. MySQL Administrator 1.2
  2. MySQL Query Browser 1.2
  3. MySQL Migration Toolkit 1.1
This tools is easier to use. Below is the screen shot of MySQL Administrator when working with stored procedure

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


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!"


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 />"



End If

Set objRS = Nothing

Call CloseDBConn()

' Procedure to Open Database Connection
Sub OpenDBConn()

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!"


objConn.ConnectionTimeout = 120
objConn.Open strConn

If Len(Err.Description) <> 0 Then

strMessage = " " & Err.Description & " MySQL connection can't be established!"


strMessage = " MySQL connection succesfull established!"
blnConnStatus = True

End If

End If

End Sub

' Procedure to Close Database Connection
Sub CloseDBConn()

Set objConn = Nothing

End Sub


The stored procedure called MB_SelectInvoice() is look like here

CREATE DEFINER=`root`@`localhost` PROCEDURE `MB_SelectInvoice`()
SELECT * FROM jmnsms.t_invoice;