in the debug output, you'll see the SQL statements used and the error.
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Fri, 2005-10-14 22:56
> have you finished your Db2DatabaseStorage.class?
As far as I can tell, yes. But I can't really know for sure until the thing works 100%, can I?
I've enabled the debug feature, and I see the SQL but I don't see any error info in it. (And there seems to be a formatting problem... missing line returns... makes it harder to read.)
Quote:
Debug Output
getParameter permissions.file for core plugin file_exists(C:\g2data\cache\module/core/0/0/0.inc) file_exists(C:\g2data\cache\module/_all/0/0/GalleryFactoryHelper_loadRegistry.inc) Unimplemented: GalleryUrlGenerator file_exists(C:\g2data\sessions\e81cfadba19e7bb90734d1be036e0186) file_exists(C:\g2data\sessions\e81cfadba19e7bb90734d1be036e0186) getParameter cookie.domain for core plugin getParameter cookie.path for core plugin getParameter default.language for core plugin controller , view Loading plugin core Class not defined, trying to include it. file_exists(C:\My Server\gallery2\modules\core\classes\helpers/../../../../modules/core/module.inc) core plugin successfully instantiated file_exists(C:\g2data\versions.dat) file(C:\g2data\versions.dat, ) getParameter id.anonymousUser for core plugin [1129329370] can't guarantee 5 -- extending! file_exists(C:\g2data\cache\entity/0/0/5.inc) Loading plugin core core plugin successfully instantiated Check the version of the core plugin file_exists(C:\g2data\cache\module/_all/0/0/GalleryPluginHelper_fetchPluginStatus.inc) The version of the core plugin is ok file_exists(C:\My Server\gallery2\modules\core\classes/../../../modules/core/ShowItem.inc) getParameter id.rootAlbum for core plugin file_exists(C:\g2data\cache\entity/0/0/7.inc) For odbc Connect(), gallery2 is not used. Place dsn in 1st parameter.
--------------------------------------------------------------------------------
(db2): SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN (?) [ (0=>'7') ]
--------------------------------------------------------------------------------
file_exists(C:\My Server\gallery2\modules\core\classes\helpers/../../../../modules/core/classes/GalleryAlbumItem.class)
--------------------------------------------------------------------------------
(db2): SELECT g2_Entity.g_id, g2_Entity.g_creationTimestamp, g2_Entity.g_isLinkable, g2_Entity.g_linkId, g2_Entity.g_modificationTimestamp, g2_Entity.g_serialNumber, g2_Entity.g_entityType, g2_Entity.g_onLoadHandlers, g2_ChildEntity.g_parentId, g2_FileSystemEntity.g_pathComponent, g2_Item.g_canContainChildren, g2_Item.g_description, g2_Item.g_keywords, g2_Item.g_ownerId, g2_Item.g_summary, g2_Item.g_title, g2_Item.g_viewedSinceTimestamp, g2_Item.g_originationTimestamp, g2_AlbumItem.g_theme, g2_AlbumItem.g_orderBy, g2_AlbumItem.g_orderDirection FROM g2_Entity, g2_ChildEntity, g2_FileSystemEntity, g2_Item, g2_AlbumItem WHERE g2_Entity.g_id IN (?) AND g2_ChildEntity.g_id=g2_Entity.g_id AND g2_FileSystemEntity.g_id=g2_Entity.g_id AND g2_Item.g_id=g2_Entity.g_id AND g2_AlbumItem.g_id=g2_Entity.g_id [ (0=>'7') ]
--------------------------------------------------------------------------------
getParameter default.theme for core plugin Loading plugin matrix Class not defined, trying to include it. file_exists(C:\My Server\gallery2\modules\core\classes\helpers/../../../../themes/matrix/theme.inc) matrix plugin successfully instantiated Check the version of the matrix plugin file_exists(C:\g2data\cache\theme/_all/0/0/GalleryPluginHelper_fetchPluginStatus.inc) The version of the matrix plugin is ok Loading plugin core core plugin successfully instantiated Check the version of the core plugin The version of the core plugin is ok file_exists(C:\My Server\gallery2\modules\core\classes/../../../modules/core/ErrorPage.inc) getParameter id.adminGroup for core plugin
--------------------------------------------------------------------------------
(db2): SELECT g2_UserGroupMap.g_userId FROM g2_UserGroupMap WHERE g2_UserGroupMap.g_userId = ? AND g2_UserGroupMap.g_groupId = ? FETCH FIRST 1 ROWS ONLY [ (0=>'5') (1=>'3') ]
--------------------------------------------------------------------------------
realpath(C:\My Server\gallery2\modules\core\classes/../../../) file_exists(C:\My Server\gallery2\modules\core\classes/../../../lib/javascript/local/BlockToggle.js) file_exists(C:\g2data\smarty\templates_c\%%4276933715) is_dir(C:\g2data\smarty\templates_c\%%4276933715) file_exists(C:\g2data\smarty\templates_c\%%4276933715) is_writeable(C:\g2data\smarty\templates_c\%%4276933715) file_exists(C:\g2data\smarty\templates_c\%%4276933715/v_8) file_exists(C:\My Server\gallery2\modules\core\classes/../../../themes/matrix/local/theme.css)
When I removed that debug feature and added my own debug code:
Quote:
/* Execute the query */
$GLOBALS['ADODB_FETCH_MODE'] = ADODB_FETCH_NUM;
and it appears that $recordSet->RecordCount() is 0.
Quote:
$query = "SELECT g2_Entity.g_id, g2_Entity.g_creationTimestamp, g2_Entity.g_isLinkable, g2_Entity.g_linkId, g2_Entity.g_modificationTimestamp, g2_Entity.g_serialNumber, g2_Entity.g_entityType, g2_Entity.g_onLoadHandlers, g2_ChildEntity.g_parentId, g2_FileSystemEntity.g_pathComponent, g2_Item.g_canContainChildren, g2_Item.g_description, g2_Item.g_keywords, g2_Item.g_ownerId, g2_Item.g_summary, g2_Item.g_title, g2_Item.g_viewedSinceTimestamp, g2_Item.g_originationTimestamp, g2_AlbumItem.g_theme, g2_AlbumItem.g_orderBy, g2_AlbumItem.g_orderDirection FROM g2_Entity, g2_ChildEntity, g2_FileSystemEntity, g2_Item, g2_AlbumItem WHERE g2_Entity.g_id IN (?) AND g2_ChildEntity.g_id=g2_Entity.g_id AND g2_FileSystemEntity.g_id=g2_Entity.g_id AND g2_Item.g_id=g2_Entity.g_id AND g2_AlbumItem.g_id=g2_Entity.g_id". array(1) { [0]=> int(7) }
$targetIds = "". $recordSet->RecordCount() = 0.
sizeof($targetIds) = 1.
Yet I've confirmed that that query does return 1 record (from a command-line connection).
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Fri, 2005-10-14 23:20
php hint: printf("<br>\$targetIds = \"%s\".\n", var_dump($targetIds));
doesn't work since var_dump prints directly to the browser, it doesn't return a string.
@error:
hmm. so the error must be somewhere in adodb i guess. darn.
files to debug are:
function &Execute and _Execute in lib/adodb/adodb.inc.php
function ADORecordSet_db2 in lib/adodb/drivers/adodb-db2.inc.php
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Sat, 2005-10-15 00:39
By plugging in various printf()s all I can do is confirm that in the "&_Execute" function of "adodb.ini.php", immediately after the query is executed, "$rs->_numOfRows" is 0.
Sorry, but this is now significantly over my head. I can't take this debugging any further. As I said before, I'm not a hard-core developer. And I've just hit the limit of my skill.
bharat
Joined: 2002-05-21
Posts: 7994
Posted: Sat, 2005-10-15 18:06
It looks like you've made a lot of progress! Let's start by committing what you have so that we can have a checkpoint. Can you provide us with a copy of everything that you've changed?
I can try to install DB2 on one of my windows boxes to see if I can debug this further. Is the Express version ok? If not, which do you recommend?
(However, I did not include the "adodb-db2.inc.php" and "adodb-odbc.inc.php" files from the "adodb" package. You might already have them, or you can grab them from the "ADOdb" package.)
I'm not that familiar with the "Express" version of DB2, but I'm pretty sure it should have everything you need (i.e., the DB2 client and server components). (I happen to have "Enterprise Server Edition".)
You shouldn't need to use Windows for this... if it's more convenient for you to use Linux or UNIX that should work too.
If for whatever reason you cannot get the DB2 server running on your system, all you really need is the client component, and I can show you how you can set it up to access the database that resides on my server.
Let me know if you have any questions about what I've done, or DB2 in general.
bharat
Joined: 2002-05-21
Posts: 7994
Posted: Wed, 2005-10-19 07:10
Sorry for the delay! I've been busy refactoring our SQL generation code to generate a single file for all of our SQL instead of many separate files. I didn't want to commit your stuff first because it would result in 60+ new files that I'd just wind up deleting after my refactor so I waited until my code was in first.
So now the good news is that I've committed my change and then merged yours in on top of it. We now have a DB2 option in the installer and generated DB2 SQL in all the various modules. I've marked it all as experimental so that people don't think that it works. I also tweaked the algorithm that you were using to create the table and index names a little bit; check out what I've done in generate-sql.php.
I haven't yet installed DB2; that's next on my list. I'll get it up and running and then start fiddling with the code to see what snags I hit. In the meantime, if you want to cvs up and try out the latest code and make sure that you at least get as far as you did before, that'd be a big help. When you update you're going to get a lot of conflicts because I've now committed files with the same names as the files that you created in your G2. You might want to undo all your changes in your client (you've still got them in the ZIP file so you won't lose anything) before doing the cvs update. Just deleting the files is enough; cvs will restore them when you update.
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Wed, 2005-10-19 14:55
Hi Bharat.
I just renamed my entire Gallery2 directory and got a fresh copy of the whole thing, and dropped & recreated a fresh database.
The Installer didn't get as far as it used to. In Step 8 I now get an error that the SCHEMA table doesn't exist.
At the top of the page is message:
Quote:
Notice: Undefined index: Schema in C:\My Server\gallery2\modules\core\classes\GalleryStorage\DatabaseStorageExtras.class on line 1192
The stack trace:
Quote:
Error (ERROR_STORAGE_FAILURE)
in modules\core\classes\GalleryStorage\DatabaseStorage.class at line 305 (GalleryStatus::error)
in modules\core\classes\GalleryStorage.class at line 250 (DatabaseStorage::search)
in modules\core\classes\Gallery.class at line 223 (GalleryStorage::search)
in modules\core\classes\helpers\GalleryPluginHelper_simple.class at line 474 (Gallery::search)
in modules\core\classes\helpers\GalleryPluginHelper_simple.class at line 345 (GalleryPluginHelper_simple::fetchPluginList)
in modules\core\classes\GalleryCoreApi.class at line 276 (GalleryPluginHelper_simple::fetchPluginStatus)
in modules\core\classes\GalleryPlugin.class at line 175 (GalleryCoreApi::fetchPluginStatus)
in modules\core\classes\GalleryModule.class at line 357 (GalleryPlugin::activate)
in install\steps\InstallCoreModuleStep.class at line 136 (GalleryModule::activate)
in install\index.php at line 167 (InstallCoreModuleStep::loadTemplateData)
Install.log is attached here.
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Tue, 2005-11-01 17:44
Hi Larry, sorry, I've forgotten this topic.
So the current state is that we have this db2 support in g2, but it doesn't work. Not even installing works. But installing with the DB2 changes you had yourself worked.
Can you CVS update and in said file C:\My Server\gallery2\modules\core\classes\GalleryStorage\DatabaseStorageExtras.class , add var_dump($moduleSql); before
$ret = $this->_executeSql($moduleSql['table']['Schema']); around line 1193?
bharat
Joined: 2002-05-21
Posts: 7994
Posted: Tue, 2005-11-01 19:37
I've been totally slammed with other stuff and haven't been able to circle back to this either. I downloaded DB2 Express for Windows (400MB!) and installed it, but ran into snags when creating the database. I think that Express is not explicitly client/server but it's hard to tell from their documentation which is alternately far too thin or far too heavy. Larry, if you can give me a way to connect to your database from my PHP server that might be the best way for me to try to resolve this from my side. Let me know if that works for you.
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Wed, 2005-11-02 04:01
Hi, Bharat and Valiant.
Today I had a rude surprise from my ISP (Rogers Cable)... I think they've discovered the servers I've been running (my web server and DB2 server) and have slammed the door on them. I guess running servers is not allowed for residential users like me. So, the end result of that is that my DB2 server is no longer able to support clients from outside of my own LAN.
Which leaves us three options:
1) I can CVS update my changes (which I'm not particularly comfortable with), or
2) I can try to debug the set of files that have both Bharat's and my changes, or
3) I can try to help you get your DB2 installation working.
If you want to go for #3, let me know exactly what symptoms you are getting, and your platform info. Personally, I think the better option is #2 (despite the fact that I am now rather short on time these days... I'm job hunting).
Let me know which option you think is best.
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Wed, 2005-11-02 05:43
Some good news for a change:
I got a whole new image today, and now am able to get through to the "Go to my Gallery" step. But at that point I get the following errors:
Quote:
Error (ERROR_MISSING_OBJECT) :
in modules\core\classes\GalleryStorage\DatabaseStorageExtras.class at line 394 (GalleryStatus::error)
in modules\core\classes\GalleryStorage\DatabaseStorage.class at line 343 (DatabaseStorageExtras::loadEntities)
in modules\core\classes\GalleryStorage.class at line 123 (DatabaseStorage::loadEntities)
in modules\core\classes\helpers\GalleryEntityHelper_simple.class at line 82 (GalleryStorage::loadEntities)
in modules\core\classes\GalleryCoreApi.class at line 2187 (GalleryEntityHelper_simple::loadEntitiesById)
in modules\core\classes\GalleryView.class at line 388 (GalleryCoreApi::loadEntitiesById)
in modules\core\classes\GalleryView.class at line 246 (GalleryView::_getItem)
in main.php at line 335 (GalleryView::doLoadTemplate)
in main.php at line 88
in main.php at line 81
Which is pretty much where I got to on my own build tree without Bharat's changes.
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Wed, 2005-11-02 05:48
Sorry, forgot to attach the install.log to the previous posting.
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Wed, 2005-11-02 12:43
bharat will have to answer the 1, 2 or 3 question, i try to help you debugging meanwhile.
the only remarkable error from your install.log:
2005-11-02 00:40:55 [<no session id>] <hr>
(db2): UPDATE g2_PluginMap SET g_active=? WHERE g_pluginType=? AND g_pluginId=? <code>[ (0=>'0') (1=>'module') (2=>'core') ]</code>
<hr>
<br />
<b>Warning</b>: odbc_execute() [<a href='function.odbc-execute'>function.odbc-execute</a>]: SQL error: [IBM][CLI Driver][DB2/NT] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000
, SQL
and a second of that kind right after it.
in my mysql based install.log, the same line looks like this:
2005-10-26 00:50:53 [<no session id>] <hr>
(mysqlt): DELETE FROM g2_FactoryMap WHERE g_implModuleId='core' <code></code>
<hr>
no error. but maybe that's ok. since the error was not sent upstream / ignored.
@main.php Error (ERROR_MISSING_OBJECT) :
please check your current database tables.
in g2_Entity you should have a few rows, in g2_Item, g2_AlbumItem you should have a single row with the same g_id (probably g_id = 7).
for debugging, please enable buffered debug mode in config.php
(change $gallery->setDebug(false) to $gallery->setDebug('buffered');)
and then you'll get details for the Error (ERROR_MISSING_OBJECT) .
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Wed, 2005-11-02 15:44
Thanks, Valiant.
OK, I enabled the debugging and repro'd the error. I'm attaching files with the debug info.
And I've verified that the tables do contain the rows you said should be there.
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Wed, 2005-11-02 16:01
could you please check for the following query what it returns when run in a sql shell?
SELECT g2_Entity.g_id, g2_Entity.g_creationTimestamp, g2_Entity.g_isLinkable, g2_Entity.g_linkId, g2_Entity.g_modificationTimestamp, g2_Entity.g_serialNumber, g2_Entity.g_entityType, g2_Entity.g_onLoadHandlers, g2_ChildEntity.g_parentId, g2_FileSystemEntity.g_pathComponent, g2_Item.g_canContainChildren, g2_Item.g_description, g2_Item.g_keywords, g2_Item.g_ownerId, g2_Item.g_summary, g2_Item.g_title, g2_Item.g_viewedSinceTimestamp, g2_Item.g_originationTimestamp, g2_AlbumItem.g_theme, g2_AlbumItem.g_orderBy, g2_AlbumItem.g_orderDirection FROM g2_Entity, g2_ChildEntity, g2_FileSystemEntity, g2_Item, g2_AlbumItem WHERE g2_Entity.g_id IN ('7') AND g2_ChildEntity.g_id=g2_Entity.g_id AND g2_FileSystemEntity.g_id=g2_Entity.g_id AND g2_Item.g_id=g2_Entity.g_id AND g2_AlbumItem.g_id=g2_Entity.g_id;
if it doesn't return a single row, then that's the problem.
there should be a row for g_id = 7 in tables g2_Entity (g_id = 7), g2_ChildEntity (g_id = 7, parent = 0), g2_FileSystemEntity (g_id =7), g2_Item ( idem) , g2_AlbumItem (idem)
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Wed, 2005-11-02 16:16
Ah-haa... that query was failing with a datatype mismatch in the IN clause. I changed:
Quote:
WHERE g2_Entity.g_id IN ('7')
to
Quote:
WHERE g2_Entity.g_id IN (7)
and the query now returns the single row.
In which source file is this code?
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Thu, 2005-11-03 01:24
oh, that's not good.
since we're using question marks as markers for data in our queries. e.g.
$statement = "SELECT foo from bar WHERE baz = ?" and then we add
$data = 7;
and call adodb with:
$db->Execute($$statement, $data); and adodb replaces the markers (?) with the actual data ('7');
but adodb only adds quotes around the value if the data type of the value is "string".
see line 818 of lib/adodb/adodb.inc.php
so the question is: why is "7" a string and not a number? i guess 7 comes in this case from the table g2_pluginparametermap column g_value which is a varchar column.
this could explain it.
obviously DB2 has problems with that.
- can you change a DB2 configuration to accept '7' as value for numeric columns?
- else i guess we have to add a Execute function Execute($query, $data=array()) in our Db2DatabaseStorage.class
which does
for ($i = 0; $i < size($data); $i++) {
if (is_numeric($data[$i])) {
$data[$i] = doubleval($data[$i]);
}
}
return parent::Execute($query, $data);
bharat
Joined: 2002-05-21
Posts: 7994
Posted: Thu, 2005-11-03 01:58
Hm. I think (but I'm not sure) that ADOdb is typing the value based on what it is in PHP, not what the database schema expects. This means that you may be able to fix this by editing GalleryView.class and forcing the item id to be an integer. Look in modules/core/GalleryView.class around line 385 and change:
/* If we still don't have an itemId, default to the root album */
if (empty($itemId)) {
list ($ret, $itemId) =
GalleryCoreApi::getPluginParameter('module', 'core', 'id.rootAlbum');
if ($ret->isError()) {
return array($ret->wrap(__FILE__, __LINE__), null);
}
}
to:
/* If we still don't have an itemId, default to the root album */
if (empty($itemId)) {
list ($ret, $itemId) =
GalleryCoreApi::getPluginParameter('module', 'core', 'id.rootAlbum');
if ($ret->isError()) {
return array($ret->wrap(__FILE__, __LINE__), null);
}
$itemId = (int)$itemId;
}
Note the one added line in the new version where we force $itemId to be an integer. If that works let us know and we'll commit it.
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Thu, 2005-11-03 02:08
sure, that's the same fix in another file of g2.
what i tried to explain is:
g2 get's the value "7" from the database, since it selects the root album id from the pluginparametermap table. so mysql/adodb return a string "7" since the value field of that table is varchar.
then in the next select query, we need "7" as an integer and not as a string since DB2 has problems with that.
your fix for getItemId would fix this single issue. but since DB2 doesn't accept strings for numerical db columns in general, we should fix it in the Db2DatabaseStorage class, IMO.
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Thu, 2005-11-03 02:29
1) DB2 considers anything in quotes to be string, never numeric. That's not configurable.
2) I made Bharat's suggested change to GalleryView.class, but it doesn't seem to have helped. Looks like the exact same error to me (output files attached here, for your confirmation).
Shall I go ahead and try Valiant's change to Db2DatabaseStorage.class? If I do, are there are corequisite changes?
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Thu, 2005-11-03 13:02
what i find strange is that
SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN ('7');
seems to have worked, else we wouldn't know the entityType which is needed for the next query, which is the one that fails.
does DB2 have query logging? then you could see in the DB2 logs what actually was queried (else we could look at adodb logs).
i just found out that my change wouldn't work without other changes. and maybe it's also not the right place for such a change. if we change just everything numeric to a int/double type, then DB2 may have problems if we insert a int into a text column.
so maybe we'd have to fix this in a more general way, e.g. compare data type and target column type etc.
but for now, i'd change it on the application level, as bharat indicated. might be easier for now.
so forget my change and open modules/core/classes/GalleryStorage/DatabaseStorageExtras.class
replace
function loadEntities($ids) {
global $gallery;
if (empty($this->_ds->_db)) {
return array(GalleryStatus::error(ERROR_STORAGE_CONNECTION, __FILE__, __LINE__), null);
}
with
function loadEntities($ids) {
global $gallery;
if (empty($this->_ds->_db)) {
return array(GalleryStatus::error(ERROR_STORAGE_CONNECTION, __FILE__, __LINE__), null);
}
/* Convert PHP type of the $ids if necessary */
for ($i = 0; $i < sizeof($ids); $i++) {
$ids[$i] = intval($ids[$i]);
}
this will fix this issue. but i'm quite sure there are others. so a solution on db abstraction level might be better.
ps: corequisite, thanks, learned a new word today
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Thu, 2005-11-03 15:30
Are you sure that query worked? When I run it from a command line:
Quote:
C:\My Server\gallery2>db2 "SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN ('7')"
SQL0401N The data types of the operands for the operation "IN" are not compatible. SQLSTATE=42818
DB2 has transaction logging, but I'm not sure that would be helpful.
I've made the change to DatabaseStorageExtras.class, but it still looks like the same error is happening. (Files attached.)
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Thu, 2005-11-03 16:42
If it helps, I've taken a DB2 CLI (Call Level Interface) trace (CLI is DB2's own version of ODBC). It contains all SQL statements that went through the CLI interface (as does PHP/ODBC). There's a lot of info, it may or may not be intuitive. ;-)
I've zipped the file and renamed it to ".txt" do I can attach it here.
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Thu, 2005-11-03 16:46
i wonder why:
- we don't get the same error (SQL0401N) in our logs
- you get "(db2): SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN (?) [ (0=>'7') ] " in your g2 log while i get:
"(mysqlt): SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN (7)" that is, for mysql it logs the final query and for db2 it logs the query and the data separately. that's because odbc / db2 can prepare statements / bind data. but the '7' thing is the problem.
edit: i looked at the adodb sourcecode and this 0=>'7' thing is ok. the quotes are generated for all types. it's just debugging code.
well, as you see from my log, with mysql, it's no IN (7) and not IN ('7') anymore with my fix. thus i wonder what happened in your case. maybe the problem isn't G2 or adodb
could you please clear the cache with /lib/suppot/ -> cache management -> clear all caches.
and then try again?
no need to reinstall G2 / no need for the install.log.
please enable transaction logging and look at the final queries that are actually executed. it would be interesting if they show the final queries where the markers have been replaced with the data.
maybe check the whole thing with a minimalistic script:
/********************************************************
* Set up a transactional db connection
********************************************************/
error_reporting(E_ALL);
define('MYADODBPATH', '/path/to/gallery2/lib/adodb/');
require_once(MYADODBPATH . 'adodb.inc.php');
$db =& ADONewConnection('db2');
if (empty($db)) {
die('failed to get an adodb db object');
}
$ret = $db->NConnect('localhost','root','password', 'gallery2'); // host, user, pw, dbname
if (!$ret) {
die('failed to connect to the database');
}
$recordSet = $db->Execute('SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN (?)', array(7));
if (!($data = $recordSet->FetchRow()) || !is_array($data)) {
die('select query failed');
}
var_dump($data);
another idea:
maybe db2 doesn't accept ? as a marker for data bindings. googled: ? is fine.
yet another idea:
maybe we have to specify the type of the bind variables else it defaults to char?
no idea...
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Thu, 2005-11-03 17:27
I'm afraid I don't quite understand your instructions about clearing the cache, sorry.
I posted the CLI trace, but interpreting that is a real art in itself. I'm still looking into a user-friendly way to capture the SQL.
I ran the minimalistic script, and it seems to work:
Question marks are definitely valid parameter markers in DB2.
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Thu, 2005-11-03 17:33
Ah, I realized what you meant by clearing the cache. I've done so, and the error still occurs, though the debug info is much shorter:
Quote:
Error
An error has occurred.
Back to the Gallery
Error Detail -
Error (ERROR_MISSING_OBJECT) :
in modules\core\classes\GalleryStorage\DatabaseStorageExtras.class at line 399 (GalleryStatus::error)
in modules\core\classes\GalleryStorage\DatabaseStorage.class at line 343 (DatabaseStorageExtras::loadEntities)
in modules\core\classes\GalleryStorage.class at line 123 (DatabaseStorage::loadEntities)
in modules\core\classes\helpers\GalleryEntityHelper_simple.class at line 82 (GalleryStorage::loadEntities)
in modules\core\classes\GalleryCoreApi.class at line 2187 (GalleryEntityHelper_simple::loadEntitiesById)
in modules\core\classes\GalleryView.class at line 388 (GalleryCoreApi::loadEntitiesById)
in modules\core\classes\GalleryView.class at line 246 (GalleryView::_getItem)
in main.php at line 335 (GalleryView::doLoadTemplate)
in main.php at line 88
in main.php at line 81
Debug Output
getParameter permissions.file for core plugin file_exists(C:\g2data\cache\module/core/0/0/0.inc) file_exists(C:\g2data\cache\module/_all/0/0/GalleryFactoryHelper_loadRegistry.inc) Unimplemented: GalleryUrlGenerator file_exists(C:\g2data\sessions\bb4ec10f520abafdab91a48114a98617) file_exists(C:\g2data\sessions\bb4ec10f520abafdab91a48114a98617) getParameter cookie.domain for core plugin getParameter cookie.path for core plugin getParameter default.language for core plugin controller , view Loading plugin core Class not defined, trying to include it. file_exists(C:\My Server\gallery2\modules\core\classes\helpers/../../../../modules/core/module.inc) core plugin successfully instantiated file_exists(C:\g2data\versions.dat) file(C:\g2data\versions.dat, ) getParameter id.anonymousUser for core plugin [1131039065] can't guarantee 5 -- extending! file_exists(C:\g2data\cache\entity/0/0/5.inc) Loading plugin core core plugin successfully instantiated Check the version of the core plugin file_exists(C:\g2data\cache\module/_all/0/0/GalleryPluginHelper_fetchPluginStatus.inc) The version of the core plugin is ok file_exists(C:\My Server\gallery2\modules\core\classes\helpers/../../../../modules/core/ShowItem.inc) getParameter id.rootAlbum for core plugin file_exists(C:\g2data\cache\entity/0/0/7.inc) For odbc Connect(), gallery2 is not used. Place dsn in 1st parameter.
--------------------------------------------------------------------------------
(db2): SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN (?) [ (0=>'7') ]
--------------------------------------------------------------------------------
file_exists(C:\My Server\gallery2\modules\core\classes\helpers/../../../../modules/core/classes/GalleryAlbumItem.class)
--------------------------------------------------------------------------------
(db2): SELECT g2_Entity.g_id, g2_Entity.g_creationTimestamp, g2_Entity.g_isLinkable, g2_Entity.g_linkId, g2_Entity.g_modificationTimestamp, g2_Entity.g_serialNumber, g2_Entity.g_entityType, g2_Entity.g_onLoadHandlers, g2_ChildEntity.g_parentId, g2_FileSystemEntity.g_pathComponent, g2_Item.g_canContainChildren, g2_Item.g_description, g2_Item.g_keywords, g2_Item.g_ownerId, g2_Item.g_summary, g2_Item.g_title, g2_Item.g_viewedSinceTimestamp, g2_Item.g_originationTimestamp, g2_AlbumItem.g_theme, g2_AlbumItem.g_orderBy, g2_AlbumItem.g_orderDirection FROM g2_Entity, g2_ChildEntity, g2_FileSystemEntity, g2_Item, g2_AlbumItem WHERE g2_Entity.g_id IN (?) AND g2_ChildEntity.g_id=g2_Entity.g_id AND g2_FileSystemEntity.g_id=g2_Entity.g_id AND g2_Item.g_id=g2_Entity.g_id AND g2_AlbumItem.g_id=g2_Entity.g_id [ (0=>'7') ]
--------------------------------------------------------------------------------
getParameter default.theme for core plugin Loading plugin matrix Class not defined, trying to include it. file_exists(C:\My Server\gallery2\modules\core\classes\helpers/../../../../themes/matrix/theme.inc) matrix plugin successfully instantiated Check the version of the matrix plugin file_exists(C:\g2data\cache\theme/_all/0/0/GalleryPluginHelper_fetchPluginStatus.inc) The version of the matrix plugin is ok Loading plugin core core plugin successfully instantiated Check the version of the core plugin The version of the core plugin is ok file_exists(C:\My Server\gallery2\modules\core\classes\helpers/../../../../modules/core/ErrorPage.inc) getParameter id.adminGroup for core plugin
--------------------------------------------------------------------------------
(db2): SELECT g2_UserGroupMap.g_userId FROM g2_UserGroupMap WHERE g2_UserGroupMap.g_userId = ? AND g2_UserGroupMap.g_groupId = ? FETCH FIRST 1 ROWS ONLY [ (0=>'5') (1=>'3') ]
--------------------------------------------------------------------------------
realpath(C:\My Server\gallery2\modules\core\classes/../../../) file_exists(C:\My Server\gallery2\modules\core\classes/../../../lib/javascript/local/BlockToggle.js) file_exists(C:\g2data\smarty\templates_c\%%4276933715) is_dir(C:\g2data\smarty\templates_c\%%4276933715) file_exists(C:\g2data\smarty\templates_c\%%4276933715) is_writeable(C:\g2data\smarty\templates_c\%%4276933715) file_exists(C:\g2data\smarty\templates_c\%%4276933715/v_8) file_exists(C:\My Server\gallery2\modules\core\classes/../../../themes/matrix/local/theme.css)
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Thu, 2005-11-03 18:01
the mini script, does it still work when exchanging
$recordSet = $db->Execute('SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN (?)', array(7));
with
$recordSet = $db->Execute('SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN (?)', array("7"));
?
and what about running the mini script with
$statement = 'SELECT g2_Entity.g_id, g2_Entity.g_creationTimestamp, g2_Entity.g_isLinkable, g2_Entity.g_linkId, g2_Entity.g_modificationTimestamp, g2_Entity.g_serialNumber, g2_Entity.g_entityType, g2_Entity.g_onLoadHandlers, g2_ChildEntity.g_parentId, g2_FileSystemEntity.g_pathComponent, g2_Item.g_canContainChildren, g2_Item.g_description, g2_Item.g_keywords, g2_Item.g_ownerId, g2_Item.g_summary, g2_Item.g_title, g2_Item.g_viewedSinceTimestamp, g2_Item.g_originationTimestamp, g2_AlbumItem.g_theme, g2_AlbumItem.g_orderBy, g2_AlbumItem.g_orderDirection FROM g2_Entity, g2_ChildEntity, g2_FileSystemEntity, g2_Item, g2_AlbumItem WHERE g2_Entity.g_id IN (?) AND g2_ChildEntity.g_id=g2_Entity.g_id AND g2_FileSystemEntity.g_id=g2_Entity.g_id AND g2_Item.g_id=g2_Entity.g_id AND g2_AlbumItem.g_id=g2_Entity.g_id';
For odbc Connect(), gallery2 is not used. Place dsn in 1st parameter.
--------------------------------------------------------------------------------
(db2): SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN (?) [ (0=>'7') ]
what about the debug output
Quote:
For odbc Connect(), gallery2 is not used. Place dsn in 1st parameter.
?
is that related to our problem or is that just a notice? yeah, we have discussed this, but is it a problem?
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Thu, 2005-11-03 19:05
1) $recordSet = $db->Execute('SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN (?)', array("7"));
Nope, fails with "select query failed" with both values for $data.
3) "Place dsn in first parameter"
I believe this is bogus, because we are obviously able to connect to the database. I think the adodb files print this out in any DB2 case, regardless if the args are already in the right order.
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Thu, 2005-11-03 20:31
1) aha
2) aha! so it's not the data! it's the query itself.
can you also try (with the mini script):
$statement = 'SELECT g2_Entity.g_id, g2_Entity.g_creationTimestamp, g2_Entity.g_isLinkable, g2_Entity.g_linkId, g2_Entity.g_modificationTimestamp, g2_Entity.g_serialNumber, g2_Entity.g_entityType, g2_Entity.g_onLoadHandlers, g2_ChildEntity.g_parentId, g2_FileSystemEntity.g_pathComponent, g2_Item.g_canContainChildren, g2_Item.g_description, g2_Item.g_keywords, g2_Item.g_ownerId, g2_Item.g_summary, g2_Item.g_title, g2_Item.g_viewedSinceTimestamp, g2_Item.g_originationTimestamp, g2_AlbumItem.g_theme, g2_AlbumItem.g_orderBy, g2_AlbumItem.g_orderDirection FROM g2_Entity, g2_ChildEntity, g2_FileSystemEntity, g2_Item, g2_AlbumItem WHERE g2_Entity.g_id IN ("7") AND g2_ChildEntity.g_id=g2_Entity.g_id AND g2_FileSystemEntity.g_id=g2_Entity.g_id AND g2_Item.g_id=g2_Entity.g_id AND g2_AlbumItem.g_id=g2_Entity.g_id';
// no data
$recordSet = $db->Execute($statement);
and also with IN (7) instead of IN ("7")
if both work and return a single row, then it's definitely the odbc binding that is the problem, and not the data.
maybe we should use DB2 adodb drivers and change a single line in them
adodb-db2.inc.php
var $_bindInputArray = false; instead of var $_bindInputArray = true;
but please don't change that before trying the above 2 selects.
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Thu, 2005-11-03 20:51
Hmmm... neither of them work:
1) IN ("7"):
Quote:
Fatal error: Call to a member function FetchRow() on a non-object in C:\My Server\test.php on line 43
2) IN (7):
Quote:
select query failed
I suspect that's not what you were hoping for...
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Thu, 2005-11-03 20:56
Ah, but in the 2nd case, the error msg is being produced because $data is no longer being set.
$statement = 'SELECT g2_Entity.g_id, g2_Item.g_description FROM g2_Entity, g2_Item WHERE g2_Entity.g_id IN (7)';
This query does not:
Quote:
$statement = 'SELECT g2_Entity.g_id FROM g2_Entity, g2_Item WHERE g2_Entity.g_id IN (7)';
Note the only difference is that I'm including a 2nd column in the result set.
When I replace the 2nd column and join table from the Item table with ones from the ChildEntity table:
Quote:
$statement = 'SELECT g2_Entity.g_id, g2_ChildEntity.g_parentId FROM g2_Entity, g2_ChildEntity WHERE g2_Entity.g_id IN (7)';
It once again works.
Then, when when I take the full original query and remove only the Item table references, it once again works (returns a result set).
So it looks to me like the problem is specific to the Item table?
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Thu, 2005-11-03 23:51
Larry Menard wrote:
OK, now this is interesting.
This query returns a result set:
Quote:
$statement = 'SELECT g2_Entity.g_id, g2_Item.g_description FROM g2_Entity, g2_Item WHERE g2_Entity.g_id IN (7)';
this query doesn't make sense since you don't link the two tables (AND g2_Item.g_id = g2_Entity.g_id)
Quote:
This query does not:
Quote:
$statement = 'SELECT g2_Entity.g_id FROM g2_Entity, g2_Item WHERE g2_Entity.g_id IN (7)';
same here
Quote:
Note the only difference is that I'm including a 2nd column in the result set.
When I replace the 2nd column and join table from the Item table with ones from the ChildEntity table:
Quote:
$statement = 'SELECT g2_Entity.g_id, g2_ChildEntity.g_parentId FROM g2_Entity, g2_ChildEntity WHERE g2_Entity.g_id IN (7)';
It once again works.
Then, when when I take the full original query and remove only the Item table references, it once again works (returns a result set).
So it looks to me like the problem is specific to the Item table?
interesting
Quote:
So it looks to me like the problem is specific to the Item table?
hmm, that doesn't explain why the first query works.
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Fri, 2005-11-04 00:15
I think that whether they are logical or not is irrelevant. They are syntactically correct and should all return result sets. They all do return result sets when issued from the DB2 command prompt.
I'm going to try to run those same queries with only DB2's CLI-based PHP support (no ADOdb).
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Fri, 2005-11-04 01:09
OK, I created a text-mode script that uses DB2's ODBC client to run the same queries, and if I'm interpreting it correctly, they are both returning result sets.
Quote:
<?php
/* check the command line arguments */
switch ($argc)
{
/* assume defaults if no command line arguments are given */
case 1:
$dbname = "gallery2";
$username = "";
$password = "";
break;
/* if only the database name is given */
case 2:
$dbname = $argv[1];
$username = "";
$password = "";
break;
/* if the database name, username and password are given */
case 4:
$dbname = $argv[1];
$username = $argv[2];
$password = $argv[3];
break;
/* if the usage is wrong */
default:
printf(" USAGE: %s [dbAlias [userid passwd]]\n", $argv[0]);
exit(1);
break;
}
/* connect to the database with specified username and password */
print " Connecting to '$dbname' database... " ;
$dbconn = odbc_connect($dbname, $username, $password);
print "\n Connected to '$dbname' database.\n";
/* override the auto commit option */
odbc_autocommit($dbconn,FALSE);
if ($dbconn != 0)
{
echo ("\nThis query does not return a result set using ADOdb etc.\n");
$statement = 'SELECT g2_Entity.g_id, g2_Item.g_description FROM g2_Entity, g2_Item WHERE g2_Entity.g_id IN (7)';
$recordSet = odbc_exec($dbconn, $statement);
var_dump($recordSet);
echo ("\nThis query does return a result set using ADOdb etc.\n");
$statement = 'SELECT g2_Entity.g_id FROM g2_Entity WHERE g2_Entity.g_id IN (7)';
$recordSet = odbc_exec($dbconn, $statement);
var_dump($recordSet);
}
/* disconnect from the database */
print "\n Disconnecting from '$dbname' database...";
odbc_close($dbconn);
print "\n Disconnected from '$dbname' database.\n";
?>
Returns:
Quote:
C:\My Server\gallery2>php tbuse.php gallery2 g2user g2pwd
Connecting to 'gallery2' database...
Connected to 'gallery2' database.
This query does not return a result set using ADOdb etc.
resource(5) of type (odbc result)
This query does return a result set using ADOdb etc.
resource(6) of type (odbc result)
Disconnecting from 'gallery2' database...
Disconnected from 'gallery2' database.
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Fri, 2005-11-04 02:14
I found out how to print the result set properly, confirming that with plain ODBC the queries both work.
Updated script:
Quote:
<?php
/* check the command line arguments */
switch ($argc)
{
/* assume defaults if no command line arguments are given */
case 1:
$dbname = "gallery2";
$username = "";
$password = "";
break;
/* if only the database name is given */
case 2:
$dbname = $argv[1];
$username = "";
$password = "";
break;
/* if the database name, username and password are given */
case 4:
$dbname = $argv[1];
$username = $argv[2];
$password = $argv[3];
break;
/* if the usage is wrong */
default:
printf(" USAGE: %s [dbAlias [userid passwd]]\n", $argv[0]);
exit(1);
break;
}
/* connect to the database with specified username and password */
print "\n Connecting to '$dbname' database... " ;
$dbconn = odbc_connect($dbname, $username, $password);
print "\n Connected to '$dbname' database.\n";
/* override the auto commit option */
odbc_autocommit($dbconn,FALSE);
if ($dbconn != 0)
{
echo ("\n\nQuery 1: This query DOES NOT return a result set using ADOdb etc.\n");
$statement = 'SELECT g2_Entity.g_id, g2_Item.g_description FROM g2_Entity, g2_Item WHERE g2_Entity.g_id IN (7)';
$result = odbc_exec($dbconn, $statement);
$array = odbc_fetch_resultset($result);
for($i=1; odbc_fetch_row($result,$i); $i++)
{
printf ("\n G_ID = \"%d\", G_DESCRIPTION = \"%s\".\n",
$array[$i]["G_ID"], $array[$i]["G_DESCRIPTION"]);
}
echo ("\n\nQuery 2: This query DOES return a result set using ADOdb etc.\n");
$statement = 'SELECT g2_Entity.g_id FROM g2_Entity WHERE g2_Entity.g_id IN (7)';
$result = odbc_exec($dbconn, $statement);
$array = odbc_fetch_resultset($result);
for($i=1; odbc_fetch_row($result,$i); $i++)
{
printf ("\n G_ID = \"%d\".\n", $array[$i]["G_ID"]);
}
}
/* disconnect from the database */
print "\n\n Disconnecting from '$dbname' database...";
odbc_close($dbconn);
print "\n Disconnected from '$dbname' database.\n";
function odbc_fetch_resultset($resID)
{
/* find the number of fields in the result set */
$fCount = odbc_num_fields($resID);
/* the following for loop stores the field names into the array $fNames */
for($i = 1; $i <= $fCount; $i++)
{
$fNames[$i] = odbc_field_name($resID, $i);
}
/* create an associative array $resultSet with the key 'fieldNames'
pointing to the $fNames array */
$resultSet = array("fieldNames" => $fNames);
/* store all the rows into the $resultSet array */
for($i = 1; odbc_fetch_row($resID, $i); $i++)
{
/* create an array to store each row */
$record = array();
/* the following for loop creates an associative array $record with the
field names as keys that point to the respective field values in each
row */
for($j = 1; $j <= $fCount; $j++)
{
/* store the field name into the variable $fName */
$fName = odbc_field_name($resID, $j);
$record[$fName] = odbc_result($resID, $j);
}
/* add each row to the $resultSet array */
$resultSet[$i] = $record;
}
Connecting to 'gallery2' database...
Connected to 'gallery2' database.
Query 1: This query DOES NOT return a result set using ADOdb etc.
G_ID = "7", G_DESCRIPTION = "This is the main page of your Gallery".
Query 2: This query DOES return a result set using ADOdb etc.
G_ID = "7".
Disconnecting from 'gallery2' database...
Disconnected from 'gallery2' database.
(I based my script on the DB2-provided sample PHP application "sqllib\samples\php\tbuse.php".)
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Fri, 2005-11-04 09:57
hmmm, thanks for the investigation.
since adodb's db2 driver also uses odbc, but instead of odbc_exec, it used odbc_prepare and odbc_execute, it must be a problem with binding or with one of those functions. do you agree?
can you use odbc_prepare and odbc_execute in your test script too? if that works, it's the way that adodb configures / uses the odbc connection / functions.
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Fri, 2005-11-04 10:29
btw: have you already tried this:
Quote:
maybe we should use DB2 adodb drivers and change a single line in them
adodb-db2.inc.php
var $_bindInputArray = false; instead of var $_bindInputArray = true;
but please don't change that before trying the above 2 selects.
forget all other changes we made etc. it might just work when we don't use odbc_bind.
well, we tested that in one of our mini scripts or a similar case was tested. but i'd like to see if it just works with a single 1 line fix....i hope so!!
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Fri, 2005-11-04 16:50
I changed my non-ADOdb script to use odbc_prepare() and odbc_execute(), and I'm getting the following warnings:
Quote:
PHP Warning: odbc_prepare(): SQL error: [IBM][CLI Driver] CLI0005W Option value changed. SQLSTATE=01S02, SQL state 01S02 in SQLPrepare in C:\My Server\gallery2\tbuse.php on line 47
PHP Warning: odbc_execute(): SQL error: [IBM][CLI Driver] CLI0005W Option value changed. SQLSTATE=01S02, SQL state 01S02 in SQLExecute in C:\My Server\gallery2\tbuse.php on line 48
By further testing I determined that this is being caused by the join. The DB2 doc for this CLI005W msg says basically DB2 CLI handled it under the covers and it can be ignored.
It then goes on to process the result set successfully. So it does work even if I use odbc_prepare() and odbc_execute().
Then I changed my "adodb-db2.inc.php" as you described, and it made no difference to the adodb test script... still no result set.
But, ladies and gentlemen, I think I've found the whole problem. I discovered a function called odbc_errormsg(). I added it to my adodb test script and it says:
Quote:
[Microsoft][ODBC Cursor Library] Positioned request cannot be performed because result set was generated by a join condition
So there it is. It looks like the ODBC driver cannot handle result sets from joins. It seems that DB2's ODBC/CLI driver can handle it though (the warning msg I got from my non-ADOdb script).
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Fri, 2005-11-04 17:51
FYI, last month I started a conversation with the DB2 Linux Product Planner, about 'ibm_db2', a DB2 driver package for PHP he owns. (BTW, he seems very keen to get Gallery to support DB2.)
Then I discovered that the 'ibm_db2' package would have to be supported by the ADOdb package.
Then he said there were plans to do so.
So I sent him the following reminder today...
Quote:
From: Larry Menard
To: Dan Scott
Sent: Friday, November 04, 2005 12:32 PM
Subject: Re: Fw: ibm_db2 package
Hi, Dan.
Any news on the ibm-db2 support in the ADOdb package? I'm having an awful pile of problems with the existing DB2 support in ADOdb. The most recent problem: JOIN support.
[Microsoft][ODBC Cursor Library] Positioned request cannot be performed because result set was generated by a join condition
In a non-ADOdb context, DB2's CLI/ODBC driver apparently does something under the covers to make it work and produces a warning message:
PHP Warning: odbc_prepare(): SQL error: [IBM][CLI Driver] CLI0005W Option value changed. SQLSTATE=01S02, SQL state 01S02 in SQLPrepare in C:\My Server\gallery2\tbuse.php on line 47
CLI0005W Option value changed.
Explanation: The driver did not support the specified option value and substituted a similar value.
User Response: No action needed.
Thanks, Dan.
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
E-mail and MSN Messenger:
----- Original Message -----
From: Dan Scott
To: Larry Menard
Sent: Friday, October 07, 2005 1:45 PM
Subject: Re: Fw: ibm_db2 package
There is a package called ADOdb, but it does not currently support ibm_db2.
However, one of our partners is working on adding that support -- probably another couple of weeks.
Dan
--
Dan Scott
Product Planner, DB2 for Linux, UNIX, and Windows
Phone: (905) 413-3170
"Larry Menard" <larry.menard@rogers.com>
10/07/2005 01:36 PM
To Dan Scott/Toronto/IBM@IBMCA
cc
Subject Re: Fw: ibm_db2 package
Possible dead end... apparently there's a package called "Adodb" that would have to support the 'ibm_db2' extension. I doubt it does. Can you confirm that?
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
E-mail and MSN Messenger:
----- Original Message -----
From: Dan Scott
To: Larry Menard
Sent: Monday, October 03, 2005 9:11 AM
Subject: Re: Non-CLI PHP client for UDB?
Larry!
As far as PHP and DB2 goes, I'm your man... the ibm_db2 extension is actually a CLI-based PHP driver for UDB, but unlike Unified ODBC it doesn't suck It has been developed by, and is supported by, IBM for access to DB2.
Getting DB2 support in Gallery would be _very_ cool. You should be able to use ibm_db2 (it has real support for LOBs, for example) just fine. Requires a DB2 client of V8.2 or higher, though.
The docs for ibm_db2 (written by yours truly) are available from http://php.net/ibm_db2. If you run into any problems, feel free to post a note to the pecl-dev mailing list (accessible via normal mailing list means or via NNTP at news.php.net); I watch that list religiously.
Dan
--
Dan Scott
Product Planner, DB2 for Linux, UNIX, and Windows
Phone: (905) 413-3170 "Larry Menard" <larry.menard@rogers.com>
10/02/2005 02:17 PM
To Dan Scott/Toronto/IBM@IBMCA
cc
Subject Non-CLI PHP client for UDB?
Hi, Dan. Long time no see. ;-) How are things?
I don't know if you're still the one to ask about this, but it's got your name on it, so... ;-)
I'm working on adding support for DB2 to a package from SourceForge. The package is called "Gallery", it's written in PHP, and it uses an RDBMS as a backend. Currently it supports only Oracle, MySQL, Ingres and PostgreSQL.
I'm currently trying to integrate DB2 support via the ODBC/CLI layer, but have hit a snag. Did IBM ever deliver a non-CLI PHP driver for UDB? I found something called "ibm_db2" package on a site called "pecl.php.net". Is that it?
I appreciate your guidance here. Thanks, Dan.
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
E-mail and MSN Messenger:
good work!
so you say it works even with odbc_prepare + odbc_execute, but not with adodb-db2.inc.php.
so we either find out where the adodb db2 / odbc files configure odbc incorrectly / use the wrong options.
or we just wait for ibm_db2 support in adodb.
your call, whatever you prefer.
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Sat, 2005-11-05 00:34
I'm not sure I completely understand the architecture of ODBC drivers and ADOdb, but my understanding of the error message is that the error is coming from Microsoft's ODBC driver itself, not ADOdb. And it's not necessarily broken, it may be a known limitation of the Microsoft ODBC driver (result sets from joins). The web searching I've done seems to support that:
So I doubt there's anything ADOdb can do about it, or if so, it would be a huge piece of work.
The "ibm_db2" package uses DB2's ODBC (CLI) driver, which appears to have implemented an automatic work-around for that missing functionality, so hopefully when ADOdb supports "ibm_db2" it will support result sets from joins.
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Sat, 2005-11-05 00:48
Quote:
I changed my non-ADOdb script to use odbc_prepare() and odbc_execute(), and I'm getting the following warnings:
Quote:
PHP Warning: odbc_prepare(): SQL error: [IBM][CLI Driver] CLI0005W Option value changed. SQLSTATE=01S02, SQL state 01S02 in SQLPrepare in C:\My Server\gallery2\tbuse.php on line 47
PHP Warning: odbc_execute(): SQL error: [IBM][CLI Driver] CLI0005W Option value changed. SQLSTATE=01S02, SQL state 01S02 in SQLExecute in C:\My Server\gallery2\tbuse.php on line 48
By further testing I determined that this is being caused by the join. The DB2 doc for this CLI005W msg says basically DB2 CLI handled it under the covers and it can be ignored.[/quote[
you're using
$dbconn = odbc_connect($dbname, $username, $password);
odbc_prepare and odbc_execute and it gives a warning, but it works (or at least i understand your post like that).
and adodb uses the same functions. probably with the same arguments. so both of them should use the same odbc driver. unless you use different arguments in the connect call to select another odbc driver.
and if it's about the odbc driver, can't you just switch the odbc driver / source in windows? and before banging your head against the wall because i'm talking bs, just tell me to shut up i'm just trying to help intuitively...
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Sat, 2005-11-05 01:20
And I definitely appreciate your help.
Your understanding of my post is correct (in the DB2 CLI/ODBC case).
Like I said, "I'm not sure I completely understand the architecture of ODBC drivers" , but:
What I know for certain is that:
1) DB2 provides their own ODBC driver (in the form of their CLI libraries).
2) Messages from DB2's ODBC/CLI driver begin with eyecatcher "[IBM][CLI Driver]".
What I'm deducing from the symptoms is that:
1) Since the error message returned from odbc_errormsg() begins with eyecatcher "[Microsoft][ODBC Cursor Library]", the ADOdb case is using a Microsoft-provided ODBC library, not DB2's.
2) Note that the other difference between my two test scripts is that the ADOdb script runs in a browser (IE6 SP2) whereas the non-ADOdb script is run from a command line. Something in each of the two cases is somehow getting a different ODBC driver (perhaps it depends on what's in the PATH in each of the environments). If it's not ADOdb that is causing the different ODBC driver to be selected, then perhaps it's the browser.
As for switching the ODBC driver at will, I wish I knew how to do that.
I'm going to post a question to newsgroup "microsoft.public.odbc" to see if I can get any confirmation of this.
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Sat, 2005-11-05 02:03
Update: I've now eliminated one of the differences... both ADOdb and non-ADOdb scripts now run in the browser.
Same results as before:
- ADOdb script fails (uses Microsoft ODBC driver).
- Non-ADOdb works (uses DB2's ODBC driver).
So the difference is not due to the browser.
Perhaps ADOdb is the culprit after all? But I don't know why it's using the Microsoft ODBC driver.
I'm attaching my test scripts here. If you run it on Windows, I would think that you should be able to reproduce the ADOdb failure... if Bharat gets his DB2 installation working (which I'm still willing to help with) you should also be able to see that the DB2 ODBC/CLI case works.
valiant
Joined: 2003-01-04
Posts: 32509
Posted: Sat, 2005-11-05 02:22
in driver/adodb-odbc.inc.php there are odbc_connect calls.
could you verify these are called with the exact same arguments as in your script?
and if not, change your script to match exactly what adodb is doing to connect and check if the driver changes.
IMO it can only choose / switch between multiple odbc data sources if you have both of them defined in your windows odbc adminstration panel.
Larry Menard
Joined: 2005-10-01
Posts: 757
Posted: Sat, 2005-11-05 03:35
Good call, Valiant.
My non-ADOdb script is using an odbc_connect() with only 3 args. The ADOdb script is using the odbc_connect() on line 60, which has 4 args. The fourth arg is apparently 'cursor mode', $this->curmode, which in my case is integer "1".
When I modify my non-ADOdb script to use that 4th arg (specifying the value 1), it now also uses the Microsoft driver. Also, I now get the same message about the join, but apparently it's only a Warning because the result set is successfully produced, but the G_DESCRIPTION field doesn't seem to be retrieved:
Quote:
Connecting to 'gallery2' database...
Connected to 'gallery2' database.
Query 1: This query DOES NOT return a result set using ADOdb etc.
Warning: odbc_result() [function.odbc-result]: SQL error: [Microsoft][ODBC Cursor Library] Positioned request cannot be performed because result set was generated by a join condition, SQL state SL002 in SQLGetData in C:\My Server\gallery2\test-non-adodb.php on line 83
G_ID = "7", G_DESCRIPTION = "".
Query 2: This query DOES return a result set using ADOdb etc.
G_ID = "7".
Disconnecting from 'gallery2' database...
Disconnected from 'gallery2' database.
So to recap...
Quote:
Script with MS ODBC and ADOdb does not produce a result set.
Script with MS ODBC and NO ADOdb does produce a result set, but it doesn't seem to contain both columns.
Script with DB2 ODBC and NO ADOdb does produce a result set that does contain both columns.
So I'm led to suspect that there may be multiple problems... one with the MS ODBC driver, and one with ADOdb.
Then I wondered what would happen if I explicitly specified cursor mode "0"? So I tried it, and...
It worked! Still get the warning, but now the result set contains both columns.
Quote:
Connecting to 'gallery2' database...
Connected to 'gallery2' database.
Query 1: This query DOES NOT return a result set using ADOdb etc.
Warning: odbc_prepare() [function.odbc-prepare]: SQL error: [IBM][CLI Driver] CLI0005W Option value changed. SQLSTATE=01S02, SQL state 01S02 in SQLPrepare in C:\My Server\gallery2\test-non-adodb.php on line 23
Warning: odbc_execute() [function.odbc-execute]: SQL error: [IBM][CLI Driver] CLI0005W Option value changed. SQLSTATE=01S02, SQL state 01S02 in SQLExecute in C:\My Server\gallery2\test-non-adodb.php on line 24
G_ID = "7", G_DESCRIPTION = "This is the main page of your Gallery".
Query 2: This query DOES return a result set using ADOdb etc.
G_ID = "7".
Disconnecting from 'gallery2' database...
Disconnected from 'gallery2' database.
So... apparently cursors are tricky in ODBC. Why is $this->curmode set to 1? Can we make it get set to 0?
Posts: 32509
so it's now executing select, update, delete statements for the first time.
have you finished your Db2DatabaseStorage.class?
you now need to activate "buffered" debug mode.
FAQ: How to set/use Gallery in debug mode?
in the debug output, you'll see the SQL statements used and the error.
Posts: 757
> have you finished your Db2DatabaseStorage.class?
As far as I can tell, yes. But I can't really know for sure until the thing works 100%, can I?
I've enabled the debug feature, and I see the SQL but I don't see any error info in it. (And there seems to be a formatting problem... missing line returns... makes it harder to read.)
When I removed that debug feature and added my own debug code:
I get:
I suspect $targetIds should not be empty. Any ideas why it is?
Posts: 757
Ah, apparently $targetIds is not empty, it is int(7). And when I plug that into that query it works fine for me (returns a 1-record result set).
Posts: 757
I added some more debug code:
and it appears that $recordSet->RecordCount() is 0.
Yet I've confirmed that that query does return 1 record (from a command-line connection).
Posts: 32509
php hint: printf("<br>\$targetIds = \"%s\".\n", var_dump($targetIds));
doesn't work since var_dump prints directly to the browser, it doesn't return a string.
@error:
hmm. so the error must be somewhere in adodb i guess. darn.
files to debug are:
function &Execute and _Execute in lib/adodb/adodb.inc.php
function ADORecordSet_db2 in lib/adodb/drivers/adodb-db2.inc.php
Posts: 757
By plugging in various printf()s all I can do is confirm that in the "&_Execute" function of "adodb.ini.php", immediately after the query is executed, "$rs->_numOfRows" is 0.
Sorry, but this is now significantly over my head. I can't take this debugging any further. As I said before, I'm not a hard-core developer. And I've just hit the limit of my skill.
Posts: 7994
It looks like you've made a lot of progress! Let's start by committing what you have so that we can have a checkpoint. Can you provide us with a copy of everything that you've changed?
I can try to install DB2 on one of my windows boxes to see if I can debug this further. Is the Express version ok? If not, which do you recommend?
Posts: 757
I've ZIPped up my changed files and put them on my home web server.
(However, I did not include the "adodb-db2.inc.php" and "adodb-odbc.inc.php" files from the "adodb" package. You might already have them, or you can grab them from the "ADOdb" package.)
I'm not that familiar with the "Express" version of DB2, but I'm pretty sure it should have everything you need (i.e., the DB2 client and server components). (I happen to have "Enterprise Server Edition".)
You shouldn't need to use Windows for this... if it's more convenient for you to use Linux or UNIX that should work too.
If for whatever reason you cannot get the DB2 server running on your system, all you really need is the client component, and I can show you how you can set it up to access the database that resides on my server.
Let me know if you have any questions about what I've done, or DB2 in general.
Posts: 7994
Sorry for the delay! I've been busy refactoring our SQL generation code to generate a single file for all of our SQL instead of many separate files. I didn't want to commit your stuff first because it would result in 60+ new files that I'd just wind up deleting after my refactor so I waited until my code was in first.
So now the good news is that I've committed my change and then merged yours in on top of it. We now have a DB2 option in the installer and generated DB2 SQL in all the various modules. I've marked it all as experimental so that people don't think that it works. I also tweaked the algorithm that you were using to create the table and index names a little bit; check out what I've done in generate-sql.php.
I haven't yet installed DB2; that's next on my list. I'll get it up and running and then start fiddling with the code to see what snags I hit. In the meantime, if you want to cvs up and try out the latest code and make sure that you at least get as far as you did before, that'd be a big help. When you update you're going to get a lot of conflicts because I've now committed files with the same names as the files that you created in your G2. You might want to undo all your changes in your client (you've still got them in the ZIP file so you won't lose anything) before doing the cvs update. Just deleting the files is enough; cvs will restore them when you update.
Posts: 757
Hi Bharat.
I just renamed my entire Gallery2 directory and got a fresh copy of the whole thing, and dropped & recreated a fresh database.
The Installer didn't get as far as it used to. In Step 8 I now get an error that the SCHEMA table doesn't exist.
At the top of the page is message:
The stack trace:
Install.log is attached here.
Posts: 32509
Hi Larry, sorry, I've forgotten this topic.
So the current state is that we have this db2 support in g2, but it doesn't work. Not even installing works. But installing with the DB2 changes you had yourself worked.
Can you CVS update and in said file C:\My Server\gallery2\modules\core\classes\GalleryStorage\DatabaseStorageExtras.class , add var_dump($moduleSql); before
$ret = $this->_executeSql($moduleSql['table']['Schema']); around line 1193?
Posts: 7994
I've been totally slammed with other stuff and haven't been able to circle back to this either. I downloaded DB2 Express for Windows (400MB!) and installed it, but ran into snags when creating the database. I think that Express is not explicitly client/server but it's hard to tell from their documentation which is alternately far too thin or far too heavy. Larry, if you can give me a way to connect to your database from my PHP server that might be the best way for me to try to resolve this from my side. Let me know if that works for you.
Posts: 757
Hi, Bharat and Valiant.
Today I had a rude surprise from my ISP (Rogers Cable)... I think they've discovered the servers I've been running (my web server and DB2 server) and have slammed the door on them. I guess running servers is not allowed for residential users like me. So, the end result of that is that my DB2 server is no longer able to support clients from outside of my own LAN.
Which leaves us three options:
1) I can CVS update my changes (which I'm not particularly comfortable with), or
2) I can try to debug the set of files that have both Bharat's and my changes, or
3) I can try to help you get your DB2 installation working.
If you want to go for #3, let me know exactly what symptoms you are getting, and your platform info. Personally, I think the better option is #2 (despite the fact that I am now rather short on time these days... I'm job hunting).
Let me know which option you think is best.
Posts: 757
Some good news for a change:
I got a whole new image today, and now am able to get through to the "Go to my Gallery" step. But at that point I get the following errors:
Which is pretty much where I got to on my own build tree without Bharat's changes.
Posts: 757
Sorry, forgot to attach the install.log to the previous posting.
Posts: 32509
bharat will have to answer the 1, 2 or 3 question, i try to help you debugging meanwhile.
the only remarkable error from your install.log:
and a second of that kind right after it.
in my mysql based install.log, the same line looks like this:
no error. but maybe that's ok. since the error was not sent upstream / ignored.
@main.php Error (ERROR_MISSING_OBJECT) :
please check your current database tables.
in g2_Entity you should have a few rows, in g2_Item, g2_AlbumItem you should have a single row with the same g_id (probably g_id = 7).
for debugging, please enable buffered debug mode in config.php
(change $gallery->setDebug(false) to $gallery->setDebug('buffered');)
and then you'll get details for the Error (ERROR_MISSING_OBJECT) .
Posts: 757
Thanks, Valiant.
OK, I enabled the debugging and repro'd the error. I'm attaching files with the debug info.
And I've verified that the tables do contain the rows you said should be there.
Posts: 32509
could you please check for the following query what it returns when run in a sql shell?
SELECT g2_Entity.g_id, g2_Entity.g_creationTimestamp, g2_Entity.g_isLinkable, g2_Entity.g_linkId, g2_Entity.g_modificationTimestamp, g2_Entity.g_serialNumber, g2_Entity.g_entityType, g2_Entity.g_onLoadHandlers, g2_ChildEntity.g_parentId, g2_FileSystemEntity.g_pathComponent, g2_Item.g_canContainChildren, g2_Item.g_description, g2_Item.g_keywords, g2_Item.g_ownerId, g2_Item.g_summary, g2_Item.g_title, g2_Item.g_viewedSinceTimestamp, g2_Item.g_originationTimestamp, g2_AlbumItem.g_theme, g2_AlbumItem.g_orderBy, g2_AlbumItem.g_orderDirection FROM g2_Entity, g2_ChildEntity, g2_FileSystemEntity, g2_Item, g2_AlbumItem WHERE g2_Entity.g_id IN ('7') AND g2_ChildEntity.g_id=g2_Entity.g_id AND g2_FileSystemEntity.g_id=g2_Entity.g_id AND g2_Item.g_id=g2_Entity.g_id AND g2_AlbumItem.g_id=g2_Entity.g_id;
if it doesn't return a single row, then that's the problem.
there should be a row for g_id = 7 in tables g2_Entity (g_id = 7), g2_ChildEntity (g_id = 7, parent = 0), g2_FileSystemEntity (g_id =7), g2_Item ( idem) , g2_AlbumItem (idem)
Posts: 757
Ah-haa... that query was failing with a datatype mismatch in the IN clause. I changed:
to
and the query now returns the single row.
In which source file is this code?
Posts: 32509
oh, that's not good.
since we're using question marks as markers for data in our queries. e.g.
$statement = "SELECT foo from bar WHERE baz = ?" and then we add
$data = 7;
and call adodb with:
$db->Execute($$statement, $data); and adodb replaces the markers (?) with the actual data ('7');
but adodb only adds quotes around the value if the data type of the value is "string".
see line 818 of lib/adodb/adodb.inc.php
so the question is: why is "7" a string and not a number? i guess 7 comes in this case from the table g2_pluginparametermap column g_value which is a varchar column.
this could explain it.
obviously DB2 has problems with that.
- can you change a DB2 configuration to accept '7' as value for numeric columns?
- else i guess we have to add a Execute function Execute($query, $data=array()) in our Db2DatabaseStorage.class
which does
for ($i = 0; $i < size($data); $i++) {
if (is_numeric($data[$i])) {
$data[$i] = doubleval($data[$i]);
}
}
return parent::Execute($query, $data);
Posts: 7994
Hm. I think (but I'm not sure) that ADOdb is typing the value based on what it is in PHP, not what the database schema expects. This means that you may be able to fix this by editing GalleryView.class and forcing the item id to be an integer. Look in modules/core/GalleryView.class around line 385 and change:
/* If we still don't have an itemId, default to the root album */ if (empty($itemId)) { list ($ret, $itemId) = GalleryCoreApi::getPluginParameter('module', 'core', 'id.rootAlbum'); if ($ret->isError()) { return array($ret->wrap(__FILE__, __LINE__), null); } }to:
/* If we still don't have an itemId, default to the root album */ if (empty($itemId)) { list ($ret, $itemId) = GalleryCoreApi::getPluginParameter('module', 'core', 'id.rootAlbum'); if ($ret->isError()) { return array($ret->wrap(__FILE__, __LINE__), null); } $itemId = (int)$itemId; }Note the one added line in the new version where we force $itemId to be an integer. If that works let us know and we'll commit it.
Posts: 32509
sure, that's the same fix in another file of g2.
what i tried to explain is:
g2 get's the value "7" from the database, since it selects the root album id from the pluginparametermap table. so mysql/adodb return a string "7" since the value field of that table is varchar.
then in the next select query, we need "7" as an integer and not as a string since DB2 has problems with that.
your fix for getItemId would fix this single issue. but since DB2 doesn't accept strings for numerical db columns in general, we should fix it in the Db2DatabaseStorage class, IMO.
Posts: 757
1) DB2 considers anything in quotes to be string, never numeric. That's not configurable.
2) I made Bharat's suggested change to GalleryView.class, but it doesn't seem to have helped. Looks like the exact same error to me (output files attached here, for your confirmation).
Shall I go ahead and try Valiant's change to Db2DatabaseStorage.class? If I do, are there are corequisite changes?
Posts: 32509
what i find strange is that
SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN ('7');
seems to have worked, else we wouldn't know the entityType which is needed for the next query, which is the one that fails.
does DB2 have query logging? then you could see in the DB2 logs what actually was queried (else we could look at adodb logs).
i just found out that my change wouldn't work without other changes. and maybe it's also not the right place for such a change. if we change just everything numeric to a int/double type, then DB2 may have problems if we insert a int into a text column.
so maybe we'd have to fix this in a more general way, e.g. compare data type and target column type etc.
but for now, i'd change it on the application level, as bharat indicated. might be easier for now.
so forget my change and open modules/core/classes/GalleryStorage/DatabaseStorageExtras.class
replace
function loadEntities($ids) { global $gallery; if (empty($this->_ds->_db)) { return array(GalleryStatus::error(ERROR_STORAGE_CONNECTION, __FILE__, __LINE__), null); }with
function loadEntities($ids) { global $gallery; if (empty($this->_ds->_db)) { return array(GalleryStatus::error(ERROR_STORAGE_CONNECTION, __FILE__, __LINE__), null); } /* Convert PHP type of the $ids if necessary */ for ($i = 0; $i < sizeof($ids); $i++) { $ids[$i] = intval($ids[$i]); }this will fix this issue. but i'm quite sure there are others. so a solution on db abstraction level might be better.
ps: corequisite, thanks, learned a new word today
Posts: 757
Are you sure that query worked? When I run it from a command line:
DB2 has transaction logging, but I'm not sure that would be helpful.
I've made the change to DatabaseStorageExtras.class, but it still looks like the same error is happening. (Files attached.)
Posts: 757
If it helps, I've taken a DB2 CLI (Call Level Interface) trace (CLI is DB2's own version of ODBC). It contains all SQL statements that went through the CLI interface (as does PHP/ODBC). There's a lot of info, it may or may not be intuitive. ;-)
I've zipped the file and renamed it to ".txt" do I can attach it here.
Posts: 32509
i wonder why:
- we don't get the same error (SQL0401N) in our logs
- you get "(db2): SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN (?) [ (0=>'7') ] " in your g2 log while i get:
"(mysqlt): SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN (7)" that is, for mysql it logs the final query and for db2 it logs the query and the data separately. that's because odbc / db2 can prepare statements / bind data. but the '7' thing is the problem.
edit: i looked at the adodb sourcecode and this 0=>'7' thing is ok. the quotes are generated for all types. it's just debugging code.
well, as you see from my log, with mysql, it's no IN (7) and not IN ('7') anymore with my fix. thus i wonder what happened in your case. maybe the problem isn't G2 or adodb
could you please clear the cache with /lib/suppot/ -> cache management -> clear all caches.
and then try again?
no need to reinstall G2 / no need for the install.log.
please enable transaction logging and look at the final queries that are actually executed. it would be interesting if they show the final queries where the markers have been replaced with the data.
maybe check the whole thing with a minimalistic script:
/********************************************************
* Set up a transactional db connection
********************************************************/
error_reporting(E_ALL);
define('MYADODBPATH', '/path/to/gallery2/lib/adodb/');
require_once(MYADODBPATH . 'adodb.inc.php');
$db =& ADONewConnection('db2');
if (empty($db)) {
die('failed to get an adodb db object');
}
$ret = $db->NConnect('localhost','root','password', 'gallery2'); // host, user, pw, dbname
if (!$ret) {
die('failed to connect to the database');
}
$recordSet = $db->Execute('SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN (?)', array(7));
if (!($data = $recordSet->FetchRow()) || !is_array($data)) {
die('select query failed');
}
var_dump($data);
another idea:
maybe db2 doesn't accept ? as a marker for data bindings. googled: ? is fine.
yet another idea:
maybe we have to specify the type of the bind variables else it defaults to char?
no idea...
Posts: 757
I'm afraid I don't quite understand your instructions about clearing the cache, sorry.
I posted the CLI trace, but interpreting that is a real art in itself. I'm still looking into a user-friendly way to capture the SQL.
I ran the minimalistic script, and it seems to work:
Question marks are definitely valid parameter markers in DB2.
Posts: 757
Ah, I realized what you meant by clearing the cache. I've done so, and the error still occurs, though the debug info is much shorter:
Posts: 32509
the mini script, does it still work when exchanging
$recordSet = $db->Execute('SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN (?)', array(7));
with
$recordSet = $db->Execute('SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN (?)', array("7"));
?
and what about running the mini script with
$statement = 'SELECT g2_Entity.g_id, g2_Entity.g_creationTimestamp, g2_Entity.g_isLinkable, g2_Entity.g_linkId, g2_Entity.g_modificationTimestamp, g2_Entity.g_serialNumber, g2_Entity.g_entityType, g2_Entity.g_onLoadHandlers, g2_ChildEntity.g_parentId, g2_FileSystemEntity.g_pathComponent, g2_Item.g_canContainChildren, g2_Item.g_description, g2_Item.g_keywords, g2_Item.g_ownerId, g2_Item.g_summary, g2_Item.g_title, g2_Item.g_viewedSinceTimestamp, g2_Item.g_originationTimestamp, g2_AlbumItem.g_theme, g2_AlbumItem.g_orderBy, g2_AlbumItem.g_orderDirection FROM g2_Entity, g2_ChildEntity, g2_FileSystemEntity, g2_Item, g2_AlbumItem WHERE g2_Entity.g_id IN (?) AND g2_ChildEntity.g_id=g2_Entity.g_id AND g2_FileSystemEntity.g_id=g2_Entity.g_id AND g2_Item.g_id=g2_Entity.g_id AND g2_AlbumItem.g_id=g2_Entity.g_id';
$data = array(7);
$recordSet = $db->Execute($statement, $data);
and with $data = array("7"); ?
from the debug log:
what about the debug output
?
is that related to our problem or is that just a notice? yeah, we have discussed this, but is it a problem?
Posts: 757
1) $recordSet = $db->Execute('SELECT g_id, g_entityType FROM g2_Entity WHERE g_id IN (?)', array("7"));
Yes... produces:
2) Long query
Nope, fails with "select query failed" with both values for $data.
3) "Place dsn in first parameter"
I believe this is bogus, because we are obviously able to connect to the database. I think the adodb files print this out in any DB2 case, regardless if the args are already in the right order.
Posts: 32509
1) aha
2) aha! so it's not the data! it's the query itself.
can you also try (with the mini script):
$statement = 'SELECT g2_Entity.g_id, g2_Entity.g_creationTimestamp, g2_Entity.g_isLinkable, g2_Entity.g_linkId, g2_Entity.g_modificationTimestamp, g2_Entity.g_serialNumber, g2_Entity.g_entityType, g2_Entity.g_onLoadHandlers, g2_ChildEntity.g_parentId, g2_FileSystemEntity.g_pathComponent, g2_Item.g_canContainChildren, g2_Item.g_description, g2_Item.g_keywords, g2_Item.g_ownerId, g2_Item.g_summary, g2_Item.g_title, g2_Item.g_viewedSinceTimestamp, g2_Item.g_originationTimestamp, g2_AlbumItem.g_theme, g2_AlbumItem.g_orderBy, g2_AlbumItem.g_orderDirection FROM g2_Entity, g2_ChildEntity, g2_FileSystemEntity, g2_Item, g2_AlbumItem WHERE g2_Entity.g_id IN ("7") AND g2_ChildEntity.g_id=g2_Entity.g_id AND g2_FileSystemEntity.g_id=g2_Entity.g_id AND g2_Item.g_id=g2_Entity.g_id AND g2_AlbumItem.g_id=g2_Entity.g_id';
// no data
$recordSet = $db->Execute($statement);
and also with IN (7) instead of IN ("7")
if both work and return a single row, then it's definitely the odbc binding that is the problem, and not the data.
maybe we should use DB2 adodb drivers and change a single line in them
adodb-db2.inc.php
var $_bindInputArray = false; instead of var $_bindInputArray = true;
but please don't change that before trying the above 2 selects.
Posts: 757
Hmmm... neither of them work:
1) IN ("7"):
2) IN (7):
I suspect that's not what you were hoping for...
Posts: 757
Ah, but in the 2nd case, the error msg is being produced because $data is no longer being set.
When I added a var_dump($recordSet);, I get:
Posts: 757
OK, now this is interesting.
This query returns a result set:
This query does not:
Note the only difference is that I'm including a 2nd column in the result set.
When I replace the 2nd column and join table from the Item table with ones from the ChildEntity table:
It once again works.
Then, when when I take the full original query and remove only the Item table references, it once again works (returns a result set).
So it looks to me like the problem is specific to the Item table?
Posts: 32509
this query doesn't make sense since you don't link the two tables (AND g2_Item.g_id = g2_Entity.g_id)
same here
interesting
hmm, that doesn't explain why the first query works.
Posts: 757
I think that whether they are logical or not is irrelevant. They are syntactically correct and should all return result sets. They all do return result sets when issued from the DB2 command prompt.
I'm going to try to run those same queries with only DB2's CLI-based PHP support (no ADOdb).
Posts: 757
OK, I created a text-mode script that uses DB2's ODBC client to run the same queries, and if I'm interpreting it correctly, they are both returning result sets.
Returns:
Posts: 757
I found out how to print the result set properly, confirming that with plain ODBC the queries both work.
Updated script:
Produces:
(I based my script on the DB2-provided sample PHP application "sqllib\samples\php\tbuse.php".)
Posts: 32509
hmmm, thanks for the investigation.
since adodb's db2 driver also uses odbc, but instead of odbc_exec, it used odbc_prepare and odbc_execute, it must be a problem with binding or with one of those functions. do you agree?
can you use odbc_prepare and odbc_execute in your test script too? if that works, it's the way that adodb configures / uses the odbc connection / functions.
Posts: 32509
btw: have you already tried this:
forget all other changes we made etc. it might just work when we don't use odbc_bind.
well, we tested that in one of our mini scripts or a similar case was tested. but i'd like to see if it just works with a single 1 line fix....i hope so!!
Posts: 757
I changed my non-ADOdb script to use odbc_prepare() and odbc_execute(), and I'm getting the following warnings:
By further testing I determined that this is being caused by the join. The DB2 doc for this CLI005W msg says basically DB2 CLI handled it under the covers and it can be ignored.
It then goes on to process the result set successfully. So it does work even if I use odbc_prepare() and odbc_execute().
Then I changed my "adodb-db2.inc.php" as you described, and it made no difference to the adodb test script... still no result set.
But, ladies and gentlemen, I think I've found the whole problem. I discovered a function called odbc_errormsg(). I added it to my adodb test script and it says:
So there it is. It looks like the ODBC driver cannot handle result sets from joins. It seems that DB2's ODBC/CLI driver can handle it though (the warning msg I got from my non-ADOdb script).
Posts: 757
FYI, last month I started a conversation with the DB2 Linux Product Planner, about 'ibm_db2', a DB2 driver package for PHP he owns. (BTW, he seems very keen to get Gallery to support DB2.)
Then I discovered that the 'ibm_db2' package would have to be supported by the ADOdb package.
Then he said there were plans to do so.
So I sent him the following reminder today...
Posts: 32509
Larry Menard,
good work!
so you say it works even with odbc_prepare + odbc_execute, but not with adodb-db2.inc.php.
so we either find out where the adodb db2 / odbc files configure odbc incorrectly / use the wrong options.
or we just wait for ibm_db2 support in adodb.
your call, whatever you prefer.
Posts: 757
I'm not sure I completely understand the architecture of ODBC drivers and ADOdb, but my understanding of the error message is that the error is coming from Microsoft's ODBC driver itself, not ADOdb. And it's not necessarily broken, it may be a known limitation of the Microsoft ODBC driver (result sets from joins). The web searching I've done seems to support that:
http://media.datadirect.com/download/docs/slnk/devref/devodbc.html#wp14576 (search for JOIN)
http://www.phpbuilder.com/lists/phplib-list/199910/0035.php
So I doubt there's anything ADOdb can do about it, or if so, it would be a huge piece of work.
The "ibm_db2" package uses DB2's ODBC (CLI) driver, which appears to have implemented an automatic work-around for that missing functionality, so hopefully when ADOdb supports "ibm_db2" it will support result sets from joins.
Posts: 32509
Posts: 757
And I definitely appreciate your help.
Your understanding of my post is correct (in the DB2 CLI/ODBC case).
Like I said, "I'm not sure I completely understand the architecture of ODBC drivers"
, but:
What I know for certain is that:
1) DB2 provides their own ODBC driver (in the form of their CLI libraries).
2) Messages from DB2's ODBC/CLI driver begin with eyecatcher "[IBM][CLI Driver]".
What I'm deducing from the symptoms is that:
1) Since the error message returned from odbc_errormsg() begins with eyecatcher "[Microsoft][ODBC Cursor Library]", the ADOdb case is using a Microsoft-provided ODBC library, not DB2's.
2) Note that the other difference between my two test scripts is that the ADOdb script runs in a browser (IE6 SP2) whereas the non-ADOdb script is run from a command line. Something in each of the two cases is somehow getting a different ODBC driver (perhaps it depends on what's in the PATH in each of the environments). If it's not ADOdb that is causing the different ODBC driver to be selected, then perhaps it's the browser.
As for switching the ODBC driver at will, I wish I knew how to do that.
I'm going to post a question to newsgroup "microsoft.public.odbc" to see if I can get any confirmation of this.
Posts: 757
Update: I've now eliminated one of the differences... both ADOdb and non-ADOdb scripts now run in the browser.
Same results as before:
- ADOdb script fails (uses Microsoft ODBC driver).
- Non-ADOdb works (uses DB2's ODBC driver).
So the difference is not due to the browser.
Perhaps ADOdb is the culprit after all? But I don't know why it's using the Microsoft ODBC driver.
I'm attaching my test scripts here. If you run it on Windows, I would think that you should be able to reproduce the ADOdb failure... if Bharat gets his DB2 installation working (which I'm still willing to help with) you should also be able to see that the DB2 ODBC/CLI case works.
Posts: 32509
in driver/adodb-odbc.inc.php there are odbc_connect calls.
could you verify these are called with the exact same arguments as in your script?
and if not, change your script to match exactly what adodb is doing to connect and check if the driver changes.
IMO it can only choose / switch between multiple odbc data sources if you have both of them defined in your windows odbc adminstration panel.
Posts: 757
Good call, Valiant.
My non-ADOdb script is using an odbc_connect() with only 3 args. The ADOdb script is using the odbc_connect() on line 60, which has 4 args. The fourth arg is apparently 'cursor mode', $this->curmode, which in my case is integer "1".
When I modify my non-ADOdb script to use that 4th arg (specifying the value 1), it now also uses the Microsoft driver. Also, I now get the same message about the join, but apparently it's only a Warning because the result set is successfully produced, but the G_DESCRIPTION field doesn't seem to be retrieved:
So to recap...
So I'm led to suspect that there may be multiple problems... one with the MS ODBC driver, and one with ADOdb.
Then I wondered what would happen if I explicitly specified cursor mode "0"? So I tried it, and...
It worked! Still get the warning, but now the result set contains both columns.
So... apparently cursors are tricky in ODBC. Why is $this->curmode set to 1? Can we make it get set to 0?