in reply to Mass inserts with SQLite + CSV + execute_array?

From my reading of the DBI docs, you kind of have that backwards. 

my @params;
while (my $order = <$csv_fh>)
{
  push @{$params[0]}, $order->{orderid};
  push @{$params[1]}, $order->{volremain};
  push @{$params[2]}, $order->{price};
}
$sth->bind_param_array(1, $params[0]);
$sth->bind_param_array(2, $params[1]);
$sth->bind_param_array(3, $params[2]);

[download]
That looks closer to what DBI says. Also, make sure you have AutoCommit off. That helps most DB's, though I've not tried SQLite yet.

(Yes, that code can be optimised, and likely even shortened, but it's too early in the morning here, so I thought I'd just try to convey the message, and leave it an excersise to the reader to clean it up :-P )

Replies are listed 'Best First'.
Re^2: Mass inserts with SQLite + CSV + execute_array?
by Xenofur (Monk) on Dec 14, 2008 at 15:24 UTC
    Yeah, as noted in the edit up top, i noticed that. And aside from using a hash i'm using exactly the same code as you now.

    As for AutoCommit, that is what the Begin/End block is there for, it does the same thing, only locally constrained.
[reply]

      I wasn't aware that DBI used BEGIN and END blocks to temporarily disable AutoCommit. I would suggest actually setting AutoCommit => 0 in your DBI->connect statement if you aren't already.

[reply]
        AutoCommit is the opposite of being in a transaction. Quote DBI,
        [begin_work] enables transactions (by turning AutoCommit off) until the next call to commit or rollback. After the next commit or rollback, AutoCommit will automatically be turned on again.

        He should be using begin_work and commit, though. DBI might not check for do("BEGIN").

[reply]
[d/l]
[select]
        It might be a SQLite oddity, but i assure you it's the same. :)

        Reference the SQLite documentation on that: http://www.sqlite.org/lang_transaction.html
[reply]

In Section Seekers of Perl Wisdom