Since I had no useful debugging information (as the error was at saving time, I wasn’t even able to build the app nor to execute it), I tried to simplify my query and “complex” it step by step. Finally I ended up with something odd, for what I have to elaborate about my query:
[SIZE=5]Some more introduction[/SIZE]
I work on a shared hosting service provider that asigns the database schemas’s name (MySQL DATABASE) tied to the hosting name. If my domain name was mynewdomainname.com, then my databases would be something like mynewdom_database1.
My client’s hosting handles the same way the database names.
The catch is this: My client’s requirements ask me to keep the data stored in 4 diferent schemas (say myclien_projects, myclien_finances, myclien_humanresources, myclien_warehouses). This is not a problem as I can execute queries in a database different as the one used for the app connection (using the third parameter of sc_lookup “Connection”). The problem arises when my client’s requirements ask for this:
1.- Get a report combining information of myclien_finance and myclien_warehouses together, for example
2.- Don’t mix data from different databases at mysql server
By requirement 1, I build some complex queries including INNER JOIN and UNION statements
By requirement 2, on the other hand, I can’t create views or modify the databases so I have one single sc_connection (I know, I know, It’s kind of unefficient, but that’s what they want…)
Anyway, this was not a big deal before. I managed to work around those issues by using inner joins, unions and the “connection” parameter of many scriptcase macros. The only thing you might see is that, as I can’t use two databases for a scriptcase connection, in my queries I explicitly name the database of a table, like this:
T1.col1 AS col1,
T2.col2 AS col2
FROM mynewdom_finances.myTable AS T1
INNER JOIN mynewdom_warehouses.myOtherTable As T2 On T1.col3 = T2.col3
This, again, works as intended: my client is happy and so am I… at least before deployment… That because my working evironment uses my databases named according to my domainname (and so I wrote it in the INNER JOINs), but on deployment they have another name.
This time, I created a workaround using the Scriptcase connection variables and a session variable like this (as a reminder, [sc_glo_banco] stores the database name of the connection) :
[db_prefix] = substr([sc_glo_banco], 0, strpos([sc_glo_banco],'_'));
and then, I use the session variable [db_prefix] in any inner join query.
Then again, It worked surprisingly good, until…
[SIZE=5]Back to the UNION STATEMENT[/SIZE]
My previous working UNION statements took their data from the same database (so, there was no need of the [db_prefix] variable), but this time, I needed to get in a GRID, the info of separate tables from separate databases, so I needed to use a INNER JOIN and my variable [db_prefix].
Then Scriptcase had enough of my tweaking (and perhaps also of my twerking) and crashed like I explained. If I use the UNION statement with the INNER JOIN of a fixed database (writting the name of the database instead of using the variable), it works as expected, but if I use my variable in the query, it crashes
I guess the only way I have to master this is by understanding how scriptcase’s grids works the SQL queries, what it really does when a query has a variable before being set (it’s set on execution, unless the system performs some kind of test run before compiling…).
Of course, that or convincing my client to open up their requirements…
Or to dismiss the new report application the way they want…
Or by the simplest (and ugliest) “change the coded database names just before deployment, and test it live at deployment” (yikes!)
Is it? I mean, I kind of know many solutions to this problem, still I don’t feel as it is solved
At least I feel like this thread may serve as inspiration for someone who needs to do any of those things I did, except of course, the last one…
Thanks a lot kafecadm. I’ll try to use your answer to support my plea to my client…