downloads | documentation | faq | getting help | mailing lists | reporting bugs | sites | links | my 
search for in the  
view the version of this page
Last updated: Thu, 21 Aug 2003

LXX. Unified ODBC functions


In addition to normal ODBC support, the Unified ODBC functions in PHP allow you to access several databases that have borrowed the semantics of the ODBC API to implement their own API. Instead of maintaining multiple database drivers that were all nearly identical, these drivers have been unified into a single set of ODBC functions.

The following databases are supported by the Unified ODBC functions: Adabas D, IBM DB2, iODBC, Solid, and Sybase SQL Anywhere.

Note: There is no ODBC involved when connecting to the above databases. The functions that you use to speak natively to them just happen to share the same names and syntax as the ODBC functions. The exception to this is iODBC. Building PHP with iODBC support enables you to use any ODBC-compliant drivers with your PHP applications. iODBC is maintained by OpenLink Software. More information on iODBC, as well as a HOWTO, is available at


To access any of the supported databases you need to have the required libraries installed.



Include Adabas D support. DIR is the Adabas base install directory, defaults to /usr/local.


Include SAP DB support. DIR is SAP DB base install directory, defaults to /usr/local.


Include Solid support. DIR is the Solid base install directory, defaults to /usr/local/solid.


Include IBM DB2 support. DIR is the DB2 base install directory, defaults to /home/db2inst1/sqllib.


Include Empress support. DIR is the Empress base install directory, defaults to $EMPRESSPATH. From PHP4, this option only supports Empress Version 8.60 and above.


Include Empress Local Access support. DIR is the Empress base install directory, defaults to $EMPRESSPATH. From PHP4, this option only supports Empress Version 8.60 and above.


Include Birdstep support. DIR is the Birdstep base install directory, defaults to /usr/local/birdstep.


Include a user defined ODBC support. The DIR is ODBC install base directory, which defaults to /usr/local. Make sure to define CUSTOM_ODBC_LIBS and have some odbc.h in your include dirs. E.g., you should define following for Sybase SQL Anywhere 5.5.00 on QNX, prior to run configure script: CPPFLAGS="-DODBC_QNX -DSQLANY_BUG" LDFLAGS=-lunix CUSTOM_ODBC_LIBS="-ldblib -lodbc".


Include iODBC support. DIR is the iODBC base install directory, defaults to /usr/local.


Include Easysoft OOB support. DIR is the OOB base install directory, defaults to /usr/local/easysoft/oob/client.


Include unixODBC support. DIR is the unixODBC base install directory, defaults to /usr/local.


Include OpenLink ODBC support. DIR is the OpenLink base install directory, defaults to /usr/local. This is the same as iODBC.


Include DBMaker support. DIR is the DBMaker base install directory, defaults to where the latest version of DBMaker is installed (such as /home/dbmaker/3.6).

To disable unified ODBC support in PHP 3 add --disable-unified-odbc to your configure line. Only applicable if iODBC, Adabas, Solid, Velocis or a custom ODBC interface is enabled.

The windows version of PHP has built in support for this extension. You do not need to load any additional extension in order to use these functions.

Runtime Configuration

The behaviour of these functions is affected by settings in php.ini.

Table 1. Unified ODBC Configuration Options

odbc.default_db *NULLPHP_INI_ALL
odbc.default_user *NULLPHP_INI_ALL
odbc.default_pw *NULLPHP_INI_ALL

Note: Entries marked with * are not implemented yet.

For further details and definition of the PHP_INI_* constants see ini_set().

Here's a short explanation of the configuration directives.

odbc.default_db string

ODBC data source to use if none is specified in odbc_connect() or odbc_pconnect().

odbc.default_user string

User name to use if none is specified in odbc_connect() or odbc_pconnect().

odbc.default_pw string

Password to use if none is specified in odbc_connect() or odbc_pconnect().

odbc.allow_persistent boolean

Whether to allow persistent ODBC connections.

odbc.check_persistent boolean

Check that a connection is still valid before reuse.

odbc.max_persistent integer

The maximum number of persistent ODBC connections per process.

odbc.max_links integer

The maximum number of ODBC connections per process, including persistent connections.

odbc.defaultlrl integer

