MySQL UNION in Grid application

Scriptcase 8 Discussion
, , ,
#1

Introduction
I have a running grid with a MySQL sentence that unites a single constant row with a regular select statement; somehitng like this:

(SELECT 
'StringConstant1' AS `col1`, 
'StringConstant2' AS `col2`, 
'StringConstant3' AS `col3` ) 
UNION 
(SELECT 
col1, 
col2, 
col3 
FROM myTable )

this works marvelous.

Now consider the next situation:

I want to create a grid with a MySQL UNION sentence of two SELECT statements, but one of them has some INNER JOIN statements and explicitly retrieves the same type and amount of columns; something like this:

(SELECT 
T1.`col1`, 
T1.`col2`, 
T2.`col3` 
FROM myTable AS T1
INNER JOIN myOtherTable AS T2 ON T1.col1 = T2.col1
) 
UNION 
(SELECT 
col1, 
col2, 
col3 
FROM myTable )

AFAIK, the first statement returns only 3 columns, as well as the second one.

I tested my query and it works, but when I use it to create a Grid app, scriptcase crashes even the saving process (a pop up shows up and It looses any field in the app)

My question
Why is Scriptcase crashing with a working query, even when it supports the UNION structure?

I practically don’t know a thing about PDO implementation and behaviour on PHP, but if the query works, how Scriptcase handles the queries? Didn’t it only take the result of the PDO execution of the SQL on the server?

#2

Hello Daniel:

TBH with you sometimes, Scriptcase has problems with complex queries, so my recomendation is… create a view… and use such view to create your grid.

Regards.

P.S.- Viva M?xico cab… <- complete la frase con lo que des?e >.<

#3

[SIZE=5][UPDATE][/SIZE]

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:

SELECT 
    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

[SIZE=5]Speculations[/SIZE]
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!)

[SIZE=5]Conclusion[/SIZE]
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…

#4

I agree with kafecadm that creating a view often is often the cure for many types of problems with grid queries. On the other hand, I also understand your hesitation of having to change database names manually in the production environment. Let me offer you a possible solution:

In the onApplicationInit or onLoad section of your grid application have some code that “create or replace view …”, where the view is created in the main database that SC is connected to. Now, in the “create or replace view…” SQL code you can incorporate the prefix of the database as taken from the actual database name in the production (shared hosting) environment – by using the SC database variables that you are already familiar with. Thus your query will automatically adjust itself in both the development and production environment and not need any manual adjustment. I have not tried this out. It is just an idea, but it is what I would do and test, were I in your situation.

#5

Thanks for the advice Frank. I get your Idea, just I think It might not work very well on a multi user environment (as more than one user could access the site and thus execute the “create or replace view…” statement.)

Final Update
Finally I agreed with my client on a middle ground (kind of…) for allowing me to use views on the database (though I must create them on a new database… for not “contaminate” ). At least I don’t need the INNER JOIN, and as the VIEWs are defined directly into the database, I define them explicitly using the database name, but Scriptcase’s deployment don’t need to handle them…

Thanks for the help everyone!

#6

Yes, with many concurrent users there will probably be locking issues. I guess, I got too fascinated about idea of automating the whole thing :-). Sounds like a good solution with views in a separate database. I get the impression that it is a specialised application/installation for a particular customer, and so I guess having to set it up in the operation environment is just fine.

For my own application, I will also manually set up the operation database for the first few installations. Later, I have this idea of automating the deployment and perhaps even have a configuration part of the application that will take care of setting up the final database set up.

Have a good weekend, Daniel!