DB_odbtp

Overview
Requirements
DSN specification
ODBTP options
ODBC options
Databases supported by dbsyntax
Example DSN specifications

 

Overview

DB_odbtp is a PEAR DB driver that connects to a database using ODBTP (Open Database Transport Protocol). ODBTP allows any platform to remotely access the ODBC facilities on a Win32 host to connect to a database. A PHP application on a Linux, UNIX or Novell platform will be able to access a Microsoft SQL Server, Microsoft Access, Visual FoxPro, or any other database residing on a Win32 platform.

PHP developers on Windows can use the odbtp driver instead of the odbc, ado and mssql drivers. The odbtp extension provides better performance and more capabities than the extensions used by the other drivers. For example, Microsoft Access and Microsoft SQL Server can store UNICODE text data. However, the odbtp driver is the only one that provides full support for UNICODE text data and queries. The odbtp driver also has the exact behaviour on both Win32 and non-Win32 installations.
 

Requirements

The DB_odbtp package requires the prior installation of the ODBTP PHP extension and Win32 service. These components can be obtained at http://odbtp.sourceforge.net. The ODBTP service must be installed on a WinNT, WinXP or Win2K host whose ODBC facilities can access the desired database. The ODBTP extension and DB_odbtp package can be installed on any computer that can connect to the ODBTP service.
 

DSN specification

Full specification:
    odbtp(dbsyntax)://username:password@hostspec:port/database?opt1=val1&opt2=val2...&optN=valN
Minimal specification:
    odbtp://hostspec
The dbsyntax option can be any one of the following values:

    access -- Microsoft Access
    db2 -- IBM DB2
    dsn -- General ODBC DSN
    excel -- Microsoft Excel
    mssql -- Microsoft SQL Server
    mysql -- MySQL
    oracle -- Oracle
    sybase -- Sybase
    text -- Text Files
    vfp -- Visual FoxPro

The hostspec option can either be the hostname or IP address of an ODBTP service, or it can be the name of an ODBTP interface. In the case of the minimal specification, hostspec must be the name of an interface. The default value for hostspec is 127.0.0.1.

For more details about PEAR DB's DSN specification, see Introduction - DSN from the PEAR Manual.
 

ODBTP options

NameDescription
  
connid   ODBTP connection Id. This is only required when reconnecting to a reserved connection.
  
conntimeout   ODBTP TCP/IP connection timeout in seconds.
  
conntype   ODBTP connection type. it can either be single, reserved or normal. The default is normal.
  
dbparam   The name of the ODBC connect string parameter for specifying the database. The value is ODBC driver dependent. Examples are DATABASE, DBQ, SOURCEDB and DSN. The default is DATABASE.
  
odbcport   The ODBC connect string PORT parameter value. This option is included to eliminate ambiguity with the PEAR DB DSN's port option.
  
odbcprotocol   The ODBC connect string PROTOCOL parameter value. This option is included to eliminate ambiguity with the PEAR DB DSN's protocol option.
  
readtimeout   ODBTP TCP/IP read timeout in seconds.
  
rowcache   Enable row caching. If set to yes, then all row data will be placed in memory immediately after quere execution. This allows predetermination of row counts, and backward fetches without having to use a cursor.
  
txnisol   Transaction isolation level to use when auto commit has been disabled. Valid values are ReadUncommitted, ReadCommitted, RepeatableRead, Serializable and Default.
  
unicode   Enable UNICODE query text processing. If set to yes, then all query text will be read as UTF-8 encoded UNICODE text. And, if the underlying database is Microsoft Access, all text data will be returned as UTF-8 encoded UNICODE text.
vardatasize   Sets the maximum allowed byte size for variable length data, i.e., CLOBs (text) and BLOBs (image). The default is 64KB.
  

 

ODBC options

If an option is not recognized as a PEAR DB DSN or ODBTP option, then it is assumed to be an ODBC connect string option. Thus, unrecognized options are added to the ODBC connect string that will be sent to the ODBTP server.
 

Databases supported by dbsyntax

Databasedbsyntax   Additionally defined options and their defaults
   
