in reply to No stored procedure bashing on my watch!
in thread Recoding a multi-sql-statement storedProc transaction in a script

I know next to nothing about SQL Server

I've never used SQL Server. My comments refer to Oracle.

Oracle lets everyone create and manage his own stored procedures.

Only if you have DBAs who are happy to grant that privilege. None of the ones I've worked with were.

When you back up your database, that backup includes stored procedures. You can update them very easily using the DB SQL client. Of course, you want to put them into source control. You can use the same source control system you use for your Perl code.

No special procedure is required to make perl code live in a typical environment. For stored procs, you have to connect to the database and install the new version. It's not horrible, but it is more work than file-based stuff. And it adds to versioning problems between code and database. A good automated code deploy tool could probably fix this issue, but most places don't have one.

There are also the performance advantages of not having to parse and prepare the SQL every time (that will be done only once, and not just once per connection, which is the best you can do from the outside), of using native database datatypes, and of reduced network traffic.

With Oracle, when you use placeholders correctly, statements are parsed and then kept in the cache. Sending the request again doesn't require it be parsed again. The network stuff is a savings if you need to examine lots of rows but won't actually be needing them in your final results, as I mentioned. Otherwise, it's negligible.

accessibilty of the code from other programming environments

That's the big argument in favor of stored procs, and the only one that I actually buy. I don't think PL/SQL is an adequate language for this kind of development though.

Forgive the rant

Ditto! We disagree about most of this, but some of that is surely due to different database policies at our places of work.

try to keep my programs completely free of any SQL (for the same reasons that I keep them free of HTML)

There are some nice modules for keeping your SQL statements in a separate file. That's a nice way to do it for people like me who want to avoid stored procs.

  • Comment on Re: No stored procedure bashing on my watch!

Replies are listed 'Best First'.
Re^2: No stored procedure bashing on my watch!
by Anonymous Monk on Dec 16, 2004 at 15:46 UTC
    As a former DBA, not wanting to use stored procedures sounds to me as someone coming perlmonks asking how to do "X", without using modules. And the reason "my DBA doesn't give me permission" is similar as "I don't want to use Perl modules because the sysadmin doesn't let me install any".

    I'm a great fan of stored procedures, and IMO, no application should ever touch a table directly. Not even with a select. Stored procedures should act as an extra abstraction level. Besides from the reasons given by other people, it gives database people the opportunity to re-organize the data layout (add/delete columns, split tables) without having to modify an unknown number of programs - all that's needed is to change the relevant stored procedures. Granted, probably less important for a database that's the backend for a short-lived website, but very important for databases that will last for decades, with a myriad of applications running against it. If there's any intention your database is going to last for a while, in an environment that might change don't use any SQL code in your applications (except for calling stored procedures). Ever.

    If your DBA doesn't give you permission, you have a problem. But not a problem different than your sysadmin not giving you permission to write files either. Would you accept the latter, and search for a way to get programs on the system (perhaps by typing them in on the command line each time you want to run them)? Or would you make it so that you get access? Assuming your application is important for someone, go and get the access you need! Talk to your manager.

[reply]
      don't use any SQL code in your applications (except for calling stored procedures). Ever.

      Shameless plug: DBIx::ProcedureCall makes Perl wrappers for stored procedures, eliminating the SQL to call them from your application source code as well. Only works for Oracle at the moment, though (contributions for other DBMS always welcome).

[reply]
      I don't buy it. Change the data structures around in a significant way and hide it all with stored procs? You'd end up with something messy and slow, just like all of those awful "we'll fix it with a view" solutions.

      Many DBAs are reluctant to give out permission for developers to modify stored procs. They see it as a way to make sure that all SQL is filtered through them. The problem is, they tend not to have any time to look at your SQL either, so the vetting process becomes a bottleneck.

      That sort of thing can be fixed, albeit slowly and painfully, but it doesn't change the fact that stored procs normally use crippled languages. I wouldn't want to use them for anything with actual programming logic in it.

