Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113

    Unanswered: Catch a select and execute a different one instead

    Our developers have gone and introduced a bug in a software release we did last night causing the application to fail all over the place. Basically an embedded piece of SQL is selecting the wrong fields.

    Given the processes involved in building a new version of our application a lot of our users are stuck up a certain body of water without a certain tool.

    I don't know whether my mind is playing tricks on me or not but I have a very very vague thought that I've ready something previously about being able to catch an SQL statement as it comes to the server and replace it with a different one.

    Is this possible or are those around me correct in saying I'm losing my marbles?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Is the faulty SQL code embedded in your code or is it in a View, Stored Procedure, Trigger or User Defined Function?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I think you may be thinking about INSTEAD OF TRIGGERS, but those are for I/U/D operations.
    Dave

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Sounds like it is time to enforce the following rule:
    Thou shalt only use stored procedures. Embedded SQL is forbidden.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by MCrowley View Post
    Sounds like it is time to enforce the following rule:
    *Unless it's an OR/M that can easily be re-gen'd.



    What is the faulty sql and what do you need to actually run? You might be able to do it with some view and schema trickery.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    I've spent countless hours trying to tell our application developers they shouldn't be embedding SQL for exactly this reason but no.. it's easier for them to just whack the code in there instead of sending a request to the DB team.

    The faulty SQL was referencing an incorrectly spelt column name so caused an error every time it was fired at the db server.

    Unfortunately our application build and deployment process takes the better part of 24 hours so I was hoping for a temporary server-side solution that could get us out of the mud whilst we built and deployed a new application. That was done over the weekend anyway so we're fine now - just had a lot of disgruntled users on Friday/Saturday.

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The faulty SQL was referencing an incorrectly spelt column name so caused an error every time it was fired at the db server.
    How could this ever get passed the testing stage?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    There was something you could do, BUT ... there are a lot of BUTs.

    Anyway. Let's say that your table is A which has a single column named my_col.
    And your select is SELECT my_con FROM A.
    You may:
    1. rename A as A1
    2. CREATE VIEW A (my_con) AS SELECT my_col AS my_con FROM A1
    3. TEST it, as the code above is written directly into the browser
    4. Pray
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Quote Originally Posted by Wim View Post
    How could this ever get passed the testing stage?
    The what stage, now?

  10. #10
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Quote Originally Posted by Wim View Post
    How could this ever get passed the testing stage?
    Don't get me started on that...

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by EngadaSQL View Post
    Unfortunately our application build and deployment process takes the better part of 24 hours so I was hoping for a temporary server-side solution that could get us out of the mud whilst we built and deployed a new application. That was done over the weekend anyway so we're fine now - just had a lot of disgruntled users on Friday/Saturday.
    Screw the app devs. They need to feel that heat from the users when the promote something to production that was CLEARLY untested. 24 hour build/deploy is unacceptable too.

    Anyway, the view would have "fixed" the issue. Now that it's no longer hot button, I'd say it's time to look at fixing your process and assigning accountability where appropriate.

    Also, revisit that OR/M idea I was talking about. Combined with very light weight unit tests (ie: can I create one of these here database objects?), it will prevent this from ever happening again. It's an easy compromise. You're going to run in to a slightly different flavor of the same bs whether they're using sprocs or not until the test/promotion process is at least marginally fixed anyway.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •