Support for DB2 databases?

> list ($success, $outputArray) = $platform->exec(array(array($cmd . $fileName)));

$cmd = "db2cmd /c /w /i db2 -vf C:\WINDOWS\TEMP\db22E77.tmp". 
array(0) { }

> normal exec prepended with cmd /c

$cmd = "cmd.exe /c db2cmd /c /w /i db2 -vf C:\WINDOWS\TEMP\db22E7D.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) "" }

I just noticed something. Look at the path in which the temp file(s) are being created... "C:\WINDOWS\TEMP". Isn't that supposed to be something under "C:\G2DATA"? But then again, it is successfully creating the SQL file in "C:\WINDOWS\TEMP", so perhaps that's not the issue either. I just thought the temp files were supposed to be created under "\g2data".

Another observation:

Even

list ($success, $outputArray) = $platform->exec(array(array('dir', $fileName)));

returns nothing in $outputArray.

Another observation:

I added some debug code in WinNtPlatform::exec, and this doesn't look right:

Why is the temporary file name specified twice? I don't quite understand the foreach loops that construct $command.

I'm calling it exactly the way you posted yesterday.

	    /*
	     * Need to install the JAR file for the java UDF here
	     * because the path to the jar file is variable, so can't be hard-coded in
	     * 'createDb2Udfs.sql'.
	     *
	     * The INSTALL call looks like it's unsupported in CLI (and therefore PHP)
	     * so we have to use (shudder) CLP.
	     *
	     * And DB2 considers the CLP call to be a separate application, so it requires
	     * its own CONNECT and CONNECT RESET.  Which means the userid and password need
	     * to be included in the CLP script.
	     */

	    // 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 $fileName";
	    }
	    else
	    {
	      $cmd = "db2 -vf $fileName";
	    }

	    list ($success, $outputArray) = $platform->exec(array(array($cmd, $fileName)));
printf("<br>Back in Db2DatabaseStorage::configureStore, \$outputArray = ");
var_dump($outputArray);

BTW: I hadn't created a "g2data\tmp". That explains the Windows directory thing.

you don't have to create the tmp dir, it should create that in the storage setup step in the installer.
if it didn't, then there's something wrong.
(that has nothing to do with db2)

it should create these subdirs in g2data:
'albums',
'cache',
'locks',
'sessions',
'tmp',
'plugins_data',
'plugins_data/modules',
'plugins_data/themes',
'smarty',
'smarty/templates_c'

@configureStore code:
ah! i posted alternatives and you used both. sorry, that was a misunderstanding.

either use $fileName in
$cmd = "db2cmd /c /w /i db2 -vf $fileName";

or leave it there out and use it in

list ($success, $outputArray) = $platform->exec(array(array($cmd, $fileName)));

but don't specify it twice.

i recommend:

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

list ($success, $outputArray) = $platform->exec(array(array($cmd, $fileName)));

please run the installer again from scratch (before empty g2data, drop the db). and then post the install.log. thank you!

Ah-haa... when I remove the "cmd /c" it works.

please try:

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

$cmdArgs[] = $fileName;

list ($success, $outputArray) = $platform->exec(array($cmdArgs));

sorry for all this...

this array approach of defining each arg separately is actually how it should be done. sorry, my bad. didn't realize this sooner. on linux, it would have worked without that, but the windows command line needs " " around each arg.

this is the final solution for this cmd fun.

WAD = without a doubt? (my mother tongue isn't English)

@error:
hmm, if we get there an error, is it a platform (filesystem / exec) or a storage (db) failure?
let's use platform error there...

return GalleryStatus::error(ERROR_PLATFORM_FAILURE, __FILE__, __LINE__);

yeah, well, then replace
return GalleryStatus::error(ERROR_PLATFORM_FAILURE, __FILE__, __LINE__);
with
return GalleryStatus::error(ERROR_STORAGE_FAILURE, __FILE__, __LINE__);

OK, I now have a completely working install of DB2. Basic G2 functionality seems fine to me, based on what I did a few days ago (and I haven't changed any SQL since then).

Bear in mind that:

1) This is an implementation of the 'last-resort' CLP solution to the problem of DB2's CLI not handling INSTALL_JAR. If Dan (Scott) is willing to pursue this problem through appropriate channels we can pursue a better solution. (I can't pursue it through formal channels myself because, well, I don't exactly have a license for my copy of DB2 :-O).

2) We're still using DB2's Unified ODBC, not Dan's 'ibm_db2' PHP extension. As a result, the Unit Tests are horribly contaminated with CLI0005W warnings, to the point that it is impossible to make sense of the test results. I'm now going to begin working on developing a driver file for ADOdb that will support Dan's 'ibm_db2' PHP extension, and when it's ready I'll do the migration and revisit the Unit Tests.

