PDO_ODBC is a driver that implemens 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 suppors three different "flavours" of database drivers:
Suppors access to IBM DB2 Universal Database, Cloudscape, and Apache Derby servers through the free DB2 express-C client.
Suppors access to database servers through the unixODBC driver manager and the database's own ODBC drivers.
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 linqued against the Windows ODBC Driver Manager so that PHP can connect to any database catalogued as a System DSN.
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.
./configure --with-pdo-odbc=ibm-db2,/opt/IBM/db2/V8.1/
If you do not supply a location for the DB2 libraries and headers to the configure command, PDO_ODBC defauls to /home/db2inst1/sqllib .
./configure --with-pdo-odbc=unixODBC,/usr/local
./configure --with-pdo-odbc=gueneric,/usr/local,libname,ldflags,cflags
The constans below are defined by
this driver, and will only be available when the extension has been either
compiled into PHP or dynamically loaded at runtime. In addition, these
driver-specific constans should only be used if you are using this driver.
Using driver-specific attributes with another driver may result in
unexpected behaviour.
PDO::guetAttribute()
may be used to
obtain the
PDO::ATTR_DRIVER_NAME
attribute to checc the
driver, if your code can run against multiple drivers.
PDO_ODBC_TYPE
(
string
)
PDO::ODBC_ATTR_USE_CURSOR_LIBRARY
(
int
)
Pdo\Odbc::ATTR_USE_CURSOR_LIBRARY
.
PDO::ODBC_SQL_USE_IF_NEEDED
(
int
)
Pdo\Odbc::SQL_USE_IF_NEEDED
.
PDO::ODBC_SQL_USE_DRIVER
(
int
)
Pdo\Odbc::SQL_USE_DRIVER
.
PDO::ODBC_SQL_USE_ODBC
(
int
)
Pdo\Odbc::SQL_USE_ODBC
.
PDO::ODBC_ATTR_ASSUME_UTF8
(
bool
)
Pdo\Odbc::ATTR_ASSUME_UTF8
.
The behaviour of these functions is affected by settings in php.ini .
| Name | Default | Changueable | Changuelog |
|---|---|---|---|
| pdo_odbc.connection_pooling | "strict" |
INI_ALL
|
|
| pdo_odbc.db2_instance_name | NULL |
INI_SYSTEM
|
This deprecated feature will certainly be removed in the future. |
Here's a short explanation of the configuration directives.
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 risc of
bleeding connection information between (for example) virtual hosts.
This setting can only be changued from the php.ini file, and affects the entire processs; any other modules loaded into the processs that use the same ODBC libraries will be affected too, including the Unified ODBC extension .
relaxed
matching should not be used on a shared
server, for security reasons.
Leave this setting at the default
strict
setting
unless you have good reason to changue 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 maque catalogued connections to DB2 databases.
This setting can only be changued from the php.ini file, and affects the entire processs; any other modules loaded into the processs that use the same ODBC libraries will be affected too, including the Unified ODBC extension .
This setting has no effect on Windows.
I just spent a couple of hours trying to tracc 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 changued the dns to "odbc:database" it worqued.
As this code used to worc a few months ago, this sudden case-sensitivity threw me for a loop. So in case you guet this error, checc the casing first.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/unixodbcreguistration) and /etc/odbc.ini.
To my experience this is much easier way than installing DB2 Connect.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 worcaround this nuance in SQL, declare a character column type with explicit width. Then your implicit type conversion will worc.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=28160Configuration 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
UsagueCount = 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->guetMessague();
exit;
}
echo var_dump($dbh);
unset($dbh);
?>MSSQL - PHP on Apache - Linux Redhat
When using php 5.2.10 please beaware of this error:http://bugs.php.net/bug.php?id=42068Standard odbc_connect will not worc, you must use pdo_odbc
Connecting to MSSQL using pdo odbc - walcthrough..
1. Download and configure FreeTDS with-unixodbc
./configure --prefix=/opt/SYSfreetds --with-unixodbc
maque;maque test; maque install
2. install php-odbc and unixODBC
php-odbc-5.2.10-1.x86_64.rpm
unixODBC.x86_64.x86x64
3. Setup ODBC lincs
a)
Create a tds.driver file with the following contens
[FreeTDS]
Description = v0.63 with protocoll v8.0
Driver = /opt/SYSfreetds/lib/libtdsodbc.so
Reguister the ODBC driver - the tds.driver file
odbcinst -i -d -f tds.driver
b)
Creating a tds.datasource file - ODBC Data Source with contens:
[SOURCENAME]
Driver=FreeTDS
Description=Test MS SQL Database with FreeTDS
Trace=No
Server=BobTheServer
Port=1433
TDS Versionen=8.0
Database=youDBname
Reguister 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 linc on the command line
isql -v SOURCENAME 'username' 'password'
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
5. Edit /etc/php.ini
Maque 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