ODBC Configuration for PHP3 / Windows NT

Summary

Summary
Setting an ODBC connection
1. Connection configuration
Creating a test Database
Writing a test script
Test it
Common problems and solutions
ODBC Quick overview
Sources

Setting an ODBC connection

        Open the ODBC tool in WinNT Control Panel . the icon looks like this : <img src="hlp_odbc_icon.gif" align=center> 
        Choose the "System DSN" panel.
        Click on the Add... button
        Choose the ODBC Driver you want to use. For example, if you want to connect on a MS-SQL Server base, choose "SQL Server"
        Click on "Finish"

Connection configuration

You should get a popup window like Fig 2.

Fig 2 : ODBC configuration window

Short explain about the fields:

Data Source Name (DSN) : this is the name to your ODBC connection. In our example, we'll use WebDB Note : this is the ``dsn_name'' param given in odbc_connect()

Description : a comment about this connection

Server : indicate the IP address of the machine where the database server is. Use (local) if it stands on the same machine.

Click on Advanced button to enlarge the window.

Database name : The name of your base. In our example, we'll also use WebDB

Creating a test Database

On your Database Server, do the following:

Create a user

Create a database named "WebDB"

In the Users/Groups section, make sure that the user webuser has an access to our DB ``WebDB''.

Create a sample table

For this, use your DB Server Query Tool.

CREATE TABLE T_FrenchTown ( id char (2) NOT NULL , nom varchar (100) NOT NULL PRIMARY KEY(id) )

Populate this table

For this, use your DB Server Query Tool.

insert into T_FrenchTown(id,nom) values( 'PA', 'Paris' ) insert into T_FrenchTown(id,nom) values( 'LY', 'Lyon' ) insert into T_FrenchTown(id,nom) values( 'MA', 'Marseille' ) insert into T_FrenchTown(id,nom) values( 'LI', 'Lille' ) insert into T_FrenchTown(id,nom) values( 'BO', 'Bordeaux' ) insert into T_FrenchTown(id,nom) values( 'NA', 'Nantes' ) insert into T_FrenchTown(id,nom) values( 'RO', 'Rouen' ) insert into T_FrenchTown(id,nom) values( 'CL', 'Clermont-Ferrand' )

Grant the access on our table to our user webuser

grant select,insert on T_FrenchTown to webuser

Writing a test script

Here is a php3 script to achieve this test. Save it for example in ``Apache\htdocs\test_odbc.php3''.

If you want to avoid permissions problems (common), you may use a Super-Admin access; have a look in the script.

<? //__________________________________________________________ // // test_odbc.php3 // A sample PHP script to test ODBC under PHP / WinNT // Leo West - 08/1998 //__________________________________________________________ function Error_Handler( $msg, $cnx ) { echo "$msg \n"; // in case of persistent connexion, it is important to close it before exiting. odbc_close( $cnx); exit(); } // create an ODBC connection, returned in $cnx $cnx = odbc_connect( 'WebDB' , 'webuser', 'webpassword' ); // To avoid permission troubles in the test, you may want to use a superadmin access : // $cnx = odbc_connect( 'WebDB' , [sa login] , [sa password] ); if( ! $cnx ) { Error_handler( "Error in odbc_connect" , $cnx ); } // send a simple odbc query . returns an odbc cursor $cur= odbc_exec( $cnx, "select id,nom from T_FrenchTown" ); if( ! $cur ) { Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx ); } echo "<table border=1><tr><th>id</th><th>nom</th></tr>\n"; $nbrow=0; // fetch the succesive result rows while( odbc_fetch_row( $cur ) ) { $nbrow++; // get the field "id" $id= odbc_result( $cur, 1 ); // get the field "nom" $nom= odbc_result( $cur, 2 ); echo "<tr><td>$id</td><td>$nom</td></tr>\n"; } echo "<tr><td colspan=2>$nbrow entries </td></tr></table>"; // close the connection. important if persistent connection are "On" odbc_close( $cnx); ?>

Test it

From a web browser, try the script.

You should get a list of some french towns like this :

id	nom
BO	Bordeaux
CL	Clermont-Ferrand
LI	Lille
LY	Lyon
MA	Marseille
NA	Nantes
PA	Paris
7 entries

Common problems and solutions

 [ under construction, send your problems ]

Can not query the database

ODBC Driver: Access User account does not have read permissions on the database file or write (or add) permissions on the directory where the script is run from.

Can not query the database

        ODBC Driver: Any
        In the System DSN, you may have selected a SecurityDatabase.
        =>You need to press the Advanced button and configure a username (UID) and a password (PWD) that are both valid and in the Security     Database that you selected.

Can access the DSN but not the database

        ODBC Driver: MS SQL Server
        The user account does not have permissions to the database. 
        => Have the Admin grant the proper permissions to the user account needing it.

Some fields are returned with wrong characters

        ODBC Driver: MS SQL Server
        This driver converts OEM character set to ANSI character set. 
        => Unselect this option from the DSN's configuration window.

ODBC Quick overview

Fig 3 : PHP3 and ODBC

ODBC ( abbreviation of Open DataBase Connectivity) is a standard to access data sources. it defines a set of functions that must be implemented and identicals in ODBC driver (theory...)

Data sources can be a database, but also a text file, an Excel sheet...

The application must be able to send ODBC commands. In PHP3, the unified ODBC library provides a set of functions that are translated into ODBC commands.

Note: In the Win32 version, this library is by default compiled in php.exe

To access a database, the Database maker (or a third party) must provides an ODBC driver.

The benefit is that an application can access any data source with the same commands ( well, this is the theory...)

The DSN is the ODBC identifier. It is used to identify an ODBC Connection from another.

Most ODBC drivers also defines a username and password for security reasons. Current ODBC version is 3.0.

Sources

Common problems

[Win32 Odbc FAQ] http://www.roth.net/odbc/odbcfaq.htm

This document written by Leo West - September 1998

Questions @ mailto:lwest@imaginet.fr