Skip Menu |
 

This queue is for tickets about the DBD-ODBC CPAN distribution.

Report information
The Basics
Id: 58553
Status: resolved
Priority: 0/
Queue: DBD-ODBC

People
Owner: Nobody in particular
Requestors: ribasushi [...] leporine.io
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: 1.24
Fixed in: 1.24_2



Subject: Executing a BACKUP command silently fails with DBD::ODBC
MIME-Version: 1.0
X-Mailer: MIME-tools 5.427 (Entity 5.427)
X-RT-Original-Encoding: utf-8
Content-Type: multipart/mixed; boundary="----------=_1277105102-2909-31"
Content-Length: 0
Content-Type: text/plain; charset="UTF-8"
Content-Disposition: inline
Content-Transfer-Encoding: binary
Content-Length: 339
Download (untitled) / with headers
text/plain 339b
When executing a BACKUP statement via $dbh->do, the command seems to complete, but nothing actually happens on the server. The same command works with DBD::Sybase. Attached the test script (easy to follow, but not easy to run as it implies checking server-side files), the output of said script, and the output with DBI_TRACE=5. Cheers!
Subject: backup_test_output.txt
MIME-Version: 1.0
Content-Type: text/plain; charset="utf-8"; name="backup_test_output.txt"
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline; filename="backup_test_output.txt"
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: ascii
Content-Length: 418
ok 1 - No backup file # 9.00.3042.00 via dbi:ODBC:server=192.168.0.12;port=1433;driver=FreeTDS;tds_version=8.0 ok 2 - no throw not ok 3 - Backup Test_odbc.bak created # Failed test 'Backup Test_odbc.bak created' # at backup_test.pl line 49. ok 4 - No backup file # 9.00.3042.00 via dbi:Sybase:server=192.168.0.12:1433 ok 5 - no throw ok 6 - Backup Test_sybase.bak created 1..6 # Looks like you failed 1 test of 6.
Subject: backup_test_output_with_trace.txt
MIME-Version: 1.0
Content-Type: text/plain; charset="utf-8"; name="backup_test_output_with_trace.txt"
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline; filename="backup_test_output_with_trace.txt"
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: ascii
Content-Length: 24778

Message body is not shown because it is too large.

