ODBC and DB2 Functions (PDO_ODBC)

Einführung

PDO_ODBC is a driver that implements the PHP Data Objects (PDO) interface to enable access from PHP to databases through ODBC drivers or through the IBM DB2 Call Level Interface (DB2 CLI) library. PDO_ODBC currently supports three different "flavours" of database drivers:

ibm-db2: Supports access to IBM DB2 Universal Database, Cloudscape, and Apache Derby servers through the free DB2 express-C client.
unixODBC: Supports access to database servers through the unixODBC driver manager and the database's own ODBC drivers.
generic: Offers a compile option for ODBC driver managers that are not explicitly supported by PDO_ODBC.

On Windows, php_pdo_odbc.dll has to be enabled as extension in php.ini. It is linked against the Windows ODBC Driver Manager so that PHP can connect to any database cataloged as a System DSN.

Installation

PDO_ODBC on UNIX systems

PDO_ODBC is included in the PHP source. You can compile the PDO_ODBC extension as either a static or shared module using the following configure commands.
ibm_db2
./configure --with-pdo-odbc=ibm-db2,/opt/IBM/db2/V8.1/
To build PDO_ODBC with the ibm-db2 flavour, you have to have previously installed the DB2 application development headers on the same machine on which you are compiling PDO_ODBC. The DB2 application development headers are an installable option in the DB2 servers, and are also available as part of the DB2 Application Development Client freely available for download from the IBM developerWorks » website.

If you do not supply a location for the DB2 libraries and headers to the configure command, PDO_ODBC defaults to /home/db2inst1/sqllib.
unixODBC
./configure --with-pdo-odbc=unixODBC,/usr/local
If you do not supply a location for the unixODBC libraries and headers to the configure command, PDO_ODBC defaults to /usr/local.
generic
./configure --with-pdo-odbc=generic,/usr/local,libname,ldflags,cflags

Vordefinierte Konstanten

Die folgenden Konstanten werden von diesem Treiber definiert und sind nur verfügbar, wenn die Erweiterung entweder in PHP einkompiliert oder dynamisch zur Laufzeit geladen wurde. Darüber hinaus sind diese Konstanten treiberspezifisch und sollten nur mit diesem Treiber benutzt werden. Treiberspezifische Attribute mit einem anderen Treiber zu nutzen, kann zu unerwarteten Ergebnissen führen. Wenn der Code mit verschiedenen Treibern funktioniert, kann PDO::getAttribute() verwendet werden, um mit PDO::ATTR_DRIVER_NAME den aktuellen Treiber zu ermitteln.

PDO_ODBC_TYPE (string)

PDO::ODBC_ATTR_USE_CURSOR_LIBRARY (int)

This option controls whether the ODBC cursor library is used. The ODBC cursor library supports some advanced ODBC features (e.g. block scrollable cursors), which may not be implemented by the driver. The following values are supported:

PDO::ODBC_SQL_USE_IF_NEEDED (the default): use the ODBC cursor library when needed.
PDO::ODBC_SQL_USE_DRIVER: never use the ODBC cursor library.
PDO::ODBC_SQL_USE_ODBC: always use the ODBC cursor library.

PDO::ODBC_ATTR_ASSUME_UTF8 (bool)

Windows only. If true, UTF-16 encoded character data (CHAR, VARCHAR and LONGVARCHAR) is converted to UTF-8 when reading from or writing data to the database. If false (the default), character encoding conversion may be done by the driver.

Laufzeit-Konfiguration

Das Verhalten dieser Funktionen wird durch Einstellungen in der php.ini beeinflusst.

**PDO_ODBC Configuration Options**
Name	Standard	Veränderbar	Changelog
pdo_odbc.connection_pooling	"strict"	`INI_ALL`
pdo_odbc.db2_instance_name	NULL	`INI_SYSTEM`	Dieses veraltete Feature wird in der Zukunft sicherlich entfernt.

Weitere Details und die Definitionen der INI_*-Konstanten sind unter Wo Konfigurationseinstellungen gesetzt werden können zu finden.

Hier eine kurze Erklärung der Konfigurationsoptionen:

pdo_odbc.connection_pooling string

Whether to pool ODBC connections. Can be one of "strict", "relaxed" or "off" (equals to ""). The parameter describes how strict the connection manager should be when matching connection parameters to existing pooled connections. strict is the recommend default, and will result in the use of cached connections only when all the connection parameters match exactly. relaxed will result in the use of cached connections when similar connection parameters are used. This can result in increased use of the cache, at the risk of bleeding connection information between (for example) virtual hosts.

This setting can only be changed from the php.ini file, and affects the entire process; any other modules loaded into the process that use the same ODBC libraries will be affected too, including the Unified ODBC extension.

Warnung

relaxed matching should not be used on a shared server, for security reasons.

Tipp

Leave this setting at the default strict setting unless you have good reason to change it.

pdo_odbc.db2_instance_name string

If you compile PDO_ODBC using the db2 flavour, this setting sets the value of the DB2INSTANCE environment variable on Linux and UNIX operating systems to the specified name of the DB2 instance. This enables PDO_ODBC to resolve the location of the DB2 libraries and make cataloged connections to DB2 databases.

This setting has no effect on Windows.

Inhaltsverzeichnis

PDO_ODBC DSN — Connecting to ODBC or DB2 databases

add a note

User Contributed Notes 5 notes

down

ChristianF ¶

