DSN and DSN-less connections

DSN stands for 'Data Source Name'. It is an easy way to assign useful and easily rememberable names to data sources which may not be limited to databases alone. If you do not know how to set up a system DSN read our tutorial How to set up a system DSN.

For our example below lets assume that our DSN points to an Access database called 'examples.mdb' and that we will be selecting records from the table 'cars'.

<?php
//connect to a DSN "myDSN"
$conn = odbc_connect('myDSN','','');

if ($conn)
{   //the SQL statement that will query the database
$query = "select * from cars";   //perform the query
$result=odbc_exec($conn, $query);

echo "<table border=\"1\"><tr>";
//print field name
$colName = odbc_num_fields($result);
for ($j=1; $j<= $colName; $j++)
{
    echo "<th>";
    echo odbc_field_name ($result, $j );
   echo "</th>";
}
//fetch tha data from the database
while(odbc_fetch_row($result))
{
    echo "<tr>";
    for($i=1;$i<=odbc_num_fields($result);$i++)
    {
      echo "<td>";
      echo odbc_result($result,$i);
      echo "</td>";
    }
    echo "</tr>";
}

echo "</td> </tr>";
echo "</table >";
//close the connection
odbc_close ($conn);
}
else echo "odbc not connected";
?>

Remember that 'myDSN' above is name of the DSN. Also note that you can change the table name from 'cars' to the name of your table and point the DSN to whatever database you like. One other thing to remember is that you can set up a DSN on your own machine though if you are using a hosting company you may have to ask the webmaster.

DSN-less connection

DSN-less connections don't require creation of system level DSNs for connecting to databases and provide an alternative to DSNs. We will now see how to connect to a database via PHP using Connection String in place of DSN name.

<?php

//create an instance of the  ADO connection object$conn = new COM ("ADODB.Connection")

  or die("Cannot start ADO");

//define connection string, specify database driver$connStr = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source= c:\inetpub\wwwroot\db\examples.mdb";

  $conn->open($connStr); //Open the connection to the database

//declare the SQL statement that will query the database

$query = "SELECT * FROM cars";

//execute the SQL statement and return records

$rs = $conn->execute($query);

$num_columns = $rs->Fields->Count();

echo $num_columns . "<br>"; 

for ($i=0; $i < $num_columns; $i++) {

    $fld[$i] = $rs->Fields($i);

}

echo "<table>";
while (!$rs->EOF)  //carry on looping through while there are records

{

    echo "<tr>";

    for ($i=0; $i < $num_columns; $i++) {

        echo "<td>" . $fld[$i]->value . "</td>";

    }

    echo "</tr>";

    $rs->MoveNext(); //move on to the next record

}

echo "</table>";

//close the connection and recordset objects freeing up resources

$rs->Close();

$conn->Close();

$rs = null;

$conn = null;

?>

DSN and DSN-less connections

0 comments:

Labels

Blog Archive