Re: DBIx::DBH - Perl extension for simplifying database connections
On Tue, Nov 30, 2004 at 10:40:34PM +, Nicholas Clark wrote:
On Tue, Nov 30, 2004 at 10:32:12PM +, Tim Bunce wrote:
On Tue, Nov 30, 2004 at 09:38:47PM +, Nicholas Clark wrote:
On Tue, Nov 30, 2004 at 08:53:51PM +, Tim Bunce wrote:
The one that I've hit - specifying port and host, Pg vs Mysql (and
SQlite):
if ($dbspec-{driver} eq 'DBI:Pg') {
# Aaargh. Why aren't these things standarised?
$dsn = DBI:Pg:host=$dbspec-{domain};
# Aargh. W.T.F. is this case sensitivity here? It fails to connect
unless
# the name is all lowercase (as is stored within the non-case
preserving
# pg DB)
$dsn .= lc ;dbname=$dbspec-{db_name} if length $dbspec-{db_name};
$dsn .= ;port=$dbspec-{port} if defined $dbspec-{port};
} else {
$dsn .= :port=$dbspec-{port} if defined $dbspec-{port};
}
It seems to me that the problem is of your own making.
Why have separate hash elements for all these things in the first place?
Oops. 1 more line would have helped. This precedes my previous code:
my $dsn = $dbspec-{driver}:$dbspec-{db_name}:$dbspec-{domain};
Even if I keep the DSN in a flat string, I have to remember that MySQL
wants driver:DBname:hostname:port=# while Pg wants
driver:dbname:host=name;port=#
and to me needing to remember all this trivia seems wasteful.
It doesn't actually matter if its in a hash or flat string, there's too much
driver specific already even in the simple bits I would like flexibility in.
In general I find that a hash ref is useful as an argument to a function as
it provides named parameters, which avoids needing to remember a list order
for parameters, and also to have to fill in defaults to make the list up to
the position of the parameter you wish to change.
I guess I don't find it natural thinking about parameters as a single string.
Do you generally pass URLs around as a string or broken up into a hash?
Tim.
Re: DBIx::DBH - Perl extension for simplifying database connections
I know what it does, I'm trying to find real examples that demonstrate why people think it's needed. Nick has provided a good one. Any others? Tim. On Tue, Nov 30, 2004 at 06:37:01PM -0800, Terrence Brannon wrote: I don't get it. Can someone give me some small but real examples of the problem that's being solved here? well the ABSTRACT says it all... I regurgitate: quote DBIx::DBH is designed to facilitate and validate the process of creating DBI database connections. It's chief and unique contribution to this set of modules on CPAN is that it forms the DSN string for you, regardless of database vendor. Another thing about this module is that it takes a flat Perl hash as input, making it ideal for converting HTTP form data and or config file information into DBI database handles. It also can form DSN strings for both major free databases and is subclassed to support extension for other databases. DBIx::DBH provides rigorous validation on the input parameters via Params::Validate. It does not allow parameters which are not defined by the DBI or the database vendor driver into the hash /quote -- Terrence Brannon, [EMAIL PROTECTED]
Re: DBIx::DBH - Perl extension for simplifying database connections
Tim Bunce wrote: On Tue, Nov 30, 2004 at 10:40:34PM +, Nicholas Clark wrote: I guess I don't find it natural thinking about parameters as a single string. Do you generally pass URLs around as a string or broken up into a hash? Tim. quote src=http://search.cpan.org/~gaas/libwww-perl-5.802/lib/HTTP/Request/Common.pm POST 'http://www.perl.org/survey.cgi', [ name = 'Gisle Aas', email = '[EMAIL PROTECTED]', gender = 'M', born = '1964', perc = '3%', ]; /quote -- Terrence Brannon, [EMAIL PROTECTED]
fetchrow_array performance
Hi,
I've got a problem with a simple fetch which just takes hours (literaly) to
complete.
It's a simple select statement which, in the current case, returns about 300
rows within seconds.
But the loop is finished a couple of hours later then the last result is
printed.
Here's a code snippet:
my $SQL = select oh_deliverynumber, ol_linereference,
oh.batchrunidentifier, oh.transactiondate
from orderheader oh, orderline ol
where ol_linereference not in (select sc_linereference from
shipconfirm)
and oh_deliverynumber=ol_deliverynumber
and
to_date(oh.transactiondate,'MMDDHH24MISS')+8=sysdate;
my $Statement = $Database-prepare($SQL);
if (defined($Database-errstr)) {
$Rollback=1;
warn $Database-errstr;
}
$Statement-execute();
if (defined($Statement-errstr)) {
$Rollback=1;
warn $Statement-errstr;
}
if (defined($Statement-fetchrow_array)) {
$Rollback=1;
warn $TextOnResult;
my @row;
while ( @row = $Statement-fetchrow_array ) {
warn @row\n;
}
}
So for me it looks like the fetchrow_array function doesn't notice that
there are no more rows to fetch.
But I don't know whether it's my database, the DBD::Oracle module or DBI
itself.
Has anyone experienced the same?
Any other idea?
DBI: 1.45
DBD::Oracle 1.16
Oracle client: 8.1.7.0
Database: 8.1.7.2
Best regards,
Olly
Re: fetchrow_array performance
Sorry for the top post - old version of Groupwise at work :-(
You aren't error checking your dbi statements - do you have RaiseError
set to on (1)? If you don't, check each dbi statement like this:
my $Statement = $Database-prepare($SQL)
or die(Prepare died: $DBI::errstr);
Read about error checking in the perldocs by doing 'perldoc DBI' at a
command prompt.
*** I don't understand your problem. Seem like you are saying that the
script finishes right after it prints the last row - how is that wrong?
How would you expect it to finish any sooner? The fact that it may take
hours to print all the rows selected doesn't matter, does it? Printing
is inherently slow, in comparison to the small amount of time it takes
to loop through all the rows without printing them.
I'm probably just not understanding your problem - I don't understand
why you are surprised that it takes a long time to print all the rows
selected.
Hardy Merrill
Schoenwaelder Oliver [EMAIL PROTECTED] 12/01/04
07:07AM
Hi,
I've got a problem with a simple fetch which just takes hours
(literaly) to
complete.
It's a simple select statement which, in the current case, returns
about 300
rows within seconds.
But the loop is finished a couple of hours later then the last result
is
printed.
Here's a code snippet:
my $SQL = select oh_deliverynumber, ol_linereference,
oh.batchrunidentifier, oh.transactiondate
from orderheader oh, orderline ol
where ol_linereference not in (select sc_linereference
from
shipconfirm)
and oh_deliverynumber=ol_deliverynumber
and
to_date(oh.transactiondate,'MMDDHH24MISS')+8=sysdate;
my $Statement = $Database-prepare($SQL);
if (defined($Database-errstr)) {
$Rollback=1;
warn $Database-errstr;
}
$Statement-execute();
if (defined($Statement-errstr)) {
$Rollback=1;
warn $Statement-errstr;
}
if (defined($Statement-fetchrow_array)) {
$Rollback=1;
warn $TextOnResult;
my @row;
while ( @row = $Statement-fetchrow_array ) {
warn @row\n;
}
}
So for me it looks like the fetchrow_array function doesn't notice
that
there are no more rows to fetch.
But I don't know whether it's my database, the DBD::Oracle module or
DBI
itself.
Has anyone experienced the same?
Any other idea?
DBI: 1.45
DBD::Oracle 1.16
Oracle client: 8.1.7.0
Database: 8.1.7.2
Best regards,
Olly
RE: fetchrow_array performance
I agree - set RasieError. But even more, it is impossible to tell what is
going on in the database without a 10046 trace.
After connecting, issue alter session set events '10046 trace name context
forever, level 8'. This will create a trace file in the directory defined by
'user_dump_dest'. Find the correct file and run tkprof against it. Work with
your DBA on finding the cause of the slowdown from the trace file.
--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-Original Message-
From: Hardy Merrill [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 01, 2004 5:24 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: fetchrow_array performance
Sorry for the top post - old version of Groupwise at work :-(
You aren't error checking your dbi statements - do you have RaiseError
set to on (1)? If you don't, check each dbi statement like this:
my $Statement = $Database-prepare($SQL)
or die(Prepare died: $DBI::errstr);
Read about error checking in the perldocs by doing 'perldoc DBI' at a
command prompt.
*** I don't understand your problem. Seem like you are saying that the
script finishes right after it prints the last row - how is that wrong?
How would you expect it to finish any sooner? The fact that it may take
hours to print all the rows selected doesn't matter, does it? Printing
is inherently slow, in comparison to the small amount of time it takes
to loop through all the rows without printing them.
I'm probably just not understanding your problem - I don't understand
why you are surprised that it takes a long time to print all the rows
selected.
Hardy Merrill
Schoenwaelder Oliver [EMAIL PROTECTED] 12/01/04
07:07AM
Hi,
I've got a problem with a simple fetch which just takes hours
(literaly) to
complete.
It's a simple select statement which, in the current case, returns
about 300
rows within seconds.
But the loop is finished a couple of hours later then the last result
is
printed.
Here's a code snippet:
my $SQL = select oh_deliverynumber, ol_linereference,
oh.batchrunidentifier, oh.transactiondate
from orderheader oh, orderline ol
where ol_linereference not in (select sc_linereference
from
shipconfirm)
and oh_deliverynumber=ol_deliverynumber
and
to_date(oh.transactiondate,'MMDDHH24MISS')+8=sysdate;
my $Statement = $Database-prepare($SQL);
if (defined($Database-errstr)) {
$Rollback=1;
warn $Database-errstr;
}
$Statement-execute();
if (defined($Statement-errstr)) {
$Rollback=1;
warn $Statement-errstr;
}
if (defined($Statement-fetchrow_array)) {
$Rollback=1;
warn $TextOnResult;
my @row;
while ( @row = $Statement-fetchrow_array ) {
warn @row\n;
}
}
So for me it looks like the fetchrow_array function doesn't notice
that
there are no more rows to fetch.
But I don't know whether it's my database, the DBD::Oracle module or
DBI
itself.
Has anyone experienced the same?
Any other idea?
DBI: 1.45
DBD::Oracle 1.16
Oracle client: 8.1.7.0
Database: 8.1.7.2
Best regards,
Olly
This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is
intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.
RE: fetchrow_array performance
Are you sure the query only takes a few seconds and not that it only
takes a few seconds to return the rows that makes your selection
criteria. Not sure how big your database is but for anything other than
trivial data volumes I would say that performance will be very poor.
(obviously that is just an opinion of somebody who has no information
about your DB and what you are trying to achieve!)
How long does the query take if you run it from SQL*Plus (ie. How long
before you get a prompt back). What does the execution plan look like?
Ken.
--
IT Infrastructure Manager
beCogent Ltd
T: +44 1236 628140
E: [EMAIL PROTECTED]
-Original Message-
From: Reidy, Ron [mailto:[EMAIL PROTECTED]
Sent: 01 December 2004 12:52
To: Hardy Merrill; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: fetchrow_array performance
I agree - set RasieError. But even more, it is impossible to tell what
is going on in the database without a 10046 trace.
After connecting, issue alter session set events '10046 trace name
context forever, level 8'. This will create a trace file in the
directory defined by 'user_dump_dest'. Find the correct file and run
tkprof against it. Work with your DBA on finding the cause of the
slowdown from the trace file.
--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-Original Message-
From: Hardy Merrill [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 01, 2004 5:24 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: fetchrow_array performance
Sorry for the top post - old version of Groupwise at work :-(
You aren't error checking your dbi statements - do you have RaiseError
set to on (1)? If you don't, check each dbi statement like this:
my $Statement = $Database-prepare($SQL)
or die(Prepare died: $DBI::errstr);
Read about error checking in the perldocs by doing 'perldoc DBI' at a
command prompt.
*** I don't understand your problem. Seem like you are saying that the
script finishes right after it prints the last row - how is that wrong?
How would you expect it to finish any sooner? The fact that it may take
hours to print all the rows selected doesn't matter, does it? Printing
is inherently slow, in comparison to the small amount of time it takes
to loop through all the rows without printing them.
I'm probably just not understanding your problem - I don't understand
why you are surprised that it takes a long time to print all the rows
selected.
Hardy Merrill
Schoenwaelder Oliver [EMAIL PROTECTED] 12/01/04
07:07AM
Hi,
I've got a problem with a simple fetch which just takes hours
(literaly) to
complete.
It's a simple select statement which, in the current case, returns
about 300
rows within seconds.
But the loop is finished a couple of hours later then the last result
is
printed.
Here's a code snippet:
my $SQL = select oh_deliverynumber, ol_linereference,
oh.batchrunidentifier, oh.transactiondate
from orderheader oh, orderline ol
where ol_linereference not in (select sc_linereference
from
shipconfirm)
and oh_deliverynumber=ol_deliverynumber
and
to_date(oh.transactiondate,'MMDDHH24MISS')+8=sysdate;
my $Statement = $Database-prepare($SQL);
if (defined($Database-errstr)) {
$Rollback=1;
warn $Database-errstr;
}
$Statement-execute();
if (defined($Statement-errstr)) {
$Rollback=1;
warn $Statement-errstr;
}
if (defined($Statement-fetchrow_array)) {
$Rollback=1;
warn $TextOnResult;
my @row;
while ( @row = $Statement-fetchrow_array ) {
warn @row\n;
}
}
So for me it looks like the fetchrow_array function doesn't notice
that
there are no more rows to fetch.
But I don't know whether it's my database, the DBD::Oracle module or
DBI
itself.
Has anyone experienced the same?
Any other idea?
DBI: 1.45
DBD::Oracle 1.16
Oracle client: 8.1.7.0
Database: 8.1.7.2
Best regards,
Olly
This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information is
intended
to be for the use of the individual or entity named above. If you are
not the
intended recipient, please be aware that any disclosure, copying,
distribution
or use of the contents of this information is prohibited. Please notify
the
sender of the delivery error by replying to this message, or notify us
by
telephone (877-633-2436, ext. 0), and then delete it from your system.
Any opinions expressed in this E-mail may be those of the individual and not
necessarily the company. This E-mail and any files transmitted with it are
confidential and solely for the use of the intended recipient. If you are not
the intended recipient or the person responsible for delivering to the intended
recipient, be
Errors Installing DBD::Oracle on Windows
I am having diffuculty getting past the nmake (make) step for installing the
DBD::Oracle perl module on Windows.
The perl Makefile.PL step completes, but with warnings of missing libraries
(see below).
It appears I am missing key .lib's, but I can't find them anywhere, execpt that
they seem to be on the Visual C++ CD.
Can DBD::Oracle be installed on Windows without the Visual C++ (using Watcom cl
or Cygwin gcc)?
Thanks,
David
-
C:\dfw\DBD-Oracle-1.16perl Makefile.PL -v
*** You're using Microsoft Visual C++ compiler but the LIB and INCLUDE environme
nt
variables are not both set. If you have 'unresolved external symbol'
errors during linking, run the vcvars32.bat batch file to set up your
LIB and INCLUDE environment variables, and try again.
A copy of vcvars32.bat can typically be found in the following
directories under your Visual Studio install directory:
Visual C++ 6.0: vc98\bin
Visual Studio .NET: vc7\bin
Using DBI 1.46 (for perl 5.008003 on MSWin32-x86-multi-thread) installed in C:/P
erl/site/lib/auto/DBI/
Configuring DBD::Oracle ...
Remember to actually *READ* the README file!
Especially if you have any problems.
Using Oracle in C:/oracle/ora92
DEFINE _SQLPLUS_RELEASE = 902000100 (CHAR)
Oracle version 9.2.0.1 (9.2)
Found oci directory
Using OCI directory 'oci'
Found oci/lib/MSVC/oci.lib library
Found oci/lib/MSVC/ociw32.lib library
Found oci/lib/MSVC/oramts.lib library
Found oci/lib/MSVC/oraocci9.lib library
Using oci/lib/MSVC/OCI.lib
Checking for functioning wait.ph
System: perl5.008003
Compiler: cl -MD -Zi -DNDEBUG -O1 -nologo -Gf -W3 -MD -Zi -DNDEBUG -O1 -DWIN32
-D_CONSOLE -DNO_STRICT -DHAVE_DES_FCRYPT -DNO_HASH_SEED -DPERL_IMPLICIT_CONTEX
T -DPERL_IMPLICIT_SYS -DUSE_PERLIO -DPERL_MSVCRT_READFIX
Linker: not found
Sysliblist:
MakeMaker (v6.17)
Checking if your kit is complete...
Looks good
ABSTRACT_FROM = q[Oracle.pm]
AUTHOR = q[Tim Bunce ([EMAIL PROTECTED])]
DEFINE = q[ -DUTF8_SUPPORT -DNEW_OCI_INIT -DORA_OCI_VERSION=\9.2.0.1\]
DIR = []
EXE_FILES = [q[ora_explain]]
INC = q[-IC:/oracle/ora92/oci/include -IC:/oracle/ora92/rdbms/demo -IC:/Perl/
site/lib/auto/DBI/]
LIBS = [q[-LC:/oracle/ora92/oci/LIB/MSVC OCI]]
NAME = q[DBD::Oracle]
OBJECT = q[$(O_FILES)]
PREREQ_PM = { DBI=q[0] }
VERSION_FROM = q[Oracle.pm]
clean = { FILES=q[xstmp.c Oracle.xsi dll.base dll.exp sqlnet.log libOracle.d
ef ora_explain mk.pm] }
dist = { DIST_DEFAULT=q[clean distcheck disttest tardist], COMPRESS=q[gzip
-v9], PREOP=q[$(MAKE) -f Makefile.old distdir], SUFFIX=q[gz] }
Using PERL=C:\Perl\bin\perl.exe
Potential libraries are '-LC:/oracle/ora92/oci/LIB/MSVC OCI oldnames.lib kerne
l32.lib user32.lib gdi32.lib winspool.lib comdlg32.lib advapi32.lib shell32.lib
ole32.lib oleaut32.lib netapi32.lib uuid.lib wsock32.lib mpr.lib winmm.lib ve
rsion.lib odbc32.lib odbccp32.lib msvcrt.lib':
'OCI' found as 'C:/oracle/ora92/oci/LIB/MSVC\OCI.lib'
'oldnames.lib' not found as 'C:/oracle/ora92/oci/LIB/MSVC\oldnames.lib'
'oldnames.lib' not found as 'C:/PROGRA~1/MICROS~3/VC98/lib\oldnames.lib'
'oldnames.lib' not found as 'C:\Perl\lib/CORE\oldnames.lib'
Note (probably harmless): No library found for oldnames.lib
'kernel32.lib' not found as 'C:/oracle/ora92/oci/LIB/MSVC\kernel32.lib'
'kernel32.lib' not found as 'C:/PROGRA~1/MICROS~3/VC98/lib\kernel32.lib'
'kernel32.lib' not found as 'C:\Perl\lib/CORE\kernel32.lib'
Note (probably harmless): No library found for kernel32.lib
'user32.lib' not found as 'C:/oracle/ora92/oci/LIB/MSVC\user32.lib'
'user32.lib' not found as 'C:/PROGRA~1/MICROS~3/VC98/lib\user32.lib'
'user32.lib' not found as 'C:\Perl\lib/CORE\user32.lib'
Note (probably harmless): No library found for user32.lib
'gdi32.lib' not found as 'C:/oracle/ora92/oci/LIB/MSVC\gdi32.lib'
'gdi32.lib' not found as 'C:/PROGRA~1/MICROS~3/VC98/lib\gdi32.lib'
'gdi32.lib' not found as 'C:\Perl\lib/CORE\gdi32.lib'
Note (probably harmless): No library found for gdi32.lib
'winspool.lib' not found as 'C:/oracle/ora92/oci/LIB/MSVC\winspool.lib'
'winspool.lib' not found as 'C:/PROGRA~1/MICROS~3/VC98/lib\winspool.lib'
'winspool.lib' not found as 'C:\Perl\lib/CORE\winspool.lib'
Note (probably harmless): No library found for winspool.lib
'comdlg32.lib' not found as 'C:/oracle/ora92/oci/LIB/MSVC\comdlg32.lib'
'comdlg32.lib' not found as 'C:/PROGRA~1/MICROS~3/VC98/lib\comdlg32.lib'
'comdlg32.lib' not found as 'C:\Perl\lib/CORE\comdlg32.lib'
Note (probably harmless): No library found for comdlg32.lib
'advapi32.lib' not found as 'C:/oracle/ora92/oci/LIB/MSVC\advapi32.lib'
'advapi32.lib' not found as 'C:/PROGRA~1/MICROS~3/VC98/lib\advapi32.lib'
'advapi32.lib' not found as 'C:\Perl\lib/CORE\advapi32.lib'
Note (probably harmless): No library found for advapi32.lib
'shell32.lib' not found as 'C:/oracle/ora92/oci/LIB/MSVC\shell32.lib'
Re: DBIx::DBH - Perl extension for simplifying database connections
instead of having to haul around the code to figure this out, why not create a handy documentary web service somewhere where you fill out the blanks and get an appropriate connection string? Loading a module every time you start the program just to create something that is a permanent per-installation configuration constant strikes me has if not ugly, at least hideously post-modern. I'll even donate a spot of server space to the cause if you don't have it already. On Tue, 30 Nov 2004 15:58:48 -0500, Mark Stosberg [EMAIL PROTECTED] wrote: On Tue, Nov 30, 2004 at 08:53:51PM +, Tim Bunce wrote: I don't get it. Can someone give me some small but real examples of the problem that's being solved here? To give the database name with DBD::Pg, you use: dbname=$dbname With mysql, it's different database=$database With this ConnectFoo module, it could be the same: dbname = $dbname, Mark -- David L Nicol Happy hacking! http://www.amazon.com/exec/obidos/ASIN/0596002874/tipjartransactioA/
Re: DBIx::DBH - Perl extension for simplifying database connections
On Tue, Nov 30, 2004 at 11:02:31PM -0600, David Nicol wrote: instead of having to haul around the code to figure this out, why not create a handy documentary web service somewhere where you fill out the blanks and get an appropriate connection string? Loading a module every time you start the program just to create something that is a permanent per-installation configuration constant strikes me has if not ugly, at least hideously post-modern. I'll even donate a spot of server space to the cause if you don't have it already. It's a kind offer, but does your server reach me when I'm working offline? Doubtful. But I have a local CPAN mirror, and run local database servers for development. Nicholas Clark
RE: DBIx::DBH - Perl extension for simplifying database connectio ns
Tim Bunce wrote on 30 November 2004 23:32
On Tue, Nov 30, 2004 at 09:38:47PM +, Nicholas Clark wrote:
On Tue, Nov 30, 2004 at 08:53:51PM +, Tim Bunce wrote:
I don't get it. Can someone give me some small but real examples
of the problem that's being solved here?
The one that I've hit - specifying port and host, Pg vs
Mysql (and SQlite):
if ($dbspec-{driver} eq 'DBI:Pg') {
# Aaargh. Why aren't these things standarised?
$dsn = DBI:Pg:host=$dbspec-{domain};
# Aargh. W.T.F. is this case sensitivity here? It fails
to connect unless
# the name is all lowercase (as is stored within the
non-case preserving
# pg DB)
$dsn .= lc ;dbname=$dbspec-{db_name} if length
$dbspec-{db_name};
$dsn .= ;port=$dbspec-{port} if defined $dbspec-{port};
} else {
$dsn .= :port=$dbspec-{port} if defined $dbspec-{port};
}
It seems to me that the problem is of your own making.
Why have separate hash elements for all these things in the
first place?
In my case because if I connect to the same database from two different
servers with two different versions of Sybase Open Client then DBD::Sybase
and the underlying drivers need connection strings that are totally
different (and not at all intuitive IMO) but contain basically the same
data. Thus I have an ini file that contains the appropriate info along with
a special entry that determines which style to use. Now only one thing needs
to be changed between the two machines.
This is part of the code that handles building the connection string from
the ini file:
if (lc($style) eq short) {
$fmt=DRIVER={%s};UID=%s;PWD=%s;SRVR=%s;DB=%s;
@args=qw(driver username password server dbname);
} elsif (lc($style) eq long) {
$fmt=DRIVER={%s};UID=%s;PWD=%s;NLN=%s;NA=%s;DB=%s;
@args=qw(driver username password protocol server dbname);
And here is what the inifile looks like:
[dsn]
; this is the setting for GOBS01 PRODUCTION
style= short
driver = Sybase ASE ODBC Driver
username = him_or_me
password = uhuh
server = that_server_there
dbname = funky_db
protocol = blahblah
Now if we used two connection strings we have all of that data duplicated in
the two strings, which as we all know is a scenario just crying out for
refactoring. We in fact did do this for some time, but it was always falling
out of synch when the data needed to change and the ops folks were forever
changing the wrong string.
Ive got other examples of this. If you use DBD::ODBC you need an entirely
different connection string than when using the faster and better
DBD::Sybase which has as I already mentioned different requirements
depending on local features. Since we had some weird compatibilitiy issue
with Sybase Open Client for a while in some places our code had to fallback
to using DBD::ODBC when DBD::Sybase diver was unavailable, resulting in even
more connection string variants.
IMO the idea of this module is great and frankly resolves something that ive
heard many a folk (here and elsewhere) bitch about being one of DBI's few
annoyances. (That and $dbh-selectall_arrayref($query,{Slice={}}) ;-)
I will say that I agree entirely with those who argue that DBIx::DBH is a
bad name. IMO DBI itself should have a more transparent way of handling this
so an additional module is not required. Surely there is base information
that pretty well all serious DB's will require to open a connection so
mandating a driver agnostic interface to providing those details and then
letting the driver do the rest would seem to make sense.
Yves
RE: DBIx::DBH - Perl extension for simplifying database connectio ns
Orton, Yves stupidly wrote on 01 December 2004 12:54 Tim Bunce wrote on 30 November 2004 23:32 On Tue, Nov 30, 2004 at 09:38:47PM +, Nicholas Clark wrote: On Tue, Nov 30, 2004 at 08:53:51PM +, Tim Bunce wrote: I don't get it. Can someone give me some small but real examples of the problem that's being solved here? snip And here is what the inifile looks like: [dsn] style= short driver = Sybase ASE ODBC Driver username = him_or_me password = uhuh server = that_server_there dbname = funky_db protocol = blahblah This particular example shows the scenario of the ODBC drivers requiring different strings based on the Open Client version installed. As I mentioned we have encountered this problem with DBD::Sybase as well, and in combination with fallbacks to ODBC. I grabbed the wrong example code, but I think the fact that this confusion was even possible just underlines the point here. :-) Yves
Re: DBIx::DBH - Perl extension for simplifying database connections
On Wed, Dec 01, 2004 at 02:02:57AM -0800, Terrence Brannon wrote: Tim Bunce wrote: On Tue, Nov 30, 2004 at 10:40:34PM +, Nicholas Clark wrote: I guess I don't find it natural thinking about parameters as a single string. Do you generally pass URLs around as a string or broken up into a hash? quote src=http://search.cpan.org/~gaas/libwww-perl-5.802/lib/HTTP/Request/Common.pm POST 'http://www.perl.org/survey.cgi', [ name = 'Gisle Aas', email = '[EMAIL PROTECTED]', gender = 'M', born = '1964', perc = '3%', ]; /quote I didn't say *can* you do that, I asked if he/you/we *generally* did that. Tim.
RE: fetchrow_array performance
Thx for all the answers.
Well, you're right. I check for error against DB handle, not against
statement.
But that's just a typo. The statement itself works.
The problem is within the loop:
while ( @row = $Statement-fetchrow_array ) {
warn @row\n;
}
If I rewrite this to
print Here we start...\n;
while ( @row = $Statement-fetchrow_array ) {
warn @row\n;
}
print ...and here it ends\n;
you'll see the 300+ @row lines of the query in a few seconds printed, but
...and here it ends is printed a couple of hours (!) later.
To get the results it's the same response time as from being executed via
sqlplus.
I'll trace it and see what I'll get.
Thx so far,
Olly
-Original Message-
From: Gaul, Ken [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 01, 2004 2:08 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: fetchrow_array performance
Are you sure the query only takes a few seconds and not that it only
takes a few seconds to return the rows that makes your selection
criteria. Not sure how big your database is but for anything
other than
trivial data volumes I would say that performance will be very poor.
(obviously that is just an opinion of somebody who has no information
about your DB and what you are trying to achieve!)
How long does the query take if you run it from SQL*Plus (ie. How long
before you get a prompt back). What does the execution plan look like?
Ken.
--
IT Infrastructure Manager
beCogent Ltd
T: +44 1236 628140
E: [EMAIL PROTECTED]
-Original Message-
From: Reidy, Ron [mailto:[EMAIL PROTECTED]
Sent: 01 December 2004 12:52
To: Hardy Merrill; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: fetchrow_array performance
I agree - set RasieError. But even more, it is impossible to
tell what
is going on in the database without a 10046 trace.
After connecting, issue alter session set events '10046 trace name
context forever, level 8'. This will create a trace file in the
directory defined by 'user_dump_dest'. Find the correct file and run
tkprof against it. Work with your DBA on finding the cause of the
slowdown from the trace file.
--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-Original Message-
From: Hardy Merrill [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 01, 2004 5:24 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: fetchrow_array performance
Sorry for the top post - old version of Groupwise at work :-(
You aren't error checking your dbi statements - do you have RaiseError
set to on (1)? If you don't, check each dbi statement like this:
my $Statement = $Database-prepare($SQL)
or die(Prepare died: $DBI::errstr);
Read about error checking in the perldocs by doing 'perldoc DBI' at a
command prompt.
*** I don't understand your problem. Seem like you are
saying that the
script finishes right after it prints the last row - how is
that wrong?
How would you expect it to finish any sooner? The fact that
it may take
hours to print all the rows selected doesn't matter, does it?
Printing
is inherently slow, in comparison to the small amount of time it takes
to loop through all the rows without printing them.
I'm probably just not understanding your problem - I don't understand
why you are surprised that it takes a long time to print all the rows
selected.
Hardy Merrill
Schoenwaelder Oliver [EMAIL PROTECTED] 12/01/04
07:07AM
Hi,
I've got a problem with a simple fetch which just takes hours
(literaly) to
complete.
It's a simple select statement which, in the current case, returns
about 300
rows within seconds.
But the loop is finished a couple of hours later then the last result
is
printed.
Here's a code snippet:
my $SQL = select oh_deliverynumber, ol_linereference,
oh.batchrunidentifier, oh.transactiondate
from orderheader oh, orderline ol
where ol_linereference not in (select
sc_linereference
from
shipconfirm)
and oh_deliverynumber=ol_deliverynumber
and
to_date(oh.transactiondate,'MMDDHH24MISS')+8=sysdate;
my $Statement = $Database-prepare($SQL);
if (defined($Database-errstr)) {
$Rollback=1;
warn $Database-errstr;
}
$Statement-execute();
if (defined($Statement-errstr)) {
$Rollback=1;
warn $Statement-errstr;
}
if (defined($Statement-fetchrow_array)) {
$Rollback=1;
warn $TextOnResult;
my @row;
while ( @row = $Statement-fetchrow_array ) {
warn @row\n;
}
}
So for me it looks like the fetchrow_array function doesn't notice
that
there are no more rows to fetch.
FAQ is broken
The published link at [EMAIL PROTECTED] is not working. I thought someone should mention it. - Gordon Corzine Email: [EMAIL PROTECTED] Salem State College
DBI script help
Greeting, all,
I have a script which converts query output into Excel spread sheet which is
working fine, I am a UNIX scripting guy and I am having difficulty making some
simple changes to the script, hope I will get some help.
I want to send a email from the script only if it fetches any data excluding
header using
uuencode excel_dump_1.xls excel_dump_1.xls |mailx -s blah blah.. [EMAIL
PROTECTED] which will send the excel spread sheet as an attachment.
Thanks in advance
#!/usr/bin/perl
use warnings;
use FileHandle;
use DBI;
use strict;
use Spreadsheet::WriteExcel;
#use Spreadsheet::WriteExcel::Big;
use Getopt::Long;
use constant LINES_PER_BOOK = 60001;
my $dbh1 = DBI-connect(dbi:Oracle:db1, user1, user1 ) or die Can't make
1st database connect: $DBI::errstr\n;
$dbh1-{RowCacheSize} = 100;
my $sql=q{
select *
from table1
where status!='Active'
};
print Preparing SQL\n;
my $sth = $dbh1-prepare($sql);
print Executing SQL\n;
$sth-execute();
print Creating Workbook\n;
my $workbook = Spreadsheet::WriteExcel-new(newWorkBookName());
die unable to create workbook - $!\n unless $workbook;
$workbook-set_tempdir('/home/user1/LC');
my $worksheet = $workbook-addworksheet();
my $colNames = $sth-{NAME_uc};
print Fetching data\n;
my $rowCount=0;
my $lineCount=0;
$worksheet-write_row($lineCount,0,$colNames);
print \n;
while( my $ary = $sth-fetchrow_arrayref ) {
print $rowCount;
print . unless $rowCount++%1000;
if ( ++$lineCount = LINES_PER_BOOK ) {
$workbook-close;
my $workBookName = newWorkBookName();
$workbook =
Spreadsheet::WriteExcel-new($workBookName);
die unable to create workbook - $!\n unless $workbook;
$worksheet = $workbook-addworksheet();
$lineCount=0;
$worksheet-write_row($lineCount,0,$colNames);
$lineCount=1;
print \nNew Workbook: $workBookName\n;
}
$worksheet-write_row($lineCount,0,$ary);
}
print \n;
$workbook-close;
$sth-finish;
$dbh1-disconnect;
{
my $workBookNumber = 0;
sub newWorkBookName {
return /home/user1/LC/excel_dump_ . ++$workBookNumber . .xls;
}
}
-
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
Re: DBI script help
Hello Robert:
You could use:
MIME::QuotedPrint;
MIME::Base64;
Mail::Sendmail
There are some examples on the internet showing you
how to attach using sendmail.
regards,
David
--- Robert [EMAIL PROTECTED] wrote:
Greeting, all,
I have a script which converts query output into
Excel spread sheet which is working fine, I am a
UNIX scripting guy and I am having difficulty making
some simple changes to the script, hope I will get
some help.
I want to send a email from the script only if it
fetches any data excluding header using
uuencode excel_dump_1.xls excel_dump_1.xls |mailx -s
blah blah.. [EMAIL PROTECTED] which will send the
excel spread sheet as an attachment.
Thanks in advance
#!/usr/bin/perl
use warnings;
use FileHandle;
use DBI;
use strict;
use Spreadsheet::WriteExcel;
#use Spreadsheet::WriteExcel::Big;
use Getopt::Long;
use constant LINES_PER_BOOK = 60001;
my $dbh1 = DBI-connect(dbi:Oracle:db1, user1,
user1 ) or die Can't make 1st database connect:
$DBI::errstr\n;
$dbh1-{RowCacheSize} = 100;
my $sql=q{
select *
from table1
where status!='Active'
};
print Preparing SQL\n;
my $sth = $dbh1-prepare($sql);
print Executing SQL\n;
$sth-execute();
print Creating Workbook\n;
my $workbook =
Spreadsheet::WriteExcel-new(newWorkBookName());
die unable to create workbook - $!\n unless
$workbook;
$workbook-set_tempdir('/home/user1/LC');
my $worksheet = $workbook-addworksheet();
my $colNames = $sth-{NAME_uc};
print Fetching data\n;
my $rowCount=0;
my $lineCount=0;
$worksheet-write_row($lineCount,0,$colNames);
print \n;
while( my $ary = $sth-fetchrow_arrayref ) {
print $rowCount;
print . unless $rowCount++%1000;
if ( ++$lineCount = LINES_PER_BOOK ) {
$workbook-close;
my $workBookName = newWorkBookName();
$workbook =
Spreadsheet::WriteExcel-new($workBookName);
die unable to create workbook - $!\n unless
$workbook;
$worksheet = $workbook-addworksheet();
$lineCount=0;
$worksheet-write_row($lineCount,0,$colNames);
$lineCount=1;
print \nNew Workbook: $workBookName\n;
}
$worksheet-write_row($lineCount,0,$ary);
}
print \n;
$workbook-close;
$sth-finish;
$dbh1-disconnect;
{
my $workBookNumber = 0;
sub newWorkBookName {
return /home/user1/LC/excel_dump_ .
++$workBookNumber . .xls;
}
}
-
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail
SpamGuard.
Re: DBIx::DBH - Perl extension for simplifying database connections
On Wed, Dec 01, 2004 at 10:09:55AM -0500, Ricardo SIGNES wrote: * Tim Bunce [EMAIL PROTECTED] [2004-12-01T04:48:40] I know what it does, I'm trying to find real examples that demonstrate why people think it's needed. Nick has provided a good one. Any others? I have a very similar set of uses to Nick's. Our model classes at work can connect to SQLite, MSSQL (ODBC) or MSSQL (ADO). We store config data as something like: db_driver: ODBC db_user: kibo db_pass: grep db_name: users It DTRT with regard to building a DSN string that contains databasename=users or Initial Catalog=users or dbname=users.db and so on. It's not the hardest problem in the world, but it's more lines to maintain. Why not store something like this?: db_user: kibo db_pass: grep db_dsn: dbi:...:... Tim.
Re: DBIx::DBH - Perl extension for simplifying database connections
At 10:39 AM 12/1/2004, you wrote:
On Wed, Dec 01, 2004 at 09:56:01AM -0500, John Siracusa wrote:
On Wed, 1 Dec 2004 09:46:24 +, Tim Bunce [EMAIL PROTECTED] wrote:
Do you generally pass URLs around as a string or broken up into a hash?
If they had different formats for different consumers, I would. (And even
today, I use my own URI objects when I know I'll have to do any significant
amount of manipulation.)
I think this module is definitely useful. I already store my DSNs in
hashes
and assemble the pieces as necessary depending on the driver.
Lots of people do, it seems, but I'm not getting much background about why.
FWIW, the reason I'm digging here is because I agree there may be
some value in the DBI supporting something along these lines, but
I need a better understanding of the underlying issues. More real-
world examples would help.
It'll always come down to the issue of why not store complete DSNs?
and so far that's not been well covered by the feedback I've got.
Hi,
In our case we have several databases in a replication line using mysql. In
some cases we might want to connect to a server other than the first in
line, but most often we connect to the first in line/master. I also wanted
to make sure that if the script happened to be running on the master local
host that it would not connect using the host name and end up using TCP on
localhost.
Does that make any sense? I am not sure now that I think about it why I
could not just store the DSN for everything, but only separate out the
hostname. But then that is all I am doing anyway.
+-+---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+-+---+--+-+-+---+
| dbname | varchar(40) | | PRI | | |
| dborder | tinyint(2)| YES | | NULL| |
| ip | varchar(15) | | MUL | 0 | |
| servername | varchar(100) | | PRI | | |
| username| varchar(20) | | | | |
| password| varchar(20) | | | | |
| raise_error | enum('1','0') | | | 1 | |
| autocommit | enum('1','0') | | | 1 | |
+-+---+--+-+-+---+
I don't use the IP right now, but record it anyway. The server name is
important because I use the localhost name to match up with that, and if it
matches then don't add in the hostname to the connect string.
I just looked at DBIx::DBH and I have to say I don't get it. Why is that
any easier than the default?
I thought at first this thread was talking about managing connections..
Thanks,
Eric
Tim.
Re: DBIx::DBH - Perl extension for simplifying database connections
On Dec 1, 2004, at 10:39 AM, Tim Bunce wrote:
On Wed, Dec 01, 2004 at 09:56:01AM -0500, John Siracusa wrote:
On Wed, 1 Dec 2004 09:46:24 +, Tim Bunce [EMAIL PROTECTED]
wrote:
Do you generally pass URLs around as a string or broken up into a
hash?
If they had different formats for different consumers, I would. (And
even
today, I use my own URI objects when I know I'll have to do any
significant
amount of manipulation.)
I think this module is definitely useful. I already store my DSNs in
hashes
and assemble the pieces as necessary depending on the driver.
Lots of people do, it seems, but I'm not getting much background about
why.
FWIW, the reason I'm digging here is because I agree there may be
some value in the DBI supporting something along these lines, but
I need a better understanding of the underlying issues. More real-
world examples would help.
It'll always come down to the issue of why not store complete DSNs?
and so far that's not been well covered by the feedback I've got.
Tim.
I have to agree with Tim. In my DBIx::HA module that manages
high-availability and has multiple data sources per database handle,
here's how the config looks like:
$DATABASE::conf{'my_db'} = {
max_retries = 2,
db_stack = [ #format: dsn, username, password
[ 'dbi:Sybase:server=;database=A', 'user',
'pass' ]
[ 'dbi:Oracle:server=;database=B', 'u1', 'p1' ]
[ 'dbi:Pg:host=aaa;port=1212', 'u2', 'p2' ]
]
connectoninit = 0,
pingtimeout = -1,
connecttimeout = 3,
executetimeout = 20,
callback_function = \Travolta::callback_HA,
failoverlevel = 'application',
};
The DSNs are so different, and allow for so much choice, that it's just
as easy to fully qualify them.
For example, the Sybase DSN can take hostname=, scriptname=, etc...
H
Re: DBIx::DBH - Perl extension for simplifying database connections
On Thu, Dec 02, 2004 at 08:34:20AM +1100, Ron Savage wrote: On Wed, 1 Dec 2004 18:39:00 +, Tim Bunce wrote: H Tim need a better understanding of the underlying issues. More real- world examples would help. Here's a real world example of how I do things, although I am in no way convinced that such a module as we are discussing is needed... Presumably because, like me, you tend to use entire DSN strings rather than trying to construct them from parts: Class - new(site = 'local') to Class - new(site = 'remote'), and use that to index into a hash to get the connexion parameters. local = ['dbi:mysql:db_name1:127.0.0.1', 'username1', 'password1'], remote = ['dbi:mysql:db_name2:dbhost.quadrahosting.com.au', 'username2', 'password2'], Tim.
Re: DBIx::DBH - Perl extension for simplifying database connections
On Wed, Dec 01, 2004 at 01:45:50PM -0500, John Siracusa wrote: On Wed, 1 Dec 2004 18:39:00 +, Tim Bunce [EMAIL PROTECTED] wrote: FWIW, the reason I'm digging here is because I agree there may be some value in the DBI supporting something along these lines, but I need a better understanding of the underlying issues. More real- world examples would help. It'll always come down to the issue of why not store complete DSNs? and so far that's not been well covered by the feedback I've got. I like to be able to get at the pieces in isolation. Sometimes I want to know the driver name, for example, without having to actually connect. FYI see DBI-parse_dsn(...) I also like to be able to switch the database type by just changing the driver name. Looking up the DSN format is annoying. So I do it once and put it in a module. Then I just deal with the pieces without caring how they have to be assembled for a particular database. Heck, I no longer even know all the various DSN formats for the database types I deal with...and I like it that way :) Okay. Thanks. Tim.
Re: Apparent DBD::Oracle 1.16 unicode regression
On Wed, 2004-12-01 at 12:18 -0500, Jesse Vincent wrote: The bug I'm describing isn't something I've managed to duplicate in-house. Everyone who's hitting it is running perl 5.8.3 or newer (Some are running 5.8.5). They're all running against Oracle 9.2.0.x. So, you are allowing them to make a direct oracle connection? Assuming you are, then, can you ask one of those complaining to at least provide you with the output requested? I suspect an oracle client version mismatch with the server. If the build make tests run clean (against YOUR database), and their environment does not match the exceptions skipped, then it is most likely be an environment issue for their application. Lincoln
Re: Apparent DBD::Oracle 1.16 unicode regression
On Wed, 2004-12-01 at 19:01 -0500, Jesse Vincent wrote: On Wed, Dec 01, 2004 at 06:59:18PM -0500, Lincoln A. Baxter wrote: On Wed, 2004-12-01 at 12:18 -0500, Jesse Vincent wrote: The bug I'm describing isn't something I've managed to duplicate in-house. Have you tried? Everyone who's hitting it is running perl 5.8.3 or newer (Some are running 5.8.5). They're all running against Oracle 9.2.0.x. So, you are allowing them to make a direct oracle connection? Assuming you are, then, can you ask one of those complaining to at least provide you with the output requested? I suspect an oracle client version mismatch with the server. Is there a change in 1.16 that would cause this to become an issue, since backing down to 1.15 makes the issue vanish. And it appears that _everyone_ I have on Oracle hits the issue when going up to 1.16. Well, you can always diff the files... it is open source after all. The obvious answer is yes, HUGE changes. It's documented in the changes file. Oracle Unicode support was non-trivial to add. Things were significantly reworked, and there were 7 (or was it 8?) release candidates before 1.16 was officially released. The LOB code was more problematic than the non-LOB code. And it may not have been as throughly tested as some of the other code, although the long test is pretty detailed. And it is working fine at my employer, where we have it running in production. It works with both the 9.2 client to an 8.1.7 database, and from a 9.2 client to a 9.2 database. Is is possible that some combinations of conditions that don't work were missed in all the testing that was done? You bet. We found a bunch of combinations that we need to except. mostly having to do with OLDER oracle client software or OLDER versions of oracle on the database side. The later is apparently not the problem for you, but I would think that you could ask at least one of your customers for the information I asked you for. BTW: Have them use YOUR database (in ORACLE_USERID) while installing 1.16. Since you say this breaks _every_ one of your customers, however, I find myself wondering if you have something mis-configured, or at least pretty unique in your database or environment. I assume your customers told you DBD-Oracle-1.16 installed cleanly? Lincoln
Re: DBIx::DBH - Perl extension for simplifying database connections
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 This seems to be a solution in search of a problem. With the exception of perhaps port, host, and database, the dsn arguments are pretty DBMS-specific, meaning that they cannot really be mapped from one DBD to another, nor can any common rules be devised. It also seems like an awful lot of overhead to make a module where a couple of lines in the script will suffice. And if you find that you end up making changes in many spots, then you should probably refactor your scripts. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200412011017 -BEGIN PGP SIGNATURE- iD8DBQFBreFbvJuQZxSWSsgRAlRJAJ42f13jjfnKIJmGIXSprl/z+D5QYACeOOmx 4pa9XkRCDqYPxC6kJ95vqHE= =mDCA -END PGP SIGNATURE-
Re: DBI script help
Try this.
Add these lines to end of script.
if ( $rowCount 0 ) {
system(uuencode excel_dump_1.xls excel_dump_1.xls
|mailx -s 'blah blah..' [EMAIL PROTECTED]);
}
or Good thing is use perl module Mail::Sender
--- Robert [EMAIL PROTECTED] wrote:
Greeting, all,
I have a script which converts query output into
Excel spread sheet which is working fine, I am a
UNIX scripting guy and I am having difficulty making
some simple changes to the script, hope I will get
some help.
I want to send a email from the script only if it
fetches any data excluding header using
uuencode excel_dump_1.xls excel_dump_1.xls |mailx -s
blah blah.. [EMAIL PROTECTED] which will send the
excel spread sheet as an attachment.
Thanks in advance
#!/usr/bin/perl
use warnings;
use FileHandle;
use DBI;
use strict;
use Spreadsheet::WriteExcel;
#use Spreadsheet::WriteExcel::Big;
use Getopt::Long;
use constant LINES_PER_BOOK = 60001;
my $dbh1 = DBI-connect(dbi:Oracle:db1, user1,
user1 ) or die Can't make 1st database connect:
$DBI::errstr\n;
$dbh1-{RowCacheSize} = 100;
my $sql=q{
select *
from table1
where status!='Active'
};
print Preparing SQL\n;
my $sth = $dbh1-prepare($sql);
print Executing SQL\n;
$sth-execute();
print Creating Workbook\n;
my $workbook =
Spreadsheet::WriteExcel-new(newWorkBookName());
die unable to create workbook - $!\n unless
$workbook;
$workbook-set_tempdir('/home/user1/LC');
my $worksheet = $workbook-addworksheet();
my $colNames = $sth-{NAME_uc};
print Fetching data\n;
my $rowCount=0;
my $lineCount=0;
$worksheet-write_row($lineCount,0,$colNames);
print \n;
while( my $ary = $sth-fetchrow_arrayref ) {
print $rowCount;
print . unless $rowCount++%1000;
if ( ++$lineCount = LINES_PER_BOOK ) {
$workbook-close;
my $workBookName = newWorkBookName();
$workbook =
Spreadsheet::WriteExcel-new($workBookName);
die unable to create workbook - $!\n unless
$workbook;
$worksheet = $workbook-addworksheet();
$lineCount=0;
$worksheet-write_row($lineCount,0,$colNames);
$lineCount=1;
print \nNew Workbook: $workBookName\n;
}
$worksheet-write_row($lineCount,0,$ary);
}
print \n;
$workbook-close;
$sth-finish;
$dbh1-disconnect;
{
my $workBookNumber = 0;
sub newWorkBookName {
return /home/user1/LC/excel_dump_ .
++$workBookNumber . .xls;
}
}
-
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail
SpamGuard.
__
Do you Yahoo!?
The all-new My Yahoo! - What will yours do?
http://my.yahoo.com
Connecting to a remote server
Hello, I have a script that is connecting to two different databases , one is local and the other remote. The local Informix DBI connection works a treat but I am having trouble getting the remote mysql connection to work. I keep get error: Client does not support authentication protocol requested by server. I have checked and upgraded, and now both local and remote machines are running mysql 4.1, and the same client. I have also checked and the mysql port is open on the remote server. All the search results I could find regarding this error said to upgrade the the mysql server, which I have done and now there should be no protocol barriers since they are running the same version. Does anyone have any ideas what may be causing this error other than different versions of the server. Or probably even better, some instructions on how to really turn up or tweak my dbh debugging so that I can get more details on the failed connection attempt. this is my connection statement: my $db2 = dbname; my $dbhMysql = DBI-connect(DBI:mysql:$db2:xxx.xxx.xxx.xxx, 'uname', 'pword') || die print Could not connect to database($db2): $!\n; this being the same as my local connection (minus the host IP address) which does work. Thanks, mike