8 years ago


I just spent a couple of hours trying to track down the Exception "Could not find driver". This was despite having ODBC and PDO_ODBC installed, and all of the configuration seemed to be correct.

Turned out the problem was that I used ODBC in upper-case in the dsn. As soon as I changed the dns to "odbc:database" it worked.

As this code used to work a few months ago, this sudden case-sensitivity threw me for a loop. So in case you get this error, check the casing first.

down

ethan dot nelson at ltd dot org ¶

16 years ago


Using SQL 2005, PDO_ODBC and datetime fields is a royal pain.  MSDN documentation on CAST CONVERT shows that there is supposed to be an implicit convert between character types and datetime types.  That's true... until you put it in a stored procedure and use variable declarations.

For instance this fails:

declare @date varchar;
SET @date = '20080101';
SELECT cast(@date AS datetime) AS poo

While this succeeds:
declare @date varchar(19);
SET @date = '20080101';
SELECT cast(@date AS datetime) AS poo

The PDO Driver appears to attempt an implicit conversion and so it fails whenever you try to insert data into datetime column types.

So to workaround this nuance in SQL, declare a character column type with explicit width.  Then your implicit type conversion will work.

down

Ariz Jacinto ¶

12 years ago


Using SQL Server Native Client 11.0 on Linux as a PDO_ODBC driver:



Download the SQL Server Native Client 11.0 on Linux ODBC Driver:

http://www.microsoft.com/download/en/details.aspx?id=28160



Configuration ODBC:



/usr/local/etc/odbcsys.ini

--

[SQL Server Native Client 11.0]

Description = Microsoft SQL Server ODBC Driver V1.0 for Linux

Driver = /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1720.0

UsageCount = 1



/usr/local/etc/odbc.ini

--

[MSSQLServer]

Driver = SQL Server Native Client 11.0

Description = Sample Database

Trace = Yes

Server = 

Port = 1433

Database = 



Test the connection:

mssqltest.php

--

<?php

    putenv('ODBCSYSINI=/usr/local/etc');

    putenv('ODBCINI=/usr/local/etc/odbc.ini');

    $username = "";

    $password = "";

    try {

      $dbh = new PDO("odbc:MSSQLServer",

                    "$username",

                    "$password"

                   );

    } catch (PDOException $exception) {

      echo $exception->getMessage();

      exit;

    }

    echo var_dump($dbh);

    unset($dbh);

?>

down

tuomas ¶

15 years ago


If you want to avoid installing DB2 Connect and/or PECL modules ibm_db2 and PDO_IBM, you can also use IBM DB2 databases trough unixODBC.

If you have DB2 database on a i server you need to install IBM iAccess (http://www.ibm.com/systems/i/software/access/linux/index.html) and unixODBC. Just install the libraries (rpm) and modify configurations in /etc/odbcinst.ini (sample configuration in /opt/ibm/iSeriesAccess/unixodbcregistration) and /etc/odbc.ini.

To my experience this is much easier way than installing DB2 Connect.

down

harry dot forum at p-boss dot com ¶

14 years ago


MSSQL - PHP on Apache - Linux Redhat

When using php 5.2.10 please beaware of this error:

http://bugs.php.net/bug.php?id=42068

Standard odbc_connect will not work, you must use pdo_odbc

Connecting to MSSQL using pdo odbc - walkthrough.. 

1. Download and configure FreeTDS with-unixodbc

./configure --prefix=/opt/SYSfreetds --with-unixodbc

make;make test; make install

2. install php-odbc and unixODBC

         php-odbc-5.2.10-1.x86_64.rpm
         unixODBC.x86_64.x86x64

3. Setup ODBC links

a)
Create a tds.driver file with the following contents

  [FreeTDS]
  Description     = v0.63 with protocol v8.0
  Driver          = /opt/SYSfreetds/lib/libtdsodbc.so

Register the ODBC driver - the tds.driver file

  odbcinst -i -d -f tds.driver

b)
Creating a tds.datasource file - ODBC Data Source with contents:

  [SOURCENAME]
  Driver=FreeTDS
  Description=Test MS SQL Database with FreeTDS
  Trace=No
  Server=BobTheServer
  Port=1433
  TDS Version=8.0
  Database=youDBname

Register the ODBC data source

  odbcinst -i -s -f tds.datasource

Beware that the odbc.ini file will be installed in the current users home directory. This may need to be used if you are using a webserver as the apache home directory could be different.

Ensure .odbc.ini is in apaches home directory, possibly "/var/www"

4. Test the ODBC link on the command line

  isql -v SOURCENAME 'username' 'password'

  +---------------------------------------+
  | Connected!                            |
  |                                       |
  | sql-statement                         |
  | help [tablename]                      |
  | quit                                  |
  |                                       |
  +---------------------------------------+
 SQL>

5. Edit /etc/php.ini

  Make sure the following is set:
     mssql.secure_connection = On
 

6. Restart apache gracefully

7. PHP to run:

  <?
  $dbh= new PDO('odbc:SOURCENAME', 'username', 'password');
  $stmt = $dbh->prepare("$query");
  $stmt->execute();
  while ($row = $stmt->fetch()) {
      print_r($row);
  }
  unset($dbh); unset($stmt);
  ?>

Trouble-shooting:

Please try strace/ truss if you encounter issues. It could be you are referencing wrong libraries somewhere.

Ensure you have restarted apache once the odbc files are in place

add a note