Database Connectivity

Team-Fly    

Macromedia® DreamWeaver® MX Unleashed
By Matthew Pizzi, Zak Ruvalcaba
Table of Contents
Chapter 17.  Web Application Preparation


Database connectivity varies depending on the middleware you are using and the type of database you're trying to access.

For example, if you are using PHP/mySQL, you can generally place a command in your PHP files to access or point to a MySQL server and let it go at that. Database connectivity is fast, simple, and repeatable.

In PHP, database connectivity might look like this:

 if (!($mylink = mysql_connect( "localhost", "root", "")))  {     print "<h3>could not connect to database</h3>\n";     exit; } mysql_select_db( "test"); 

The first part is an if clause that tests to see if the mysql_connect command can actually reach the database server. If it can't, PHP will exit gracefully with a message. Otherwise, PHP executes the mysql_select_db command, which, as the name of the command implies, selects a database for access.

In PHP, putting this command in a header.php file and then including this file on all other pages means that every part of your application has instant database access.

The same can be said for JSP and ASP implementations. Each of these languages has similar approaches to connecting to a data source.

However, if you are trying to access an ODBC data source, such as a SQL Server database, an Excel spreadsheet, or a Microsoft Access database, you need to set up a Data Source Name (DSN) on your Windows machine.

A DSN can allow faster access to a data source, and depending on the type of DSN you set up User, System, or File you can benefit from security settings.

Setting Up a DSN

The following example shows you how to set up a DSN that points to a Microsoft Access database. The example then shows you how to create a simple ASP file that queries this database.

Step 1: Set Up a Test Database

The first step is setting up a simple Microsoft Access database. Open Microsoft Access and create a table. This table should have two fields:

  • name

  • birthday

Populate this table with some names and birthdays of your friends or relatives it doesn't matter what the table holds, we're just using it to make sure we can see the database.

Name the table mytable. When you're done, save the database as dsntest.mdb.

Step 2: Create the DSN

Now that you've created the database, you're ready to set up a DSN that points to the database. Follow these instructions to set up a DSN:

  1. Click the Start Button and go to the Control Panel.

  2. Click Administrative Tools and then select Data Sources (ODBC).

  3. Click the System DSN tab.

    Figure 17.6. The System DSN tab of Data Sources.

    graphics/17fig06.jpg

  4. Click Add.

  5. Select the Microsoft Access database drive and click Finish.

    Figure 17.7. Select Microsoft Access from the list of available drivers.

    graphics/17fig07.jpg

  6. Provide your new DSN with a name. Generally speaking, it's a good idea to give your DSN a name that is descriptive. Call your new DSN testaccessdb.

    Figure 17.8. Choose a name for your DSN.

    graphics/17fig08.jpg

  7. Under Database, click Select and browse to find the dsntest.mdb database file. Click OK when you're finished.

  8. Click Advanced to set a username and password for the dsntest.mdb database. Set both of these to test for this example. Click OK when you're finished.

  9. Click OK to close the ODBC Microsoft Access DSN Setup.

  10. You should now see testaccessdb listed as a Microsoft Access Driver on the System DSN list. Click OK to close the ODBC System Administrator dialog box.

Step 3: Create the ASP File

Now that you have a database and a DSN pointing at it, you can create the ASP file to access the database through a Web browser. To create the ASP file, follow these steps:

  1. Open Dreamweaver MX.

  2. Create a new file and open the code view. Then type the following ASP code:

     <%  dsn = "testaccessdb" dbuser = "test" dbpass = "test" Set Conn = Server.CreateObject("ADODB.Connection") Set rs = Server.CreateObject("ADODB.RecordSet") Conn.Open dsn, dbuser, dbpass 

    First you set up some variables. The dsn variable holds the name of the DSN you set up in step 2. The dbuser and dbpass variables hold the username and password you set up.

    After that, the code sets up a connection object and a recordset object. The first handles the connection to the database, and the second will handle any and all information we request from that database.

    Finally, the code opens a connection to the DSN by accepting parameters for the DSN, the username, and the password.

  3. Enter the following ASP code into Dreamweaver:

     sql="select * from mytable"  Response.Write "<B>SQL STATEMENT: </B>" & sql & "<HR>" RS.Open sql, Conn %> 

    After you've set up the connection, build a sql variable to hold your SQL statement. Then pass this SQL statement to the recordset object you've created.

  4. Enter the following ASP code into Dreamweaver:

     <table border=1>  <tr valign=bottom> <% For i = 0 to RS.Fields.Count - 1 %> <td><b><% = RS(i).Name %></b></td> <% Next %> </tr> 

    This code pulls out the names of the fields in your database (name and birthday, in this case) and places them in table cells. The For loop captures all the field names efficiently, so this code works whether you have 1 field or 500 or make lots of changes to your database table.

  5. Enter the following ASP code into Dreamweaver:

     <%  Do While Not RS.EOF %> <tr valign=top> <% For i = 0 to RS.Fields.Count - 1 %> <td><% = RS(i) %></td> <% Next %> </tr> 

    This code presents a While loop that continues until the end of the recordset retrieved by the SQL query. It pulls out, using a For loop, each row in your database (in this case, the individual names and birthdays you stored in the database table).

  6. Enter the following ASP code into Dreamweaver:

     <%  RS.MoveNext Loop RS.Close Conn.Close %> </table> 

    This code tells ASP to continue looping through the recordset object until all information gathered by the SQL query has been displayed. Then the ASP code closes the recordset and connection objects, saving memory.

    NOTE

    You'll notice from this ASP example that some languages are a lot more verbose than others. For example, to do the preceding in PHP would have required about one-third the amount of code. Does this mean that PHP is inherently better than ASP? No, not necessarily, although some language purists might use this kind of comparison to state their case for a favorite development language. You have to remember that a language is no better or worse than any other. The only way a language should be judged is if you can learn it quickly and if it allows you to do the things you need to do.

  7. Save the file as test.asp and place it in your Web server's docroot folder.

  8. You can test this file by pointing your browser at the file. The file should display the contents of your dsntest.mdb table.


    Team-Fly    
    Top


    Macromedia Dreamweaver MX Unleashed
    Macromedia Dreamweaver MX 2004 Unleashed
    ISBN: 0672326310
    EAN: 2147483647
    Year: 2002
    Pages: 321

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net