smackdab has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, 
I want to walk one table and:  
  summarize some raw data      (select ...
  create a new summary record  (insert into ... (same tbl)
  delete the raw data          (delete from ... (same tbl)
  # I don't think I can use a single (complex) SQL stmt...

[download]
I'll put this in a transaction...(like the recent DBI and transactions post)

And I plan on only supporting a relational db, but haven't decided: (sql, oracle, mysql)

The DBI docs state that:

Portable applications should not assume that a new statement can be prepared and/or executed while still fetching results from a previous statement.

This happens to me testing the latest DBI/DBD::ODBC

If I open another $dbh, I don't think transactions will work

Am I missing something?

thanks!!!

Replies are listed 'Best First'.
Re: dbi and mult statements
by lachoy (Parson) on Jul 11, 2002 at 03:48 UTC

    When doing something like this, you have two options. First, if you're not dealing with too much data, you can select it all into a data structure, start a transaction, then do the inserts and deletes while iterating through the data structure. Once done you can commit/rollback.

    The other option is to create two handles. The first one just performs the select and iterates through the rows. The second one does the inserts and deletes with transaction control.

    You definitely cannot do this in a single statement, unless that statement calls a stored procedure which does the actual work :-)

    Chris
    M-x auto-bs-mode

[reply]
      That makes sense ;-) glad I asked !
[reply]

Back to Seekers of Perl Wisdom