Microsoft Access   access  
'dbparam' => 'DBQ'
'driver' => '{Microsoft Access Driver (*.mdb)}'
'username' => 'admin'
'password' => ''
IBM DB2   db2  
'dbparam' => 'DATABASE'
'driver' => '{IBM DB2 ODBC Driver}'
'hostname' => 'localhost'
'odbcport' => '50000'
'odbcprotocol' => 'TCPIP'
General ODBC DSN   dsn  
'dbparam' => 'DSN'
Microsoft Excel   excel  
'dbparam' => 'DBQ'
'driver' => '{Microsoft Excel Driver (*.xls)}'
'driverid' => '790'
Microsoft SQL Server   mssql  
'dbparam' => 'DATABASE'
'driver' => '{SQL Server}'
'server' => '(local)'
MySQL   mysql  
'dbparam' => 'DATABASE'
'driver' => '{mySQL}'
'server' => 'localhost'
'odbcport' => '3306'
'option' => '131072'
'stmt' => ''
Oracle   oracle  
'dbparam' => 'DBQ'
'driver' => '{Oracle ODBC Driver}'
Sybase   sybase  
'dbparam' => 'DATABASE'
'driver' => '{Sybase ASE ODBC Driver}'
'srvr' => 'localhost'
Text Files   text  
'dbparam' => 'DBQ'
'driver' => '{Microsoft Text Driver (*.txt; *.csv)}'
'extensions' => 'asc,csv,tab,txt'
Visual FoxPro   vfp  
'dbparam' => 'SOURCEDB'
'driver' => '{Microsoft Visual FoxPro Driver}'
'sourcetype' => 'DBF'
'exclusivee' => 'NO'

 

Example DSN specifications

A.   MS SQL Server and ODBTP server on different hosts.
$dsn = 'odbtp(mssql)://myuid:mypwd@odbtp.someehere.com/mydb?server=myserver';
B.   MS SQL Server and ODBTP server on same host.
$dsn = 'odbtp(mssql)://myuid:mypwd@myserver/mydb';
C.   PHP not on same host as MS Access database and ODBTP server.
$dsn = 'odbtp(access)://207.66.121.4/c:\somepath\mydb.mdb';
D.   PHP, MS Access database and ODBTP server on same host. UNICODE support enabled.
$dsn = 'odbtp(access):///c:\somepath\mydb.mdb?unicode=yes';
E.   An ODBTP server that does not listen on port 2799, the default.
$dsn = 'odbtp(access)://207.66.121.4:7255/c:\somepath\mydb.mdb';
F.   MS Excel spreadsheet on remote ODBTP server.
$dsn = 'odbtp(excel)://odbtp.somewhere.com/c:\somepath\mysheet.xls';
G.   Text files on local ODBTP server. Row cache enabled.
$dsn = 'odbtp(text):///c:\PathToTextFiles?rowcache=yes';
H.   Visual FoxPro database on remote ODBTP server.
$dsn = 'odbtp(vfp)://207.66.121.4/c:\PathToDBFs';
I.   Visual FoxPro DBC on local ODBTP server.
$dsn = 'odbtp(vfp):///c:\somepath\mydb.dbc?sourcetype=DBC';
J.   DSN array for Sybase. Connection type set to single.
$dsn = array( 'phptype' => 'odbtp',
              'dbsyntax' => 'sybase',
              'hostspec' => 'odbtp.somewhere.com',
              'username' => 'myuid',
              'password' => 'mypwd',
              'database' => 'mydb',
              'srvr' => 'mysrvr',
              'conntype' => 'single' );
K.   IBM DB2 not listening on port 50000, the default.
$dsn = 'odbtp(db2)://myuid:mypwd@207.66.121.4/mydb?odbcport=51000';
L.   Connection to Oracle. ODBTP read timeout set to 300 seconds
$dsn = 'odbtp(oracle)://myuid:mypwd@odbtp.someehere.com/mydb?readtimeout=300';
M.   General ODBC DSN on locat ODBTP server.
$dsn = 'odbtp(dsn):///mydsn';
N.   Connecting with an ODBTP interface.
$dsn = 'odbtp://myinterface';
O.   Database for which a dbsyntax value has not been defined.
$dsn = array( 'phptype' => 'odbtp',
              'driver' => '{INTERSOLV InterBase ODBC Driver (*.gdb)}',
              'hostspec' => '207.66.121.4',
              'server' => 'localhost',
              'username' => 'myuid',
              'password' => 'mypwd',
              'database' => 'localhost:C:\Home\Data\Mydb.gdb',
              'dbparam' => 'DATABASE' );