eval {
    for(@stmts) {
        my $sth = $dbh->prepare($_);
        $sth->execute;
    }
};
[download]

Why do you think that's painful? The only difference is the eval wrapper and the following if. In any case you'd have to execute all forty statements -- either in your script or in your proc.

Now, the fact that you're using variables with number suffixes is a quick hint that you should be using an array instead. In other words, make an array of statement handles, prepare them, then:

eval {
    $_->execute foreach @sth;
};
if ($@) {
    $dbh->rollback;
} else {
    $dbh->commit;
}
[download]

I should have elaborated more in my OP. The pain was simply having to rewrite the entire script as separate statements. Some of the statements update or grab values that are then used in subsequent statements. Sample SQL code below ---

ALTER PROCEDURE sp @foo VARCHAR(250), @bar VARCHAR(250) AS 

BEGIN TRAN

DECLARE @baz NUMERIC, @quux NUMERIC

SELECT @baz = col FROM table1 WHERE condition
SELECT @quux = col FROM table2 WHERE condition = @baz

INSERT INTO table3 (col1, col2, col3) 
VALUES (@foo, @bar, @quux)

UPDATE someother nonsense

COMMIT TRAN
[download]

I spent a good while getting all my SQL ducks in order getting it so it worked properly. Now, I have to do this in the Perl script, so I have to now write these statements separately, run them one by one, grab values from them to run subsequent statements, check for errors, commit or rollback. I was hoping to just run the entire block of multiple statements as a single script. Hence, the pain.

Patient: Doctor, it hurts when I do this.
Doctor: Don't do that.

I guess I just don't get it. You had working SQL that did what you wanted in a stored proc that you called from your program. Now you want to take it apart, and reconstitute the pieces into your program, and you're finding it painful. So... Is there any reason why you're re-writing it, other than to prove that you don't like pain?

Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.

> What exactly is it that you find burdensome about this?... Is it just the prepare/execute steps?

Yup. On the other hand, if you

> can't imagine any other way of doing it

then that is the answer I was looking for. If there is no other way, then I will just write it up. Thanks for the info.

A shortcut for a prepare/execute pair is the do method, which will save you some typing.

If you can figure out a way to take your original block of code and turn it into seperate SQL statements, Perl is quite good at that sort of thing. For example if statements are seperated by blank lines or semicolons, you could use split to get the list of statements to execute.

Stored procedures have a number of disadvantages compared to SQL embedded in a perl script. One big one is that you need DBA privileges to manage them. Another is that updating them and backing them up is typically more complex than the basic source control steps used for perl code. The biggest one for me is that things which are not pure SQL -- i.e. procedural stuff in the programming language supported by the database -- is usually done in some crippled language without modern constructs or solid debugging tools like Perl's. There is a performance advantage to them when they involve examining a large number of rows that are not actually needed in the end result, but this is not that common a situation.

Stored procedures have a number of disadvantages compared to SQL embedded in a perl script.

I know next to nothing about SQL Server, but as an Oracle guy, I have to speak up in support of stored procedures.

One big one is that you need DBA privileges to manage them.

If that is really the case, it is an SQL Server oddity. Oracle lets everyone create and manage his own stored procedures.

Another is that updating them and backing them up is typically more complex than the basic source control steps used for perl code.

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.

The biggest one for me is that things which are not pure SQL -- i.e. procedural stuff in the programming language supported by the database -- is usually done in some crippled language without modern constructs or solid debugging tools like Perl's.

Well, those languages are domain languages, and are mainly intended to query the database, which they can do much more elegantly than more general languages (including Perl). Also, some DBMS have plugins that let you write stored procedures in Perl (not sure if that is a good idea, though).

There is a performance advantage to them when they involve examining a large number of rows that are not actually needed in the end result, but this is not that common a situation.

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.

And then we have: easier transaction management in Perl (just keep AutoCommit on, one transaction can always be a single statement), shorter, simpler code (Perl just needs to call stored procedures, which is simple with our excellent DBI, the stored procedures themselves are usually more concise than their Perl counterparts), accessibilty of the code from other programming environments, the ability to fix code and to see dependencies in one place (rather than everywhere on your server farm) and data validation right in the database.

Forgive the rant, but I am a hopeless PL/SQL fan boy, and try to keep my programs completely free of any SQL (for the same reasons that I keep them free of HTML). It is all about separation of concerns and using the right tool for the job.

I'm with Thilosophy on this one.

Sure, you might need to have DBA (or DBO == database owner) privileges to manage stored procedures, but that's actually a good thing - there should be some control on what SQL is run against a server, but source control is simply done the same way as with any other language - store source files in CVS (or perforce, or...), and load the source files to production systems in a controled manner (just as you'd move perl code into production in a controled manner).

In terms of performance, my tests on Sybase show that stored procedures called as RPCs give the best performance because you don't have no SQL parsing overhead, and you usually don't have the query plan generation overhead either.

I agree that doing procedural processing in SQL is bad, but if you can perform your requests as set operations then you're ahead.

In addition, for Sybase at least it is important to keep transactions short, and wrapping them in a stored procedure is one way of achieving that.

Michael

see my response above. I wrote the program for one installation. There I had access to the db, and was able to create the storedproc in the database. Now I am using the same program for another installation... in this case it is not yet clear if I will have access to the db to create a storedproc in it. Hence, I am trying to redo the script with all the SQL in the script itself.

In my work application, I use a mini language to express updates to the database schema, and I have code to parse that out and execute the necessary SQL. Here's a part of the update we used when we were moving to referencing users by id instead of by handle:

  desc: category mod/ed referenced by id
  table: change moderator moderator_handle varchar(32), + 
      change editor editor_handle varchar(32)
  table: add moderator int(11) unsigned not null, + 
      add editor int(11) unsigned not null
  [loop: (uid, uhandle) select distinct u.id, u.handle + 
      from users u, category c +
      where u.handle = c.moderator_handle or u.handle = c.editor_handl
+e
    pop: update category set moderator = *uid where moderator_handle =
+ *uhandle
    pop: update category set editor = *uid where editor_handle = *uhan
+dle
  ] 
  table: drop moderator_handle, drop editor_handle
[download]

The code doing this is complex and intimately tied up with a lot of other stuff, so I'm not going to try to post it here. But I see no reason why you couldn't do something similar to parse the stored procedures and execute them directly - it'll take some pain to get the code working, but once you've done that you'll have the benefit that you only need to develop and maintain one version of each stored procedure.

Hugo