Re: Slow connection to Oracle 9i
On Sun, Oct 17, 2004 at 09:06:56PM -0400, Paul Appleby wrote: I have a simple Perl 5.6 test script that uses DBI and DBD::Oracle to connect to a local Oracle 9i database table and retrieve the data in the three small fields of its only two records. Why is the connection time so long and how can I shorten it? It is 3 to 4 times longer than retrieving data from a MySQL database. Only 3 to 4 times longer than retrieving data from a MySQL? You're lucky, it's often longer! :) This is the connection string: $dbh = DBI-connect(dbi:Oracle:$dbname, $user, $passwd) or die(OOPS: $DBI::errstr); It takes 2.9342188835144 seconds to connect to the database. It takes 0.0100140571594238 seconds to retrieve the data and print it. It takes almost 1 second longer using this connection string: $dbh = DBI-connect(dbi:Oracle:host=$location;sid=$dbname, $user, $passwd); I'd guess the difference is that the second is forcing a network connection (even if host is localhost). I also used d:DProf and ran the test script, and then dprofpp -u to analyze the resulting tmon.out file: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. Oracle is widely known to be slow to connect to. The usual approach is to try to stay connected rather than keep reconnecting. Oracle MTS may help but has its own set of problems. Tim.
Re: Slow connection to Oracle 9i
Paul Appleby wrote: I have a simple Perl 5.6 test script that uses DBI and DBD::Oracle to connect to a local Oracle 9i database [...] Why is the connection time so long and how can I shorten it? [...] It takes 2.9342188835144 seconds to connect to the database. How long does it take with sqlplus? E.g. on our W2K 1193 MHz server (Oracle 8.1.7.2.1): timethis echo exit | sqlplus -s / TimeThis : Command Line : echo exit | sqlplus -s / TimeThis :Start Time : Mon Oct 18 10:17:15 2004 TimeThis : Command Line : echo exit | sqlplus -s / TimeThis :Start Time : Mon Oct 18 10:17:15 2004 TimeThis : End Time : Mon Oct 18 10:17:15 2004 TimeThis : Elapsed Time : 00:00:00.109 Steffen
Re: Slow connection to Oracle 9i
Well, do you know what cause that delay? Between an Oracle client and Database Server? - Original Message - From: Steffen Goeldner [EMAIL PROTECTED] To: Paul Appleby [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, October 18, 2004 11:34 AM Subject: Re: Slow connection to Oracle 9i Paul Appleby wrote: I have a simple Perl 5.6 test script that uses DBI and DBD::Oracle to connect to a local Oracle 9i database [...] Why is the connection time so long and how can I shorten it? [...] It takes 2.9342188835144 seconds to connect to the database. How long does it take with sqlplus? E.g. on our W2K 1193 MHz server (Oracle 8.1.7.2.1): timethis echo exit | sqlplus -s / TimeThis : Command Line : echo exit | sqlplus -s / TimeThis :Start Time : Mon Oct 18 10:17:15 2004 TimeThis : Command Line : echo exit | sqlplus -s / TimeThis :Start Time : Mon Oct 18 10:17:15 2004 TimeThis : End Time : Mon Oct 18 10:17:15 2004 TimeThis : Elapsed Time : 00:00:00.109 Steffen
ANNOUNCE: DBD::ADO 2.93
file: $CPAN/authors/id/S/SG/SGOELDNER/DBD-ADO-2.93.tar.gz size: 42606 bytes md5: c3cad1e4b5576895939423f8130da1bb Changes: Implemented do(). Changed $sth-DESTROY to call finish( $sth ) instead of $sth-finish. The old call caused clearing of errors e.g. in $dbh-do(). Replaced duplicated code in disconnect() with a call to rollback(). Minor changes to db/st handle attributes. Minor changes to table_info()'s parameter handling. Modified tests (t/19misc.t, t/25curs.t) to use bind_param(). Added INSERT tests (t/12ins_b.t, t/12ins_q.t). Documented a SQLOLEDB bug, reported by Vahe Sarkissian. Some test results: Microsoft.Jet.OLEDB.4.0 All tests successful, 47 subtests skipped. VFPOLEDB Failed Test Stat Wstat Total Fail Failed List of Failed -- t\12ins_q.t4 1024444 9.09% 40-43 t\14rows.t 2 512182 11.11% 13 15 t\41ddtbl.t1 256331 3.03% 16 t\43ddpk.t 1 256161 6.25% 11 53 subtests skipped. Failed 4/27 test scripts, 85.19% okay. 8/578 subtests failed, 98.62% okay. SQLOLEDB Failed Test Stat Wstat Total Fail Failed List of Failed -- t\12ins_b.t2 512642 3.12% 32-33 t\42ddcol.t1 256251 4.00% 24 28 subtests skipped. Failed 2/27 test scripts, 92.59% okay. 3/578 subtests failed, 99.48% okay. MSDAORA All tests successful, 50 subtests skipped. Steffen
CPU usage for Oraperl emulation
Hi I am using Oraperl emulation for DBD:Oracle for a perl program in HP-UX11i. I would like to know the amount of CPU usage by Oraperl since we are having CPU usage constraints. I would also like to know whether waiting for input will put the state of the process to 'Sleep'. Thanks and Regards, Devaraj
FW: CPU usage for Oraperl emulation
Hi I am using Oraperl emulation for DBD:Oracle for a perl program in HP-UX11i. I would like to know the amount of CPU usage by Oraperl since we are having CPU usage constraints. I would also like to know whether waiting for input will put the state of the process to 'Sleep'. Thanks and Regards, Devaraj
Re: CPU usage for Oraperl emulation
On Mon, Oct 18, 2004 at 02:20:14PM +0530, rajarathnam, devaraj wrote: Hi I am using Oraperl emulation for DBD:Oracle for a perl program in HP-UX11i. I would like to know the amount of CPU usage by Oraperl since we are having CPU usage constraints. The CPU usage by Oraperl (plus DBI + DBD::Oracle) is not significant. Your application will use much more CPU. I would also like to know whether waiting for input will put the state of the process to 'Sleep'. It will. Tim.
Re: Slow connection to Oracle 9i
Tim, It is 3 to 4 times longer than retrieving data from a MySQL database. Only 3 to 4 times longer than retrieving data from a MySQL? You're lucky, it's often longer! :) That was 3 to 4 times longer using the data from d:DProf. But it's 20 times longer using Time::HiRes to measure the time it takes to connect to the database. I guess I'm not so lucky after all. It takes 2.9342188835144 seconds to connect to the database. It takes 0.0100140571594238 seconds to retrieve the data and print it. It takes almost 1 second longer using this connection string: $dbh = DBI-connect(dbi:Oracle:host=$location;sid=$dbname, $user, $passwd); I'd guess the difference is that the second is forcing a network connection (even if host is localhost). DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. -- Sincerely, Paul Appleby
Re: Slow connection to Oracle 9i
On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim.
Open source perl database interface GUIs for unix?
I'd like to get a list of open source perl (DBI based) database interface GUIs for unix (Tk/Gtk etc). If people could send me (off-list) details of any they know I'll summarize back to the list. Thank you. Tim.
RE: difficulties with utf-8 characters using DBD::Oracle, where works using DBD::Pg (PostgreSQL) - success!
Success at last! I had the dba create an instance with the database
character set AL32UTF8 (thanks to the excellent documentation with
DBD::Oracle 1.16, which explained the weird behavior of Oracle with plain
UTF8), and used NLS_LANG=.UTF8, and DBD::Oracle 1.16 (1.15 did not work
100%, even with the new Oracle instance).
Now, French looks like French, Japanese looks like Japanese, etc.
Tim saves my life once again!
Thanks,
Susan Cassidy
-Original Message-
From: Tim Bunce [mailto:[EMAIL PROTECTED]
Sent: Friday, October 15, 2004 2:01 AM
To: Susan Cassidy
Cc: [EMAIL PROTECTED]
Subject: Re: difficulties with utf-8 characters using DBD::Oracle, where
works using DBD::Pg (PostgreSQL)
Try http://homepage.eircom.net/~timbunce/DBD-Oracle-1.16-rc7-
20040826.tar.gz
and read the documentation about unicode carefully.
Let me know how it goes.
Tim.
On Thu, Oct 14, 2004 at 04:06:41PM -0700, Susan Cassidy wrote:
Hello,
I have a cgi application that works fine using DBD::Pg to insert/select
data
from a PostgreSQL using UTF-8 (database created as UNICODE). We have
data
in multiple languages stored, which has been working fine.
I have modified the application to use either Oracle or PostgreSQL,
depending on a config file. The PostgreSQL part still works fine - web
page
shows up correctly (we specify utf-8 encoding in the header), no
problems.
The Oracle way is problematic.
From SQLPLUS, it appears that I can INSERT and SELECT data in French,
for
example, and it all looks correct. The environment in my Linux window
has
these variables:
NLS_LANG=.UTF8 - this also works with
NLS_LANG=AMERICAN_AMERICA.UTF8
ORACLE_SID=STSDEV1
ORACLE_BASE=/home/oracle
LANG=UTF-8
ORA_NLS33=/home/oracle/product/9.2.0/ocommon/nls/admin/data
ORACLE_HOME=/home/oracle/product/9.2.0
I set ORACLE_HOME, ORACLE_SID, ORA_NLS33, and NLS_LANG environment
variables
in httpd.conf, and in programs that I run for tests that are not running
as
web apps.
If I connect via DBD::Oracle, I get some of the French special
characters to
come out right, and others do not. I have been told that some (when
retrieved) are actually encoded in UTF8, and others are Latin1.
I use the same input data, fetch the same translated data, etc. The
only
differences that are left seems to be DBD::Oracle, Oracle itself, and
the
environment settings for Oracle.
I extracted some basic data, known to be utf8, and inserted it into a
table
using Oracle SQLLDR. Then, I retrieved it using a sql script, via
sqlplus,
spooling the output to a file. If I read that file, and output it to a
web
page, it looks fine.
If I read the data via DBD::Oracle, it has garbage characters instead of
the
special characters.
This seems to point to DBD::Oracle being the cause of the problems.
Perhaps
some method I need to call that I did not get from the documentation?
I will append the basic test program below (simple program, instead of
giant
application - same type of results):
Any advice gratefully received. I have never had so much trouble with a
DBD
application, and have used DBD::Oracle before with no trouble.
Susan Cassidy
-
#!/usr/local/bin/perl
use CGI;
use DBI;
our $dbh;
our $sth;
$dbuser=xxx;
$dbpasswd=yyy;
$dbserver='devsys';
$db_sid='TEST1';
#$ENV{NLS_LANG}='AMERICAN_AMERICA.UTF8';
$ENV{NLS_LANG}='.UTF8';
$ENV{ORA_NLS33}='/home/oracle/product/9.2.0/ocommon/nls/admin/data';
$ENV{ORACLE_HOME}='/home/oracle/product/9.2.0';
$dbh= DBI-connect(dbi:Oracle:host=$dbserver;sid=$db_sid, $dbuser,
$dbpasswd,
{PrintError = 0, AutoCommit = 1}) or errexit( Unable to connect
to
$dbserver: $DBI::errstr);
my $html_hdr=EOF;
html
head
titleSYSTRAN - UTF8 Test/title
meta http-equiv=Content-Type content=text/html; charset=utf-8
link rel=stylesheet href=http://www.systransoft.com/Systran.css;
type=text/css
/head
h3Sample data/h3
table cellpadding=0 cellspacing=2 border=1
EOF
my $cgi=new CGI;
print $cgi-header( -charset='utf-8');
print $html_hdr;
print EOF;
tr bgcolor=silver
tdTU/td
tdEnglish/td
tdFrench/td
/tr
EOF
my (@data);
my ($select_stmt)= EOF;
SELECT source, target from test_trans
EOF
execute_db_statement($select_stmt, __LINE__);
while (@data = $sth-fetchrow_array) {
foreach (@data) { $_='' unless defined}
next if ($data[0] eq '');
print 'trtd',(join /tdtd,@data),/td/tr\n;
}
#check for problems with premature termination
errexit($sth-errstr) if $sth-err;
print
no suitable installation target
Hi ! I have this message chen I tape the line : ppm install DBI.ppd Error : no suitable installation target What is the reason of this message. I didn't find anything on the web. Could you help me please ? Thanks, Raph - Créez gratuitement votre Yahoo! Mail avec 100 Mo de stockage ! Créez votre Yahoo! Mail Le nouveau Yahoo! Messenger est arrivé ! Découvrez toutes les nouveautés pour dialoguer instantanément avec vos amis.Téléchargez GRATUITEMENT ici !
RE: Slow connection to Oracle 9i
Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim.
Re: Slow connection to Oracle 9i
Steffan, Try measuring the time it takes to connect with SqlPlus by using Time::HiRes with the variables $time1 and $time2 both set to gettimeofday() and the the length of time set to $time2-$time1, as follows: $time1=gettimeofday(); your code runs here; $time2=gettimeofday(); print $time2-$time1; Paul Paul Appleby wrote: I have a simple Perl 5.6 test script that uses DBI and DBD::Oracle to connect to a local Oracle 9i database [...] Why is the connection time so long and how can I shorten it? [...] It takes 2.9342188835144 seconds to connect to the database. How long does it take with sqlplus? E.g. on our W2K 1193 MHz server (Oracle 8.1.7.2.1): timethis echo exit | sqlplus -s / TimeThis : Command Line : echo exit | sqlplus -s / TimeThis :Start Time : Mon Oct 18 10:17:15 2004 TimeThis : Command Line : echo exit | sqlplus -s / TimeThis :Start Time : Mon Oct 18 10:17:15 2004 TimeThis : End Time : Mon Oct 18 10:17:15 2004 TimeThis : Elapsed Time : 00:00:00.109 Steffen -- Sincerely, Paul Appleby
RE: Slow connection to Oracle 9i
Maybe Oracle does not think it is a problem, but I (and lots of others) would disagree. These issues are correctable. In the case of slow logins, it depends on what is being done when the connection is made. When making connections that are dedicated server (not MTS) a process is created. If your server is memory and/or CPU bound, this could take a long time. Other causes could be the any after-logon triggers that may be firing. I would suggest: 1. Read the DBD::Oracle docs. There is a section (albeit, dated) that describes how one might make connection times faster. 2. Read the Net Services Admin Guide (http://download-west.oracle.com/docs/cd/B10501_01/network.920/a96580.pdf) Investigate the use of the PRE_SPAWNED and QUEUESIZE SQL*Net parameters. I have found these very useful in several environments I have worked in. 3. If still you are still not satisfied, open a TAR with Oracle support. - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: BAXTER, LINCOLN A [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 11:17 AM To: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. 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: Slow connection to Oracle 9i
On Mon, Oct 18, 2004 at 11:48:11AM -0600, Reidy, Ron wrote: 1. Read the DBD::Oracle docs. There is a section (albeit, dated) that describes how one might make connection times faster. 2. Read the Net Services Admin Guide (http://download-west.oracle.com/docs/cd/B10501_01/network.920/a96580.pdf) Investigate the use of the PRE_SPAWNED and QUEUESIZE SQL*Net parameters. I have found these very useful in several environments I have worked in. Patches welcome! A new DBD::Oracle release is close. (Honest!) Tim. 3. If still you are still not satisfied, open a TAR with Oracle support. - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: BAXTER, LINCOLN A [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 11:17 AM To: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. 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: Open source perl database interface GUIs for unix?
On Mon, Oct 18, 2004 at 05:09:05PM +0100, Tim Bunce wrote: I'd like to get a list of open source perl (DBI based) database interface GUIs for unix (Tk/Gtk etc). I should clarify that a little. Basically I'm intereted in something that'll open a dialog box asking for connection details, then present a window split between an SQL area and a results area. Key things are a) Perl DBI based, b) open source, c) well structured internally so it can be extended/subclassed/hooked/whatever to alter behaviour. Can't be web-based. Tim. If people could send me (off-list) details of any they know I'll summarize back to the list. Thank you. Tim.
RE: Slow connection to Oracle 9i
My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby
RE: Slow connection to Oracle 9i
Perhaps this'll help: http://perl.apache.org/docs/1.0/guide/performance.html#Persistent_DB_Connections There's a link on that site to Tim Bunce's Advanced DBI talk, but it returns a 500. Tim??? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] QuadTech, Sussex, WI USA -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul
RE: Slow connection to Oracle 9i
No. Each user will be using the same application that logs in to the same schema. But the users are all anonymous visitors to a web site. Paul Are you logging in each user to a unique oracle schemna? If so, no hope... (other than oracle tuning per Tim's message -- pre-spawned listeners on the database can make a BIG difference ... talk to your dba's) Lincoln -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 4:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby -- Sincerely, Paul Appleby
RE: Slow connection to Oracle 9i
Look at using Apache::DBI for persistent connections. - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 2:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby 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: Slow connection to Oracle 9i
H...one thing I thought of is to check the validity of the handle, in case the Oracle instance bounces (i.e. The Oracle instance is available, but the persistent connection no longer exists). Does that automagically happen in the connect or should there be code to check for a specific error either on the connect or maybe the statement handle? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] QuadTech, Sussex, WI USA -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:51 PM To: Paul Appleby; BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i Perhaps this'll help: http://perl.apache.org/docs/1.0/guide/performance.html#Persistent_DB_Connections There's a link on that site to Tim Bunce's Advanced DBI talk, but it returns a 500. Tim??? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] QuadTech, Sussex, WI USA -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul
Re: Slow connection to Oracle 9i
I have a DBIx::HA (High Availability) module on CPAN, but I'm readying a next version real soon now that will take care of this for you. If you combine Apache+mod_perl+Apache::DBI+DBIx::HA, you'll get what you need. I'm debugging one last instance of a dbh having an ActiveKid handle when it shouldn't, and then I'll release it. It will require DBI 1.44+ (needs swap_inner_handle()). On Oct 18, 2004, at 2:05 PM, Jesse, Rich wrote: H...one thing I thought of is to check the validity of the handle, in case the Oracle instance bounces (i.e. The Oracle instance is available, but the persistent connection no longer exists). Does that automagically happen in the connect or should there be code to check for a specific error either on the connect or maybe the statement handle? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] QuadTech, Sussex, WI USA -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:51 PM To: Paul Appleby; BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i Perhaps this'll help: http://perl.apache.org/docs/1.0/guide/ performance.html#Persistent_DB_Connections There's a link on that site to Tim Bunce's Advanced DBI talk, but it returns a 500. Tim??? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] QuadTech, Sussex, WI USA -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul
Re: no suitable installation target
On Mon, 18 Oct 2004 16:51:35 +0200 (CEST), Raphael wrote: Hi Raphael ppm install DBI.ppd Error : no suitable installation target It's been years since I had this problem, so I'm really guessing, but I think it's due to a conflict between the Perl you are using and the Perl used to prepare the PPD. By conflict I mean something like this: one was compiled with threads and one without. -- Cheers Ron Savage, [EMAIL PROTECTED] on 19/10/2004 http://savage.net.au/index.html
Re: Open source perl database interface GUIs for unix?
On Mon, 18 Oct 2004 19:24:03 +0100, Tim Bunce wrote: Hi Tim Can't be web-based. Oh :-((. -- Cheers Ron Savage, [EMAIL PROTECTED] on 19/10/2004 http://savage.net.au/index.html
Invalid cursor state when using PRINT in MSSQL
I am using DBI and DBD-ODBC to connect to an MSSQL Server and this query
gets an invalid cursor state:
PRINT 'starting select'
select count(*) from anytable
PRINT 'finished'
If I only run the PRINT command... it works.
If I run the full query against a sybase server... only the top select
prints.
I am using:
Win32 AS-5.8.0 Multithread build 806 DBI(1.43)DBD::ODBC(1.07) MSSQL Server I
get a invalid cursor state when I run any SQL with a PRINT statement at the
beginning of any SQL statement.
The error:
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid
cursor state (SQL-24000)(DB
D: dbd_describe/SQLNumResultCols err=-1) at H:\progs\dbi_test.pl line 10.
Example code:
#c:/perl/bin/perl
use DBI;
my $dbh = DBI-connect( 'dbi:ODBC:TEST_DSN', '','', {RaiseError= 1} );
my $sql = qq#
PRINT 'starting select'
select count(*) from anytable
PRINT 'finished'
#;
my $sth = $dbh-prepare( $sql );
my $rv = $sth-execute();
while ( my $hr = $sth-fetchrow_hashref ){
print $$hr{$_}, \n for ( keys %$hr );
print Err: , $dbh-errstr, \n if $dbh-errstr;
}
Does this have anything to do with how SQL_SUCCESS_WITH_INFO is being
handled by the driver?
How do I get all the results..
Thanks!
James
Re: Slow connection to Oracle 9i
On Mon, Oct 18, 2004 at 04:39:25PM -0400, Paul Appleby wrote: My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Apart from the other (good) advice here, which you should follow first, it is possible, with DBD::Oracle, to 'reauthenticate' a $dbh for a different user. See the reauthenticate method in the docs. Tim. Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby
Re: Slow connection to Oracle 9i
No reauthenticate() method in DBD:Oracle v 1.12 or in the ActiveState Perl 5.6 docs. Is there a Windows IIS equivalent for the Apache::DBI for persistent connections? Is there a way to configure or set Oracle form its Enterprise Management Console to establish a persistent connection to a particular table in a particular schema for a particular user? Paul On Mon, Oct 18, 2004 at 04:39:25PM -0400, Paul Appleby wrote: My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Apart from the other (good) advice here, which you should follow first, it is possible, with DBD::Oracle, to 'reauthenticate' a $dbh for a different user. See the reauthenticate method in the docs. Tim. Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby -- Sincerely, Paul Appleby
Re: Slow connection to Oracle 9i
I realize I may not have been totally clear in my earlier post, which should have said: My CGI application will be called by different visitors to the web site at different times, but they all connect to the same database using the same connection variables, i.e. the same user/password. Are you saying the first visitor to the web site's connection can be left open for all the other visitors? How? Perhaps, though you understand it as such without this clarification. Paul On Mon, Oct 18, 2004 at 04:39:25PM -0400, Paul Appleby wrote: My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Apart from the other (good) advice here, which you should follow first, it is possible, with DBD::Oracle, to 'reauthenticate' a $dbh for a different user. See the reauthenticate method in the docs. Tim. Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby -- Sincerely, Paul Appleby
Re: Slow connection to Oracle 9i
Paul, Without knowing much about Oracle (although I can talk your ear off about Sybase), you want to cache connections to the server and set a limit (maybe its 1!) and use this pool of connections to connect to your server. Major advantage: No overhead for creating the connection on startup. Major disadvantage: Need to goto java afaik. Apache has some modules written for it to do something like this, hit google. There maybe a dbi module that I am unaware of that can provide this capability to your webserver via cgi/bin perl. Hit CPAN search. Paul Appleby wrote on 10/18/2004, 7:57 PM: I realize I may not have been totally clear in my earlier post, which should have said: My CGI application will be called by different visitors to the web site at different times, but they all connect to the same database using the same connection variables, i.e. the same user/password. Are you saying the first visitor to the web site's connection can be left open for all the other visitors? How? Perhaps, though you understand it as such without this clarification. Paul On Mon, Oct 18, 2004 at 04:39:25PM -0400, Paul Appleby wrote: My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Apart from the other (good) advice here, which you should follow first, it is possible, with DBD::Oracle, to 'reauthenticate' a $dbh for a different user. See the reauthenticate method in the docs. Tim. Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby -- Sincerely, Paul Appleby -- Your Friendly Neighborhood DBA, Chuck
Re: Invalid cursor state when using PRINT in MSSQL
my $sql = qq#
PRINT 'starting select'
select count(*) from anytable
PRINT 'finished'
#;
is not valid sql.
try this:
my $sql = qq#
select count(*) from anytable
#;
my $sth = $dbh-prepare( $sql );
print 'starting select';
my $rv = $sth-execute();
while ( my $hr = $sth-fetchrow_hashref ){
print $$hr{$_}, \n for ( keys %$hr );
print Err: , $dbh-errstr, \n if $dbh-errstr;
}
print 'finished';
_
Jeff Seger
Fairchild Semiconductor
[EMAIL PROTECTED]
Moosmann, James [EMAIL PROTECTED]
10/18/2004 06:46 PM
To: '[EMAIL PROTECTED]' [EMAIL PROTECTED]
cc:
Subject:Invalid cursor state when using PRINT in MSSQL
I am using DBI and DBD-ODBC to connect to an MSSQL Server and this query
gets an invalid cursor state:
PRINT 'starting select'
select count(*) from anytable
PRINT 'finished'
If I only run the PRINT command... it works.
If I run the full query against a sybase server... only the top select
prints.
I am using:
Win32 AS-5.8.0 Multithread build 806 DBI(1.43)DBD::ODBC(1.07) MSSQL Server
I
get a invalid cursor state when I run any SQL with a PRINT statement at
the
beginning of any SQL statement.
The error:
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid
cursor state (SQL-24000)(DB
D: dbd_describe/SQLNumResultCols err=-1) at H:\progs\dbi_test.pl line 10.
Example code:
#c:/perl/bin/perl
use DBI;
my $dbh = DBI-connect( 'dbi:ODBC:TEST_DSN', '','', {RaiseError= 1} );
my $sql = qq#
PRINT 'starting select'
select count(*) from anytable
PRINT 'finished'
#;
my $sth = $dbh-prepare( $sql );
my $rv = $sth-execute();
while ( my $hr = $sth-fetchrow_hashref ){
print $$hr{$_}, \n for ( keys %$hr );
print Err: , $dbh-errstr, \n if $dbh-errstr;
}
Does this have anything to do with how SQL_SUCCESS_WITH_INFO is being
handled by the driver?
How do I get all the results..
Thanks!
James
RE: Invalid cursor state when using PRINT in MSSQL
Jeff,
It is VERY VALID syntax for both MSSQL ... AND.. Sybase. I have been coding
in Perl and DBI for several years now and I am NOT trying to use the perl
print function. Here is the skinny on PRINT direct from our DBA for MSSQL
Server PRINT :
The PRINT statement takes either one character or a Unicode string
expression as a parameter. It returns the string as a message to the
application. The message is returned as an informational error in ADO, OLE
DB, and ODBC applications. SQLSTATE is set to 01000, the native error is set
to 0, and the error message string is set to the character string specified
in the PRINT statement. The string is returned to the message handler
call-back function in DB-Library applications.
The example I gave is just minimal (...sigh) to display the error, and I am
not sure if you understand the question.
I asked this question in Perl Monks and got this informative reply:
PRINT '...begin' select * from foo.bar.mytable
SQLExecDirect:
In:hstmt = 0x00991FB8, szSqlStr = , cbSqlStr = -3
Return:SQL_SUCCESS_WITH_INFO=1
stmt:szSqlState = 01000, *pfNativeError = 0, *pcbErrorMsg = 55,
*ColumnNumber = -1, *RowNumber
+ = 1
MessageText = [Microsoft][ODBC SQL Server Driver][SQL Server]...begin
Get Data All:
-1 rows affected by INSERT/UPDATE/DELETE or other statement.
col1, col2, col3
1, row1, row1
2, row2, row2
2 rows fetched from 3 columns.
MS SQL Server help also mentions that you have to call SQLError right after
statement is executed.
The timing of calling SQLError is critical when output from PRINT or
RAISERROR statements are included in a result set. The call to SQLError to
retrieve the PRINT or RAISERROR output must be made immediately after the
statement that receives SQL_ERROR or SQL_SUCCESS_WITH_INFO. This is
straightforward when only a single SQL statement is executed, as in the
examples above. In these cases, the call to SQLExecDirect or SQLExecute
returns SQL_ERROR or SQL_SUCCESS_WITH_INFO and SQLError can then be called.
It is less straightforward when coding loops to handle the output of a batch
of SQL statements or when executing SQL Server stored procedures.
***
I have researched this and this looks like a ODBC driver issue. You get the
first print and the select if you use Sybase, but you don't get the ending
PRINT. We have some VERY long running an complex queries and the PRINTS are
embedded in STORED PROCEDURES. I am trying to use the output of the PRINTS
to give feedback to the user when the queries will finish.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Monday, October 18, 2004 9:08 PM
To: Moosmann, James
Cc: '[EMAIL PROTECTED]'
Subject: Re: Invalid cursor state when using PRINT in MSSQL
my $sql = qq#
PRINT 'starting select'
select count(*) from anytable
PRINT 'finished'
#;
is not valid sql.
try this:
my $sql = qq#
select count(*) from anytable
#;
my $sth = $dbh-prepare( $sql );
print 'starting select';
my $rv = $sth-execute();
while ( my $hr = $sth-fetchrow_hashref ){
print $$hr{$_}, \n for ( keys %$hr );
print Err: , $dbh-errstr, \n if $dbh-errstr;
}
print 'finished';
_
Jeff Seger
Fairchild Semiconductor
[EMAIL PROTECTED]
Moosmann, James [EMAIL PROTECTED]
10/18/2004 06:46 PM
To:'[EMAIL PROTECTED]' [EMAIL PROTECTED]
cc:
Subject:Invalid cursor state when using PRINT in MSSQL
I am using DBI and DBD-ODBC to connect to an MSSQL Server and this query
gets an invalid cursor state:
PRINT 'starting select'
select count(*) from anytable
PRINT 'finished'
If I only run the PRINT command... it works.
If I run the full query against a sybase server... only the top select
prints.
I am using:
Win32 AS-5.8.0 Multithread build 806 DBI(1.43)DBD::ODBC(1.07) MSSQL Server I
get a invalid cursor state when I run any SQL with a PRINT statement at the
beginning of any SQL statement.
The error:
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid
cursor state (SQL-24000)(DB
D: dbd_describe/SQLNumResultCols err=-1) at H:\progs\dbi_test.pl line 10.
Example code:
#c:/perl/bin/perl
use DBI;
my $dbh = DBI-connect( 'dbi:ODBC:TEST_DSN', '','', {RaiseError= 1} );
my $sql = qq#
PRINT 'starting select'
select count(*) from anytable
PRINT 'finished'
#;
my $sth = $dbh-prepare( $sql );
my $rv = $sth-execute();
while ( my $hr = $sth-fetchrow_hashref ){
print $$hr{$_}, \n for ( keys %$hr );
print Err: , $dbh-errstr, \n if $dbh-errstr;
}
Does this have anything to do with how SQL_SUCCESS_WITH_INFO is being
handled by the driver?
How do I get all the results..
Thanks!
James
RE: Invalid cursor state when using PRINT in MSSQL
James
if you think it's a DBD::ODBC issue, why not use DBD::Sybase instead?
Dan
Moosmann, James [EMAIL PROTECTED]
19/10/2004 16:17
To: '[EMAIL PROTECTED]' [EMAIL PROTECTED]
cc: '[EMAIL PROTECTED]' [EMAIL PROTECTED], (bcc: Dan
Horne/IT/AKLWHG/WHNZ)
Subject:RE: Invalid cursor state when using PRINT in MSSQL
Jeff,
It is VERY VALID syntax for both MSSQL ... AND.. Sybase. I have been
coding
in Perl and DBI for several years now and I am NOT trying to use the perl
print function. Here is the skinny on PRINT direct from our DBA for MSSQL
Server PRINT :
The PRINT statement takes either one character or a Unicode string
expression as a parameter. It returns the string as a message to the
application. The message is returned as an informational error in ADO, OLE
DB, and ODBC applications. SQLSTATE is set to 01000, the native error is
set
to 0, and the error message string is set to the character string
specified
in the PRINT statement. The string is returned to the message handler
call-back function in DB-Library applications.
The example I gave is just minimal (...sigh) to display the error, and I
am
not sure if you understand the question.
I asked this question in Perl Monks and got this informative reply:
PRINT '...begin' select * from foo.bar.mytable
SQLExecDirect:
In:hstmt = 0x00991FB8, szSqlStr = , cbSqlStr = -3
Return:SQL_SUCCESS_WITH_INFO=1
stmt:szSqlState = 01000, *pfNativeError = 0, *pcbErrorMsg = 55,
*ColumnNumber = -1, *RowNumber
+ = 1
MessageText = [Microsoft][ODBC SQL Server Driver][SQL
Server]...begin
Get Data All:
-1 rows affected by INSERT/UPDATE/DELETE or other statement.
col1, col2, col3
1, row1, row1
2, row2, row2
2 rows fetched from 3 columns.
MS SQL Server help also mentions that you have to call SQLError right
after
statement is executed.
The timing of calling SQLError is critical when output from PRINT or
RAISERROR statements are included in a result set. The call to SQLError to
retrieve the PRINT or RAISERROR output must be made immediately after the
statement that receives SQL_ERROR or SQL_SUCCESS_WITH_INFO. This is
straightforward when only a single SQL statement is executed, as in the
examples above. In these cases, the call to SQLExecDirect or SQLExecute
returns SQL_ERROR or SQL_SUCCESS_WITH_INFO and SQLError can then be
called.
It is less straightforward when coding loops to handle the output of a
batch
of SQL statements or when executing SQL Server stored procedures.
***
I have researched this and this looks like a ODBC driver issue. You get
the
first print and the select if you use Sybase, but you don't get the ending
PRINT. We have some VERY long running an complex queries and the PRINTS
are
embedded in STORED PROCEDURES. I am trying to use the output of the
PRINTS
to give feedback to the user when the queries will finish.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Monday, October 18, 2004 9:08 PM
To: Moosmann, James
Cc: '[EMAIL PROTECTED]'
Subject: Re: Invalid cursor state when using PRINT in MSSQL
my $sql = qq#
PRINT 'starting select'
select count(*) from anytable
PRINT 'finished'
#;
is not valid sql.
try this:
my $sql = qq#
select count(*) from anytable
#;
my $sth = $dbh-prepare( $sql );
print 'starting select';
my $rv = $sth-execute();
while ( my $hr = $sth-fetchrow_hashref ){
print $$hr{$_}, \n for ( keys %$hr );
print Err: , $dbh-errstr, \n if $dbh-errstr;
}
print 'finished';
_
Jeff Seger
Fairchild Semiconductor
[EMAIL PROTECTED]
Moosmann, James [EMAIL PROTECTED]
10/18/2004 06:46 PM
To:'[EMAIL PROTECTED]' [EMAIL PROTECTED]
cc:
Subject:Invalid cursor state when using PRINT in MSSQL
I am using DBI and DBD-ODBC to connect to an MSSQL Server and this query
gets an invalid cursor state:
PRINT 'starting select'
select count(*) from anytable
PRINT 'finished'
If I only run the PRINT command... it works.
If I run the full query against a sybase server... only the top select
prints.
I am using:
Win32 AS-5.8.0 Multithread build 806 DBI(1.43)DBD::ODBC(1.07) MSSQL Server
I
get a invalid cursor state when I run any SQL with a PRINT statement at
the
beginning of any SQL statement.
The error:
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid
cursor state (SQL-24000)(DB
D: dbd_describe/SQLNumResultCols err=-1) at H:\progs\dbi_test.pl line 10.
Example code:
#c:/perl/bin/perl
use DBI;
my $dbh = DBI-connect( 'dbi:ODBC:TEST_DSN', '','', {RaiseError= 1} );
my $sql = qq#
PRINT 'starting select'
select count(*) from anytable
PRINT 'finished'
#;
my $sth = $dbh-prepare( $sql );
my $rv = $sth-execute();
while ( my $hr = $sth-fetchrow_hashref ){
print $$hr{$_}, \n for ( keys %$hr );
print Err: , $dbh-errstr, \n if $dbh-errstr;
}
Does this
RE: Invalid cursor state when using PRINT in MSSQL
Dan,
The server and all the SP's I am running this on is MSSQL and not Sybase. I
have another DSN to a Sybase server and tested this behavior on that one,
and reported the results here. I was hoping to find someone who knew the
ODBC driver guts well enough to know what the problem was or knew a
solution.
I personally don't use PRINT in my SQL and before I go ask these other
programmers to stop doing what works everywhere else but in DBI::ODBC I was
hoping to find the solution. Perhaps this is just broken here and it cannot
be fixed. So far everyone acknowledges the problem, but no one knows why it
doesn't work or how to fix it. If this is the wrong place to discuss this,
then I apologize and will try to find the correct forum.
Thanks.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, October 18, 2004 11:47 PM
To: Moosmann, James
Cc: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: RE: Invalid cursor state when using PRINT in MSSQL
James
if you think it's a DBD::ODBC issue, why not use DBD::Sybase instead?
Dan
Moosmann, James [EMAIL PROTECTED]
19/10/2004 16:17
To:'[EMAIL PROTECTED]'
[EMAIL PROTECTED]
cc:'[EMAIL PROTECTED]' [EMAIL PROTECTED], (bcc: Dan
Horne/IT/AKLWHG/WHNZ)
Subject:RE: Invalid cursor state when using PRINT in MSSQL
Jeff,
It is VERY VALID syntax for both MSSQL ... AND.. Sybase. I have been coding
in Perl and DBI for several years now and I am NOT trying to use the perl
print function. Here is the skinny on PRINT direct from our DBA for MSSQL
Server PRINT :
The PRINT statement takes either one character or a Unicode string
expression as a parameter. It returns the string as a message to the
application. The message is returned as an informational error in ADO, OLE
DB, and ODBC applications. SQLSTATE is set to 01000, the native error is set
to 0, and the error message string is set to the character string specified
in the PRINT statement. The string is returned to the message handler
call-back function in DB-Library applications.
The example I gave is just minimal (...sigh) to display the error, and I am
not sure if you understand the question.
I asked this question in Perl Monks and got this informative reply:
PRINT '...begin' select * from foo.bar.mytable
SQLExecDirect:
In:hstmt = 0x00991FB8, szSqlStr = , cbSqlStr = -3
Return:SQL_SUCCESS_WITH_INFO=1
stmt:szSqlState = 01000, *pfNativeError = 0, *pcbErrorMsg = 55,
*ColumnNumber = -1, *RowNumber
+ = 1
MessageText = [Microsoft][ODBC SQL Server Driver][SQL Server]...begin
Get Data All:
-1 rows affected by INSERT/UPDATE/DELETE or other statement.
col1, col2, col3
1, row1, row1
2, row2, row2
2 rows fetched from 3 columns.
MS SQL Server help also mentions that you have to call SQLError right after
statement is executed.
The timing of calling SQLError is critical when output from PRINT or
RAISERROR statements are included in a result set. The call to SQLError to
retrieve the PRINT or RAISERROR output must be made immediately after the
statement that receives SQL_ERROR or SQL_SUCCESS_WITH_INFO. This is
straightforward when only a single SQL statement is executed, as in the
examples above. In these cases, the call to SQLExecDirect or SQLExecute
returns SQL_ERROR or SQL_SUCCESS_WITH_INFO and SQLError can then be called.
It is less straightforward when coding loops to handle the output of a batch
of SQL statements or when executing SQL Server stored procedures.
***
I have researched this and this looks like a ODBC driver issue. You get the
first print and the select if you use Sybase, but you don't get the ending
PRINT. We have some VERY long running an complex queries and the PRINTS are
embedded in STORED PROCEDURES. I am trying to use the output of the PRINTS
to give feedback to the user when the queries will finish.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Monday, October 18, 2004 9:08 PM
To: Moosmann, James
Cc: '[EMAIL PROTECTED]'
Subject: Re: Invalid cursor state when using PRINT in MSSQL
my $sql = qq#
PRINT 'starting select'
select count(*) from anytable
PRINT 'finished'
#;
is not valid sql.
try this:
my $sql = qq#
select count(*) from anytable
#;
my $sth = $dbh-prepare( $sql );
print 'starting select';
my $rv = $sth-execute();
while ( my $hr = $sth-fetchrow_hashref ){
print $$hr{$_}, \n for ( keys %$hr );
print Err: , $dbh-errstr, \n if $dbh-errstr;
}
print 'finished';
_
Jeff Seger
Fairchild Semiconductor
[EMAIL PROTECTED]
Moosmann, James [EMAIL PROTECTED]
10/18/2004 06:46 PM
To:'[EMAIL PROTECTED]' [EMAIL PROTECTED]
cc:
Subject:Invalid cursor state when using PRINT in MSSQL
I am using DBI and DBD-ODBC to connect to an MSSQL Server and this query
gets an
Re: Slow connection to Oracle 9i
Please read the documentation for Apache::DBI. In Apache 1.3 and mod_perl 1.xx, when using Apache::DBI in conjunction with DBI you get the following: Each Apache process runs one perl instance. Each perl instance loads up one instance of Apache::DBI. Each instance of Apache::DBI automatically 'caches' all DBI connections using the connect params as the key. So for example, let's say that you set up Apache to have a maximum of 50 clients, with an average of 30 clients active at any one time. Suppose also that you will be connecting to 2 databases, with the following params: DBI-connect(dbi:Oracle:database=db_A;server=serv_A,'user1','pass1', \%params1); DBI-connect(dbi:Oracle:database=db_B;server=serv_B,'user1','pass1', \%params2); Then you will get a maximum of 50 open connections on server A, and 50 open connections on server B. Your average connections will be 30 on each server. Each Apache child will connect once to each database server, so you have 2 db connections per child. Each connection will stay open for a very long time, and only die when the Apache child dies. If you don't use Apache::DBI, then you will open and close connects every time a script or handler fires, and that's not fun. For Apache 2.0 and mod_perl 2.xx, things are more complicated and I only know how it was supposed to work (i.e. I don't know the current state of things). On Oct 18, 2004, at 5:02 PM, Chuck Fox wrote: Paul, Without knowing much about Oracle (although I can talk your ear off about Sybase), you want to cache connections to the server and set a limit (maybe its 1!) and use this pool of connections to connect to your server. Major advantage: No overhead for creating the connection on startup. Major disadvantage: Need to goto java afaik. Apache has some modules written for it to do something like this, hit google. There maybe a dbi module that I am unaware of that can provide this capability to your webserver via cgi/bin perl. Hit CPAN search. Paul Appleby wrote on 10/18/2004, 7:57 PM: I realize I may not have been totally clear in my earlier post, which should have said: My CGI application will be called by different visitors to the web site at different times, but they all connect to the same database using the same connection variables, i.e. the same user/password. Are you saying the first visitor to the web site's connection can be left open for all the other visitors? How? Perhaps, though you understand it as such without this clarification. Paul On Mon, Oct 18, 2004 at 04:39:25PM -0400, Paul Appleby wrote: My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Apart from the other (good) advice here, which you should follow first, it is possible, with DBD::Oracle, to 'reauthenticate' a $dbh for a different user. See the reauthenticate method in the docs. Tim. Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby -- Sincerely, Paul Appleby -- Your Friendly Neighborhood DBA, Chuck
