Using Ar Or Querybuilder With Ibm Db2 Functions

Yii 1.1 General Discussion
1

Short version: I’m trying to figure out if it’s possible to use AR or QueryBuilder using ibm db2 functions.

Confused? Here’s the long version:

We use an IBM iSeries machine (as/400) and run Zend server 6 on it.

The ONLY way I’ve found that allows you to connect to db2 using php is with this:


db2_connect ( string $database , string $username , string $password , [ array $options ] )

An example in action:


db2_connect($HOST, $UID, $PWD, array( 'i5_naming' => DB2_I5_NAMING_ON, 'i5_lib' => 'HMS' ))

Now this connection works just fine and that’s great. What’s not so great is that I have to code all the sql queries the hard way.

I’ve dug around the web for some kind of way to provide a db component that yii can use:


'db2' => array(

		  'connectionString' => 'ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;',

	//or//'connectionString' => 'odbc:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;',

		  'username' => 'username',

		  'password' => 'password',

		  'class' => 'ext.yiidb2.CIbmDB2Connection',

		),

The odbc can’t be used because it’s for linux/windows, we are using ios 6.

The ibm driver is not compatible because we don’t have an actual database name( or port ), only the folder that holds all the schemas(hence the need for those ibm db2 functions).

Digging further I found out that the our db uses different schemas than the ones provided by the yii extensions I found.

A colleague of mine suggested plugging zend components into yii, seeing as the zend framework adapter config files accept the parameters I provide, that is, the host/uid/pwd and options(can’t use the db without selecting that schema ). I’d like to leave this as a last resort if possible.

Any help and suggestions are very much welcome! I’ve been scratching my head over this for several days now.

Thanks! :lol:

2

You’re running your webserver on iOS 6? That extension you mentioned seems to be using the pdo_ibm module anyway, so you have to install it somehow.

You wrote that you don’t have a hostname, but you specified an example where there is a $HOST variable.

3

I’ll quote what I wrote in my post: “The ibm driver is not compatible because we don’t have an actual database name”

The $HOST variable is just that, the host. Like, localhost, or 11.22.33.44.

The pdo_ibm module is installed.

From php.net regarding it…

[i]The DSN can be any of the following:

a ) Data source setup using db2cli.ini or odbc.ini

b ) Catalogued database name i.e. database alias in the DB2 client catalog

c ) Complete connection string in the following format: DRIVER={IBM DB2 ODBC DRIVER};DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;\ [/i]

a ) is a no go, we’re running ios6

b ) don’t have this

c ) as previously mentioned, no database name or port to use in this driver.

I’ve tried everything I could think of to use c), but no luck :confused:

4

After looking at the source code of the pdo_ibm module and the CDbConnection try this using values from your first example of db2_conect:





'db2' => array(

                  'connectionString' => "ibm:$HOST",

                  'username' => $UID,

                  'password' => $PWD,

                  'class' => 'ext.yiidb2.CIbmDB2Connection',

                  'attributes' => array(

                      'i5_naming' => DB2_I5_NAMING_ON,

                      'i5_lib' => 'HMS'

                  ),

                ),

The pdo_ibm module detects ‘=’ in the connection string, without it it skips some parsing.

5

This looks like it might work. I remember when creating a PDO connection ibm:$HOST at a whim it worked fine. I want to hope this works, but I’m afraid of getting too hopeful only to have my hopes dashed again.

Thanks for your help! I’ll take a look and see if it works on Monday and let you know how it goes. :)

6

Maybe this link will be of some use.

From it I understand that port 446 is the default port and the command WRKRDBDIRE can be used to lookup the database name.

7

WRKRDBDIRE would give a list of all databases on most machines. On ours it doesn’t give anything useful. I believe this is because all of the files are in one place and not placed inside of relational databases. Also checked port 446, and many others (found through a command I can’t recall right now).

Thanks for trying to help though, it’s much appreciated. :)

8

I think it is because the database name was not explicitly given during db2 configuration. It used to be not mandatory. I could be wrong here, but in that case the default name should be *LOCAL. Not much of a name, but you did not see that name?

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Fdb2%2Frbafzmstrelationaldb.htm

9

*LOCAL is exactly what I saw. It didn’t help much the first time, but I’ll try again if nineinchnick’s advice is no help. It would certainly make for a strange looking connectionstring.

The network engineer that set it all up doesn’t want to change anything and risk a worst case scenario. We need the machine working at all times.

Thanks a bunch!

10

I’ve had some moderate success with this.

When using the following…


$users = Yii::app()->db2->createCommand()

    ->select('TID, TPROD, TTYPE')

    ->from('ITHI')

	->limit(5)

	->text();

I find that the sql it builds is perfectly fine.

