[ic] complex sql select statements --SOLVED!

[interchange-users@icdevgroup.org (John Allman)]

John Allman wrote:

> Mike Heins wrote:
>
>> Quoting John Allman (allmanj@houseofireland.com):
>>  
>>
>>> Can you suggest something in the configurations which might be 
>>> causing this? or can you suggest a way to trace the exact source of 
>>> the problem? The only real difference between the catalogs that i 
>>> can see is that one uses two databases...
>>>
>>>   
>>
>>
>> No. That is what a consultant would do. You don't get top-level specific
>> help for nothing.
>>
>>  
>>
> Fair enough. I made a copy of the databases from the other catalog 
> that it was accessing for the cattable and products tables, and after 
> updating the DSN connection strings and user/passwords it now works 
> fine with no errors. apparently the issue was related to accessing two 
> databases. This solution will do me for now, although it means if i 
> want to keep the databases in sync i'll have to dump the tables and 
> load them into the other database. If there is something that springs 
> to mind that might be causing this behaviour please let me know as 
> this solution is fairly ugly. Otherwise, thanks for all your help.
>
> John
>
>
> _______________________________________________
> interchange-users mailing list
> interchange-users@icdevgroup.org
> http://www.icdevgroup.org/mailman/listinfo/interchange-users

Getting the above solution got me to thinking, and after trying to go 
down a variation on the theme i spotted an error in my catalog.cfg. i 
had the following lines:

#ifndef SQLDSN
Variable    SQLDSNCAT2          dbi:mysql:cat2
Variable    SQLUSERCAT2         cat2user
...

whereas i should have had #ifndef SQLDSNCAT2

the weird thing is that i *had* already defined SQLDSN earlier in the 
configuration file, so those lines should not have been parsed at all 
and the __SQLDSNCAT2__ connection string in my 
dbconf/mysql/products.mysql should have thrown up errors, which it 
didn't. Or, if it was parsed, it should have worked without any 
problems. i certainly wouldn't have expected this kind of behaviour.

but there you go - that's that. thanks again for everyones help and 
suggestions.

John