No announcement yet.

A long day in SQL county

  • Filter
  • Time
  • Show
Clear All
new posts

  • A long day in SQL county

    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:


    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 and then saved the application in utter ruin.
    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 COUNT(*) as totalcount FROM computers WHERE computers.userID = AS totalcount
    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,

    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.

  • #2
    A short tip: basically, I would put a complex sql query in a view. Lock at this URL for views in mysql.

    Many RAD-tools (not only SC) have problems with complex querys. With views is that much easier and faster.
    Best regards: - Reinhard -

    I use ScriptCase 8 Enterprise Edition, Version 8.(latest)


    • #3
      try group by; it may be faster
      select count(*), userid from computers group by userid

      (you can join any other tables)


      • #4
        Hi Reinhard,

        thanks for the advice. Somehow most SQL tutorials managed to forget about that minor functionality
        Read into it, wrote the views and got to realize that it puts a massive load on the system (as in 5 minutes of continuous max load on the MySQL server). Oh the joys of big tables and complex views *g*
        Asynchronous calculations and DB Warehouse, here I come (after trying to optimize the queries, there's likely still some meat in there, but I doubt it's enough to allow for runtime calculations).
        Anyway: Thanks, it was a major step forward on my way to Making Things Work.