However when I try to use the ->queryAll(); problems happen.

The stack trace points towards CDbCommand->queryAll()

This is the exception yii gives me:


CDbCommand failed to execute the SQL statement: CDbCommand failed to prepare the SQL statement: SQLSTATE[42704]: Undefined object: -204 ITHI in ADMIN type *FILE not found. (SQLPrepare[-204] at /patched-php-src-5.4.16/php-5.4.16/ext/pdo_ibm/ibm_driver.c:140). The SQL statement executed was: SELECT TID, TPROD, TTYPE

FROM ITHI FETCH FIRST 5 ROWS ONLY

Now a little background:

When connected to db2 I need to select the HMS Schema, and inside this the table ITHI should be used to retrieve data.

ADMIN is the $UID, so I don’t know why that is in the exception.

With


$dbc = db2_connect($HOST, $UID, $PWD, array ('i5_naming' => DB2_I5_NAMING_ON, 'i5_lib' => 'HMS'));

When I execute the generated query "SELECT TID, TPROD, TTYPE FROM ITHI FETCH FIRST 5 ROWS ONLY" using




$query = "SELECT TID, TPROD, TTYPE FROM ITHI FETCH FIRST 5 ROWS ONLY";

$data = db2_exec($dbc, $query);

I get the results I need. So the sql that querybuilder generates works just fine.

I suspect that the way the connection is established(or the way the query is executed) might be the problem, so I’ll have to go digging through the Class files.

Input and suggestions are very much welcome! :)

11

Just in case: yiidb2 had a recent bugfix. Do you have the latest version? Could you check with the author of yiidb2 whether this might be a bug?

12

Got the latest files last week. B)

13

How does your connection string look like now?

14 

'db2' => array(

                  'connectionString' => "ibm:$HOST",

                  'username' => $UID,

                  'password' => $PWD,

                  'class' => 'ext.yiidb2.CIbmDB2Connection',

                  'attributes' => array(

                      'i5_naming' => DB2_I5_NAMING_ON,

                      'i5_lib' => 'HMS'

                  ),

                ),

Still having trouble too. Not entirely sure, but I think the attributes property is for PDO attributes, not the i5_ ones.

This is so frustrating. <_<

I’ve taken a look at some of the Zend adapter config files. They have input for host, user, pass and the options array where I would put in the i5_ stuff.

I don’t suppose you know how to plug in that part of zend into yii? :D

15

Maybe try this notation:




'db2' => array(

                  'connectionString' => "ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=*LOCAL;HOSTNAME=$HOST;PROTOCOL=TCPIP;UID=$UID;PWD=$PWD;",

                  'class' => 'ext.yiidb2.CIbmDB2Connection',

                  'attributes' => array(

                      'i5_naming' => DB2_I5_NAMING_ON,

                      'i5_lib' => 'HMS'

                  ),

                ),
16

A single long parameter? It’s worth a shot, thanks! :)

Edit: btw, in zend studio when connecting to the machine to view the schemas I see a connection string on properties, it might be useful.


jdbc:as400:HOST
17

Good news everybody! I’ve finally succeeded with my troubles!

Here’s what I did.

1)The File- yiidb2-ibm_db2-beta4.zip

I installed this beta extension of yiidb2 found at http://www.yiiframework.com/ext/files/?id=1193

Beneath the download link you see: "Using php extension ibm_db2"

I believe it’s based upon the ibm db2 functions( the ones I need to access my db2 - http://php.net/manual/en/book.ibm-db2.php)

A big thanks to Edgard Messias for this awesome extension. Hope you keep working on and perfecting it! :lol:

2)My db config looks like this:


'db' => array(

		  'connectionString' => 'ibm:$HOST',

		  'username' => '$UID',

		  'password' => '$PWD',

		  'class' => 'ext.yiidb2.CIbmDB2Connection',

		),

No extra attributes needed, which is awesome.

The way to access schemas is to indicate so within your queries by appending the schema next to the table name. So if my schema is HMS, and I have a table called ITHO inside it, the from part of the querybuilder would look like this: ->from(‘HMS.ITHO’)

3)Now this is where I hit a snag, I could perform Read actions just fine, but got an error with any Create, Update and Delete actions.

After a quick search on the IBM website for my sql error code, "-7008", I found out that Journaling was not enabled on HMS.

So after creating the journal receiver, then creating a journal for my file and finally start the journaling on the ITHO file everything worked fine.

I’ve got full CRUD functionality without needing to specify a port or database name! :D

Hope this helps out whoever gets stuck with the same problem as me.

Big thanks to Edgard Messias and everyone’s suggestions on this topic. Ciao!

18

I thank you to you for being able to improve that my extension.

Any problem can report on GitHub.

I live in Brazil, sorry my english.