On Wed, Jul 14, 2004 at 11:46:21AM +0200, Michael Peppler wrote:
> On Tue, 2004-07-13 at 17:04, Michael Peppler wrote:
> > On Tue, 2004-07-13 at 16:46, Tim Bunce wrote:
> > > On Tue, Jul 13, 2004 at 03:26:13PM +0200, Michael Peppler wrote:
> >
> > > > Sample code:
> > > >
> > > > my $dbh = DBI->connect('dbi:Sybase:server=MY_SERVER;bulkLogin=1',
> > > > $user, $pwd);
> > > > my $sth = $dbh->prepare('insert the_table values(?, ?, ?, ?, ?)",
> > > > syb_bcp_attribs => { identity_flag => 0,
> > > > identity_column => 0 }});
> > > > while(<DATA>) {
> > > > chomp;
> > > > my @row = split(/\|/, $_); # assume a pipe-delimited file...
> > > > $sth->execute(@row);
> > > > }
> > > > $dbh->commit;
> > > > print "Sent ", $sth->rows, " to the server\n";
> > > > $sth->finish;
> > >
> > > > AutoCommit is ignored for this operation (it is always off).
> > >
> > > Could you make it warn if AutoCommit is on, and include $dbh->begin_work
> > > in your examples? That would make the code more portable.
> >
> > I'll check.
>
> Grrr:
> syb_blk_init(): table=#tmp
> servermsg_cb -> number=226 severity=16 state=1 line=1 server=elanor
> text=BULK INSERT command not allowed within multi-statement transaction.
Does DBD::Sybase _explicitly_ start a transaction when AutoCommit
is turned off (and after a commit/rollback) or does the server do
it _implicitly_. And if the server does it implicitly does it (can it)
wait till the client first 'makes a change'?
> So while AutoCommit needs to be off in the driver (i.e. logically, so
> that commit()/rollback() will work without warnings), I can't tell the
> *server* that AutoCommit is off.
>
> So I'm looking at something like this:
>
> $dbh->begin_work; # turns AutoCommit off in the driver and the server
> $sth = $dbh->prepare('insert ...', { syb_bcp_attribs => ...});
> # Oh, it's a BLK operation, turn AutoCommit back ON on the server
> # and in doing so hope that there are no pending transactions
> # generated between the begin_work() call and the prepare(), because
> # they'd have to be committed (or rolled back) before the AutoCommit
> # flag can be turned on on the server.
Ug. That's a mess. Perhaps the bulk insert should happen on a cloned dbh.
Tim.