My personal suspicion is that even if Dan does agree to pursue the INSTALL_JAR problem, it will probably take many, many months to get a fix for it. Therefore, I'd suggest we checkin the files I have now. I'm attaching them here. The Unit Tests for DB2 will still be broken, but it shouldn't impact any other databases.

If you have no major concerns with this, could you please checkin these changes and let me know when they can be re-confirmed?

Thanks.

ps:
- i rewrite your configureStore code a little to comply with g2 coding standards. so i might have broken it since i can't test it.
- i didn't copy your new createDb2Udfs.sql , i copied the last ... lines (the create g2_bit_or stuff)

@connect / hostname:
yeah, there seem to be 2 methods.
adodb just uses

_connect($argDSN, $argUsername, $argPassword, $argDatabasename)

if ($this->debug && $argDatabasename && $this->databaseType != 'vfp') {
ADOConnection::outp("For odbc Connect(), $argDatabasename is not used. Place dsn in 1st parameter.");
}

odbc_connect($argDSN,$argUsername,$argPassword);

and the 1st argument to _connect is $hostname for other adodb drivers.

Actually, $argDSN is exactly what I'd expect from an ODBC database. The darabase / hostname management is done in the ODBC manager of Windowx. There you create a new data source name and configure it (ser db driver, hostname, db name, ...). that's what i did a couple of years ago to access with MS Access oracle 8i databases.

so we'd have a single input field for DB2 when using the current adodb db2 driver. and we'd call it Data Source Name.

i guess we could also pass the other method to ODBC. we'd have to assemble the DSN-less long string on our own...

what do you plan to do for ibm-db2?

$argDSN can be the long connect string (i guess). but it can also be (and that's the normal usage) just the "data source name", a simple name, which is an ODBC data source name, registered in your windows odbc manager.

no we don't have to support the long DSN-less functionality.

adodb db2 / odbc does nothing special about the the non-DSN form (the long long string). it just passes the userinput to the odbc_connect call.

you can do the same in your adodb ibm-db2 driver.
Maybe let's call it "Database Name / ODBC Data Source Name" in the installer, and you can put additional docs on codex.gallery2.org .

1) ok, fixed.
2) thanks, fixed
3) no. can't follow you there. IMO it's correct. (use the nightly snapshots and not cvs, cvs is 2 weeks old or so)
i'm checking
if (!$success) {
error
}
! means NOT so if i test if (! boolean) then it tests if the boolean value is false and if so, it enters the if block.

4), 5) wow, sorry.

@still problems:
after fixing 4/5, i used an example $outputArray to check the parser and it works...

please try the next nightly:

here's the new section i'm using:

	    /* 2. Execute the command */
	    list ($success, $outputArray) = $platform->exec(array($cmdArgs));

	    /*
	     * 3. Cleanup: Delete the SQL file since it contains the DB username / password and
	     *    it's not needed anymore
	     */
	    @$platform->unlink($fileName);

	    /* 4. Check for successful installation of the UDF */
	    if (!$success) {
		return GalleryStatus::error(ERROR_PLATFORM_FAILURE, __FILE__, __LINE__);
	    }

	    /* We expect a success code (DB20000I) right after the INSTALL_JAR line */
	    $ok = $checkNextLine = false;
	    while ($line = array_shift($outputArray)) {
		if (!$checkNextLine && strpos(strtoupper($line), 'CALL SQLJ.INSTALL_JAR(') === 0) {
		    $checkNextLine = true;
		} else if ($checkNextLine && strpos($line, 'DB20000I ') === 0) {
		    $ok = true;
		    break;
		} else {
		    $checkNextLine = false;
		}
	    }

	    if (!$ok) {
		return GalleryStatus::error(ERROR_STORAGE_FAILURE, __FILE__, __LINE__);
	    }
 

