Using ODBC with PHP

Using ODBC with PHP
Submitted by johnlim on Fri, 18/06/2004 - 5:21pm.
Using ODBC with PHP ODBC is one of Microsoft's earliest technologies for connecting to databases. It is very popular and widely used. This article describes how to setup and use ODBC from a PHP perspective.

Also see DSN-less connections with PHP.

What is ODBC?
Open DataBase Connectivity is an Application Programming Interface (API) that allows a programmer to abstract a program from a database. When writing code to interact with a database, you have to add code that talks to a particular database using a proprietary API. If you want your program to talk to an Access, FoxPro and Oracle databases you have to code your program with three different database API's. This can be quite the daunting task causing much grief. Now, enter ODBC...

When programming to interact with ODBC you only need to use the ODBC API (a combination of ODBC extension function calls and the SQL language) to talk to different database products. The ODBC Manager will figure out how to contend with the type of database you are targeting. Regardless of the database type you are using, all of your calls will be to the ODBC API. All that you need to do is have installed an ODBC driver that is specific to the type of database you will be using.

Creating A ODBC Data Source Name (DSN)
After you have ensured that the ODBC driver for your database is installed, you normally need to create a Data Source Name (DSN), which contains all the connection details required to login (accept maybe id and password).

Walkthrough: How to Setup a DSN. A pictorial step-by-step guide. The next button is hidden on the bottom-right. Sorry but we Windows programmers are not user interface experts ;-)

Connecting to ODBC
There is an excellent tutorial on using PHP's ODBC extension at ASPToday, a popular ASP web site.

An example taken from the above article:

<?
# connect to a DSN "mydb" with a user and password "marin"
$connect = odbc_connect("mydb", "marin", "marin");

# query the users table for name and surname $query = "SELECT name, surname FROM users";

# perform the query $result = odbc_exec($connect, $query);

# fetch the data from the database while(odbc_fetch_row($result)){ $name = odbc_result($result, 1); $surname = odbc_result($result, 2); print("$name $surname\n"); }

# close the connection odbc_close($connect); ?>

More ODBC Links:
tri: Zend's PHP & ODBC Tutorial
tri: ODBC Reference from PHP manual
tri: Using ODBC on Linux to connect to MSSQL7
tri: Unix PHP-ODBC Howto
tri: A commercial ODBC for Unix from OpenLink. Hint: They call their product Universal Data Access Driver Suites.
tri: OpenLink has released their ODBC Driver Manager as Open Source: iODBC
tri: ODBC Socket Server for accessing Access from Unix. Requires 2 machines.

ODBC Bugs
Avoid using odbc_prepare and odbc_execute currently (Sept 2000). I have been testing and there are bugs in the two functions. In fact in the PHP ODBC test suite, odbc_prepare and odbc_execute are not even tested :-(

Different Database SQL Dialects cause Problems

After using ODBC for some time with different databases, you will find many differences in the SQL used by different ODBC drivers.

String Conatenation: Interbase and Oracle uses ||. Sybase, VFP, Access, MSSQL7 use +. MySQL uses CONCAT( ).

Dates: MySQL and MSSQL7: Y-m-d, VFP: {^Y-m-d}, Access #Y-m-d#.

True and false MSSQL7: 1 and 0, VFP: .T. and .F.

This is why the ADODB database wrapper library was created so we can port code used in one database product to another quickly. For an example of ADODB use:

Example 1 from the ADODB class library.

Creating DSN-less connections

DSN's have to be created from the Control Panel. Sometimes, you don't have access to the Control Panel on the webserver. You need to use a DSN-less connection instead.

DSN-less Connections with ODBC

ODBC since PHP 4.3.0 supports DSN-less connections. You can adapt these samples which require the ADOdb library (which you can download from sourceforge).

For Microsoft Access:

$db = ADONewConnection('access');
$dsn =
"Driver={Microsoft Access Driver (*.mdb)};Dbq=d:\inetpub\adodb\northwind.mdb;Uid=Admin;Pwd=;";
$db->Connect($dsn);

For Microsoft SQL Server:

$db = ADONewConnection('odbc_mssql');
$dsn = "Driver={SQL Server};Server=localhost;Database=northwind;";
$db->Connect($dsn,'userid','password');

DSN-less Connections with ADO

If you are using versions of PHP earlier than PHP 4.3.0, DSN-less connections only work with Microsoft's ADO, which is Microsoft's COM based API.

An example using the ADOdb library and Microsoft's ADO:

<?php
include('adodb.inc.php');
ADOLoadCode("ado_mssql");
$db = &ADONewConnection("ado_mssql");
print "<h1>Connecting DSN-less $db->databaseType...</h1>";

$myDSN="PROVIDER=MSDASQL;DRIVER={SQL Server};"
. "SERVER=flipper;DATABASE=ai;UID=sa;PWD=;" ;
$db->Connect($myDSN);

$rs = $db->Execute("select * from table"); $arr = $rs->GetArray(); print_r($arr); ?>