Handling of LONG fields. Specifies the number of bytes returned to variables.

odbc.defaultbinmode integer

Handling of binary data.

Resource Types

This extension has no resource types defined.

Predefined Constants

The constants below are defined by this extension, and will only be available when the extension has either been compiled into PHP or dynamically loaded at runtime.

ODBC_TYPE (integer)







SQL_CUR_USE_ODBC (integer)












SQL_CHAR (integer)

SQL_VARCHAR (integer)


SQL_DECIMAL (integer)

SQL_NUMERIC (integer)

SQL_BIT (integer)

SQL_TINYINT (integer)

SQL_SMALLINT (integer)

SQL_INTEGER (integer)

SQL_BIGINT (integer)

SQL_REAL (integer)

SQL_FLOAT (integer)

SQL_DOUBLE (integer)

SQL_BINARY (integer)



SQL_DATE (integer)

SQL_TIME (integer)


SQL_TYPE_DATE (integer)

SQL_TYPE_TIME (integer)


SQL_BEST_ROWID (integer)

SQL_ROWVER (integer)




SQL_NO_NULLS (integer)

SQL_NULLABLE (integer)


SQL_INDEX_ALL (integer)

SQL_ENSURE (integer)

SQL_QUICK (integer)

Table of Contents
odbc_autocommit -- Toggle autocommit behaviour
odbc_binmode -- Handling of binary column data
odbc_close_all -- Close all ODBC connections
odbc_close -- Close an ODBC connection
odbc_columnprivileges --  Returns a result identifier that can be used to fetch a list of columns and associated privileges
odbc_columns --  Lists the column names in specified tables. Returns a result identifier containing the information.
odbc_commit -- Commit an ODBC transaction
odbc_connect -- Connect to a datasource
odbc_cursor -- Get cursorname
odbc_data_source -- Returns information about a current connection
odbc_do -- Synonym for odbc_exec()
odbc_error -- Get the last error code
odbc_errormsg -- Get the last error message
odbc_exec -- Prepare and execute a SQL statement
odbc_execute -- Execute a prepared statement
odbc_fetch_array --  Fetch a result row as an associative array
odbc_fetch_into -- Fetch one result row into array
odbc_fetch_object --  Fetch a result row as an object
odbc_fetch_row -- Fetch a row
odbc_field_len -- Get the length (precision) of a field
odbc_field_name -- Get the columnname
odbc_field_num -- Return column number
odbc_field_precision -- Synonym for odbc_field_len()
odbc_field_scale -- Get the scale of a field
odbc_field_type -- Datatype of a field
odbc_foreignkeys --  Returns a list of foreign keys in the specified table or a list of foreign keys in other tables that refer to the primary key in the specified table
odbc_free_result -- Free resources associated with a result
odbc_gettypeinfo --  Returns a result identifier containing information about data types supported by the data source.
odbc_longreadlen -- Handling of LONG columns
odbc_next_result --  Checks if multiple results are available
odbc_num_fields -- Number of columns in a result
odbc_num_rows -- Number of rows in a result
odbc_pconnect -- Open a persistent database connection
odbc_prepare -- Prepares a statement for execution
odbc_primarykeys --  Returns a result identifier that can be used to fetch the column names that comprise the primary key for a table
odbc_procedurecolumns --  Retrieve information about parameters to procedures
odbc_procedures --  Get the list of procedures stored in a specific data source. Returns a result identifier containing the information.
odbc_result_all -- Print result as HTML table
odbc_result -- Get result data
odbc_rollback -- Rollback a transaction
odbc_setoption --  Adjust ODBC settings. Returns FALSE if an error occurs, otherwise TRUE.
odbc_specialcolumns --  Returns either the optimal set of columns that uniquely identifies a row in the table or columns that are automatically updated when any value in the row is updated by a transaction
odbc_statistics -- Retrieve statistics about a table
odbc_tableprivileges --  Lists tables and the privileges associated with each table
odbc_tables --  Get the list of table names stored in a specific data source. Returns a result identifier containing the information.

add a note add a note User Contributed Notes
Unified ODBC functions
helvecio_oliveira at yahoo dot com dot br
24-Oct-2003 06:05
Are you looking for some solution like odbc-odbc bridge? Forget it! Look for OdbTp:

