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
Problem to use DBI
Hello, I try to use DBI for the first time with Linux RedHat9 and PERL 5.8.0. I download DBI, DBD from CPAN and I try to connect text file. When I run the script, I receive this Error Message: DBI object version 1.32 does not match $DBI::VERSION 1.38 at /usr/lib/perl5/5.8.0/i386-linux-thread-multi/Dynaloader.pm line 249. I'm not an expert with linux, so could you tell what I need to do use DBI ? Thank for your help. Mark
DBD::ODBC odbc_err_handler troubles
I'am having trouble using the odbc_err_handler callback mechanisme. I really would
like to retrieve informational messages from stored-procedures (as wel as resultsets).
I'am using perl 5.6.1, MSSQL-server 2000, DBI v1.37 and DBD::ODBC v1.06. When there
is more than one informational message, I get an invalid cursor message.
Here's the code i use
use strict;
use DBI;
my $usr = 'sa';
my $pwd = 'blahblah';
my $srv = 'radar01';
my $dsn = driver={SQL Server};Server=$srv;database=ETL;uid=$usr;pwd=$pwd;;
my $dbh = DBI-connect(dbi:ODBC:$dsn,'','', { PrintError = 1 } ) || die Can't
connect: $DBI::errstr\n;
$dbh-{odbc_async_exec} = 1;
$dbh-{odbc_err_handler} = \msg_handler;
my $sth = $dbh-prepare(SQLEND);
print 'this is test 1'
print 'this is test 2'
SQLEND
my $rc = $sth-execute;
$sth-finish;
$dbh-disconnect;
sub msg_handler {
my ($state, $msg, $h) = @_;
print $h\n;
$msg =~ s/^(\[[\w\s]*\])+//;
if ($state ne '01000') {
print $msg\n;
} else {
print $msg\n;
}
return 0;
}
This results in the following output:
this is test 1
De cursorstatus is ongeldig
DBD::ODBC::st execute failed: (DBD: dbd_describe/SQLNumResultCols err=-1) at
D:\temp\perl\web\dbi_tst.pl line 20.
De cursorstatus is ongeldig is dutch for cursor status not valid
any ideas?
thanks in advance
Pieter
[EMAIL PROTECTED]
Re: Problem to use DBI
It would appear that you didn't actually install the newer version of DBI. Possibly just copied the DBI.pm and/or have PERL5LIB/-I/use lib pointing to the newer version. To use the newer version do a cd /place/you/have/dbi/source perl Makefile.PL make make test install I recommend using cd /place/you/have/dbi/source perl Makefile.PL make make test install UNINST=1 On Mon, Sep 15, 2003 at 09:22:37AM +0200, Marc Travaille wrote: Hello, I try to use DBI for the first time with Linux RedHat9 and PERL 5.8.0. I download DBI, DBD from CPAN and I try to connect text file. When I run the script, I receive this Error Message: DBI object version 1.32 does not match $DBI::VERSION 1.38 at /usr/lib/perl5/5.8.0/i386-linux-thread-multi/Dynaloader.pm line 249. I'm not an expert with linux, so could you tell what I need to do use DBI ?
RE: fatal error when make DBD-Oralce-1.14 !
ELF is a style of binary. libclntsh.so is an Oracle binary. Do you have Oracle working on your system? Can you do SQLPlus queries? If so, maybe you are compiling 32 bit mode but have 64 bit OS, or vice versa. Gregory L. Hering 4807 Bradford Drive Huntsville, AL 35805 (256) 722-6420 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, September 12, 2003 3:57 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: fatal error when make DBD-Oralce-1.14 ! Dear Sir, When I compile DBD-Oracle-1.14, something like following messages appeared. I script all steps in README to ask help, could some one help me ? ld: fatal: file /usr/oracle/app/oracle/product/9.2.0/lib//libclntsh.so: wrong ELF class: ELFCLASS64 ld: fatal: File processing errors. No output written to build collect2: ld returned 1 exit status gcc: file path prefix `/usr/ccs/bin/' never used *** Error code 1 make: Fatal error: Command failed for target `blib/arch/auto/DBD/Oracle/Oracle.so' Best Regards, George Hsu Taiwan Elitec Corporation (Affilitate of TCC) 台灣精碩科技(股)公司 許銘祥 (George Hsu) email: [EMAIL PROTECTED] DID: 02-66261229 mobile: 0922-447306 DBD-error.log
Re: Problem to use DBI
On Mon, 15 Sep 2003 10:44:27 -0400 Thomas A. Lowery [EMAIL PROTECTED] wrote: It would appear that you didn't actually install the newer version of DBI. Possibly just copied the DBI.pm and/or have PERL5LIB/-I/use lib pointing to the newer version. To use the newer version do a cd /place/you/have/dbi/source perl Makefile.PL make \ make test install I recommend using cd /place/you/have/dbi/source perl Makefile.PL make \ make test install UNINST=1 On Mon, Sep 15, 2003 at 09:22:37AM +0200, Marc Travaille wrote: I try to use DBI for the first time with Linux RedHat9 and PERL 5.8.0. I download DBI, DBD from CPAN and I try to connect text file. When I run the script, I receive this Error Message: DBI object version 1.32 does not match $DBI::VERSION 1.38 at /usr/lib/perl5/5.8.0/i386-linux-thread-multi/Dynaloader.pm line 249. The RPM version of perl-dbi installs under /usr/lib/perl5/vendor_perl instead of under /usr/lib/perl5/site_perl, so you may have both versions installed at once. Run 'rpm -q perl-dbi' to find what version is installed by rpm and then run 'rpm -e perl-dbi-x.xx-x' to remove it. You may need to install the new version of DBI again using the instructions Thomas gave. You may have to 'su' to root before the 'make install'. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Problem to use DBI
On Mon, 15 Sep 2003 11:05:09 -0700 (PDT) Michael A Chase [EMAIL PROTECTED] wrote: The RPM version of perl-dbi installs under /usr/lib/perl5/vendor_perl instead of under /usr/lib/perl5/site_perl, so you may have both versions installed at once. Run 'rpm -q perl-dbi' to find what version is installed by rpm and then run 'rpm -e perl-dbi-x.xx-x' to remove it. You may need to install the new version of DBI again using the instructions Thomas gave. You may have to 'su' to root before the 'make install'. You may also want to look into cpm2rpm which is available in CPAN. It allows you to build DBI and DBD::xxx (Oracle in my case) RPMs so you can keep track of which modules you have installed using rpm. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
... CURRVAL is not yet defined in this session ...
The following code snip demonstrates a problem I'm having
$self-{hDB}-do( SQL );
SELECT slides_data_sq.nextval FROM Dual
SQL
$self-{hDB}-do( SQL );
SELECT slides_data_sq.currval FROM Dual
SQL
DBD::Oracle::db do failed: ORA-08002: sequence SLIDES_DATA_SQ.CURRVAL is not
yet defined in this session (DBD ERROR: OCIStmtExecute) [for statement ``
SELECT slides_data_sq.currval FROM Dual
'']) at SlideOverall.pm line 1500.
You can see I do the steps back to back, why would the second command fail?
PS Autocommit is off...
Re: ... CURRVAL is not yet defined in this session ...
On Mon, Sep 15, 2003 at 02:45:38PM -0700, Tim Harsch wrote:
The following code snip demonstrates a problem I'm having
$self-{hDB}-do( SQL );
SELECT slides_data_sq.nextval FROM Dual
SQL
$self-{hDB}-do( SQL );
SELECT slides_data_sq.currval FROM Dual
SQL
DBD::Oracle::db do failed: ORA-08002: sequence SLIDES_DATA_SQ.CURRVAL is not
yet defined in this session (DBD ERROR: OCIStmtExecute) [for statement ``
SELECT slides_data_sq.currval FROM Dual
'']) at SlideOverall.pm line 1500.
You can see I do the steps back to back, why would the second command fail?
I believe this fails because you are using do() to execute select
statements. You never fetch the actual sequence number from the first
call.
perldoc DBI:
do
[...]
This method is typically most useful for non-SELECT
statements that either cannot be prepared in advance
(due to a limitation of the driver) or do not need to
be executed repeatedly. It should not be used for
SELECT statements because it does not return a
statement handle (so you can't fetch any data).
Ronald
Re: ... CURRVAL is not yet defined in this session ...
Tim Harsch wrote:
The following code snip demonstrates a problem I'm having
$self-{hDB}-do( SQL );
SELECT slides_data_sq.nextval FROM Dual
SQL
$self-{hDB}-do( SQL );
SELECT slides_data_sq.currval FROM Dual
SQL
DBD::Oracle::db do failed: ORA-08002: sequence SLIDES_DATA_SQ.CURRVAL
is not yet defined in this session (DBD ERROR: OCIStmtExecute) [for
statement `` SELECT slides_data_sq.currval FROM Dual
'']) at SlideOverall.pm line 1500.
You can see I do the steps back to back, why would the second command
fail?
PS Autocommit is off...
$dbh-do doesn't fetch. Without a fetch the sequence isn't accessed.
http://theoryx5.uwinnipeg.ca/CPAN/data/DBI/DBI.html#do
#!perl -w
use strict;
use warnings;
use DBI;
my $dbh = DBI-connect('dbi:Oracle:dev92lnx', 'test', 'test',
{
AutoCommit = 0,
RaiseError = 1,
PrintError = 0,
})
or die DBI::errstr;
my $seqval = $dbh-selectrow_array('SELECT slides_data_sq.nextval FROM
dual');
my $currval = $dbh-selectrow_array('SELECT slides_data_sq.currval FROM
dual');
print seqval: $seqval\n;
print currval: $currval\n;
$dbh-disconnect;
__END__
[EMAIL PROTECTED] /cygdrive/d/temp
$ perl test.pl
seqval: 2
currval: 2
--
Andy Hassall ([EMAIL PROTECTED]) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Getting Started with DBI/DBD and Perl for Oracle on Windows XP
I downloaded Perl last Winter and have used it to access an Oracle 9i database using ADO. I would now like to use it with DBI/DBD to access an Oracle 9i database. I find the CPAN site confusing and can't figure out how to download DBI/DBD. Can someone enlighten me? I would also love to have any example code. My OS is Windows XP Professional. Thanks in advance!
