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.
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 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.
Michael
--
Michael Peppler Data Migrations, Inc.
[EMAIL PROTECTED] http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short
or long term contract positions - http://www.peppler.org/resume.html