Equivalent solutions like,,, etc. are very expensive.

OdbTp is simple, fast and Free!!! (GNU).

A site in production using it:
Solaris+php+apache(OdbTp) -> (OdbTp)Win2K(ODBC)->(ODBC)MSQL-Server 7:

Install Example:

- Windows (OdbTp Server)
1. Download
2. Create a dir C:/odbtp and copy all content from winservice to it.
3. Go to DOS and:

cd C:
cd odbtp
odbtpctl install
odbtpctl start

4. Win install finished!!!

- Linux Mandrake 9.1 (OdbTp Client)
1. Uncompress into /tmp
2. Install:

cd /tmp/odbtp-1.0
make install

3. Install into php:
cp -r /tmp/odbtp-1.0/php/ext/ para /usr/src/php-devel/extensions/odbtp/
cp -r /usr/src/php-devel/extensions/odbtp /tmp/odbtp
cd /tmp/odbtp
make install
echo "extension =" > /etc/php/90_odbtp.ini

4. Restart o Apache.
5. Linux install finished!!!.

You can find several examples in /tmp/odbtp-1.0/php/examples/

or in
oliver D O T pliquett @ m93 D O T d e
20-Oct-2003 06:23
I post this since my hair turned gray during the last two days:
If you ever try to connect to a local MS SQL Server via PEAR and odbc, try this dsn:

$dsn = "ODBC://user:passw@ODBC_name";

where ODBC_name is the name you gave to it in your control panel ODBC manager.
No host name!!!

Also take a look at your SQL Server manager:
in the security-tab of the property-dialogue of your (local) server, select "authentication by SQL server and Windows"
so your SQL server users are accessible. And last - not least make sure your odbc config is made as a system-DSN.
(mssql2000 preview / IIS 5.1 / PHP4.3.3 as isapi)

Happy hacking

