Re: get the messages, not the rows
Dave, Sorry to hear about the lack of FLUSHMESSAGE in MS. I assumed that this command was still hanging around, after all, the code base for MS was originally Sybase.( many moons and version ago). Chuck [EMAIL PROTECTED] wrote: On Wed, Sep 17, 2003 at 05:10:21PM -0700, David L. Good [EMAIL PROTECTED] wrote: On Tue, Sep 16, 2003 at 02:21:54PM -0400, Chuck Fox [EMAIL PROTECTED] wrote: Dave, The command is for the dataserver that you are using. I assumed that the DBCC command was directed at a MS or Sybase Sql Server. Whether you are using DBD:ODBC or DBD:Sybase the effect is the same, it causes the dataserver to flush its buffers out to the application. DBD:ODBC is just a transport mechanism for the dbcc command or whatever command you are passing. OK, but it works fine (no buffering) using DBD::Sybase on Unix platforms without using the 'SET FLUSHMESSAGE ON', but doesn't when using DBD::ODBC on Win32. I'll see if I can give your suggestion a try, though. It'd be great if it works! Hmmm. Sybase has FLUSHMESSAGE, but MS-SQL Server 2000 does not. Since I don't have Sybase's ODBC driver installed (and don't really need to access Sybase from Windows) I'm kinda stuck. In any case, I was interested in a solution for SQL Server, so I guess I'm out of luck :-(
Re: get the messages, not the rows
On Wed, Sep 17, 2003 at 05:10:21PM -0700, David L. Good [EMAIL PROTECTED] wrote: On Tue, Sep 16, 2003 at 02:21:54PM -0400, Chuck Fox [EMAIL PROTECTED] wrote: Dave, The command is for the dataserver that you are using. I assumed that the DBCC command was directed at a MS or Sybase Sql Server. Whether you are using DBD:ODBC or DBD:Sybase the effect is the same, it causes the dataserver to flush its buffers out to the application. DBD:ODBC is just a transport mechanism for the dbcc command or whatever command you are passing. OK, but it works fine (no buffering) using DBD::Sybase on Unix platforms without using the 'SET FLUSHMESSAGE ON', but doesn't when using DBD::ODBC on Win32. I'll see if I can give your suggestion a try, though. It'd be great if it works! Hmmm. Sybase has FLUSHMESSAGE, but MS-SQL Server 2000 does not. Since I don't have Sybase's ODBC driver installed (and don't really need to access Sybase from Windows) I'm kinda stuck. In any case, I was interested in a solution for SQL Server, so I guess I'm out of luck :-( -- David Good[EMAIL PROTECTED] This space intentionally left blank.
Re: get the messages, not the rows
On Tue, Sep 16, 2003 at 02:21:54PM -0400, Chuck Fox [EMAIL PROTECTED] wrote: Dave, The command is for the dataserver that you are using. I assumed that the DBCC command was directed at a MS or Sybase Sql Server. Whether you are using DBD:ODBC or DBD:Sybase the effect is the same, it causes the dataserver to flush its buffers out to the application. DBD:ODBC is just a transport mechanism for the dbcc command or whatever command you are passing. OK, but it works fine (no buffering) using DBD::Sybase on Unix platforms without using the 'SET FLUSHMESSAGE ON', but doesn't when using DBD::ODBC on Win32. I'll see if I can give your suggestion a try, though. It'd be great if it works! -- David Good[EMAIL PROTECTED] This space intentionally left blank.
Re: get the messages, not the rows
On Mon, Sep 15, 2003 at 09:23:18AM -0400, Chuck Fox [EMAIL PROTECTED] wrote: David, In regards to the buffering, in Sybase and MS there is a set command SET FLUSHMESSAGE ON, that will force the buffers to flush from OpenClient to the OS. But the discussion was about using DBD::ODBC, not OpenClient (DBD::Sybase). Does it also work with ODBC? -- David Good[EMAIL PROTECTED] This space intentionally left blank.
Re: get the messages, not the rows
Dave, The command is for the dataserver that you are using. I assumed that the DBCC command was directed at a MS or Sybase Sql Server. Whether you are using DBD:ODBC or DBD:Sybase the effect is the same, it causes the dataserver to flush its buffers out to the application. DBD:ODBC is just a transport mechanism for the dbcc command or whatever command you are passing. Chuck [EMAIL PROTECTED] wrote: On Mon, Sep 15, 2003 at 09:23:18AM -0400, Chuck Fox [EMAIL PROTECTED] wrote: David, In regards to the buffering, in Sybase and MS there is a set command SET FLUSHMESSAGE ON, that will force the buffers to flush from OpenClient to the OS. But the discussion was about using DBD::ODBC, not OpenClient (DBD::Sybase). Does it also work with ODBC?
Re: get the messages, not the rows
David,
In regards to the buffering, in Sybase and MS there is a set command SET
FLUSHMESSAGE ON, that will force the buffers to flush from OpenClient to
the OS.
HTH,
Chuck Fox
[EMAIL PROTECTED] wrote:
On Sat, Sep 13, 2003 at 01:08:27PM -0400, Jeff Urlwin [EMAIL PROTECTED] wrote:
Is there a way to execute an SQL command and get the
messages, not the records?
I mean, I'd like to run
DBCC CHECKDB ('databasename')
parse the output and add the result to a daily report of my
servers' health. The question is how do I get the messages.
I'm using DBI+DBD::ODBC and MS SQL Server 2000.
Buried in the DBD::ODBC t/20SqlServer.t is an example. You need to set an
error handler and set odbc_async_exec. In there is the snippet that calls
dbcc TRACESTATUS(-1) which runs during the tests (the safest thing I think
I could find to run on generic tests, but I believe DBCC
CHECKDB('databasename') was the reason the original patches were submitted
by David L. Good and included by me... The only clear documentation is in
the Changes file. The Changes file also points to a specific file which
calls checkdb...
You don't really need to set odbc_async_exec unless you need to get the
messages as soon as possible after they are generated. In my case, I wanted
to be able to send an alert as soon as my program saw a DBCC error but still
let the DBCC continue. Unfortunately, it seems that ODBC buffers the output,
so even with odbc_async_exec set you get the messages in chunks, so an error
could sit in a buffer for quite some time (say, while a really large table
is being DBCC'd) before you'll see it.
use strict;
use DBI;
sub err_handler {
my ($state, $msg) = @_;
# Strip out all of the driver ID stuff
$msg =~ s/^(\[[\w\s]*\])+//;
print === state: $state msg: $msg\n;
return 0;
}
my $dbh = DBI-connect(dbi:ODBC:PERL_TEST_SQLSERVER, $ENV{DBI_USER},
$ENV{DBI_PASS})
|| die Can't connect: $DBI::errstr\n;
$dbh-{odbc_err_handler} = \err_handler;
$dbh-{odbc_async_exec} = 1;
print odbc_async_exec is: $dbh-{odbc_async_exec}\n;
my $sth;
$sth = $dbh-prepare(dbcc checkdb(model)) || die $dbh-errstr;
$sth-execute || die $dbh-errstr;
$sth-finish;
$dbh-disconnect;
Regards,
Jeff
Thanks, Jenda
(And sorry if it's on line XX of the fine manual and I just
did not find it:) = [EMAIL PROTECTED] ===
http://Jenda.Krynicky.cz = When it comes to wine, women
and song, wizards are allowed to get drunk and croon as much
as they like.
-- Terry Pratchett in Sourcery
RE: get the messages, not the rows
If MSSQL Server hasn't diverged too far from being a
derivative of Sybase, you would trap those messages in an
error or a message handler - but that only works in sybperl
and DBD::Sybase AFAIK. I don't know how you would get to the
those callbacks in ODBC. HTH somewhat.
Hmmm... You can do this in the more recent DBD::ODBCs.
See below.
To:
[EMAIL PROTECTED]
Jenda Krynicky cc:
[EMAIL PROTECTED] Subject: get
the messages, not the rows
11 Sep 2003 10:37 AM
Is there a way to execute an SQL command and get the
messages, not the records?
I mean, I'd like to run
DBCC CHECKDB ('databasename')
parse the output and add the result to a daily report of my
servers' health. The question is how do I get the messages.
I'm using DBI+DBD::ODBC and MS SQL Server 2000.
Buried in the DBD::ODBC t/20SqlServer.t is an example. You need to set an
error handler and set odbc_async_exec. In there is the snippet that calls
dbcc TRACESTATUS(-1) which runs during the tests (the safest thing I think
I could find to run on generic tests, but I believe DBCC
CHECKDB('databasename') was the reason the original patches were submitted
by David L. Good and included by me... The only clear documentation is in
the Changes file. The Changes file also points to a specific file which
calls checkdb...
use strict;
use DBI;
sub err_handler {
my ($state, $msg) = @_;
# Strip out all of the driver ID stuff
$msg =~ s/^(\[[\w\s]*\])+//;
print === state: $state msg: $msg\n;
return 0;
}
my $dbh = DBI-connect(dbi:ODBC:PERL_TEST_SQLSERVER, $ENV{DBI_USER},
$ENV{DBI_PASS})
|| die Can't connect: $DBI::errstr\n;
$dbh-{odbc_err_handler} = \err_handler;
$dbh-{odbc_async_exec} = 1;
print odbc_async_exec is: $dbh-{odbc_async_exec}\n;
my $sth;
$sth = $dbh-prepare(dbcc checkdb(model)) || die $dbh-errstr;
$sth-execute || die $dbh-errstr;
$sth-finish;
$dbh-disconnect;
Regards,
Jeff
Thanks, Jenda
(And sorry if it's on line XX of the fine manual and I just
did not find it:) = [EMAIL PROTECTED] ===
http://Jenda.Krynicky.cz = When it comes to wine, women
and song, wizards are allowed to get drunk and croon as much
as they like.
-- Terry Pratchett in Sourcery
Re: get the messages, not the rows
On Sat, Sep 13, 2003 at 01:08:27PM -0400, Jeff Urlwin [EMAIL PROTECTED] wrote:
Is there a way to execute an SQL command and get the
messages, not the records?
I mean, I'd like to run
DBCC CHECKDB ('databasename')
parse the output and add the result to a daily report of my
servers' health. The question is how do I get the messages.
I'm using DBI+DBD::ODBC and MS SQL Server 2000.
Buried in the DBD::ODBC t/20SqlServer.t is an example. You need to set an
error handler and set odbc_async_exec. In there is the snippet that calls
dbcc TRACESTATUS(-1) which runs during the tests (the safest thing I think
I could find to run on generic tests, but I believe DBCC
CHECKDB('databasename') was the reason the original patches were submitted
by David L. Good and included by me... The only clear documentation is in
the Changes file. The Changes file also points to a specific file which
calls checkdb...
You don't really need to set odbc_async_exec unless you need to get the
messages as soon as possible after they are generated. In my case, I wanted
to be able to send an alert as soon as my program saw a DBCC error but still
let the DBCC continue. Unfortunately, it seems that ODBC buffers the output,
so even with odbc_async_exec set you get the messages in chunks, so an error
could sit in a buffer for quite some time (say, while a really large table
is being DBCC'd) before you'll see it.
use strict;
use DBI;
sub err_handler {
my ($state, $msg) = @_;
# Strip out all of the driver ID stuff
$msg =~ s/^(\[[\w\s]*\])+//;
print === state: $state msg: $msg\n;
return 0;
}
my $dbh = DBI-connect(dbi:ODBC:PERL_TEST_SQLSERVER, $ENV{DBI_USER},
$ENV{DBI_PASS})
|| die Can't connect: $DBI::errstr\n;
$dbh-{odbc_err_handler} = \err_handler;
$dbh-{odbc_async_exec} = 1;
print odbc_async_exec is: $dbh-{odbc_async_exec}\n;
my $sth;
$sth = $dbh-prepare(dbcc checkdb(model)) || die $dbh-errstr;
$sth-execute || die $dbh-errstr;
$sth-finish;
$dbh-disconnect;
Regards,
Jeff
Thanks, Jenda
(And sorry if it's on line XX of the fine manual and I just
did not find it:) = [EMAIL PROTECTED] ===
http://Jenda.Krynicky.cz = When it comes to wine, women
and song, wizards are allowed to get drunk and croon as much
as they like.
-- Terry Pratchett in Sourcery
--
David Good[EMAIL PROTECTED]
This space intentionally left blank.
get the messages, not the rows
Is there a way to execute an SQL command and get the messages, not
the records?
I mean, I'd like to run
DBCC CHECKDB ('databasename')
parse the output and add the result to a daily report of my servers'
health. The question is how do I get the messages.
I'm using DBI+DBD::ODBC and MS SQL Server 2000.
Thanks, Jenda
(And sorry if it's on line XX of the fine manual and I just did not
find it:)
= [EMAIL PROTECTED] === http://Jenda.Krynicky.cz =
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery
Re: get the messages, not the rows
If MSSQL Server hasn't diverged too far from being a derivative of Sybase, you
would trap those messages in an error or a message handler - but that only works
in sybperl and DBD::Sybase AFAIK. I don't know how you would get to the those
callbacks in ODBC.
HTH somewhat.
--
Matt
To: [EMAIL PROTECTED]
Jenda Krynicky cc:
[EMAIL PROTECTED] Subject: get the messages, not the rows
11 Sep 2003 10:37 AM
Is there a way to execute an SQL command and get the messages, not
the records?
I mean, I'd like to run
DBCC CHECKDB ('databasename')
parse the output and add the result to a daily report of my servers'
health. The question is how do I get the messages.
I'm using DBI+DBD::ODBC and MS SQL Server 2000.
Thanks, Jenda
(And sorry if it's on line XX of the fine manual and I just did not
find it:)
= [EMAIL PROTECTED] === http://Jenda.Krynicky.cz =
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery
Re: get the messages, not the rows
From: [EMAIL PROTECTED]
If MSSQL Server hasn't diverged too far from being a derivative of
Sybase, you would trap those messages in an error or a message handler
- but that only works in sybperl and DBD::Sybase AFAIK. I don't know
how you would get to the those callbacks in ODBC. HTH somewhat. --
Matt
I tried to get them from the errors but they do not seem to be there.
This is what I tried:
use DBI;
my $db = DBI-connect('dbi:ODBC:jobodbc2', '', 'xxx',
{PrintError = 0,RaiseError = 1,LongReadLen = 65536});
my $rows = $db-do(q{DBCC CHECKDB ('jobviper')});
$db-disconnect();
print ROWS: $rows\n;
print ERROR: .$db-errstr.\n;
No exception raised, nothing in errstr :-(
Jenda
Jenda
= [EMAIL PROTECTED] === http://Jenda.Krynicky.cz =
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery
RE: [dbi] get the messages, not the rows
From: Martin J. Evans [EMAIL PROTECTED]
I haven't tried it in a long time (other than running make test) but
from perldoc DBD::ODBC:
odbc_async_exec
Allow asynchronous execution of queries. Right now,
this causes a spin-loop (with a small sleep) until
the sql is complete. This is useful, however, if you
want the error handling and asynchronous messages (see the
err_handler) below. See t/20SQLServer.t for an example of
this.
Swet. It works:
use DBI;
my $text = '';
my $dbh = DBI-connect('dbi:ODBC:jobodbc2', 'TMPJOBVIPERADMIN',
'jobviper',
{PrintError = 0,RaiseError = 1,LongReadLen = 65536,
odbc_async_exec = 1,
odbc_err_handler = sub {
my ($state, $msg) = @_;
# Strip out all of the driver ID stuff
$msg =~ s/^(\[[\w\s]*\])+//;
$text .= $msg.\n;
return 0;
}
});
$sth = $dbh-prepare(dbcc CHECKDB ('jobviper'));
$sth-execute;
print $text;
$dbh-disconnect();
It seems that the -prepare() and -execute() is necessary, it
doesn't work with -do().
Thanks, Jenda
= [EMAIL PROTECTED] === http://Jenda.Krynicky.cz =
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery
RE: [dbi] get the messages, not the rows
On 11-Sep-2003 Jenda Krynicky wrote:
From: Martin J. Evans [EMAIL PROTECTED]
I haven't tried it in a long time (other than running make test) but
from perldoc DBD::ODBC:
odbc_async_exec
Allow asynchronous execution of queries. Right now,
this causes a spin-loop (with a small sleep) until
the sql is complete. This is useful, however, if you
want the error handling and asynchronous messages (see the
err_handler) below. See t/20SQLServer.t for an example of
this.
Swet. It works:
use DBI;
my $text = '';
my $dbh = DBI-connect('dbi:ODBC:jobodbc2', 'TMPJOBVIPERADMIN',
'jobviper',
{PrintError = 0,RaiseError = 1,LongReadLen = 65536,
odbc_async_exec = 1,
odbc_err_handler = sub {
my ($state, $msg) = @_;
# Strip out all of the driver ID stuff
$msg =~ s/^(\[[\w\s]*\])+//;
$text .= $msg.\n;
return 0;
}
});
$sth = $dbh-prepare(dbcc CHECKDB ('jobviper'));
$sth-execute;
print $text;
$dbh-disconnect();
It seems that the -prepare() and -execute() is necessary, it
doesn't work with -do().
It won't work with do() because do is for non-result-set generating SQL like
inserts etc and doesn't do the asynchronous stuff . Glad you have it working OK
though.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