Hmmm, a var_dump($outputArray) shows:

$outputArray = array(16) { [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(128) "CALL SQLJ.INSTALL_JAR('file:C:\My Server\gallery2\modules\core\classes\GalleryStorage\DatabaseStorage\g2_db2.jar', 'g2_db2_jar')" [10]=> string(50) "DB20000I The CALL command completed successfully." [11]=> string(0) "" [12]=> string(0) "" [13]=> string(13) "CONNECT RESET" [14]=> string(49) "DB20000I The SQL command completed successfully." [15]=> string(0) "" }

Note that some lines of output (most notably, the first line) are followed by a 0-byte line. Could that be fooling your array_shift()?

@"CALL SQLJ.REMOVE_JAR()" :

i thought it would just return a DB2 error code line and then advance to the next call and return a db2 success code for the install call.

please verify with the new code that it really doesn't work when the remove call is included in the script. if so, we'll have to remove it from the generated SQL file.

i've already updated the file in cvs with the last changes, but i didn't remove the "call remove ..jar " from the generated sql file.
could you confirm that this is absolutely needed?

ok then, i've removed the remove call.

but that also means that unless you can't use the erase option of the installer. (which would remove the whole installation such that you can re-install just with the installer). in the install core module step, we'd then receive an error, because the install jar error will error out.
unless you find a "install or replace" call, we'd have to copy the generate sql file, execute, parse results for cleanStore() too.

To fix the four UDF removals in cleanStore(), do you have any problems with using dynamic variables, like the following?

       $this->_traceStart();

	$sql1 = 'DROP FUNCTION G2_BIT_AND(CHAR(32), CHAR(32))';
	$sql2 = 'DROP FUNCTION BIT_AND(SMALLINT, SMALLINT)';
	$sql3 = 'DROP FUNCTION G2_LIKE(VARCHAR(4000), VARCHAR(4000))';
	$sql4 = 'DROP FUNCTION G2_BIT_OR(CHAR(32))';

	/*
	 * These SQLJ.*_JAR() calls blow up, looks like CLI can't handle it.
	 * See configureStore() for a CLP workaround.
	 * $recordSet5 = $tmpDb->Execute("CALL SQLJ.REMOVE_JAR ('g2_db2_jar')");
	 */

	for ($ctr = 1; $ctr <= 4; $ctr++)
	{
	    $runMe = "recordSet" . $ctr;
	    $sqlToRun = "sql" . $ctr;
	    ${$runMe} = $tmpDb->Execute("${$sqlToRun}");
	    if (empty(${$runMe})) {
		return GalleryStatus::error(ERROR_STORAGE_FAILURE, __FILE__, __LINE__);
	    }
	}

        $tmpDb->Close();
        $this->_traceStop();

(I haven't tested this yet, wanted to check with you first.)

Yes, much nicer.

I guess you can commit it if you like. I haven't tested it yet, but cleanStore() is already broken anyway.

It's broken because $recordSet1 through $recordSet4 are currently not tested. Only $recordSet is tested, and it was never created.

I screwed this up when I added the DROP FUNCTION statements for my UDFs. Originally there was only $recordSet, but when I added the other UDFs I renamed that to $recordSet1, and incremented each new statement by 1. But I forgot about the if (empty(...)) checking.

I'm trying to test these changes to cleanStore(), but I can't figure out how to invoke that code. I tried the "Start Over" link in the installer, but I don't think it invokes that code.

How can I test cleanStore()?

Hmm... the only options I see are "Redo this step" and "Continue to Step 6".

Sorry, nope... I close my browser and open a new instance, fire up the installer, at Step 5 I take "Redo", click "Save" again, but I still get exactly the same thing... "Redo this step" or "Continue to Step 6".