Databases FAQ's

 

  • How do I connect to a Access database using ASP?
  • How do I connect to a MySQL database using PHP?
  • Do you support MS-SQL Server?
  • Where do I put my Access Database?




    • How do I connect to a Access database using ASP?

    All ASP scripts are written in VBS

    The current version of ADO installed on our servers is MDAC 2.7. Detailed component information can be obtained from Microsoft or one of the many scripting sites on the Web. Search on google for "recordset.movenext" or something similar to find them.

    Before looking at the code, the following points should be considered when using Access databases in a shared hositng environment:

  • All scripts run on a shared server, and thus what happens on one site has an effect on the stability of certain components on other sites on the same server. Access ADO is such a component. It is a shared resource, and should be programmed correctly.
  • The Jet access engine has a limited set of resources. Once these are exhausted, the script will generate errors. These are normally temporary in nature and can occur when server loading is very heavy. These are operating system limitations, and cannot be extended or isolated by us.
  • You should use only Access 2000 or higher databases. If you use Access 97 you may receive the error:
    [Microsoft][ODBC Microsoft Access Driver] Cannot open database '(unknown)'. It may not be a database that your application recognizes, or the file may be corrupt.
    This is due to an issue in MDAC 2.5 and greater and is only resolved by using a newer version of Access.
  • Use as few connections as possible. If you are using session and application based objects, use a single application based connection. Otherwise, use one per script, explicitly opening the connection at the start of the script, and then closing it at the end of the script.

    • Using ADO in ASP to connect to an Access Database on Your Site

    Now to the code. First intialize the variables. This isn't strictly necessary, but it's good programming practice to do so.

    dim conn, strsql, rsuser, strMDBPath
    set conn=server.createobject("ADODB.Connection")
    set rsuser=server.createobject("ADODB.Recordset")

    Next set up the database connection. Always explicitly open the connection. Do not use a connection string with objects such as a command or a recordset object. These implicitly open a database connection which they then leave open to time out after the script closes. This connection accesses the Northwinds database which is stored in the Private directory below the web site root folder HTDOCS. Note the use of the Server.MapPath method to derive the correct path on the server.

    strMDBpath = Server.MapPath("..\private\northwind.mdb")
    conn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;
    DATA SOURCE=" & strMDBPath

    Now open the resordset using the current connection. In this case we are querying the customer table.

    strsql = "select top 10 (city) from customers"
    rsuser.open strsql,conn,1,2

    With the recordset now open, we iterate through it and output the results to the page. Note that we don't need to use the movefirst method as the recordset points to the first record by default when opened. With an empty recordset, the loop exits immediately without errors.

    do while not rsuser.eof
    response.write rsuser("City") & "<br>"
    rsuser.movenext
    loop

    Finally we close the objects used.

    rsuser.close
    conn.close
    set ruser = nothing
    set conn = nothing

    Go Top

    How do I connect to a MySQL database using PHP?

    Once your MySQL database is installed on your account and your scripts are working satisfactorily you can connect to your MySQL database from your website.

    Example using PHP
    The following is an example using PHP to open a connection to the MySQL database, return an array from the database, and then close the connection:

    <?php
    mysql_connect("server_IP_address", "mysql_user", "mysql_password") or
    die("could not connect");
    mysql_select_db("mydb");


    $result = mysql_query("SELECT id, name FROM mytable");


    while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    printf ("ID: %s Name: %s", $row[0], $row[1]);
    }


    mysql_free_result($result);
    mysql_close;
    ?>

    NOTE: You need to replace "server_IP_address", "mysql_user" and "mysql_password" in the above example, with the relevant details that were emailed to you, e.g. with the "Server IP Address", "Database Username" and "Database Password"

    Other resources

    For information on connecting via PERL DBI, click here
    For information on connecting via PHP, click here


    Go Top

    Do you support MS-SQL Server?

    Upon special request we can support Microsoft SQL server aswell. Please email us your requirements and we will be glad to discuss your data needs further.


    Go Top

    Where do I put my Access Database?

    The private folder accessible from your FTP client would suit best for access, paradox and other databases. This is due to the inaccessibility of this folder from web browsers and thus protecting you database from unauthorised download.

    To refer to the database from within your scripts use

    "e:\domains\yourdomainname.com\user\private\yourdatabase.mdb"
    and replace yourdomainname.com with the domain you registered and yourdatabase.mdb with the name of the database you placed into your private directory

     


    Go Top