Glover logo

On Line Databases

 Index

Home Glovers Contact Form

Pegasus Mail

Pegasus Mail Pmail Features

Pmail Extensions

Populist Delphi (1) Delphi (2) Dos Email

Digital Cameras

Digital images Renumber images Slideshows

Databases

Online Databases Access & ASP Access & PHP Mysql & ASP Mysql & PHP

Connection to MySQL databases - ASP scripting

In this example ASP has to reach a MySQL database through a ODBC connection. Again, as in the other examples, the connection is "DSN-less". The MySQL web site provides a suitable ODBC package enabling MS Windows applications such as the MS Access desktop program or Sql-Server. It is also possible to script queries in ASP via ODBC, although MySQL which has its origins in Unix/Linux (which do not support ASP), doesn't provide a direct path to the data through ASP. For the purposes of this illustrative example, ODBC was used to create a MySQL version of the original MS Access "authors" database. Thus, the ASP script described here operates on this MySQL version.
The connection requires two statements, the first:-
strConn = "Driver={MySQL ODBC 3.51 Driver};" & "Server=127.0.0.1;Database=authors;UID=root;PWD=;OPTION=35"
specifies the ODBC driver and its parameters, Server (localhost or '127.0.0.1' here), Database name, MySQL username (='root' in this example), Password and OPTION (35 in this case). The 2nd string is:-
Set oConn = Server.CreateObject("ADODB.Connection")
which is the same as in the other ASP scripting example. Thereafter, the remainder of the script is the same as the other ASP example. The full script is shown below.

<%@ LANGUAGE = VBScript %> <% Option Explicit %> <HTML> <HEAD><TITLE>Simple ADO Query</TITLE></HEAD> <BODY BGCOLOR="White" topmargin="10" leftmargin="10"> <!-- Display Header --> <font size="4" face="Arial, Helvetica"> <b>Simple MySQL Query with ASP</b></font><br /> <hr size="1" color="#000000" /> Contacts within the Authors Database:<br><br> <% Dim oConn ,strConn Dim oRs Dim filePath Dim Index ' Map authors database to physical path ' filePath = Server.MapPath("./../../incl/auth.mdb") ' Create ADO Connection Component to connect ' with sample database ' % > ' filepath = <% = filePath % > ' < % strConn = "Driver={MySQL ODBC 3.51 Driver};" strConn = strConn & "Server=127.0.0.1;Database=authors; UID=root;PWD=;OPTION=35" ' "MySQL ODBC 3.51 Driver" is the name of the MySql ODBC Driver ' The Server host is 'localhost' i.e, with an IP address ' of: 127.0.0.1 ' "U3a" is the name of the MysSql database which contains ' the "authors" table (see the SELECT SQL statement below) ' oConn is a pointer and acts as a sort of file handle to the ' connection to the MySQL database via the ODBC machinery. set oConn = Server.CreateObject("ADODB.Connection") oConn.Open strConn ' Execute a SQL query and store the results ' within recordset oRs. It's important to note that, ' from now-on you can use the VBscript commands familiar to all ' MS Access programmers. You DON'T have to learn MySQL's ' language. The ODBC machinery in effect translates ' not only the MySQL database's data contents, but also ' wraps up MySQL's data manipulation statements, allowing ' you to use good old VBScript... Set oRs = oConn.Execute("SELECT * From authors") %> <TABLE border = 1><tr bgcolor = '#DDEEAA'><th>ID</th><th>Author</th> <th>Year Born</th></tr> <% Do while (Not oRs.eof) %> <tr bgcolor = '#CCCCFF'> <% For Index=0 to (oRs.fields.count-1) %> <TD VAlign=top><% = oRs(Index)%></TD> <% Next %> </tr> <% oRs.MoveNext Loop %> </TABLE> <% oRs.close oConn.close %> </BODY></HTML>

   Demo: Example of above code