[reply]

        Anything you can do from embedded SQL, you can do from a stored procedure. Instead of preparing and executing the SQL, you invoke the SP that does it for and returns the same results.

        No extra programming logic needs to be written in the SP language. It can be, often saving the need to ship large volumes of data between the DBM and the application program, and then back again, but it doesn't need to be.

        The difference is that that when the schema changes, the application program(s) don't. When a new schema is being developed, the SPs get modified in step and tested in isolation of any applications. When the switchover comes to pass, the existing DB is renamed and the new DB renamed to the old name. Next connection, all the applications using SPs are automatically using the new schema without a single line of their code needing to change.

        It is exactly the same process of encapsulation that using objects gives you: OO-101: Tenent 1: Isolate the data format from the caller.

        It also provides for an isolation layer between the application code and the DBM vendor. Provided the new DBM vendor also supports SPs and the DBI interface allows access to them, then you simply write the equivalent SP with the same name in the new vendor SP language and again, the applications don't need any modification.

        It also allows the SPs to make use of vendor specific optimisations and extensions without fear of lock-in.

        Examine what is said, not who speaks.        The end of an era!
        "But you should never overestimate the ingenuity of the sceptics to come up with a counter-argument." -Myles Allen
        "Think for yourself!" - Abigail        "Time is a poor substitute for thought"--theorbtwo         "Efficiency is intelligent laziness." -David Dunham
        "Memory, processor, disk in that order on the hardware side. Algorithm, algorithm, algorithm on the code side." - tachyon
[reply]
      ...don't use any SQL code in your applications (except for calling stored procedures). Ever.

      Abso-bloody-lutely!

      Examine what is said, not who speaks.        The end of an era!
      "But you should never overestimate the ingenuity of the sceptics to come up with a counter-argument." -Myles Allen
      "Think for yourself!" - Abigail        "Time is a poor substitute for thought"--theorbtwo         "Efficiency is intelligent laziness." -David Dunham
      "Memory, processor, disk in that order on the hardware side. Algorithm, algorithm, algorithm on the code side." - tachyon
[reply]
      I feel like poor Paco.

      I fully understand the value of storedProcs, and the value of abstracting, and the value of not using SQLs in the scripts, etc. I merely wanted to find out how to replicate a multi-command SQL proc in my script, because, well..., because I wanted to.

      I have come to the conclusion that it is messy to do so because DBI doesn't seem to provide the ability of abstracting the transactions for me, but that is a good and valuable conclusion.

      Now, I have a minor quibble to pick with the above post.

      Besides from the reasons given by other people, it gives database people the opportunity to re-organize the data layout (add/delete columns, split tables) without having to modify an unknown number of programs - all that's needed is to change the relevant stored procedures.

      Well, what the heck are storeprocs other than an "unknown number of programs?" I can't see how the db structure can be changed, and yet storedProcs can prevent any pain? Whether I get to the data via a SQL statement in my Perl script, or via a storedProc in the database called from my Perl script, somewhere, someone will have to change something.

      I think the dba types tend to think one way only -- that the world depends on the database they are custodians of. While that may be true in their realm, there are tons of small programs and processes that are run using databases where the dba can't do everything. The programmer has to find ways to do things in a variety of ways.

      That is the kind of situation I ran into.

      Anyway, thanks everyone for the input. Every bit helps.

[reply]
        Well, what the heck are storeprocs other than an "unknown number of programs?"
        They are a known number of programs, all written in the same language, and stored centrally. Given a database, it's very easy to extract out all its stored procedures - and its quite easy to find out which tables each stored procedure touches. But if the SQL is scattered around over many applications, written in a myriad of languages, all running somewhere on the network (and for some organizations, the "network" can consist of thousands of machines), it's much harder to get everything updated if the schemas change.
[reply]

In Section Seekers of Perl Wisdom