On Wed, Jul 14, 2004 at 02:05:13PM +0200, Michael Peppler wrote:
> On Wed, 2004-07-14 at 13:50, Tim Bunce wrote:
> > 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'?
>
> DBD::Sybase can do either, depending on the configuration. The default
> up to now was to start an explicit transaction in the driver, and my
> copy here now defaults to using the server/protocol level AutoCommit
> functionality (the one used by Sybase's ODBC and JDBC drivers). There
> are various advantages in using this. Sybase will start a transaction on
> the first statement that follows the AutoCommit OFF request, or that
> follows a COMMIT or ROLLBACK, including a simple SELECT statement.
That's also the way Oracle and many other dbs work.
The error message says "within" (which is what prompted my question)
so has something "started" a transaction before the bulk insert?
> Interestingly the message quoted above did NOT appear when I used an
> older version of Sybase's libs, even though the server I was connected
> to is the same (Sybase's latest released version.)
So is the error from the client libs and not from the server?
> > > 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.
>
> Maybe.
>
> Right now I've got it working (I think correctly :-) but I'll need to
> throw more tests at it to be sure.
Okay.
Tim.
