Guides

ODBC connection to a database

Examples in ASP e PHP code

ODBC Open DataBase Connectivity) is a standard application programming interface for accessing database management systems, developed by the SQL Group in 1992.
The purpose of ODBC is to make the database access protocol independent of database systems and operating systems.

Hosting Solutions provides the opportunity to use ODBC for some of the Windows plans on our list. To check if a Windows plan supports ODBC, please check the service features on our website.

ATTENTION

  • When connecting via ODBC to an MS Access database, the database and the folder where it is located must have write permissions even if the database is read-only. In fact, the Microsoft libraries that manage access to the database create temporary files (*.ldb) at the same level as the database (*.mdb) and, if no write permissions are available, the IIS web server will return an error 500
  • We also recommend ​not placing the database in the site's main folder (root) because, as mentioned in the previous point, this would force the authorising of write permissions on each element of the website, which would be extremely risky.
  • Once a DSN (Data Source Name, a DSN contains all the data required to connect to a database) has been created, the code below can be used to connect to MS Access, MS SQL Server and MySQL databases.

The following are examples of ODBC connections using the ASP and PHP languages.

ODBC with ASP

In the example, the Data Source Name (DSN) is test_Cfvvgd.

If you have an MS Access database that is not protected by credentials, you can simply open the connection with the instruction Conn.Open "test_Cfvvgd"

<%
Set Conn=Server.CreateObject("ADODB.Connection")
Conn.Open "test_Cfvvgd", "nomeutente", "password"
Set Rs=Server.CreateObject("ADODB.Recordset")
Rs.Open "SELECT * FROM Tabella",Conn
...
%>

ODBC whit PHP

PHP also provides the programmer with a library for accessing a database via ODBC. More information can be found at php.net

In the example the Data Source Name (DSN) is test_Cfvvgd.

If you have an MS Access database that is not protected by credentials, enter the empty string "" in place of the database protection credentials.

<?php
$dsn = 'test_Cfvvgd';
$connessione = odbc_connect("test_Cfvvgd", "nomeutente","password");
$interrogazione = "select * from categorie";
$html_risultato ='';
if ($risultato = odbc_exec($connessione, $interrogazione)) {
     do {
        $html_risultato .= '<tr>';
        $html_risultato .= '<td> '.odbc_result($risultato, "idcategoria").'</td>';
        $html_risultato .= '<td> '.odbc_result($risultato, "nomecategoria").'</td>';
        $html_risultato .= '</tr>';
    } while (odbc_fetch_row($risultato));
}
odbc_close($connessione);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<HTML>
<head>
<title> Test Connessione ODBC </title>
<meta name="Generator" content="EditPlus">
<meta name="Author" content="">
<meta name="Keywords" content="">
<meta name="Description" content="">
</head>
<body>
<table border="1">
    <tr>
        <th colspan="3">
            <h3>Tabella Risultati Query: <?php echo $interrogazione; ?></h3>
        </th>
    </tr>
    <tr>
        <th>ID categoria</th>
        <th>Nome categoria</th>
    </tr>
    <?php echo $html_risultato; ?>
</table>
</body>
</html>

It is important to highlight the fact that the positioning of the php code, in particular the opening of the connection, the access to the data and the closing of the connection, must be done before the tag .
In this way, the server will complete the data access before sending the page to the client (usually the browser), making navigation more fluid.