Hello fellow forum dwellers,
How it all began:
I’ve recently been handed the joyful job of creating a dashboard application that draws its information from various tables within a single database. Me never having touched a single piece of PHP or SQL before (I do fine designing a database, but coding against one is another thing altogether), I was particularly looking forward to using ScriptCase to cover for that lack of knowledge. So off I went with my shiny new toy to reinvent the way Dashboards were meant to be made.
Yeah, right.
Calamity strikes:
The first assassination attempt upon my optimism occured when I checked the documentation, especially the pdf guide. shudder (Not because there was bad info in there, mind: It’s just well hidden in all the redundant - and badly formatted - clutter)
Yet that was but the first of 'em: Limiting the number of selected values is a bit hard, the container-application - an aspect I had put in considerable hope - turns out to be absolutely inflexible and reasonably unpredictable (try setting column-width to moderately sized segments in units of “em”). Is there any way to create widgets that are custom-sized? Partially overlapping widgets? To actually give the user some say in how they are placed on the dashboard? Or a way to provide a widget-library for the users, from which they can choose the ones they like best? As it is, i need to provide them fixed-width columns and widgets and stack them vertically, with the user having to rearrange them on their own, each time they load it …
but I’m straying from my path, especially from the reason I post it in the database section …
Rise of the one evil
Alright, I realized the onboard tools wouldn’t do all my work for me (shucks), but that I’d need to step up and do my part. First I gave up on perfect Dashboard and settled on adequate, what I had would have to do. Then there was the trouble with getting the data I wanted: Custom fields are unsortable, grid-replaced original fields would still act as if they were the values they replaced. That left me with only one place where I could reasonably expect to get the data I want:
SQL
The problem: I never had the joy of doing that. Reading up on the topic (I really can only recommend visiting Wikipedia for this, while having a local MySQL installation at hand to test things out. The documentation there is really comprehensible) I started on building my SQL skills, testing things as I went in MySQL. Finally confident in those skills I crafted a beautiful SELECT command that would grab all the information I want, calculate all the info I need and return the finalized tables to the Application. All calculation done on the side of SQL had to mean it would work. Right? Right?!
Sooo … after rigorous testing I put it into my grid application and told it to run with it.
Which is when the One True Evil (aka. Demon Murphey), reared its head, terminated my execution in fatal errors (something about multiple SELECT commands in a single query, which I hadn’t known to be a crime), smashed all my lovingly configured fields, messed up the groups, wiped out the links [b]and then saved the application in utter ruin.[/b]
Sorry for being a bit testy about it, but that made my day.
I really have to wonder: How come one misconfigured SQL command - one that’s actually legal in SQL - can trash my application without even bothering to tell me that that’s what it’s planning to do?
Did I somehow inadvertently disable safeguards / validation?
Since the original SQL command is a bit complex I recreated the scenario with a sample command (which worked just fine in wrecking the Application I created for the test):
SELECT
vorname,
nachname,
(SELECT COUNT(*) as totalcount FROM computers WHERE computers.userID = employees.id) AS totalcount
FROM
employees
In the original version with quite a few more fields (42) and lots of time spent on tuning it the damage was proportionally worse. Yeah, I learned to create copies and don’t touch the originals until I compiled the changes and tested it in ScriptCase.
Guessing what happened:
If I had to guess what happened, I’d assume that the SQL code failed some ScriptCase internal validation, the query was aborted and returned null and some helpful automatic user-assist mechanism decides to adjust the configuration to the recent changes. And since it saves an application when running it …
Anyway, best regards,
Fred
Ps.: If anybody can tell me where can find info that reliably tells me what the hell hit me and what else can cause this, I’d much appreciate it.