Subject: backup_test.pl
MIME-Version: 1.0
Content-Type: text/x-perl; name="backup_test.pl"
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline; filename="backup_test.pl"
Content-Transfer-Encoding: binary
Content-Length: 989
Download backup_test.pl
text/x-perl 989b
use warnings; use strict; use Test::More; use Test::Exception; use DBI; my $dsns = { odbc => 'dbi:ODBC:server=192.168.0.12;port=1433;driver=FreeTDS;tds_version=8.0', sybase => 'dbi:Sybase:server=192.168.0.12:1433', }; for my $type (keys %$dsns) { my $fn = "Test_${type}.bak"; unlink "/mnt/$fn" if (-f "/mnt/$fn"); ok (! -f "/mnt/$fn", "No backup file"); lives_ok { my $dbh = DBI->connect( $dsns->{$type}, 'sa', '123456' ); note $dbh->selectrow_hashref('master.dbo.xp_msver ProductVersion')->{Character_Value} . " via $dsns->{$type}"; $dbh->do (<<EOS); BACKUP DATABASE [BackTest] TO DISK = N'D:\\SQL_BACKUPS\\$fn' WITH DESCRIPTION = N'Test $type Backup', NAME = N'TST $type BKP', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM, NOFORMAT, NOINIT EOS } 'no throw'; sleep 2; # async file creation ok (-f "/mnt/$fn", "Backup $fn created"); } done_testing;
MIME-Version: 1.0
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-2906-1277107235-1372.58553-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 1279
Download (untitled) / with headers
text/plain 1.2k
On Mon Jun 21 03:25:02 2010, RIBASUSHI wrote: Show quoted text
> When executing a BACKUP statement via $dbh->do, the command seems to > complete, but nothing actually happens on the server. The same command > works with DBD::Sybase. Attached the test script (easy to follow, but > not easy to run as it implies checking server-side files), the output of > said script, and the output with DBI_TRACE=5. > > Cheers! >
When you call procedures in MS SQL Server you often need to use prepare/execute/odbc_more_results because SQL Server batches up results and anything printed in the procedure can count as a result. It you do not move through all the results in the procedure the procedure is not finished! I would try this: $sth = prepare(backup....) $sth->execute do { my @row; while (@row = $sth->fetchrow_array()) { # do stuff here } } while ($sth->{odbc_more_results}); The above is just pseudo code - you still need to check for errors. e.g., check $sth->err after odbc_more_results returns false as the loop terminates when there are a) no more results and b) an error. There is an example in t/20SqlServer.t that comes with DBD::ODBC - you can find it by browsing the source on CPAN. Martin -- Martin J. Evans Wetherby, UK
MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-2906-1277107235-1372.58553-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
References: <rt-3.8.HEAD-2906-1277107235-1372.58553-0-0 [...] rt.cpan.org>
Content-Type: multipart/mixed; boundary="----------=_1277109946-2912-55"
Message-ID: <rt-3.8.HEAD-2912-1277109946-816.58553-0-0 [...] rt.cpan.org>
X-RT-Original-Encoding: utf-8
Content-Length: 0
Content-Disposition: inline
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 1370
Download (untitled) / with headers
text/plain 1.3k
On Mon Jun 21 04:00:35 2010, MJEVANS wrote: Show quoted text
> On Mon Jun 21 03:25:02 2010, RIBASUSHI wrote:
> > When executing a BACKUP statement via $dbh->do, the command seems to > > complete, but nothing actually happens on the server. The same command > > works with DBD::Sybase. Attached the test script (easy to follow, but > > not easy to run as it implies checking server-side files), the output of > > said script, and the output with DBI_TRACE=5. > > > > Cheers! > >
> > When you call procedures in MS SQL Server you often need to use > prepare/execute/odbc_more_results because SQL Server batches up results > and anything printed in the procedure can count as a result. It you do > not move through all the results in the procedure the procedure is not > finished! I would try this: > > $sth = prepare(backup....) > $sth->execute > do { > my @row; > while (@row = $sth->fetchrow_array()) { > # do stuff here > } > } while ($sth->{odbc_more_results}); > > > The above is just pseudo code - you still need to check for errors. > e.g., check $sth->err after odbc_more_results returns false as the loop > terminates when there are a) no more results and b) an error. >
Interesting - I am attaching new test/result, showing that there is nothing to read from the handle, but it doesn't work *unless* you read:
MIME-Version: 1.0
Subject: backup_test_output.txt
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Type: text/plain; charset="utf-8"; name="backup_test_output.txt"
Content-Disposition: inline; filename="backup_test_output.txt"
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 968
# 9.00.3042.00 via dbi:ODBC:server=192.168.0.12;port=1433;driver=FreeTDS;tds_version=8.0 ok 1 - No backup file ok 2 - no throw via odbc/do not ok 3 - Backup Test_odbc.bak created # Failed test 'Backup Test_odbc.bak created' # at backup_test.pl line 76. ok 4 - No backup file ok 5 - no throw via odbc/prepare ok 6 - Backup Test_odbc.bak created ok 7 - No backup file # odbc prepare/execute returns: # ...nothing ok 8 - no throw via odbc/prepare_with_fetch ok 9 - Backup Test_odbc.bak created # 9.00.3042.00 via dbi:Sybase:server=192.168.0.12:1433 ok 10 - No backup file ok 11 - no throw via sybase/do ok 12 - Backup Test_sybase.bak created ok 13 - No backup file ok 14 - no throw via sybase/prepare ok 15 - Backup Test_sybase.bak created ok 16 - No backup file # sybase prepare/execute returns: # $VAR1 = [ # [] # ]; ok 17 - no throw via sybase/prepare_with_fetch ok 18 - Backup Test_sybase.bak created 1..18 # Looks like you failed 1 test of 18.
Subject: backup_test.pl
MIME-Version: 1.0
Content-Type: text/x-perl; name="backup_test.pl"
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline; filename="backup_test.pl"
Content-Transfer-Encoding: binary
Content-Length: 1692
Download backup_test.pl
text/x-perl 1.6k
use warnings; use strict; use Test::More; use Test::Exception; use Data::Dumper; use DBI; my $dsns = { odbc => 'dbi:ODBC:server=192.168.0.12;port=1433;driver=FreeTDS;tds_version=8.0', sybase => 'dbi:Sybase:server=192.168.0.12:1433', }; for my $drv (keys %$dsns) { my $fn = "Test_${drv}.bak"; my $dbh = DBI->connect( $dsns->{$drv}, 'sa', '123456', { RaiseError => 1, PrintError => 0 }, ); note $dbh->selectrow_hashref('master.dbo.xp_msver ProductVersion')->{Character_Value} . " via $dsns->{$drv}"; my $sql = <<EOS; BACKUP DATABASE [BackTest] TO DISK = N'D:\\SQL_BACKUPS\\$fn' WITH DESCRIPTION = N'Test $drv Backup', NAME = N'TST $drv BKP', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM, NOFORMAT, NOINIT EOS for my $call (qw/do prepare prepare_with_fetch/) { unlink "/mnt/$fn" if (-f "/mnt/$fn"); ok (! -f "/mnt/$fn", "No backup file"); lives_ok { if ($call eq 'do') { $dbh->do ($sql); } elsif ($call eq 'prepare') { $dbh->prepare ($sql)->execute; } else { my $sth = $dbh->prepare ($sql); $sth->execute; note "$drv prepare/execute returns:"; if ($drv eq 'odbc') { if ($sth->{odbc_more_results}) { note Dumper [$sth->fetchall_arrayref] while $sth->{odbc_more_results}; } else { note '...nothing'; } } else { note Dumper [$sth->fetchall_arrayref]; } } } "no throw via $drv/$call"; sleep 2; # async file creation ok (-f "/mnt/$fn", "Backup $fn created"); } } done_testing;
MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-2912-1277109946-816.58553-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <rt-3.8.HEAD-2906-1277107235-1372.58553-0-0 [...] rt.cpan.org> <rt-3.8.HEAD-2912-1277109946-816.58553-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-2373-1279909388-1687.58553-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 149
Download (untitled) / with headers
text/plain 149b
I've added FAQ entry and a note on the do method to DBD::ODBC 1.24_2 so will close this when rt sees 1.24_2. Martin -- Martin J. Evans Wetherby, UK


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.