CODEWITHSUNDEEP
phppdodb2odbc
Mike Pala
Mike Pala

Reputation: 806

php PDO ODBC DB2 construct

I am having trouble constructing a PDO. It's a function I've never used before. Let's say these are my connection variables

$database = "BLUDB";
$hostname = "dashdb-enterprise-FAKE.services.dal.bluemix.net"; 
$user     = "fakeuser";   
$password = "fakepsw";   
$port     = 50000;          
$driver  = "DRIVER={IBM DB2 ODBC DRIVER};";
$dsn     = "DATABASE=$database; " .
       "HOSTNAME=$hostname;" .
       "PORT=$port; " .
       "PROTOCOL=TCPIP; " .
       "UID=$user;" .
       "PWD=$password;";
$conn_string = $driver . $dsn;

I have tried this:

$conn = new PDO( "DB2:DRIVER={IBM DB2 ODBC DRIVER},HOSTNAME=dashdb-enterprise-FAKE.services.dal.bluemix.net,PORT=50000,DATABASE=BLUDB,PROTOCOL=TCPIP,UID=fakeuser,PWD=fakepsw");

and a few other permutations. But I keep getting this error:

Fatal error: Uncaught PDOException: could not find driver in /home/robur12/domains/idobczyce.pl/public_html/mike/dava/index.php:41 Stack trace: #0 /home/robur12/domains/idobczyce.pl/public_html/mike/dava/index.php(41): PDO->__construct('DB2:DRIVER={IBM...') #1 {main} thrown in /home/robur12/domains/idobczyce.pl/public_html/mike/dava/index.php on line 41

I have followed the process as described here: https://www.ibm.com/support/knowledgecenter/en/SSHRBY/com.ibm.swg.im.dashdb.doc/connecting/connect_connecting_php.html

I have completed the instalation and I have even run the validation command in shell

db2cli validate -dsn DashDB -connect -user bluadmin -passwd 'your_secret_password'

see output below

enter image description here

I am running under Linux s90.linuxpl.com 4.4.27-grsec #1 SMP Tue Oct 25 20:38:40 CEST 2016 x86_64

my php.ini only says 

extension=odbc.so

in the web browser phpinfo(); will return

'./configure' '--prefix=/usr/local/php7.3' '--disable-debug' '--disable-ipv6' '--enable-bcmath' '--enable-calendar' '--enable-ctype' '--enable-exif' '--enable-ftp' '--enable-gd-native-ttf' '--enable-inline-optimization' '--enable-mbstring' '--enable-pcntl' '--enable-pdo' '--enable-session' '--enable-soap' '--enable-sockets' '--enable-sysvsem' '--enable-wddx' '--enable-zip' '--with-bz2' '--with-config-file-path=/usr/local/php7.3' '--with-pear=/usr/local/php7.3/lib/php' '--with-curl=/usr/local/lib' '--with-freetype-dir' '--with-freetype-dir=/usr/local/lib' '--with-gd' '--with-gdbm' '--with-gettext' '--with-iconv=/usr/local' '--with-imap-ssl' '--with-imap=/usr/lib/' '--with-jpeg-dir=/usr/local/lib' '--with-kerberos' '--with-ldap' '--with-ldap-sasl' '--with-mcrypt' '--with-mhash' '--with-mysqli=mysqlnd' '--with-openssl=/usr/local/adds/openssl' '--with-pcre-regex' '--with-pdo-mysql=mysqlnd' '--with-png-dir=/usr/local/lib' '--with-pspell' '--with-readline' '--with-tidy' '--with-xmlrpc' '--with-xsl' '--with-zlib' '--with-zlib-dir=/usr/local/lib' '--enable-opcache' '--with-pdo-pgsql=/usr/local/pgsql' '--with-pgsql=/usr/local/pgsql' '--enable-intl' '--with-pdo-odbc=ibm-db2,/usr/local/adds/dsdriver'

but when I run php -m in the command line I do NOT see ibm_db2 and I do NOT see pdo_ibm. I only see the following:

enter image description here

I have contacted my php server provider and asked then:

"can you possibly instal the following php modules: pdo_ibm and ibm_db2?"

and they replied:

"it seems you are trying to access IBM DB2.

you should be using the PDO function following the instructions here:

https://www.php.net/manual/en/ref.pdo-odbc.php

we have compiled pdo ibm_db2 for you on Friday. PDO Driver for ODBC (ibm-db2) => enabled if you require other modules to be installed please provide a link or documentation"

Upvotes: 0

Views: 1639

Answers (2)

Mike Pala
Mike Pala

Reputation: 806

As it turns out tying php -m in the shell command line accessed my php 7.2 modules.

I needed to type /usr/local/php7.3/bin/php -m to view my php 7.3 modules.

when I did that I saw 2 additional modules: PDO and PDO_ODBC.

and the php PDO function syntax I had to use was:

$database = 'BLUDB';
$username = 'fakeusername';
$password = 'fakepassword';
$hostname = 'dashdb-fakehostname.bluemix.net';
$port = 50000;

$string= sprintf("odbc:DRIVER{PDO_ODBC};DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;",
     $database ,
     $hostname ,
     $port);

$connection = new PDO($string,$username,$password)

Upvotes: 0

mao
mao

Reputation: 12267

For some driver versions, you need to specify the prefix ibm: to use the IBM pdo extension for Db2.

Additionally there are several prerequisites that need to be installed and configured first. It helps to verify each prerequisite separately before you attempt a connection. You should install the php-cli for your version of PHP to help with problem determination (for example php7.0-cli).

In particular, verify at the shell command line (on the hostname that runs PHP) that php -m shows both pdo_ibm and ibm_db2 are loaded. If one or both are missing then either your php.ini is not configured, or the driver is not loading (in which case you should see an error message at the start of the php -m output.

Here is an example showing a full connection string instead of a catalogued database:

<?php

#
# This shows using a connection-string to a Db2-on-cloud database.
#
# Prerequisites:
# 0. Follow IBM's documenation at https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.swg.im.dbclient.php.doc/doc/c0054947.html
# 1. Modules ibm_db and pdo_ibm are already installed to match the PHP
# 2. The php.ini loads the ibm_db and pdo_ibm drivers successfully 
# 3. You already verified your Db-client is operating successfully (use db2cli validate and db2cli validate -connect).
#


$database = 'bludb';
$user = 'whatever';
$password = 'whatever';
$hostname = 'dashdb-txn-sbox-whatever.bluemix.net';
$port = 50000;

$string= sprintf("ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;",
         $database ,
         $hostname ,
         $port);

try {
  $connection = new PDO(
         $string,$user,$password, array(PDO::ATTR_PERSISTENT => TRUE, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

        $req=$connection->query("select * from syscat.views where viewschema not like 'SYS%' and viewschema not like 'IBM%'");
        foreach ($req as $row) {
                print_r($row);
        }
}
catch (Exception $e) {
  echo($e->getMessage());
}

?>

Upvotes: 1

Related Questions