Support for DB2 databases?

If the file does need to be copied to sqllib, then I was thinking that the Installer could programmatically determine the sqllib location and copy the file there... I wouldn't think we need to ask the user to do the copying.

Any suggestions about my test failures? It's really polluting my results. I ran 974 tests run, and about half of them are successful, but the tests that fail report 8,602 failures... that's about 16 errors per failing test. For example, the test "testChangeValues" is reporting 42 of these CLI0005W "failures". It'll take me days if not weeks to manually sift through all that chaff.

There's something weird here... the test continues to fail no matter what I set error_reporting to. I've tried:

- error_reporting($oldErrorReporting & ~E_WARNING);
- error_reporting($oldErrorReporting & ~E_NOTICE);
- error_reporting($oldErrorReporting & ~E_ERROR);
- error_reporting($oldErrorReporting & ~E_WARNING & ~E_NOTICE & ~E_ERROR);
- error_reporting(E_WARNING);
- error_reporting(E_NOTICE);
- error_reporting(E_ERROR);

and the error persists. I couldn't find an E_NONE or I would have tried it.

I don't know if this is related, but I also see a warning that says my development environment is not correct. However, I'v verified that these setting are correct in my 'php.ini', so this warning is incorrect.

PHP Setting              Actual Value  Expected Value(s)  
short_open_tag                     1           off or 0  
allow_call_time_pass_reference     1           off or 0  
allow_url_fopen                    1           off or 0  
include_path                  .;C:\php5\pear   /bogus

> probably you need to restart the webserver

I have, many times. There originally were other incorrect settings as well, but I've fixed them. These last four however refuse to go away. I don't think they're related to the real problem, I just thought I should mention it.

> also, you should have error_reporting E_ALL in your php.ini

It is already so.

> we should really get rid of the CLI issues

