Re: Recoding a multi-sql-statement storedProc transaction in a script

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]

Although I'm still not sure I understood what the pain you're referring to is.

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

Comment on Re: Recoding a multi-sql-statement storedProc transaction in a script Select or Download Code

Replies are listed 'Best First'.
Re^2: Recoding a multi-sql-statement storedProc transaction in a script by punkish (Priest) on Dec 14, 2004 at 23:33 UTC
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.	[reply] [d/l]
Re^3: Recoding a multi-sql-statement storedProc transaction in a script by VSarkiss (Monsignor) on Dec 15, 2004 at 01:57 UTC
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.	[reply]
Re^4: Recoding a multi-sql-statement storedProc transaction in a script by punkish (Priest) on Dec 15, 2004 at 02:00 UTC
So... Is there any reason why you're re-writing it Because, in the earlier version I had access to the db to add my storedproc to it. Now, in this new instance, there is a possibility that I may not be able to muck around with the db, so I just want to be prepared with a working version of my SQLs in my script.	[reply]