CODEWITHSUNDEEP
databaseperltransactionsdbi
Gopal SA
Gopal SA

Reputation: 959

Perl DBI - Getting records affected of each statement in a transaction

HI,

I use perl DBI do() which will execute the below SQL block like below which executes in SQL SERVER 2005

    eval {
            my $result =  do(<<SQL);
            BEGIN TRAN

            UPDATE table1 SET
            col1 = 999 where date = '2010-08-27'

            DELETE FROM table1
            where date = '2010-08-30'

            COMMIT TRAN
SQL
    $logger->info($result);
};

Now i can see that the return value $result only contains the rows affected by the first update statement.So i dont have any information about the deleted rows , But i can see that the rows are indeed deleted in the Database.

In general if i have a INSERT , DELETE , UPDATE statement inside a BEGIN TRAN , COMMIT TRAN block and if the entire block will be submitted by DBI do() method , I would need to know the exact number of statements inserted , number of statements updated and number of statements deleted.

I know that SQL SERVER's @@ROWCOUNT will give me the rows affected after each statement , but that is a SQL server variable which will be visible only inside the block. Is it possible to get the data into perl ?

Any help ?

Upvotes: 0

Views: 3066

Answers (2)

Bryan
Bryan

Reputation: 17703

What database access method is in use? Is there any reason for not moving the transaction logic outside of the T-SQL?

If you're using DBI something along these lines should satisfy your requirements:

eval {
    $dbh->begin_work;
        $dbh->do("CREATE TABLE #temp (col1 INTEGER, date DATETIME);");

        # Inserts
        my $inserted = $dbh->do("INSERT INTO #temp VALUES (1,'2010-08-27');");
        $inserted += $dbh->do("INSERT INTO #temp SELECT 999,'2010-08-27' UNION SELECT 5, '2010-08-30';");

        # Updates
        my $updated = $dbh->do("UPDATE #temp SET col1 = 999 WHERE date = '2010-08-27';");

        # Deleted
        my $deleted = $dbh->do("DELETE FROM #temp WHERE date = '2010-08-30';");
    $dbh->commit;

    print "Inserted $inserted rows.\n";
    print "Updated $updated rows.\n";
    print "Deleted $deleted rows.\n"; }

This snippet doesn't take into account making the database connection, error handling, or closing the connection, but DBI documentation should help there. http://metacpan.org/pod/DBI

You probably also want to look into the prepare and bind_param DBI methods if you're planning on executing multiple non-select statements.

Upvotes: 1

DVK
DVK

Reputation: 129559

To get the value of @@ROWCOUNT, you need to add SELECT @@ROWCOUNT 'rowcount' as the last command before "COMMIT TRAN", then the whole SQL will return the result set consising of 1 row with 1 'rowcount' column.

The only caveat is that since do() method doesn't provide you with results sets, you need to switch to prepare()/fetchrow_array()/fetchrow_array() instead, or use one of the wrapper methods like nsql() from your DB library if those are available.

For a detailed inserted/updated/deleted breakdown, simply save those @@ROWCOUNTs into variables after the insert/update/delete, and select the counts afterwards:

declare @update_count int
declare @delete_count int

UPDATE table1 SET
col1 = 999 where date = '2010-08-27'
SELECT @update_count = @@ROWCOUNT

DELETE FROM table1
where date = '2010-08-30'
SELECT @delete_count = @@ROWCOUNT

SELECT @update_count 'update_count', @delete_count '@delete_count'

Upvotes: 2

Related Questions