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.
|
<![CDATA[
<%@ 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