Oliver ;-)
mike (at) NOSPAM
24-Jul-2003 11:21
This code is from a php5.0.0(b1) abstract class I use as a baseclass for all my pages it holds the connection throughout the lifetime of the instance. It is fairly generic (and messy/not thouroughly tested yet!). It returns either a 3D Array or an Array depending on the result which you can manipulate from your other objects classes etc. It gets round al the odbc_num_rows problems many drivers eem to suffer. I hope this helps people, comments welcome. Many thanks. Mike

        final function __construct() { # not to be overidden!!
                # connection initialised during the lifetime of the object instance
                $this->$connection = odbc_connect("owl", "", ""); 
        final function __destruct() { # not to be overidden!!
                # close $connection resource upon estruction of the object instance (the page handles this).
                # mop up database connections stopping memory leaks and zombie threads. Hopefully!
        protected function dbQuery($query, $select) {
        #  $query = SQL Statement
        #  $select = boolean (true if SQL is SELECT statement, false if SQL is not SELECT statement)     
        # performs SQL query on $connection
        # the result is multiple lines). It is then up to the initaiting script to use the
        # data passed back in the array
                $result = odbc_exec($this->$connection, $query); # Query database
                while(odbc_fetch_row($result)) {
                if ($select) {
                        if ($s > 1) { # check no of rows returned action to correct array type.
                                $final = array();
                                $i = 0;
                                while($i<=$s) {
                                        while($j<=odbc_num_fields($result)) {
                                                $final[$i][$j] = odbc_result($result, $j);  # 3D Array       
                        } else {
                                $final  = odbc_fetch_row($result); # Array
                return $final;
lordlucless at flashmail dot com
17-Jun-2003 01:26
You can actually use reserved words as table names under Microsoft JET. However, whenever they are referenced they must be enclosed in square brackets [].

Thus if you want a table called Value, you use [Value] in all SQL statements referring to it. It's a bit of a pain, but it can be done.
nick at savvior dot com
12-Jun-2003 06:12
IN regards to LaLista ( message on using ASP Com object model. getting $RecordSet->fields(0); will return an object back. YOu will have to get that objects "value" property to get your code to actually output the values

craig at halo dash 17 dot net
04-Jun-2003 04:17
Just a reminder that if you're trying to DELETE, ALTER, or DROP from an Excel spreadsheet via ODBC, you're wasting your time.  The Microsoft driver doesn't support such witchcraft.  Solution?  Don't use Excel.  If you're stuck with Excel files you need to alter, then export them to CSV format, and use the ODBC driver for that.  You'll save yourself lots of headaches in the long run.
mitchind at telusplanet dot net
06-May-2003 01:58
Thanks to the great documentation here, I was able to combine several examples and figure out the rest to create a generic routine to export a SQL query from an ODBC database to Excel format. It should work on most platforms and browsers. Usage is simple and described in code - but basically you just need to format the SQL query and supply it as a hidden input in another page's form and then use this script as the "ACTION" parameter.

Ideally I'd like to add a pop up message if the number of records exceeds the MAX_RECS value - tell the user and get a confirmation to continue. Maybe someone else can add that for me - my knowledge of PHP has been stretched with this module.


// exportExcel.php
// PHP Script to Export Results of ODBC SQL Query directly into Excel
// Get SQL Statement from Form variable

// No caching of script results
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");    // Date in the past
header("Last-Modified: " gmdate("D, d M Y H:i:s") . " GMT");                                                    // always modified
header("Cache-Control: no-store, no-cache, must-revalidate");  // HTTP/1.1
header("Cache-Control: post-check=0, pre-check=0"false);
header("Pragma: no-cache");                         // HTTP/1.0

// Variables dependent on application and input form
$DSN      "YOUR DSN NAME"// Database ODBC DSN Name
$DB_USER  "";              // Database ODBC Username
$DB_PWD   "";              // Database ODBC Password
$MAX_RECS "100";           // Maximum number of records to export (ignored if empty)

$SQL_FORM_FIELD      "SQL";   // Input Form Field with SQL Query String
$NUM_RECS_FORM_FIELD "nRecs"  // Input Form Field with Number of Records
$ALLOW_REFER_FROM    ""// Only Allow usage from this website

if (empty($_SERVER['HTTP_REFERER']))
"No Referal Website");

$pos strpos("$refer""$ALLOW_REFER_FROM") or die("Not Coming From Correct Website");

// Read in SQL statment from form (regardless of GET or POST method)
if (empty(
"No Input Form Field Defined");


// Get rid of the slashes that PHP inserts and will screw up ODBC SQL queries
$sqlQuery stripslashes("$sqlQuery");

// Connect to data source
$db odbc_connect("$DSN","$DB_USER","$DB_PWD");

// Get Number of Records in query if already known
if (!empty($_REQUEST["$NUM_RECS_FORM_FIELD"])) {
$numrecs $_REQUEST["$NUM_RECS_FORM_FIELD"]; // Check this value against Max
else {
$numrecs 0// Unknown .. better set upper limit

// Attach MaxRecs limitation if defined and less than number of records in query
// or if Number of Records is unknown
if (!empty($MAX_RECS)) {
    if (
$numrecs == 0) || ($MAX_RECS $numrecs) {
$sqlQuery str_replace("SELECT ""SELECT TOP $MAX_RECS ""$sqlQuery");
// Fetch query results
$result odbc_exec($db"$sqlQuery") or die("Query failed");

// Build page of results
// Open as Excel file in browser
header('Content-type: application/');

<style type="text/css">
 body {font: 10pt/12pt Tahoma, Verdana, Helvetica, sans-serif; color: indigo; margin: .25in .5in }
 table {FONT-FAMILY:Tahoma; font-size:8pt; color:Navy; border-color:Black; border-style:Solid; border-width:1px;}
 td {background-color:AntiqueWhite;}


// returns table with basic formatting
odbc_result_all($result"border=\"1\" class=\"def\"");

//disconnect from database

LaLista (
17-Mar-2003 08:43

You can use the ADODB interface instead. It's the same code than ASP, but it's written in PHP:

= new COM("ADODB.Connection");
$RecordSet = new COM("ADODB.RecordSet");
$Connection -> Open("Provider=SQLOLEDB; DBQ=MyServer; UID=MyUser; PWD=MyPassword");
$RecordSet $Connection -> Execute("SELECT * FROM MyTable");
$RecordSet -> EOF)
"\"" $RecordSet -> Fields(0) . "\"";
"costs $$RecordSet -> Fields(1) . "< BR>";
$RecordSet -> MoveNext() #Don't forget this
$Connection -> Close();
$Connection -> Release();
$RecordSet -> Release();
$Connection null;
$RecordSet null;

Supposing this is the table:
Article(0)   Price(1)
  Wine        19.95
  CD-RW        1.49
  CD-R         0.49
  Pen          0.45

This will be the output:
    "Wine" costs $19.95< BR>
   "CD-RW" costs $1.49< BR>
   "CD-R" costs $0.49< BR>
    "Pen" costs $0.45< BR>
b dot parish at no_spam dot linst dot ac dot uk
14-Aug-2002 10:46
Accessing a Microsoft SQL Server database from PHP running under Linux:
steven at webdesignsolutions dot net
12-Aug-2002 10:59
another fetch array acts the same as mysql_fetch_array, but make a numeric array


and an associative array


I am sure people could inprive on this:

function odbc_fetch_array($R)
$FR = odbc_fetch_row($R);
for ($i = 1;$i <= odbc_num_fields($R);$i++)
    $FN = odbc_field_name($R,$i);
    $FV = odbc_result($R,$i);
    $RA[$FN] = $FV;
    $RA[$i-1] = $FV;
if ($FR == false)return false;
return $RA;
tomking2030 at hotmail dot com
21-Jun-2002 07:40
I had problems with access and reserved words. I could select data but couldn't insert data. It was a real headache but you can get around it by using [] in your insert command.

$sql66 = "INSERT INTO $tablename
([level], [ADMIN])
('$level', '$State')";

Smebody mentioned it above but heres another way to use it. Those [] also help if your having problems with "white spaces" between data.
NOSPAM-bkeyes at minnetronix dot com
09-May-2002 05:17
If you are accessing a Microsoft Access based query that contains a where clause with LIKE '*XYZ' you may find that you don't get any results returned.

If this is the case you should change the asterisk in the clause to a percent sign:


The query should now return results to PHP.

However, the query will NOT return results if run from within Access. If you need to use the query both from within Access and from PHP you will need to create 2 queries.
mr dot soetjianto at mail dot tju dot edu
18-Jan-2002 05:03
Adding to the previous post about "trouble with apostrophies", simply converting each single apostrophie into two apostrophies works for me.

Use something like:
//if magic_quotes_gpc = Off
$myInput = ereg_replace("'","''",$myInput);

//if magic_quotes_gpc = On
$myInput = ereg_replace("\'","''",$myInput);
nick at innousa dot com
17-Nov-2001 01:38
Microsoft Access Heads up:

I used the odbc drivers to connect to a DSN which pointed to an access database. Well it was on a test server so I didn't bother assigning a user/pass combo to the DSN. Well I could select, alter tables, drop tables, and create tables but I couldn't run an update or insert until I assigned a user/pass to the DSN. STUPID! Anyways save yourself some headaches when using access.
dseidel at sylphen dot com
23-Jul-2001 08:59
I have wrote an odbc_fetch_array-function. It works like the mysql_fetch_array-function.
The source:
/** the same like mysql_fetch_array for ODBC
* params : resultset
* return : assoziative array of actual row
* or false if no more entries
* author : Dierk Seidel (, KG,
* last modified : 23.07.2001
function odbc_fetch_array($res) {
if(odbc_fetch_into($res, '', $ret)) {
while (list($key,$value)=@each($ret)) {
$arret[odbc_field_name($res, $key+1)] = $value;
return $arret;
} else return false;
pgodel at lticom dot com
03-May-2001 12:57
This sounds stupid, but took me a good bunch of time to figure it out..
If you want to do a select on an excel file through ODBC,
"SELECT * from [Sheet1$]"
Where Sheet1 is the name of the sheet inside the .xls file.
Hope this helps.
dan dot polansky at seznam dot cz
03-Mar-2001 03:02
Don't forget to read user comments for odbc_connect :-)
dan dot polansky at seznam dot cz
03-Mar-2001 02:58
One nice thing about these unified ODBC functions is that you don't have to care about blobs. You just obtain the contents of the blob in the result of a select statement, not just blob identifier.

For you who do not know what a blob is my message is that with these functions you perhaps do not need to know.
dan dot polansky at seznam dot cz
03-Mar-2001 02:37
First I just repeat that examples are here:

Second: Despite confusing remarks above, using unified ODBC functions you can really connect to any database for which you create DSN (Data Source Name). That is: using unified ODBC functions you can connect to _any_ database for which you have ODBC driver. Drivers exists or example Interbase, VisualFoxPro, DBase III,IV,V etc, many drivers are part of Win98 installation, other can be obtained from companies like EasySoft and Merant. ODBC is one of the most flexible ways for connecting to databases. This is because ODBC driver exist for almost every database there is. The question is, whether the driver is free. At least there are many 30 days trial versions of drivers.
sundogcurt at netscape dot net
01-Feb-2001 06:49
I agree, I find that a little bit confusing. I am setting up with a host now that has Access support and I was sure that this was supported. But these lines stopped me in my tracks for a bit.

The following databases are supported by the Unified ODBC functions: Adabas D, IBM DB2, iODBC, Solid, and Sybase SQL Anywhere.

Note: There is no ODBC involved when connecting to the above databases. The functions that you use to speak natively to them just happen to share the same names and syntax as the ODBC functions.
vpil at retico dot com
06-Nov-2000 10:20
Additional links to ODBC_exec:
How to actually write the SQL commands:
Demystifying SQL

Introduction to Structured Query Language

Covers read, add, modify & delete of data.

Hope it helps others as well.
view at gmx dot de
03-Nov-2000 07:05
A (in my opinion) good database-abstraction-framework can be found on
jlim at natsoft dot com dot my
11-Sep-2000 09:08
For examples of how to create a Data Source Name for ODBC and examples of ODBC connections, see:
cgi at harrison dot org
16-Aug-2000 07:50
Troubles with (')apostrophies in general seems to be solved simply by disabling MAGIC_QUOTES in the php.ini
(ex: magic_quotes_gpc = Off)
esk525 at hotmail dot com
16-Aug-2000 04:52
For those having trouble with apostrophies in their ODBC queries:  If the database you are connecting to supports CHAR(39), which prints the character associated with this ASCII number, or something like it, try this:

$myInput = ereg_replace("'","'+CHAR(39)+'",$myInput);
$query = "Select * From Table Where (Field='$myInput')";

$connection = odbc_connect("mydb","myuserid","mypass");
$result = odbc($connection,$query);

p.s. Don't forget the 's around the variable in the query line... good luck
cliff at cape dot com
21-Apr-2000 02:36
When using the Microsoft Jet Database Engine with Access remeber that you cannot use reserved names like Value or Add or Date within your tables(unless I am mistaken and I hope That I am) so watch out when you are designing tables in access!
Pascal dot Guimier at wanadoo dot fr
10-Mar-2000 01:08
This is a usefull fetch_array function, inspired from SAL.phl (
Thanks to  Gianugo Rabellino (
function odbc_fetch_array($res) {                     
    $row = array();
    $result = array();
    if ($result = odbc_fetch_row($res))  {
        $nf = odbc_num_fields($res)+1;       
        for($count=1; $count < $nf; $count++) {
            $field_name = odbc_field_name($res, $count);
            $field_value = odbc_result($res, $count);
            $row[$count] = $field_value;
            /* or $row[$field_name] =  $field_value */
        return $row;                 
use it like this :

$QCateg="SELECT * FROM Table";
$ret = odbc_exec($conn, $QCateg) or die("Invalid query : $QCateg");
print "My select : <select name=myselect>";
While ($row = fetch_array($ret, 2))
    print "<option value=$row[1]>$row[2]\n";
print "</select>\n<br>";
fjohansson at bigfoot dot com
20-May-1999 05:07
A common problem with many ODBC drivers (especially thoose from Microsoft) is that it is impossible to eg returning an ordernumber after inserting the row into the table. The solution is simple,
make one field in the db to a counter, and one to a temp field(text50). While inserting the row set the temp field to microtime() (or any other uniqe number). Then do a SELECT for this row (where temp=$microtimeinserted...). / Fredrik Johansson

 Last updated: Thu, 21 Aug 2003
show source | credits | sitemap | mirror sites 
Copyright © 2001-2003 The PHP Group
All rights reserved.
This mirror generously provided by:
Last updated: Sat 01 Nov 2003 04:13:36 EST EST