in reply to fetchrow_array loop in perl 5.10

Depending on (the version of) your database driver, it might be that your database driver does not like more than one statement handle being active at the same time. In your case, that would be $searchSQL during the loop and in that loop the $insertSQL.

A workaround could be to first fetch all items into an array and then execute the insert for each element of that array.

In my experience, at least SQLite had this behaviour of not really wanting more than one active statement handle.

Replies are listed 'Best First'.
Re^2: fetchrow_array loop in perl 5.10
by choroba (Cardinal) on Sep 18, 2014 at 10:39 UTC
    first fetch all items into an array and then execute the insert for each element of that array
    Another option might be to keep two database handles, and run the search in one, and the insert in the second one.
    لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
[reply]
      yep, we already found a workaround for it, but we wanted to find a centralized fix for it, this is an existing code (thousand lines of codes are coded this way) and was working in perl 5.8
[reply]
Re^2: fetchrow_array loop in perl 5.10
by bulrush (Scribe) on Sep 18, 2014 at 19:33 UTC
    Depending on (the version of) your database driver, it might be that your database driver does not like more than one statement handle being active at the same time.

    Yep, that's what happened to me. I had to declare $dbh2 to make my stuff work. $dbh cannot handle both statements. I use DBD::Pg for Postgresql.

    Perl 5.8.8 on Redhat Linux RHEL 5.5.56 (64-bit)
[reply]

      See perhaps, DBI, fork, and clone.

      I can't tell if applicable to those problems (yours and that of the OP), but it's interesting anyway :)

[reply]

In Section Seekers of Perl Wisdom