• (cs) in reply to DrPizza
    DrPizza:
    jsmith:
    DrPizza:

    <SNIP>a bunch of completely factual, but yet not quite useful information</SNIP>

    DrPizza,

    Yes, stored procedures provide encapsulation, a well-defined protocol for accessing the database, partitioning, denormalizing, abstraction, decoupling, and scalability.  However so does:

    C#
    Java
    VB
    ... and 7 thousand other OO languages.

    Complete bollocks.  How the fuck does a piece of C# help ensure the data integrity of an old mainframe application we have working on the same database?  Oh, that's right.  It doesn't!

    Do you have anything useful to add?


    Yup.  I most cases, the C# data access layer will be implementable as a seperate tier.  Just call them from the mainframe app via Web Services.  If the language is too old to support web services, then SPs might actually be a good solution.

    DrPizza:
    And of all of them, Transact-SQL is the hardest to write, maintain, and debug(except maybe malbolge).

    Rubbish.  T-SQL is a fuckload better than C# for writing data manipulation operations in.  SQL is a set-oriented language and it excels at set-oriented operations; it also has convenient support for transactions.  C# is neither. 

    I would never suggest replacing the set operations with procedural operations.  However, the logic of which operations to run, in which order, adding activity log records, etc (all the statement level logic) is far easier in C#.

    DrPizza:
    I'm good at T-SQL, but I can still do anything in C# in half the time I can do it in T-SQL.  Decoupling example:  A working application needs a modification.  The security team decides that the current reversable encryption scheme in place for storing account password isn't good enough.  They now want a one-way hash with a salt.  Let's look at how our life would be in two situations:

    A.  You wrote all the DB access routines as SPs.  There are SPs for setting a password, and for validating a password.  Cool, now all you have to do is change the content of two SPs and run an update script on the current data (the current pw is reversible).

    Which is trivial.

    Not in SQL 2000 or earlier.

    DrPizza:
    B.  You wrote all the DB access routines in C#.  There are methods on the User class for setting a password and for validating a password.  Cool, now all you have to do is change the content of two methods and run an update method on the current data (the current pw is reversible).

    So you need to do the same database work *anyway*.  And you have to add a load of C# work on top.  And a bit of C work too, for that application, and maybe some COBOL too, because you're still using that.  Great.

    No, you add two lines of code to each of two existing methods.  Done.  The database can already persist a password, it won't care that it isn't clear text.

    DrPizza:
    "A" would be interesting to implement.  We could do one of several things;  1.  Roll our own hash algorithm (very bad idea), 2.  Write an extended stored procedure in C (That'll be fun, and look at the nifty distribution issues we've just created),

    All current databases worth using allow the use of high-level languages to implement functions within the database--which is fine.  Languages like Java or C# or C++ are good at string manipulation and this kind of complicated arithmetic, so we might as well use them for what they're good at.  In SQL Server you could write a C# function (which would just call some crypto library); in Oracle you could use Java (likewise); in DB2 I think you could choose between Java and .NET; in postgres you could use python or perl or tcl or C.  The distribution "issues" are a fuck of a lot less than having to change the application.  To "distribute" the change to the application you need to dump the change on every server and client that accesses the database, and there are inevitably more of these than there are database servers.  Making changes on the database side is easy; they're centralized and it's simple to ensure that all the upgrades happen simultaneously.  Making changes to database clients is not easy; they're not centralized and upgrading them all in parallel is difficult.

     


    Not all.  SQL 2000 has no access to a high level language.  As for distribution, you made the assumption that the application only has two physical tiers.  In my case, I'd just have to replace the middle tier code, running on a server or server farm.

     

  • (cs) in reply to Bullet
    Bullet:
    jsmith:
    Jeff S:
    jsmith:

    This is what really gets me..... why do you think that if the data access layer is outside the database that all the logic executes outside the database.  What's wrong with this:

    <FONT face="Courier New">SqlCommand cmd = new SqlCommand("UPDATE Products SET Price = Price * 1.1", cn);
    cmd.ExecuteNonQuery();</FONT>

    You don't have to be an idiot and write:

    <FONT face="Courier New">SqlCommand cmd = new SqlCommand("SELECT ProductID,  FROM Products", cn);
    DataReader dr = cmd.ExecuteReader();

    while(dr.Read())
    {
      SqlCommand cmdInner = new SqlCommand
                    ("UPDATE Products SET Price = " + 
                     (dr("Price") * 1.1).toString() + 
                     " WHERE ProductID = " +
                     dr("ProductID").toString(), cn);
      cmdInner.ExecuteNonQuery();
    }</FONT>

    Whatever you put in the SqlCommand (top example of course) will execute as efficiently as a stored procedure.



    jsmith -- what happens when that first update statement is really 60 lines long and accepts 5 parameters?  how do you debug it properly?   Where do you store the code for the T-SQL -- it is all inline, concatenated together?  Do you put it in a separate config file and load it in?

    When that code is in a stored procedure, you gain the advantage of:

    1 - any application, no matter what code they are written in, has a standard, single place to call this T-SQL code.
    2 - That T-SQL code can be fully tested, tweaked, modified, optimized, etc *without* looking through source code or config files, and/or recompiling the app. 
    3 - Your application code is much more shortable and more readable
    4 - Your T-SQL is verifed compiled.  Now,  I do agree that performance benefits of stored procs are overrated.  So forget about that.  But now your code is full syntax checked, objects are verified, column names are checked, you can examine the execution plan, etc.  Leaving the code to be executed from an ad-hoc string doesn't allow for any of this!  Don't you want your code to be verified before deploying your app?  When you write code in other languages that are compilable, do you compile your application before sending it out to production? Why not do the same to your database code?

    Another issue is the fact that in-line SQL tends to lead most developers down the path of concatenating parameters to the SQL statement and directly executing it instead of using parameters.

    So, I am not 100% sold on using stored procs all the time, but they have huge benefits and to ignore those benefits when working on a complex application would be a mistake.

    You also wrote earlier that for you, T-SQL is much harder to write than other languages.  This implies that your database code is probably not as cleanly written as it could be, since you admit to being a little weak when it comes to SQL.  Because if you are really good with it, you would know that pretty much *any* manipulation of data being done is much, much shorter, simplier, quicker and easier to write in SQL than most any other language I've worked with.  And if you are having trouble writing in SQL becuase you feel it is more difficult, isn't it eaiser to write it in an environment designed for it which compiles the code rather than embedded literals with SQL statements into your applicaiton?

    that is like saying "C++ is more difficult to write than VB ... therefore, instead of using Visual Studio to compile my C++ code, I will embedd it in string literals in my VB app and have it compiled 'on the fly' at execution time.  This will be easier than fully developing individual C++ libraries, compiling them, and then calling them from the VB application using a COM interface"  That doens't make much sense, does it?


    I *never* said I was weak in T-SQL.  I have been an MCP in SQL since 1996.  It's T-SQL that's weak.  How can you say that a language with only one looping structure and one statement-level conditional structure is easy to use?  The basic cursor template is:

    <FONT face="Courier New">DECLARE authors_cursor CURSOR FOR
    SELECT au_lname FROM authors
    WHERE au_lname LIKE "B%"
    ORDER BY au_lname

    OPEN authors_cursor

    -- Perform the first fetch.
    FETCH NEXT FROM authors_cursor

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
    BEGIN
       -- This is executed as long as the previous fetch succeeds.
       FETCH NEXT FROM authors_cursor
    END

    CLOSE authors_cursor
    DEALLOCATE authors_cursor</FONT>

    Notice the two fetches.  Because of a lack of a loop with a condition check at the end, fetch has to be done twice.  If a new variable is added, both fetches need to be updated.

    Also, SPs are NOT fully checked when they are created.  Table names and column names are not validated until the statements are run.  If an SP has several branches, testing is a pain.  You can create it WITH SCHEMABINDING to cause it to validate objects immediately, but then you have to drop the procedure to alter the underlying objects.

    Inlining code isn't a big deal.  All you need is a good tool.  It takes a few hours to write a tool that will inline any SQL and you'll never even see the string concatenation.  Put all the inlined SQL calls in a class module as static methods and it looks just like you are calling stored procedures, only easier.

    My development process is:
    1.  Write and test block of SQL in Query Analyzer
    2.  Wrap it in a method with tool
    3.  Write .Net code that calls block of SQL

    It looks identical to how most people write SPs.  It's so close that my SQL wrapper tool can actually make it a stored procedure with altering any of my calling code.

    As for concatenating, my wrapper tool ONLY supports parameters.  That's the only way to get information into the SQL block.  Concatenating SQL leads to SQL injection vulnerabilities.

    So, back to the original points:
    1 - Web services also provide a single point of access for any language.
    2 - My T-SQL is debugged in Query Analyzer.  The SQL source is stored seperately and is very simple to re-embed in the code.
    3 - The embedded SQL is in it's own code modules.  The actual business code isn't cluttered with anything.  IT LOOKS JUST LIKE A PROCEDURE CALL.
    4 - Your SP code is not verified either (see above).  Unfortunately with lazy object resolution, that is always an issue.  BTW, the first time one of my methods are run, the server has to parse the whole block of code.  If any part of it doesn't parse, the whole block is rejected.  So, as long as I run it at least once, I know that it is syntactically correct.  At the end of the day, I get as much validation as a stored procedure does.  The only way I can get burned is if I deploy a SQL block that I've never run even once.  I that case, I'd deserve whatever I get.

    Stored Procedures are just another form of modular programming.  They offer nothing magic or special.  When analyzed, nearly every "advantage" of a stored procedure is really just a choice.  Either get that advantage by modularizing within the database, or get that same advantage by modularizing outside the database.  However, T-SQL is a very weak programming language.  T-SQL only has 10 control-of-flow keyword.  Most modern programming languages have 50 or more.

    I love SQL for set oriented operations.  I'm no stranger to 300 line update statements.  But, it really is a BAD procedural language.  That's why they introduced CLR code in SQL 2005.

    The problem we run into here is that a lot of good programmers use stored procedures effectively.  Then they teach junior programmers that somehow their good programming practices were a direct result of using stored procedures and tell the junior that "they must use SPs".  The junior programmers then go on to make horrible applications.  Senior programmers should really be telling junior programmers how to make maintainable applications and always be on the lookout for better ways to make their applications maintainable.  Stored Procedures were the state of the art in the early 90s.  The world has moved on since then.  They still work and are still the best choice for some things, but there are better ways to do what most people do with stored procedures.  I'm not saying SPs have gotten worse, just the alternatives have gotten much better.

     

    No matter what kind of tool you use, if you allow the database to accept raw SQL then any developer working on another project that touches that database can write inline sql to modify data withoiut going through yuour tool...  Then you have multiple applications dependant on the data structures, field names, (potentially field order as well) , and relational constraints within the dataabase.  Changing any of these is then extremely likely to break someone's app.   If on the other hand you require all access to go threough well-designed set of SPs, all development must go through them (no logins have select, update or delete permissions on raw tables)  Then, you can change, extend, or modify anything in the database without breaking any applicatiomn or middle  iier code, because you can modify the internals of the Stored proc and keep it's public interface immutable. 



    Front end developers don't even have a SQL Connectionstring.  how are they going to issue raw SQL?  All they can do is call the methods I give them. I don't see how a well designed set of SPs is any better in this regard than a well designed set of methods.  Sure, a well designed set of SPs is better than a poorly designed set of methods, but that doesn't prove that there is any advantage to SPs.

  • (cs) in reply to jsmith
    jsmith:

    Please tell me how to do this in SQL 2000 or earlier.  They added CLR support to 2005 which finally solves the problem, but that was 15 years after people started recommending SPs to do everything.  What did they do for the previous 14 years?


    Using a DBMS that was worth it's name?

    jsmith:

    What about those who don't have 2005 yet?  If only you knew the MS world well enough to know that Microsoft SQL Server 2000 doesn't have a cryptography library.


    No, thank you. I had my share with Sybase ASE and T-SQL (which I consider an abomination). There are, however systems, where it does make sense to do (almost) everything in SPs (or Packages). It's really not my fault if customers/developers chose something different.

    l.
  • (cs) in reply to Jeff S
    Jeff S:
    jsmith:

    I *never* said I was weak in T-SQL.  I have been an MCP in SQL since 1996.  It's T-SQL that's weak.  How can you say that a language with only one looping structure and one statement-level conditional structure is easy to use?  The basic cursor template is:

    <FONT face="Courier New">DECLARE authors_cursor CURSOR FOR
    SELECT au_lname FROM authors
    WHERE au_lname LIKE "B%"
    ORDER BY au_lname

    OPEN authors_cursor

    -- Perform the first fetch.
    FETCH NEXT FROM authors_cursor

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
    BEGIN
       -- This is executed as long as the previous fetch succeeds.
       FETCH NEXT FROM authors_cursor
    END

    CLOSE authors_cursor
    DEALLOCATE authors_cursor</FONT>

    Notice the two fetches.  Because of a lack of a loop with a condition check at the end, fetch has to be done twice.  If a new variable is added, both fetches need to be updated.

    A



    jsmith -- I am not trying to be condescending or sarcastic, but that statement clearly indicates that you have a *lot* to learn about SQL.   If your complaint about the language centers around the features and syntax of *cursors*, then you are not using SQL properly at all.



    I picked a cursor example from books online because I knew it had a loop in it.  That way I didn't have to make up my own code.  It seems you have a *lot* to learn about language since you took my example of SQL having poor looping structures and turned it into a critique of my SQL abilities.  I think one of the primary defenses of a beaten debater is "attack the attacker".  Looks like I'm doing well.

    BTW, back to the original point, SQL is a horrible procedural language.

  • tdog (unregistered) in reply to Good Architecture Man
    Anonymous:
    Alex Papadimoulis:

    Though they take a bit more time to develop upfront, using database stored procedures are definitely the way to go for most information systems. They provide a looser coupling to the "data layer" by limiting the points of entry and offer stronger cohesion by breaking operations into reusable functionality.



    I have to ask; why do people think stored procedures are the way to go? I know most people on this site are .net oriented, and I know MS for the longest time told people to use them (they have since gotten away from that, but hey, it is 2006), but what architectural reason could one use to justify this travesty?

    Don't get me wrong, SPs have their place, but to use them for all your dataaccess and business logic is just plain nuts. If you are using VS.NET 2k5, look at DataSets or nHibernate. If you are using Java, take a gander at Hibernate (or EJB 3....though I still am leary of EJB) and, of course, there is Active Record for Ruby.

    BOSH!!  100% disagree.  (Direct table access + programmers)  = Disaster.

  • (cs) in reply to Jeff S
    Jeff S:
    jsmith:

    My development process is:
    1.  Write and test block of SQL in Query Analyzer
    2.  Wrap it in a method with tool
    3.  Write .Net code that calls block of SQL

    It looks identical to how most people write SPs.  It's so close that my SQL wrapper tool can actually make it a stored procedure with altering any of my calling code.



    So your "tool" takes your verified SQL code and converts it to a method.  Does your tool also convert the method *back* to T-SQL code that you cut and paste into QA for further development?

    So, you need to amend your "developement process" with the maintenance process:

    4. Use your "tool" to convert code *back* to T-SQL to make changes
    5. Test your T-SQL
    6. Use your "tool" to convert it *back* to a method again to deploy the change
    7. recompile your application or data access layer
    8. redistribute your applicaiton or data access layer

    The funny thing is, like lots of people who rebel against best practices, you are working extremely hard just to avoid using stored procedures for reasons unknown. 

    Doesn't

    1. Create + test stored proc
    2. call the stored proc where needed in your code
    3. ALTER stored proc to make changes

    seem a little bit simplier rather than your 8-step process?

    Instead of trying proving that you can develop well and efficiently and all that without using stored procedures, why not just explain in a very simple paragraph the *benefit* you get by avoiding stored procedures. Don't try to prove to use that with the proper tools and a bunch of extra steps and all that, it is "just as good" (that has been your whole position so far), explain why you feel it is *better* to not use stored procs.


    Steps 4 and 6 don't exist.  The tool save the SQL source to be edited at any time. 
    You have to do 7 and 8 as well......  unless you develop on th production server.
    It's also highly like that 5 isn't an "extra" step.  Everyone tests.

    I get several benefits from a real language-
    1.  A better development environment.  Visual Studio is far better than Enterprise Manager.  I like intellisense.
    2.  A better language.  I get to use switch, foreach, for, and a bunch of other constructs.
    3.  Better security model.  Client application have no way to even connect to the database.  I subtle but important aspect is dynamic SQL.  This is why I switched in the first place.

    In our old system, all data access was through SPs.  An app user was given the rights to all the SPs and deny direct access to the tables.  Sounds good so far.  However, whenever we implemented some sort of advanced search feature, it got fun.  We had to anticipate every possible combination of search criteria because dynamic SQL doesn't work with this security model.  If an SP creates a where clause and tries to use EXECUTE to run the query, it doesn't work.  EXECUTE runs in the security context of the user, not the SP.  Also, EXECUTE can only run 8000 byte blocks of SQL.  It sounds rediculous, but I was regularly hitting the limit on an app with a lot of built-on-the-fly pivot queries (until SQL 2005, that was the only way to do a pivot).

  • (cs) in reply to jsmith
    jsmith:
    Jeff S:
    jsmith:

    I *never* said I was weak in T-SQL.  I have been an MCP in SQL since 1996.  It's T-SQL that's weak.  How can you say that a language with only one looping structure and one statement-level conditional structure is easy to use?  The basic cursor template is:

    <font face="Courier New">DECLARE authors_cursor CURSOR FOR
    SELECT au_lname FROM authors
    WHERE au_lname LIKE "B%"
    ORDER BY au_lname

    OPEN authors_cursor

    -- Perform the first fetch.
    FETCH NEXT FROM authors_cursor

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
    BEGIN
       -- This is executed as long as the previous fetch succeeds.
       FETCH NEXT FROM authors_cursor
    END

    CLOSE authors_cursor
    DEALLOCATE authors_cursor</font>

    Notice the two fetches.  Because of a lack of a loop with a condition check at the end, fetch has to be done twice.  If a new variable is added, both fetches need to be updated.

    A



    jsmith -- I am not trying to be condescending or sarcastic, but that statement clearly indicates that you have a *lot* to learn about SQL.   If your complaint about the language centers around the features and syntax of *cursors*, then you are not using SQL properly at all.



    I picked a cursor example from books online because I knew it had a loop in it.  That way I didn't have to make up my own code.  It seems you have a *lot* to learn about language since you took my example of SQL having poor looping structures and turned it into a critique of my SQL abilities.  I think one of the primary defenses of a beaten debater is "attack the attacker".  Looks like I'm doing well.

    BTW, back to the original point, SQL is a horrible procedural language.



    Jsmith -- if you know SQL, you also know that you almost never need loops.  The fact that you feel that looping and cursor use is clumsey or inadequate for your purposes which thereforce leads you to write your data manipulation code in other languages simply means that you arent' a good set-based SQL programmer. there's nothing wrong with that.  

    I am not quite sure how you came to the conclusion that I don't know SQL because I am pointing this out to you, but if that's the assumption you are making, then I suppose you have every right to your opinion.  Ironically, this reminds me of a time a co-worker made negative judgements about my skills in SQL when I told him one time that I had no clue about cursor syntax and I never use them.  "You can't even write a cursor!? you've got a lot to learn about SQL" he told me.  It was actually pretty funny.
  • tdog (unregistered) in reply to [Si]dragon

    Anonymous:
    Alex Papadimoulis:
    Though they take a bit more time to develop upfront, using database stored procedures are definitely the way to go for most information systems. They provide a looser coupling to the "data layer" by limiting the points of entry and offer stronger cohesion by breaking operations into reusable functionality.


    Considerably more time upfront and considerably more time in maintenance.  Every time a programmer has to tackle software using stored procedures, calamity ensues.  And for what benefit?  I have yet to see the case where SPs increase reliability or reduce bugs.  Maybe improve performance.  Unfortunately, developer time is a lot more expensive than the value of the time saved.

    Violating patterns by spreading business logic across layers and adding more variables and complexity into the mix.  SPs are not a good idea.

    The database stores data.

    The business logic manipulates it.

    The view displays it.

    Sticking to these simple principles is a Good Idea.  For example, consider the plight of your typically hapless developer.  There is no reason that he or she should have to also be an expert in whatever database you're using, let alone anything.  Period.  That means using services that do the mapping between business objects and persistence.  That work is best handled by someone who is a database expert.

    I can come up with more reasons but there are plenty of materals on the subject which support this.  To get you started, Google on "Gang of Four", "Spring", "Hibernate", "Pragmatic Programmer", and so on.  Enjoy.

     

    EXCUSE ME BUT...

    If your skill level is such that it takes you longer to develop a stored procedure than to right an embedded select statement, then you should have your rights to the database revoked!  There is more to consider here too such as maintenance, security, reusability not to mention performance.  Just because they aren't the "cool" new thing anymore doesn't mean their place is no longer valid. 

  • (cs) in reply to tdog
    Anonymous:
    Anonymous:
    Alex Papadimoulis:

    Though they take a bit more time to develop upfront, using database stored procedures are definitely the way to go for most information systems. They provide a looser coupling to the "data layer" by limiting the points of entry and offer stronger cohesion by breaking operations into reusable functionality.



    I have to ask; why do people think stored procedures are the way to go? I know most people on this site are .net oriented, and I know MS for the longest time told people to use them (they have since gotten away from that, but hey, it is 2006), but what architectural reason could one use to justify this travesty?

    Don't get me wrong, SPs have their place, but to use them for all your dataaccess and business logic is just plain nuts. If you are using VS.NET 2k5, look at DataSets or nHibernate. If you are using Java, take a gander at Hibernate (or EJB 3....though I still am leary of EJB) and, of course, there is Active Record for Ruby.

    BOSH!!  100% disagree.  (Direct table access + programmers)  = Disaster.


    That's a false dichotomy.  There are other options than Direct table access or SPs.  A well defined data access layer protects the data as well as SPs.
  • (cs) in reply to jsmith
    jsmith:
    Jeff S:
    jsmith:

    My development process is:
    1.  Write and test block of SQL in Query Analyzer
    2.  Wrap it in a method with tool
    3.  Write .Net code that calls block of SQL

    It looks identical to how most people write SPs.  It's so close that my SQL wrapper tool can actually make it a stored procedure with altering any of my calling code.



    So your "tool" takes your verified SQL code and converts it to a method.  Does your tool also convert the method *back* to T-SQL code that you cut and paste into QA for further development?

    So, you need to amend your "developement process" with the maintenance process:

    4. Use your "tool" to convert code *back* to T-SQL to make changes
    5. Test your T-SQL
    6. Use your "tool" to convert it *back* to a method again to deploy the change
    7. recompile your application or data access layer
    8. redistribute your applicaiton or data access layer

    The funny thing is, like lots of people who rebel against best practices, you are working extremely hard just to avoid using stored procedures for reasons unknown. 

    Doesn't

    1. Create + test stored proc
    2. call the stored proc where needed in your code
    3. ALTER stored proc to make changes

    seem a little bit simplier rather than your 8-step process?

    Instead of trying proving that you can develop well and efficiently and all that without using stored procedures, why not just explain in a very simple paragraph the *benefit* you get by avoiding stored procedures. Don't try to prove to use that with the proper tools and a bunch of extra steps and all that, it is "just as good" (that has been your whole position so far), explain why you feel it is *better* to not use stored procs.


    Steps 4 and 6 don't exist.  The tool save the SQL source to be edited at any time. 
    You have to do 7 and 8 as well......  unless you develop on th production server.
    It's also highly like that 5 isn't an "extra" step.  Everyone tests.

    I get several benefits from a real language-
    1.  A better development environment.  Visual Studio is far better than Enterprise Manager.  I like intellisense.
    2.  A better language.  I get to use switch, foreach, for, and a bunch of other constructs.
    3.  Better security model.  Client application have no way to even connect to the database.  I subtle but important aspect is dynamic SQL.  This is why I switched in the first place.

    In our old system, all data access was through SPs.  An app user was given the rights to all the SPs and deny direct access to the tables.  Sounds good so far.  However, whenever we implemented some sort of advanced search feature, it got fun.  We had to anticipate every possible combination of search criteria because dynamic SQL doesn't work with this security model.  If an SP creates a where clause and tries to use EXECUTE to run the query, it doesn't work.  EXECUTE runs in the security context of the user, not the SP.  Also, EXECUTE can only run 8000 byte blocks of SQL.  It sounds rediculous, but I was regularly hitting the limit on an app with a lot of built-on-the-fly pivot queries (until SQL 2005, that was the only way to do a pivot).



    jsmith -- Now you are talking about building ad-hoc reporting statements and comparing that to writing data access stored procedures.  Those are completely different reasons and different contexts for accesssing the database.  Any good, flexible reporting tool will of course need to construct a SQL statement.  You are jumping all over the place, changing not only your viewpoint but the context of your arguement.  I admire your persistence in defending your position, but you still haven't even techinically stated your position.  All you are doing is trying to find ways to disagree with others who are defending stored procedures, or finding ways to refute those who disagree with you.  Not to mention being very defensive and (unfortunately) demonstrating a lack of knowledge when it comes to the concept of set-based server-side processing.


  • (cs) in reply to Jeff S
    Jeff S:
    jsmith:
    Jeff S:
    jsmith:

    I *never* said I was weak in T-SQL.  I have been an MCP in SQL since 1996.  It's T-SQL that's weak.  How can you say that a language with only one looping structure and one statement-level conditional structure is easy to use?  The basic cursor template is:

    <FONT face="Courier New">DECLARE authors_cursor CURSOR FOR
    SELECT au_lname FROM authors
    WHERE au_lname LIKE "B%"
    ORDER BY au_lname

    OPEN authors_cursor

    -- Perform the first fetch.
    FETCH NEXT FROM authors_cursor

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
    BEGIN
       -- This is executed as long as the previous fetch succeeds.
       FETCH NEXT FROM authors_cursor
    END

    CLOSE authors_cursor
    DEALLOCATE authors_cursor</FONT>

    Notice the two fetches.  Because of a lack of a loop with a condition check at the end, fetch has to be done twice.  If a new variable is added, both fetches need to be updated.

    A



    jsmith -- I am not trying to be condescending or sarcastic, but that statement clearly indicates that you have a *lot* to learn about SQL.   If your complaint about the language centers around the features and syntax of *cursors*, then you are not using SQL properly at all.



    I picked a cursor example from books online because I knew it had a loop in it.  That way I didn't have to make up my own code.  It seems you have a *lot* to learn about language since you took my example of SQL having poor looping structures and turned it into a critique of my SQL abilities.  I think one of the primary defenses of a beaten debater is "attack the attacker".  Looks like I'm doing well.

    BTW, back to the original point, SQL is a horrible procedural language.



    Jsmith -- if you know SQL, you also know that you almost never need loops.  The fact that you feel that looping and cursor use is clumsey or inadequate for your purposes which thereforce leads you to write your data manipulation code in other languages simply means that you arent' a good set-based SQL programmer. there's nothing wrong with that.  

    I am not quite sure how you came to the conclusion that I don't know SQL because I am pointing this out to you, but if that's the assumption you are making, then I suppose you have every right to your opinion.  Ironically, this reminds me of a time a co-worker made negative judgements about my skills in SQL when I told him one time that I had no clue about cursor syntax and I never use them.  "You can't even write a cursor!? you've got a lot to learn about SQL" he told me.  It was actually pretty funny.

    I said you didn't know language..... I meant English.  This is the second time you misread my post.

    The first was when you took my example and inferred that I liked cursors (I think cursors are evil and I carry evidence of that fact with me to show to anyone that I see writing a cursor)

    The second is when you took my statement "you have a *lot* to learn about language" as "you don't know SQL".  I never said that.  BTW, I still see personal attacks and now dodging the question ("you also know that you almost never need loops").  But still, SQL is a horrible procedural language.  Having a standing practice that data access code goes in SPs WILL eventually lead to some procedural logic in T-SQL.  My standing practices *never* forsce you to do that.  Nor do they prevent you from using SQL (of the embedded variety) whenever appropriate.  I think I have the best of both worlds.

  • (cs) in reply to jsmith
    jsmith:

    That's a false dichotomy.  There are other options than Direct table access or SPs.  A well defined data access layer protects the data as well as SPs.


    I just keep thinking: SPs are one of the builtin methods to aid the developer in certain task such as providing data integrity. So are constraints. Where would you keep those? Also in the data access layer? Or are you splitting that concept here already?

    l.
  • (cs) in reply to lofwyr
    lofwyr:
    jsmith:

    Please tell me how to do this in SQL 2000 or earlier.  They added CLR support to 2005 which finally solves the problem, but that was 15 years after people started recommending SPs to do everything.  What did they do for the previous 14 years?


    Using a DBMS that was worth it's name?

    jsmith:

    What about those who don't have 2005 yet?  If only you knew the MS world well enough to know that Microsoft SQL Server 2000 doesn't have a cryptography library.


    No, thank you. I had my share with Sybase ASE and T-SQL (which I consider an abomination). There are, however systems, where it does make sense to do (almost) everything in SPs (or Packages). It's really not my fault if customers/developers chose something different.

    l.

    Oracle is a database company.  Naturally, their middle tier technology (Java and PL/SQL) runs in the database.  Microsoft is an application company, their middle tier technology (COM/.Net) used to run outside the database.  Now .Net runs in the database just like Oracle.  It doesn't make SQL Server 2000 a bad database, it just makes you write applications that cater to the strengths of the environment.
  • (cs) in reply to jsmith
    jsmith:

    Oracle is a database company.  Naturally, their middle tier technology (Java and PL/SQL) runs in the database.  Microsoft is an application company, their middle tier technology (COM/.Net) used to run outside the database.  Now .Net runs in the database just like Oracle.  It doesn't make SQL Server 2000 a bad database, it just makes you write applications that cater to the strengths of the environment.


    So if we wouldn't be talking about sqlserver, using SPs for certain tasks would be appropriate, or how should I interpret your statement?

    l.
  • (cs) in reply to lofwyr

    lofwyr:
    jsmith:

    That's a false dichotomy.  There are other options than Direct table access or SPs.  A well defined data access layer protects the data as well as SPs.


    I just keep thinking: SPs are one of the builtin methods to aid the developer in certain task such as providing data integrity. So are constraints. Where would you keep those? Also in the data access layer? Or are you splitting that concept here already?

    l.

    Normal constraints go in the database.  No need to reinvent the wheel, my problem is with some features of SPs, not putting things in the database.  However, some complex constraints can't be implemented in the traditional way.  For example if a value in table x has to be between values in table y.  Then I'd put that rule in my data access layer.  Other people here would put that rule in their SPs.  I think it would be easier to code and maintain in C# than T-SQL.

    BTW, the WebPublishing Wizard is a built-in way to create web page based on data in your database.  That doesn't mean you should use it.  "It's there" is hardly a good argument to use something.

  • (cs) in reply to jsmith
    jsmith:

    Normal constraints go in the database.  No need to reinvent the wheel, my problem is with some features of SPs, not putting things in the database.

    I think that's were the argument starts: why reinvent the wheel, when it comes to SPs?

    jsmith:

    However, some complex constraints can't be implemented in the traditional way.  For example if a value in table x has to be between values in table y.  Then I'd put that rule in my data access layer.  Other people here would put that rule in their SPs.  I think it would be easier to code and maintain in C# than T-SQL.

    I'd say that depends on the knowledge of the developer - and the requirements. But again, T-SQL is quite a weak excuse for a SP/trigger language, doesn't come even close to something like PL/SQL.

    jsmith:

    BTW, the WebPublishing Wizard is a built-in way to create web page based on data in your database.  That doesn't mean you should use it.  "It's there" is hardly a good argument to use something.



    But it's also no argument for not using it either and reinventing the wheel instead.

    l.
  • (cs) in reply to jsmith
    jsmith:
    Anonymous:
    Anonymous:
    Alex Papadimoulis:

    Though they take a bit more time to develop upfront, using database stored procedures are definitely the way to go for most information systems. They provide a looser coupling to the "data layer" by limiting the points of entry and offer stronger cohesion by breaking operations into reusable functionality.



    I have to ask; why do people think stored procedures are the way to go? I know most people on this site are .net oriented, and I know MS for the longest time told people to use them (they have since gotten away from that, but hey, it is 2006), but what architectural reason could one use to justify this travesty?

    Don't get me wrong, SPs have their place, but to use them for all your dataaccess and business logic is just plain nuts. If you are using VS.NET 2k5, look at DataSets or nHibernate. If you are using Java, take a gander at Hibernate (or EJB 3....though I still am leary of EJB) and, of course, there is Active Record for Ruby.

    BOSH!!  100% disagree.  (Direct table access + programmers)  = Disaster.


    That's a false dichotomy.  There are other options than Direct table access or SPs.  A well defined data access layer protects the data as well as SPs.

    Actually, the main case for SPs is the data abstraction layer. Consider that application developers deal mostly in flattened data structures while business databases have hundreds of tables requiring joins of possibly dozens. The data complexity is best managed by the DBMS - that's what it was made for. A data access layer in the middle tier is fine once the data is abstracted out of the database, but if you try to assemble the data in the middle tier you'll get killed by performance and/or impedance mismatches. And no, Hibernate does not help much - it starts to collapse at about the same time as an untrained programmer (in database design), plus it leaves you with extra XML files to manage.

    There's also the practical issue of where you find people to do the work, e.g., not many people like to work with both aggregate data and single instantiations. Like it or not, database developers are rare and should have their skills maximized. Making them stuff databases into objects is not good for either, or the furniture. Needless to say, making application developers do database programming is even riskier.

    If there is one rule I've learned the hard way, it is:

    No SQL outside the database!

     

     

  • (cs) in reply to jsmith
    jsmith:
    RyuO:

    Anonymous:
    Alex Papadimoulis:
    Though they take a bit more time to develop upfront, using database stored procedures are definitely the way to go for most information systems. They provide a looser coupling to the "data layer" by limiting the points of entry and offer stronger cohesion by breaking operations into reusable functionality.


    Considerably more time upfront and considerably more time in maintenance.  Every time a programmer has to tackle software using stored procedures, calamity ensues.  And for what benefit?  I have yet to see the case where SPs increase reliability or reduce bugs.  Maybe improve performance.  Unfortunately, developer time is a lot more expensive than the value of the time saved.

    Violating patterns by spreading business logic across layers and adding more variables and complexity into the mix.  SPs are not a good idea.

    The database stores data.

    The business logic manipulates it.

    The view displays it.

    Sticking to these simple principles is a Good Idea.  For example, consider the plight of your typically hapless developer.  There is no reason that he or she should have to also be an expert in whatever database you're using, let alone anything.  Period.  That means using services that do the mapping between business objects and persistence.  That work is best handled by someone who is a database expert.

    I can come up with more reasons but there are plenty of materals on the subject which support this.  To get you started, Google on "Gang of Four", "Spring", "Hibernate", "Pragmatic Programmer", and so on.  Enjoy.

    Other people responded to this parochialism better than I probably will, but I feel compelled to make a few points:

    1. At some point in their careers, usually past the 10 year mark, programmers who survive graduate to enterprise-class applications, which generally involve large volumes of data. By "large volumes of data" I don't just mean large by the standards of UIs, but millions and billions of rows. You can't handle those outside the DBMS in the amount of time you will typically be given, and even if you had the time, where money is concerned nobody is going to care if your solution is in a patterns book or not. Whatever your religious views, you are going to end up using stored procedures. You'll end up becoming more tolerant, and you might even realize that there are other, equally valid, paradigms than OO.

    2. If you want to be taken seriously, learn the difference between "database" and "DBMS".

    3. The incompatibility of Java culture and DBMSes is mostly myth, IMHO caused by fear of the unknown and really misguided tools like Hibernate. Here is an example of a Java tool that bridges the gap: http://sourceforge.net/projects/amber-db.

     


    This is what really gets me..... why do you think that if the data access layer is outside the database that all the logic executes outside the database.  What's wrong with this:

    <FONT face="Courier New">SqlCommand cmd = new SqlCommand("UPDATE Products SET Price = Price * 1.1", cn);
    cmd.ExecuteNonQuery();</FONT>

    You don't have to be an idiot and write:

    <FONT face="Courier New">SqlCommand cmd = new SqlCommand("SELECT ProductID,  FROM Products", cn);
    DataReader dr = cmd.ExecuteReader();

    while(dr.Read())
    {
      SqlCommand cmdInner = new SqlCommand
                    ("UPDATE Products SET Price = " + 
                     (dr("Price") * 1.1).toString() + 
                     " WHERE ProductID = " +
                     dr("ProductID").toString(), cn);
      cmdInner.ExecuteNonQuery();
    }</FONT>

    Whatever you put in the SqlCommand (top example of course) will execute as efficiently as a stored procedure.

    I see a lot of people decrying the stored procedure as unnecessary for whatever reason but I still like the added security of them.  If you allow random (code generated) SQL statements to be executed then you are opening your database up to have people execute that code. 

    I thought that was the ONLY compelling reason to use Stored Procs.

    -Dory

  • Matt (unregistered)

    Not so sure I agree that "you should put that in a stored procedure" is always or even often good advice.  If you're talking in-house IT, maybe.  But for online or shrinkwrap products, there are better ways to abstract your data layer, that don't tie you to a single DBMS.

  • (cs) in reply to jsmith

    jsmith:
    lofwyr:
    jsmith:

    Please tell me how to do this in SQL 2000 or earlier.  They added CLR support to 2005 which finally solves the problem, but that was 15 years after people started recommending SPs to do everything.  What did they do for the previous 14 years?


    Using a DBMS that was worth it's name?

    jsmith:

    What about those who don't have 2005 yet?  If only you knew the MS world well enough to know that Microsoft SQL Server 2000 doesn't have a cryptography library.


    No, thank you. I had my share with Sybase ASE and T-SQL (which I consider an abomination). There are, however systems, where it does make sense to do (almost) everything in SPs (or Packages). It's really not my fault if customers/developers chose something different.

    l.

    Oracle is a database company.  Naturally, their middle tier technology (Java and PL/SQL) runs in the database.  Microsoft is an application company, their middle tier technology (COM/.Net) used to run outside the database.  Now .Net runs in the database just like Oracle.  It doesn't make SQL Server 2000 a bad database, it just makes you write applications that cater to the strengths of the environment.

    Ooh, an Oracle reference! I'm not sure what we're arguing about here, but Oracle's middle tier technology is a separate product (line) similar to Weblogic. It happens to store metadata and state information in its own Oracle database, but as a developer you don't see that.

    Using an Oracle database as a middle tier would be a serious mistake. It's the sort of thing we used to do before app servers existed, but nowadays we use real app servers. Think of Oracle as a virtual machine implementing a server; you wouldn't complain about a Unix server running C, or a JVM running Java, so what's wrong with Oracle having a native language?

  • (cs) in reply to lofwyr
    lofwyr:
    jsmith:

    Oracle is a database company.  Naturally, their middle tier technology (Java and PL/SQL) runs in the database.  Microsoft is an application company, their middle tier technology (COM/.Net) used to run outside the database.  Now .Net runs in the database just like Oracle.  It doesn't make SQL Server 2000 a bad database, it just makes you write applications that cater to the strengths of the environment.


    So if we wouldn't be talking about sqlserver, using SPs for certain tasks would be appropriate, or how should I interpret your statement?

    l.

    That is correct.  My problem with SQL SPs is that the language sucks.  Therefore, I prefer to not to do anything even moderately procedurally complex in T-SQL.  PL/SQL isn't nearly as bad and does have a reasonable feature set.
  • (cs) in reply to jsmith

    No you can have as many physical tiers as you want.  But in the ad-hoc reporting scenario you mention, if the logic isn't executed in the database layer, then the data must be pulled to at least the mid-tier to be manipulated - all of the data, however much that is.  Is that really the best place to handle data? 

    You also mention that you can keep the mid tier.  Really.  Did you work in an environment with a COM based mid-tier at some point and then have to switch it all to .Net?  Because that doesn't sound like keeping the mid-tier to me.  That sounds like throwing it all away.  How long have you had your existing mid-tier that handles data access?  My current company has had its stored procedure based data access layer for seven years.  It could have been much longer, but that's as old as the company is.  The last time I was looking for a job I interviewed with no less than 4 companies that had a COM based mid-tier they were planning on re-doing in .Net.  I dunno, keeping all the business logic out of the database sounds good on paper, but the companies I've seen that do that end up redoing their mid-tier in a new technology if they use MS for the mid-tier.  Or just leaving it as is.  I've seen putting all the logic in the mid-tier work once, fail once, and have the result I just mentioned (stuck in a dead technology) 4 times.  I've never seen a problem with maintaining it in stored procedures.

    I never said update/insert statements wouldn't execute in the database.  Sure they do.  I just think it's pointless to push more data out of the database to your app than is necessary when selecting.  Maybe I'm reading you wrong and you're putting logic in the sql, but I thought you were saying you did basic reads and all formatting etc in a programming language.  And I'm uncomfortable pushing table names from server to server.  If your setup is at a good secure hosting facility that's not a big concern, but if it's in-house it is a concern (i.e. your network admin with a traffic sniffer shouldn't be able to find out table names).  You also then have all the table names scattered throughout your code for anyone who has access to your dev environment. 

  • (cs) in reply to jsmith
    jsmith:

    I said you didn't know language..... I meant English.  This is the second time you misread my post.

    The first was when you took my example and inferred that I liked cursors (I think cursors are evil and I carry evidence of that fact with me to show to anyone that I see writing a cursor)

    The second is when you took my statement "you have a *lot* to learn about language" as "you don't know SQL".  I never said that.  BTW, I still see personal attacks and now dodging the question ("you also know that you almost never need loops").  But still, SQL is a horrible procedural language.  Having a standing practice that data access code goes in SPs WILL eventually lead to some procedural logic in T-SQL.  My standing practices *never* forsce you to do that.  Nor do they prevent you from using SQL (of the embedded variety) whenever appropriate.  I think I have the best of both worlds.



    Sorry, when I read language, I thought it was referring to the language we were discussing, SQL.  I didn't know that we were discussing English.  My bad, I suppose, though I will suggest in the future to be a little more clear.

    As for my "personal attacks" against you, I guess I apologize.  I don't know where I did it, but if you were offended somehow, then I am sorry.

    As for me dodging the question ... what was the question that I dodged?  I fully accept that I probably ignored or missed it, so if it was important for the debate that I give an honest answer to it, could you kindly re-ask it so that I may properly respond? 

    I stand by what I said earlier about your reasoning for avoiding writing code in database in SQL but instead choosing to do so in other languages (i.e., C# or whatnot):  if you really knew SQL well, and really understood server-side set based processing, you would never even consider such as practice.  If you consider that statement a personal attack, then agian I apologize that you are offended (and I am definitely impressed by your certification!), but unfortunately it happens to be true.

    Again, I will suggest that perhaps you should clearly state a position since it appears that it needs clarfying.  I am really not sure now, what you are saying.  Stored procs are evil?  They are good sometimes?  It depends who writes them? It depends if the DBA has bad breath or not?  They are OK to use and not OK to use based on ... ? 
    Instead of just arguing for the sake of arguing, state a clear case and let us know your position.  Why not give us a quick recap/summary? And, again, don't defend the practice of not using them as being "just as good as using them" as long as you have all these extra tools and whatnot, explain to us the benefits of *not* using stored procedures.  I am willing to learn new ideas and new concepts, if you could explain to me the benefits and they made sense, I'd be a fool not to learn from this and listen to your ideas.  Unfortunately, keep in mind that the same thing applies to you, in reverse.
  • (cs) in reply to RyuO
    RyuO:
    jsmith:
    Anonymous:
    Anonymous:
    Alex Papadimoulis:

    Though they take a bit more time to develop upfront, using database stored procedures are definitely the way to go for most information systems. They provide a looser coupling to the "data layer" by limiting the points of entry and offer stronger cohesion by breaking operations into reusable functionality.



    I have to ask; why do people think stored procedures are the way to go? I know most people on this site are .net oriented, and I know MS for the longest time told people to use them (they have since gotten away from that, but hey, it is 2006), but what architectural reason could one use to justify this travesty?

    Don't get me wrong, SPs have their place, but to use them for all your dataaccess and business logic is just plain nuts. If you are using VS.NET 2k5, look at DataSets or nHibernate. If you are using Java, take a gander at Hibernate (or EJB 3....though I still am leary of EJB) and, of course, there is Active Record for Ruby.

    BOSH!!  100% disagree.  (Direct table access + programmers)  = Disaster.


    That's a false dichotomy.  There are other options than Direct table access or SPs.  A well defined data access layer protects the data as well as SPs.

    Actually, the main case for SPs is the data abstraction layer. Consider that application developers deal mostly in flattened data structures while business databases have hundreds of tables requiring joins of possibly dozens. The data complexity is best managed by the DBMS - that's what it was made for. A data access layer in the middle tier is fine once the data is abstracted out of the database, but if you try to assemble the data in the middle tier you'll get killed by performance and/or impedance mismatches. And no, Hibernate does not help much - it starts to collapse at about the same time as an untrained programmer (in database design), plus it leaves you with extra XML files to manage.

    There's also the practical issue of where you find people to do the work, e.g., not many people like to work with both aggregate data and single instantiations. Like it or not, database developers are rare and should have their skills maximized. Making them stuff databases into objects is not good for either, or the furniture. Needless to say, making application developers do database programming is even riskier.

    If there is one rule I've learned the hard way, it is:

    No SQL outside the database!

     

     


    Are you suggesting that there is no other possible way to build an effective data abstraction layer other than SPs?  You do realize that even though MySQL isn't exactly the most robust database on the planet, and doesn't even have stored procedures, nobody has started on fire by using it.  There are a few well implemented solutions on MySQL that have a well implemented data abstraction layer.  Apparently it is possible without SPs.

    As for "No SQL outside the database!", isn't "exec" an SQL statement?  How do you run SPs?  My rule is "No SQL outside the data access layer".  Sounds the same as yours, but it doesn't force any specific technology on the problem.

     

  • (cs) in reply to Someone Who Still codes VB

    Anonymous:

    Except it is good form to say "Next i" which makes loop boundaries in nested loops a little clearer, although that doesn't work in ASP...:(

    For i=0 to 5

       'do something

    Next 'i

  • (cs) in reply to Jeff S

    Whenever I hear someone say they can implement a perfectly fine data layer in code I'm reminded of all the MySql developers who said they didn't need transactions because they could just write all of that in their code too.  Sure you could.....  but why?

    I'll second jeff again - what is the benefit to having your sql code outside of the database?  What exactly is your data access layer?  How long did you spend developing it?  How do you enforce your complex value constraints - and how is it any better than an insert/update trigger that does the same thing?  The example you gave of any value inserted into table x should be between the min and max values in table y is a trivial trigger to write.  I'd also like a example of when you're forced to use procedural structures in sp's.  In-memory temp tables aren't great, but eliminate many cases where a cursor might seem needed.

  • (cs) in reply to jsmith
    jsmith:
    RyuO:
    jsmith:
    Anonymous:
    Anonymous:
    Alex Papadimoulis:

    Though they take a bit more time to develop upfront, using database stored procedures are definitely the way to go for most information systems. They provide a looser coupling to the "data layer" by limiting the points of entry and offer stronger cohesion by breaking operations into reusable functionality.



    I have to ask; why do people think stored procedures are the way to go? I know most people on this site are .net oriented, and I know MS for the longest time told people to use them (they have since gotten away from that, but hey, it is 2006), but what architectural reason could one use to justify this travesty?

    Don't get me wrong, SPs have their place, but to use them for all your dataaccess and business logic is just plain nuts. If you are using VS.NET 2k5, look at DataSets or nHibernate. If you are using Java, take a gander at Hibernate (or EJB 3....though I still am leary of EJB) and, of course, there is Active Record for Ruby.

    BOSH!!  100% disagree.  (Direct table access + programmers)  = Disaster.


    That's a false dichotomy.  There are other options than Direct table access or SPs.  A well defined data access layer protects the data as well as SPs.

    Actually, the main case for SPs is the data abstraction layer. Consider that application developers deal mostly in flattened data structures while business databases have hundreds of tables requiring joins of possibly dozens. The data complexity is best managed by the DBMS - that's what it was made for. A data access layer in the middle tier is fine once the data is abstracted out of the database, but if you try to assemble the data in the middle tier you'll get killed by performance and/or impedance mismatches. And no, Hibernate does not help much - it starts to collapse at about the same time as an untrained programmer (in database design), plus it leaves you with extra XML files to manage.

    There's also the practical issue of where you find people to do the work, e.g., not many people like to work with both aggregate data and single instantiations. Like it or not, database developers are rare and should have their skills maximized. Making them stuff databases into objects is not good for either, or the furniture. Needless to say, making application developers do database programming is even riskier.

    If there is one rule I've learned the hard way, it is:

    No SQL outside the database!

     

     


    Are you suggesting that there is no other possible way to build an effective data abstraction layer other than SPs?  You do realize that even though MySQL isn't exactly the most robust database on the planet, and doesn't even have stored procedures, nobody has started on fire by using it.  There are a few well implemented solutions on MySQL that have a well implemented data abstraction layer.  Apparently it is possible without SPs.

    As for "No SQL outside the database!", isn't "exec" an SQL statement?  How do you run SPs?  My rule is "No SQL outside the data access layer".  Sounds the same as yours, but it doesn't force any specific technology on the problem.

    Taking the points in order:

    You can build a data abstraction layer anywhere, theoretically - that's what we did before stored procedures, using C or Ada on the database server. In practice, nowadays, it is tough - the modern OO languages are not built for large volumes of data, and performance and security concerns would lead you to put it on a separate physical tier from the data access layer and "business logic" layer. Or, you could put the data abstraction layer on the database server, which would make the money people wonder why they are paying for DBMS features you're not using.

    Forgot that many people think MySQL is a DBMS. It is not - it's a file manager with a SQL-like query language. The "no SQL outside the database" rule does not apply here, because MySQL does not support databases. To make it one you'd have to code basic DBMS functionality like read consistency from scratch, so you might as well write custom data extraction as well.

    SPs and exec statements are not SQL, they are from the procedural world. They are much more robust and much less dynamic than SQL, so there are few dangers. There's nothing odd about SP calls I can think of, either - are they any different than other forms of RPCs?

    Maybe another practical advantage will help: if all the SQL is in the database, DBAs can find it. Performance of queries is highly variable, especially over time. People who know how to tune queries are generally not the same people who know all the languages and IDEs that middle tier developers use, so the turnaround time for performance tuning is a lot less for SPs.

  • (cs) in reply to wbrianwhite

    Often business logic can be executed either in a middle tier business object, or in an SP. 

    My "rule of thumb" is that any business logic that requires data from a database be processed in order to decide what, when, or where to modify other data in the same database, or to decide what data should be returned to the front end, should be encapsulated in  (and therefore belongs in) an SP.  It makes no sense to me to pull a chunk of data down to a middle tier object so I can process it, just to construct another query, or to decide what to update, delete or insert into the same or a different table in the same database. 

    If the data is necessary for some other business process, or the p[rocessing requires data from multiple data sources,  or if a different database needs to be modified, then this process is a candidate for the middle tier.  Otherwise, it belongs in the tier closest to the source, the database tier, and that means Stored procs...

     

  • it has to be said (unregistered) in reply to RyuO

    What is this irrational hatred some people have of stored procedures and well structured architecture? C'mon guys, using stored procedures is an absolute no-brainer.

    I shudder to think of the WTF code the anti-sp crowd is foisting upon the world. The same code I'm going to have to clean up after the WTF generator moves on to his fortune 500 enteprise architect role.

    I really hope you guys get a clue somewhere along the way.

  • no (unregistered) in reply to RyuO
    RyuO:

    Maybe another practical advantage will help: if all the SQL is in the database, DBAs can find it. Performance of queries is highly variable, especially over time. People who know how to tune queries are generally not the same people who know all the languages and IDEs that middle tier developers use, so the turnaround time for performance tuning is a lot less for SPs.



    I am going to say this one more time: who is talking about SQL outside the DB? Look at a freakin' ORM.

    I don't write SPs...yet, I dont' write any SQL either. How? OR freakin' Ms. Hibernate, TopLink, nHibernate, Active Record. Even MS has this in Sql Server 2k5 called DataSets. PS. They advocate this approach over SPs as well.

    Anyway...read a little more, get more experience, then come back and we'll talk.
  • (cs) in reply to jsmith

    jsmith:
    lofwyr:
    jsmith:

    Oracle is a database company.  Naturally, their middle tier technology (Java and PL/SQL) runs in the database.  Microsoft is an application company, their middle tier technology (COM/.Net) used to run outside the database.  Now .Net runs in the database just like Oracle.  It doesn't make SQL Server 2000 a bad database, it just makes you write applications that cater to the strengths of the environment.


    So if we wouldn't be talking about sqlserver, using SPs for certain tasks would be appropriate, or how should I interpret your statement?

    l.

    That is correct.  My problem with SQL SPs is that the language sucks.  Therefore, I prefer to not to do anything even moderately procedurally complex in T-SQL.  PL/SQL isn't nearly as bad and does have a reasonable feature set.

    I quite agree - one of the things that hurt Sybase/SQL Server in the competition with Oracle was its stored procs. PL/SQL, being a derivative of Ada, is far better suited to large scale apps. I personally won't touch Sybase or SQL Server because of my distaste for the procedural language options. Well, that and the dirty reads.

  • (cs) in reply to no
    Anonymous:
    RyuO:

    Maybe another practical advantage will help: if all the SQL is in the database, DBAs can find it. Performance of queries is highly variable, especially over time. People who know how to tune queries are generally not the same people who know all the languages and IDEs that middle tier developers use, so the turnaround time for performance tuning is a lot less for SPs.



    I am going to say this one more time: who is talking about SQL outside the DB? Look at a freakin' ORM.

    I don't write SPs...yet, I dont' write any SQL either. How? OR freakin' Ms. Hibernate, TopLink, nHibernate, Active Record. Even MS has this in Sql Server 2k5 called DataSets. PS. They advocate this approach over SPs as well.

    Anyway...read a little more, get more experience, then come back and we'll talk.

    You happened to pick the wrong person for an ad hominem attack. Before stored procs were invented I put a lot of effort into generating Actor and Eiffel code from database metadata, essentially what ORM tools do today. I got farther than Hibernate did - unlike those folks I knew that classes map to relational domains, not tables. What stopped me was not the impedance mismatch, it was performance. The custom relational domains slowed SQL down, and the object overhead precluded using ORM for the most important database operations, e.g., million row transactions. 

    And yes, I have used Hibernate; in fact, I have seen it fail on 2 medium-sized projects, so I know it is not good for much more than UIs. Amber looks a lot better, because it works with the database instead of against it. Would I use Amber by choice, instead of as a defence against OO bigots? Not sure yet, still playing with it.

     

  • (cs) in reply to jsmith
    jsmith:
    Yup.

    Then add it.

    I most cases, the C# data access layer will be implementable as a seperate tier.  Just call them from the mainframe app via Web Services.  If the language is too old to support web services, then SPs might actually be a good solution.

    It's one thing to say that SPs introduce no performance advantages over dynamic SQL and argue for SQL-in-C# (etc.) vs SPs.  It's quite another to suggest web services.  Web services are a massively over-the-top solution to this kind of problem; they're expensive (in performance terms), they're complicated, the APIs vary wildly from language to language, they're new (no legacy programs can use them with any ease, unlike with SPs, where if you can talk to the database you can talk to the SP).  They offer no advantages over SPs, and considerable costs.

    I would never suggest replacing the set operations with procedural operations.  However, the logic of which operations to run, in which order, adding activity log records, etc (all the statement level logic) is far easier in C#.

    But that has nothing to do with the topic of using SPs for data insertion and extraction.

    Not in SQL 2000 or earlier.

    Even in SQL Server 2000, XPs are not complicated.

    No, you add two lines of code to each of two existing methods.

    Er, no, you don't.  You've got to add it to lots of methods, because you have lots of code hitting the database.

    Done.  The database can already persist a password, it won't care that it isn't clear text.

    The database is far and away the easiest place to say update users set password = hash(password);

    Not all.  SQL 2000 has no access to a high level language.

    Yes, it does.  Not as nice as in 2005, I'll grant you.  But it's three, through XPs.

    As for distribution, you made the assumption that the application only has two physical tiers.

    On the contrary.  I made the assumption that it has multiple "application" layers and relatively few "data" layers.

    In my case, I'd just have to replace the middle tier code, running on a server or server farm.

    Which does nothing to help all your other applications.

     

  • (cs) in reply to no
    Anonymous:
    RyuO:

    Maybe another practical advantage will help: if all the SQL is in the database, DBAs can find it. Performance of queries is highly variable, especially over time. People who know how to tune queries are generally not the same people who know all the languages and IDEs that middle tier developers use, so the turnaround time for performance tuning is a lot less for SPs.



    I am going to say this one more time: who is talking about SQL outside the DB? Look at a freakin' ORM.

    I don't write SPs...yet, I dont' write any SQL either. How? OR freakin' Ms. Hibernate, TopLink, nHibernate, Active Record. Even MS has this in Sql Server 2k5 called DataSets. PS. They advocate this approach over SPs as well.

    Anyway...read a little more, get more experience, then come back and we'll talk.

    Datasets in SQL2k5 do NOT replace SPs, you still need to specify what should be used to populate the Dataset, (a SQL Statement or an SP), and what should be used to modify database tables for changes in datasets (inserts updates and deletes) that are passsed back to the server... Again, the ADO.Net provider can ijssue SQL, or call an SP. 

    The use of Datasets is a completely independant issue from the issue of whether to use SPs or SQL.

  • no (unregistered) in reply to RyuO
    RyuO:

    You happened to pick the wrong person for an ad hominem attack. Before stored procs were invented I put a lot of effort into generating Actor and Eiffel code from database metadata, essentially what ORM tools do today. I got farther than Hibernate did - unlike those folks I knew that classes map to relational domains, not tables. What stopped me was not the impedance mismatch, it was performance. The custom relational domains slowed SQL down, and the object overhead precluded using ORM for the most important database operations, e.g., million row transactions.



    It sounds like the lazyloading factor wasn't working properly. All moderm ORMs have this facility and are able to load data when needed, not before. There is a perfomance hit (however slight), but nothing that I am going to get all up in arms about.


    And yes, I have used Hibernate; in fact, I have seen it fail on 2 medium-sized projects, so I know it is not good for much more than UIs. Amber looks a lot better, because it works with the database instead of against it. Would I use Amber by choice, instead of as a defence against OO bigots? Not sure yet, still playing with it.



    I have seen SPs fail on several small projects, several large projects and a whole bunch of medium projects. Though, I have to admit, that was because of the people who were programming the systems. Same could probably be said for hibernate. Now, notice how I never said you can't use SPs to get the job done. As a matter of fact, I think you can. However, I generally think they are a bad idea for a slew of architectural reasons along with team/developer reasons. I do believe a good developer can make proper use of SPs, btu I don't think they are the right solution for 99.9% of their uses...especially when we do have more robust ORMs today.


  • Benjamin Smith (unregistered) in reply to John Smallberries
    John Smallberries:

    As a corollary, I've also found that the bigger the company, the poorer the software quality produced.


    So, the 16 year old kid banging out some sql-injection error laden, semi-secure pile of crap in PHP or ASP is producing the finest quality software in the world?

    I'd suggest that software development, like most things,  is a balance. Go too small, you get crap. Go too big, you get crap. Somewhere in the middle (probably somewhere around 2-20 closely-associated developers) you get the optimum bang for the development buck.

    Ironically, you get more profit for the development cost (even as the quality drops significantly) as the company gets bigger, not because the software is better for the amount of money spent on it, but because the bigger company can leverage the same software into more markets and has more marketing clout....

    Go figure. Freemarket purists have their heads up their arses.
  • (cs) in reply to Benjamin Smith

    Just want to thank you all for a most amusing discussion :) No, I won't tell you my opinion on the matter, better keep out of the line of fire...

  • wolf550e (unregistered) in reply to codeman

    loneprogrammer wrote:

    BTW: if you used ALL of the parameters (nothing was null), the (Oracle) server on what was then, a fairly high-powered unix box) returned rows at the mind-numbing speed of approximately 3 rows per SECOND

    Dude, why were you doing OPTIMIZER=FIRST_ROWS on such a heavy query? ALL_ROWS would've probably been able to do faster joins (merge, hash, whatever) and finish much quicker. Did your users really had to see the first row of results immediatly? Nested Loops is often a Bad Thing.

  • (cs) in reply to RyuO

    Ssomething you can do in Oracle with stored functions that could be hard (expensive) to move to the middle tier:

    select min(myStoredFunction1(col1, col2, col3))
      from myTable
      where myStoredFunction2(col1)=myStoredFunction3(col2, col3)
      group by myStoredFunction4(col4, col5)
      order by myStoredFunction4(col4, col5);

    (Using the same expression for both "group by" and "order by" is pretty useless, it's just here to show that stored functions can be used in both places)


    To show that in a less abstract example, let's imagine a dating service that tries to find the best matching couples using a secret formula contained in a stored function called "partner_match":

    select male.id, female.id
      from persons male, persones female
      where male.gender='M'
        and female.gender='F'
        and partner_match(male.age, male.income, male.preferences, female.age, female.desires, female.preferences)>0.8
        order by 
    partner_match(male.age, male.income, male.preferences, female.age, female.desires, female.preferences)
desc;

  • (cs) in reply to ammoQ
    ammoQ:
    Ssomething you can do in Oracle with stored functions that could be hard (expensive) to move to the middle tier:

    select min(myStoredFunction1(col1, col2, col3))
      from myTable
      where myStoredFunction2(col1)=myStoredFunction3(col2, col3)
      group by myStoredFunction4(col4, col5)
      order by myStoredFunction4(col4, col5);

    (Using the same expression for both "group by" and "order by" is pretty useless, it's just here to show that stored functions can be used in both places)


    To show that in a less abstract example, let's imagine a dating service that tries to find the best matching couples using a secret formula contained in a stored function called "partner_match":

    select male.id, female.id
      from persons male, persones female
      where male.gender='M'
        and female.gender='F'
        and partner_match(male.age, male.income, male.preferences, female.age, female.desires, female.preferences)>0.8
        order by 
    partner_match(male.age, male.income, male.preferences, female.age, female.desires, female.preferences)
 desc;


    You know, that's a great example.  Let's say that the function requires procedural processing due to the algorithm; let's assume we cannot process it in a set-based manner using nice SQL statements.  We have established that a "real" language like C# is much, much better than T-SQL or P-SQL or any SQL when it comes to writing code in this manner -- as jsmith tells us, in C# he can use switch constructs, better looping, external DLL's, arrays, and so on.  Cursors are still almost never required in SQL, but sometimes iterative processing is, so lets assume this algorithm needs it.

    (The big difference, of course, is that while a loop might still be needed in SQL, each *pass* through the loop still has the ability to process thousands of rows at once, or to relate different tables through optimized joins, or to use temp tables efficiently as mentioned earlier, and so on -- unlike with pure client side processing in where the algorithm is written in C# and stored procs are not used.)

    Well, if this code was written in C#, it would need to bring back *every* row in necessary to calculate the algorithm from the database to process things. And, potentially, make hundreds of calls over and over to the database to bring back other related rows for processing. Then it would need to cache all of its results during calculation, and then sort those results, all at the client -- completely "reinventing the wheel" by ignoring those features which a databse provides.  While maybe writing that function was *easier* to do in C#, it results in an application that is much, much less efficient -- sometimes by factors of 1000s (if you've done much cursor-busting in the past, you know this is definitely true).  And, if you want to write several reports using that function, you need to be able to call this C# code, which many reporting tools do not allow.

    Writing the logic at the database layer, since it is data-related calculation, results in the function being stored in 1 place, no client side cursor is needed, and processing is done on the server.  And any client that implements ADO or OLEDB or ODBC or any other standard method of getting data from the database can benefit from the function.

    Good example, AmmoQ !
  • (cs) in reply to Bullet

    Bullet:
    No matter what kind of tool you use, if you allow the database to accept raw SQL then any developer working on another project that touches that database can write inline sql to modify data withoiut going through yuour tool...  Then you have multiple applications dependant on the data structures, field names, (potentially field order as well) , and relational constraints within the dataabase.  Changing any of these is then extremely likely to break someone's app.   If on the other hand you require all access to go threough well-designed set of SPs, all development must go through them (no logins have select, update or delete permissions on raw tables)  Then, you can change, extend, or modify anything in the database without breaking any applicatiomn or middle  iier code, because you can modify the internals of the Stored proc and keep it's public interface immutable. 

    Yes! Bullet!  This is the essence of the SP!  Well done!  I only wish I could have said it!

    p.s. Bullet, say Hi to my pals at California Lutheran University...Go Kingsmen!

  • no (unregistered) in reply to FriedEggs
    FriedEggs:

    Bullet:
    No matter what kind of tool you use, if you allow the database to accept raw SQL then any developer working on another project that touches that database can write inline sql to modify data withoiut going through yuour tool...  Then you have multiple applications dependant on the data structures, field names, (potentially field order as well) , and relational constraints within the dataabase.  Changing any of these is then extremely likely to break someone's app.   If on the other hand you require all access to go threough well-designed set of SPs, all development must go through them (no logins have select, update or delete permissions on raw tables)  Then, you can change, extend, or modify anything in the database without breaking any applicatiomn or middle  iier code, because you can modify the internals of the Stored proc and keep it's public interface immutable. 

    Yes! Bullet!  This is the essence of the SP!  Well done!  I only wish I could have said it!

    p.s. Bullet, say Hi to my pals at California Lutheran University...Go Kingsmen!



    yeah...check out webservices....yet another (and better) way of doing the above and you don't need SPs.
  • (cs) in reply to no
    Anonymous:
    FriedEggs:

    Bullet:
    No matter what kind of tool you use, if you allow the database to accept raw SQL then any developer working on another project that touches that database can write inline sql to modify data withoiut going through yuour tool...  Then you have multiple applications dependant on the data structures, field names, (potentially field order as well) , and relational constraints within the dataabase.  Changing any of these is then extremely likely to break someone's app.   If on the other hand you require all access to go threough well-designed set of SPs, all development must go through them (no logins have select, update or delete permissions on raw tables)  Then, you can change, extend, or modify anything in the database without breaking any applicatiomn or middle  iier code, because you can modify the internals of the Stored proc and keep it's public interface immutable. 

    Yes! Bullet!  This is the essence of the SP!  Well done!  I only wish I could have said it!

    p.s. Bullet, say Hi to my pals at California Lutheran University...Go Kingsmen!



    yeah...check out webservices....yet another (and better) way of doing the above and you don't need SPs.

    I know all about web services.  but I fail to see how a web service prevents another developer (who may nothing about your web service), from calling the database directly and passing constructed SQL to the query processor that is dependant on table names/structure, field names/layout, or relational constraints.   Unless you either didn't understand, or didn't read, my post, nothing about a web service is either equivilent to, (or better) than using SPs to address the issues described in my post.

  • no (unregistered) in reply to Bullet
    Bullet:
    Anonymous:
    FriedEggs:

    Bullet:
    No matter what kind of tool you use, if you allow the database to accept raw SQL then any developer working on another project that touches that database can write inline sql to modify data withoiut going through yuour tool...  Then you have multiple applications dependant on the data structures, field names, (potentially field order as well) , and relational constraints within the dataabase.  Changing any of these is then extremely likely to break someone's app.   If on the other hand you require all access to go threough well-designed set of SPs, all development must go through them (no logins have select, update or delete permissions on raw tables)  Then, you can change, extend, or modify anything in the database without breaking any applicatiomn or middle  iier code, because you can modify the internals of the Stored proc and keep it's public interface immutable. 

    Yes! Bullet!  This is the essence of the SP!  Well done!  I only wish I could have said it!

    p.s. Bullet, say Hi to my pals at California Lutheran University...Go Kingsmen!



    yeah...check out webservices....yet another (and better) way of doing the above and you don't need SPs.

    I know all about web services.  but I fail to see how a web service prevents another developer (who may nothing about your web service), from calling the database directly and passing constructed SQL to the query processor that is dependant on table names/structure, field names/layout, or relational constraints.   Unless you either didn't understand, or didn't read, my post, nothing about a web service is either equivilent to, (or better) than using SPs to address the issues described in my post.



    How does a developer using a webservice send raw SQL? I'm not sure you understand the technology.

    Also, you said this:

    "If on the other hand you require all access to go threough well-designed set of SPs, all development must go through them (no logins have select, update or delete permissions on raw tables)  Then, you can change, extend, or modify anything in the database without breaking any applicatiomn or middle  iier code, because you can modify the internals of the Stored proc and keep it's public interface immutable. "

    That is what Webservices can do when you use properly. As a great side benefit, they also can expose alot of that to the world in an API style that you can't do with SPs (two for the price of one). So, yeah, WS are better for the what you were arguing for.

    BTW. Stop with the raw SQL debate. It is old and tired. No one is advocating that.
  • (cs) in reply to no
    Anonymous:
    Bullet:
    Anonymous:
    FriedEggs:

    Bullet:
    No matter what kind of tool you use, if you allow the database to accept raw SQL then any developer working on another project that touches that database can write inline sql to modify data withoiut going through yuour tool...  Then you have multiple applications dependant on the data structures, field names, (potentially field order as well) , and relational constraints within the dataabase.  Changing any of these is then extremely likely to break someone's app.   If on the other hand you require all access to go threough well-designed set of SPs, all development must go through them (no logins have select, update or delete permissions on raw tables)  Then, you can change, extend, or modify anything in the database without breaking any applicatiomn or middle  iier code, because you can modify the internals of the Stored proc and keep it's public interface immutable. 

    Yes! Bullet!  This is the essence of the SP!  Well done!  I only wish I could have said it!

    p.s. Bullet, say Hi to my pals at California Lutheran University...Go Kingsmen!



    yeah...check out webservices....yet another (and better) way of doing the above and you don't need SPs.

    I know all about web services.  but I fail to see how a web service prevents another developer (who may nothing about your web service), from calling the database directly and passing constructed SQL to the query processor that is dependant on table names/structure, field names/layout, or relational constraints.   Unless you either didn't understand, or didn't read, my post, nothing about a web service is either equivilent to, (or better) than using SPs to address the issues described in my post.



    How does a developer using a webservice send raw SQL? I'm not sure you understand the technology.

    Also, you said this:

    "If on the other hand you require all access to go threough well-designed set of SPs, all development must go through them (no logins have select, update or delete permissions on raw tables)  Then, you can change, extend, or modify anything in the database without breaking any applicatiomn or middle  iier code, because you can modify the internals of the Stored proc and keep it's public interface immutable. "

    That is what Webservices can do when you use properly. As a great side benefit, they also can expose alot of that to the world in an API style that you can't do with SPs (two for the price of one). So, yeah, WS are better for the what you were arguing for.

    BTW. Stop with the raw SQL debate. It is old and tired. No one is advocating that.

     

    You know,  I was right, you're not reading the posts... The developer can send raw SQL because he DOESN'T HAVE TO USE YOUR WEB SERVICE.  He can use ADO.Net, ADO, ODBC, OleDB, etc., etc., 

    And if your web service is sending raw SQL, the database must be configured to accept raw SQL,  and then all those other mechanisms can ALSO send raw SQL.

  • no (unregistered) in reply to Bullet
    Bullet:

    You know,  I was right, you're not reading the posts... The developer can send raw SQL because he DOESN'T HAVE TO USE YOUR WEB SERVICE.  He can use ADO.Net, ADO, ODBC, OleDB, etc., etc., 

    And if your web service is sending raw SQL, the database must be configured to accept raw SQL,  and then all those other mechanisms can ALSO send raw SQL.



    What are you talking about? Just lock out the developers from the db. Duh. I mean, do you think I have access to Google's DB? No...

    So, just allow the WS to have access. This is not rocket science.

    Honestly, I didn't answer that question because I thought even a marginal developer wouldn't suggest such a thing...oh well...
  • (cs) in reply to no
    Anonymous:
    Bullet:

    You know,  I was right, you're not reading the posts... The developer can send raw SQL because he DOESN'T HAVE TO USE YOUR WEB SERVICE.  He can use ADO.Net, ADO, ODBC, OleDB, etc., etc., 

    And if your web service is sending raw SQL, the database must be configured to accept raw SQL,  and then all those other mechanisms can ALSO send raw SQL.



    What are you talking about? Just lock out the developers from the db. Duh. I mean, do you think I have access to Google's DB? No...

    So, just allow the WS to have access. This is not rocket science.

    Honestly, I didn't answer that question because I thought even a marginal developer wouldn't suggest such a thing...oh well...

    In spite of your sarcasm, and implied disresepect, I'll remain professional.  Let me explain more clearly.   Of course you can't access Google's DB.  But Google's developers (at some level of permissions) CAN access the DB.   There is no way to restrict raw SQL access to the DB except to restrict it to everyone.  Some internal development Manager/administrator can always give HIS developers access...  and then they can send raw SQL to the DB.  Only if ALL raw SQL access is inhibited can this be prevented.   Even then it can be a battle between Database Architectural Designers and developers, (like you), who don't understand, to keep those restrrictions in place.   But it's much, much easier to manage and enforce if the restriction is at the door to the database than if it's placed at the door to each and every application database entry point a web service. 

    On another note,  I respectfully suggest you skip the disrespectful sarcasm, it only detracts from your arguments.

  • no (unregistered) in reply to Bullet
    Bullet:

    In spite of your sarcasm, and implied disresepect, I'll remain professional.  Let me explain more clearly.   Of course you can't access Google's DB.  But Google's developers (at some level of permissions) CAN access the DB.   There is no way to restrict raw SQL access to the DB except to restrict it to everyone.  Some internal development Manager/administrator can always give HIS developers access...  and then they can send raw SQL to the DB.  Only if ALL raw SQL access is inhibited can this be prevented.   Even then it can be a battle between Database Architectural Designers and developers, (like you), who don't understand, to keep those restrrictions in place.   But it's much, much easier to manage and enforce if the restriction is at the door to the database than if it's placed at the door to each and every application database entry point a web service. 

    On another note,  I respectfully suggest you skip the disrespectful sarcasm, it only detracts from your arguments.



    That is just a ridiculous argument to make. At some level, developers actually NEED access to SQL...to trouble shoot, debug, quick report, reverse engineer. That is just common sense.

    Now, again, no raw sql in the middle tier because ORMs and SQL is not dirty, just hire a decent developer and have a good DBA.

  • (cs) in reply to no
    Anonymous:
    Bullet:

    In spite of your sarcasm, and implied disresepect, I'll remain professional.  Let me explain more clearly.   Of course you can't access Google's DB.  But Google's developers (at some level of permissions) CAN access the DB.   There is no way to restrict raw SQL access to the DB except to restrict it to everyone.  Some internal development Manager/administrator can always give HIS developers access...  and then they can send raw SQL to the DB.  Only if ALL raw SQL access is inhibited can this be prevented.   Even then it can be a battle between Database Architectural Designers and developers, (like you), who don't understand, to keep those restrrictions in place.   But it's much, much easier to manage and enforce if the restriction is at the door to the database than if it's placed at the door to each and every application database entry point a web service. 

    On another note,  I respectfully suggest you skip the disrespectful sarcasm, it only detracts from your arguments.



    That is just a ridiculous argument to make. At some level, developers actually NEED access to SQL...to trouble shoot, debug, quick report, reverse engineer. That is just common sense.

    Now, again, no raw sql in the middle tier because ORMs and SQL is not dirty, just hire a decent developer and have a good DBA.

    So on the one hand, you argue that "What are you talking about? Just lock out the developers from the db. Duh ..."

    and on the other hand,
     "At some level, developers actually NEED access to SQL..."    Can you detect an inconsistency there ?

    Seriously, in a development environment I don't care what access level developers have They obviously need a high level of access just to create, and debug the stored procs they must be writing to implement the architectural design I am talking about

    ...  in the production environment, on the other hand, for those purposes you describe, if you absolutely HAVE to allow such access, you can create another login or access account that has read-only priviliges,  If you are talking about letting developers have write access direct to the tables in production, (please say it isn't so) then you are operating in an area I refuse to go...

  • (cs) in reply to no
    Anonymous:
    FriedEggs:

    Bullet:
    No matter what kind of tool you use, if you allow the database to accept raw SQL then any developer working on another project that touches that database can write inline sql to modify data withoiut going through yuour tool...  Then you have multiple applications dependant on the data structures, field names, (potentially field order as well) , and relational constraints within the dataabase.  Changing any of these is then extremely likely to break someone's app.   If on the other hand you require all access to go threough well-designed set of SPs, all development must go through them (no logins have select, update or delete permissions on raw tables)  Then, you can change, extend, or modify anything in the database without breaking any applicatiomn or middle  iier code, because you can modify the internals of the Stored proc and keep it's public interface immutable. 

    Yes! Bullet!  This is the essence of the SP!  Well done!  I only wish I could have said it!

    p.s. Bullet, say Hi to my pals at California Lutheran University...Go Kingsmen!



    yeah...check out webservices....yet another (and better) way of doing the above and you don't need SPs.


    Web services?

    So, instead of exposing a standard set of stored procedures to be efficiently access via ODBC or OLEDB, with data being returned directly from the database, you are suggesting that instead requests for data should be sent to a web server,  which then builds a sql statement (or series of sql statements as necessary), and then connects to the database (via OLEDB or ODBC of course). and then passes that SQL to the database (directly accessing tables/views of course), and then returns the results back as a response from the web server to the client (converted to XML, of course), which the client receives as an XML file that is then converted from XML to a standard row/column data table which the client can then use.

    Makes sense, I suppose. It sounds really cool and complicated, I know it would impress lots of people, and you get to use some very hip buzzwords and all that.  I might suggest throwing AJAX into the mix somewhere as well if possible.


Leave a comment on “The Stored ÜberProcedure”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article