I don't consider this a "CLI issue". Remember, the MS ODBC driver says it doesn't support joins at all (so I'm curious how these joins work in PostgreSQL and MySQL on Windows... does G2 not use the MS ODBC driver?). CLI considers this message a Warning, but PHP seems to insist on reporting it. I'll try to create a simple testcase to demonstrate this, and post it to the DB2 newsgroup.

No (useful) answers to either of the two questions. Will let you know as soon as I hear anything. In the meantime, I might proceed with the "sqlj.install_jar()" solution in case I never get a good answer.

Thanks for letting me know how PostgreSql and MySql work. I should have guessed that because the 'adodb-odbc.php.inc' file wasn't there. ;-)

hmm, so you still get the same error in unit tests even with the READ ONLY appended, just a few errors less?

what's keeping Dan Scott from releasing a adodb-ibm-db2 driver? too low priority?

> still get the same error in unit tests even with the READ ONLY appended, just a few errors less

Yeah, a whopping 6 errors were fixed.

> what's keeping Dan Scott from releasing a adodb-ibm-db2 driver? too low priority?

He hasn't said what the holdup is, but I do have first-hand knowledge of how things are within IBM DB2 development , and I'd say it's probably not a matter of it being a low priority, but there are probably too many other high priorities and not enough resource (money/people) to do them all. I'd guess that the resource they have is being allocated to line items with the most substantial perceived bang for the buck, and I'd guess that they don't see 'ibm-db2' as a big bang-for-the-buck item. My personal opinion only.

> a work in progress driver which we could copy and improve / complete

"we" meaning "me", I see.

This has already taken much longer than I originally inteded to commit to. For the last 4 weeks I've been on the verge of abandoning this; only my stubbornness and my sense of obligation prevent me from doing so.

Since my leaving IBM earlier this year (on terms that were not exactly hostile, but not exactly friendly either) I've already done a fair bit of 'volunteer' work for them, but signing up to complete the 'ibm-db2' driver is way over the line for me. If I had the solid skills and time required, then I might do consider doing so, but I definitely don't have the skills, and I can't guarantee I'll have the time either... I'm currently job-hunting, and if/when I land a job, the time I can devote to G2 will probably be drastically and immediately reduced.

Nope, I tried to add a gallery to it, and got an error. I'll keep testing it regardless of the unit test failures.

BTW, I contacted Dan Scott, and (sort of) offered our services. His response:

As you can see, he is also excited about the prospect of adding DB2 support to Gallery.

I'll let you know when I hear back from him regarding his follow-up.

FYI, here's how it currently looks:

http://cpe0013102da23b-cm0f0079804905.cpe.net.cable.rogers.com/gallery2/main.php?g2_fromNavId=x516279d2

Valiant, could you please confirm for me that you have checked in the changes I sent you on 11/23? If so, I'll get a new build, reconfirm the changes, and send you my latest tweaks.

Thanks.

OK, no prob. I'll keep using my current build.

There's still no response regarding the 'class file absolute path' question. For the time being, I'll implement the jar file solution.

When that's done, I'll resubmit the changes to you.

Question:

In the "call sqlj.install_jar()", we need to specify the URL to the jar file, which means specifying the absolute path to it. Is there a way to reference some existing variable for that in the 'createDb2Udfs.sql' file?

If not, then that "call sqlj.install_jar()" will have to be done in DatabaseStorage::configureStore. In the example you quoted a while ago you said something like:

$path = dirname(__FILE__) . $platform->getDirectorySeparator() . 'g2_db2_bit_or.jar';
execute("call sqlj.install_jar(file://$path, 'g2_db2_jar')");

But for the actual execute(), are you sure I shouldn't use something like:

$ret = $this->_executeSql("call sqlj.install_jar(file://$path, 'g2_db2_jar')");
if ($ret->isError()) {
  return $ret->wrap(__FILE__, __LINE__);
}

Any error checking? I notice there's none in cleanStore().

BTW, you probably already presumed this, but just so you know... only the "sqlj.install_jar()" is going into "db2DatabaseStorage.class"... the "CREATE FUNCTION ()" is staying in "createDb2Udfs.sql". (No variables in it.)

FYI, I've hit a problem with running "CALL SQLJ_INSTALL(...)" from within PHP. I always get error:

Don't know why they consider it a 'warning'... it's definitely fatal.

I can't find any doc about this error, so I've posted a question to the DB2 newsgroup.

Dan has been looking into the 'sqlj.install_jar()' problem with me. I've further narrowed it down. It appears to be happening in DB2's CLI, not in PHP. It occurs even in a C program that uses the CLI api to call that SP.

I'll wait another day or two to see if Dan or anyone in the newsgroup comes up with anything helpful. But I'm starting to get a very bad feeling that we might have to come up with a Plan C. Or D. Or even E. We've hit so many dead-ends that I'm starting to think this just wasn't meant to be.

> db2_execute

Yes, even Dan's 'ibm_db2' extension uses CLI.

> alternatively, manual install

God I hope it doesn't come to that. There's got to be a way to avoid that. I'll keep thinking about it for a while. Hopefully something will work out.

If this turns out to be another bug or limitation in DB2, I'll be really disappointed. If it turns out to be something I'm doing wrong, then I'll be embarrassed but life goes on.

system prints the output, you should use exec anyway. then it won't print the output to the screen. ($platform =& $gallery->getPlatform(); $platform->exec())

nice idea yes, that could work.
but.... exec() can be disabled in PHP. in the system checks step of the installer, we check if exec is available or not. so we'd have to warn stop the user in step 5 (db step) if exec is not available and db2 is selected.
and we also would have to check if db2cmd is available for webserver user. that's a nasty dependency.

- you can identify unix / windows with:
if (GalleryUtilities::isA($platform, 'WinNtPlatform')) {
windows
} else {
unix/linux
}

what SQL file? the command line call you have above doesn't have a SQL file in it...
anyway, we can do that:
we can create the SQL file in the g2data/tmp directory and delete it afterwards.

$fileName = $platform->tempnam($gallery->getConfig('data.gallery.tmp'), 'db2_');
$buf = sprintf('db2cmd /c /w /i db2 -tvf %s %s "%s", $this->_username, $this->_password, $pathToJar);
$platform->atomicWrite($fileName, $buf);

Can we determine programmatically if exec() is disabled? If so, then we could prompt the user with something like:

> still hoping for some good news from Dan Scott / the newsgroups

Definitely agree with that. I'm forging ahead with this CLP stuff as an absolute last-resort solution.

BTW, DB2's CLP can't process multiple commands in a single buffer... they need to be in a file and then run with "-f".

i assume $outputArray is your result from exec, right?

are you sure "DB20000I The CALL command completed successfully." is not internalized / translated in different languages?
if DB20000I is a success code, i'd just check for that (unless you're really sure that the DB2 stuff is not translated.

[code]

if (GalleryUtilities::isA($platform, 'WinNtPlatform')) {
$cmd = "db2cmd /c /w /i db2 -tvf";
} else {
$cmd = "somethingelse"
}

$fileName = $platform->tempnam($gallery->getConfig('data.gallery.tmp'), 'db2_');
list ($success, $results) = $platform->exec(array(array($cmd, $fileName)));
if (!success) {
...return error...;
}
/* array to string */
$result = implode(' ', $results);
if (strpos($results, ' DB20000I ') === false) /* spaces to make sure it's not another return code */
...error handling...
}
/* success! */

...of course you could also do a for or a while loop, but i guess that's fine.

Oh, also...

How do we delete the temporary SQL file? It should be deleted ASAP because it contains a password.

> you must use the $platform->exec

I can't get the ^$$@!*#^ thing to work.

The stack trace in the browser isn't helpful, but in install.log the symptom looks like the CREATE FUNCTION is being run wthout the jar having been installed. So obviously the jar install is failing, but I don't know why.

Install.log says:

2005-11-26 15:55:54 [] Executing: cmd /c " "db2cmd /c /w /i db2 -vf " "C:\WINDOWS\TEMP\db22D8E.tmp" 2> "C:\WINDOWS\TEMP\g2d2D90.tmp" "

But there's noting in that output file:

C:\My Server\gallery2>type C:\WINDOWS\TEMP\g2d2D90.tmp
The system cannot find the file specified.

Code:

list ($success, $outputArray) = $platform->exec(array(array($cmd, $fileName)));
printf("<br>\$success = \"$success\".\n");
printf("<br>\n");
var_dump($outputArray);

Result:

$success = "". 
array(0) { } 

Using Apache 2.049.

Ah, you may have just put me on the road to the answer. My g2data directory is not under my server (therefore not writable by it). Should it be?

Yes it works with regular exec($cmd, $outputArray).

Code:

	    // Create a temporary file to hold the sql
	    $fileName = $platform->tempnam($gallery->getConfig('data.gallery.tmp'), 'db2_');

	    $pathToJar = dirname(__FILE__) . $platform->getDirectorySeparator() . 'g2_db2_bit_or.jar';

	    $buf = sprintf("CONNECT TO gallery2 USER %s USING %s\n
CALL SQLJ.REMOVE_JAR('g2_db2_jar')\n
CALL SQLJ.INSTALL_JAR('file:%s', 'g2_db2_jar')\n
CONNECT RESET", $this->_username, $this->_password, $pathToJar);

	    $platform->atomicWrite($fileName, $buf);

	    if (GalleryUtilities::isA($platform, 'WinNtPlatform'))
	    {
	      //$cmd = "db2cmd /c /w /i db2 -vf ";
	      $cmd = "db2cmd /c /w /i db2 -vf $fileName";
	    }
	    else
	    {
	      //$cmd = "db2 -vf ";
	      $cmd = "db2 -vf $fileName";
	    }

printf ("\$cmd = \"$cmd\".\n");
	    exec($cmd, $outputArray);
//	    list ($success, $outputArray) = $platform->exec(array(array($cmd, $fileName)));

//printf("<br>\$success = \"$success\".\n");
printf("<br>\n");
var_dump($outputArray);

Results:

$cmd = "db2cmd /c /w /i db2 -vf C:\WINDOWS\TEMP\db22DCF.tmp". 
array(21) { [0]=> string(38) " CONNECT TO gallery2 USER g2user USING" [1]=> string(0) "" [2]=> string(34) " Database Connection Information" [3]=> string(0) "" [4]=> string(38) " Database server = DB2/NT 8.2.2" [5]=> string(32) " SQL authorization ID = G2USER" [6]=> string(34) " Local database alias = GALLERY2" [7]=> string(0) "" [8]=> string(0) "" [9]=> string(34) "CALL SQLJ.REMOVE_JAR('g2_db2_jar')" [10]=> string(76) "SQL20201N The install, replace or remove of "G2USER .G2_DB2_JAR" failed as" [11]=> string(40) "the jar name is invalid. SQLSTATE=46002" [12]=> string(0) "" [13]=> string(0) "" [14]=> string(135) "CALL SQLJ.INSTALL_JAR('file:C:\My Server\gallery2\modules\core\classes\GalleryStorage\DatabaseStorage\g2_db2_bit_or.jar', 'g2_db2_jar')" [15]=> string(50) "DB20000I The CALL command completed successfully." [16]=> string(0) "" [17]=> string(0) "" [18]=> string(13) "CONNECT RESET" [19]=> string(49) "DB20000I The SQL command completed successfully." [20]=> string(0) "" }