Re: Help Please DBI/ODBC/ORACLE
On Tue, 26 Jun 2001, Mark A. Werner wrote: .. > I have an ORACLE database on a Solaris 2.7 system that I am accessing via > the PERL DBI > inteface Just Fine. > > But I need to be able get to it vai the PERL DBI from a LINUX 7.2 box. i think it's easier if you just download Oracle for Linux and use DBD::Oracle. i don't suppose you're in violation of your license if you just use the Linux Oracle client side. i think. but i shouldn't consider myself authoritative on that. -- Orlando Andico <[EMAIL PROTECTED]> Mosaic Communications, Inc. -BEGIN GEEK CODE BLOCK- Version: 3.1 GE d(-) s: a-25 C UBLSI$ P+++ L+++> E- W++ N(+) o K? w O-- M- !V PS(++) PE- Y PGP-- t(+)@ 5(+) X++@ R(+) tv@ b++ DI++ G e++@ h--(*) r% y+ --END GEEK CODE BLOCK--
Re: CTX_QUERY - Intermedia
Hi Simon,
Your application must have been developed against an older version of
Oracle/Intermedia. It uses the so-called 2-step query, which isn't supported
anymore in 8.1.6. Only the one-step query is supported now. But in return,
it became blazingly fast, compared to older versions.
You should check your Intermedia manual.
In any case, this isn't a DBI or DBD::Oracle problem.
Brgds,
Mark
- Original Message -
From: "Simon Mailhot" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, June 26, 2001 8:23 PM
Subject: CTX_QUERY - Intermedia
> Hi,
> I'm installing a perl program to a new computer (new installation of the
> DB on another computer, new installation of Apache, ...) . The program is
> using CTX_QUERY but it dosent work. Intermedia is installed with Oracle
> 8.1.6 and I'm using a Apache 1.3.20 server on a RedHat 6.2 system.
>
> Error in the error_log of Apache:
> PLS-00302: component 'HITTAB' must be declared
> ORA-06550: line 3, column 13:
> PL/SQL: Statement ignored (DBD ERROR: OCIStmtExecute)
> Search->lookup: execute failed: ORA-06550: line 3, column 23:
> PLS-00302: component 'CONTAINS' must be declared
> ORA-06550: line 3, column 13:
> PL/SQL: Statement ignored (DBD ERROR: OCIStmtExecute)
>
> The Perl source code look like this:
> my $sth = $self->{DBH}->prepare(q{
> BEGIN
> ctx_query.gettab(CTX_QUERY.HITTAB, ?, 2);
> END;
> });
> my $sth = $self->{DBH}->prepare(q{
> BEGIN
> ctx_query.contains(?, ?, ?);
> END;
> });
>
>
> Can you help me ?
>
> Tanks
>
> Simon
>
>
>
>
RE: Help Please DBI/ODBC/ORACLE
> -Original Message- > From: Mark A. Werner [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, June 26, 2001 5:58 PM > To: '[EMAIL PROTECTED]' > Subject: RE: Help Please DBI/ODBC/ORACLE > > > How do I tie the DBD:ORACLE to the SQL*NET ? > In other works, if I install the client and SQL*NET, how do I tell my You don't need to tell it to use it. You set up a DNS or connect through other connection parameters (see perldoc DBD::Oracle). Ilya > program to use it ? > Currently I just say > > my $dbh = DBI->connect( "dbi:Oracle:SDADATA", "username", "password" > ) > > -Original Message- > From: Steven Baldwin [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, June 26, 2001 5:53 PM > To: Mark A. Werner; '[EMAIL PROTECTED]' > Subject: RE: Help Please DBI/ODBC/ORACLE > > > Couldn't you install the Oracle client on the linux box, and access the > Solaris box via SQL*Net - using DBD::Oracle on the Linux box > exactly as you > do on the Sun box ? > > -Original Message- > From: Mark A. Werner [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, 27 June 2001 7:39 AM > To: '[EMAIL PROTECTED]' > Subject: Help Please DBI/ODBC/ORACLE > > > Hello: > > I know this should bea simple question but I have chased it > through manuals > and FAQ's > and WEB pages for 3 days. > Any help woule be GREATLY appreciatted. > > I have an ORACLE database on a Solaris 2.7 system that I am accessing via > the PERL DBI > inteface Just Fine. > > But I need to be able get to it vai the PERL DBI from a LINUX 7.2 box. > > The perl DBI book from O"REELLY makes it seem like a piece of > cake, just use > DB Proxying. > > But each step in this seem like it requires 2more steps , and so on and so > on till I Lost! > > Here's where I am > > - On LINUX I tied to install DBD::ODBC > > - This requires you to tell it where your ODBC Driver > Manager IS > > > - This requires you specify a driver (which I am > clueless on) > > Have I headed down a wrong path ? > > Is the a SIMPLER, EASIER way to do this ? > > Again, I aplogize in advace for the "basic" nature of the question, but I > have tried > to look everywhere before wrting. > > THX IN Advance > > > ** > This email and any files transmitted with it are confidential and > intended solely for the use of the individual or entity to whom they > are addressed. If you have received this email in error please notify > the system manager. > > This footnote also confirms that this email message has been swept by > MIMEsweeper for the presence of computer viruses. > > www.mimesweeper.com > **
RE: Help Please DBI/ODBC/ORACLE
Why use ODBC on non-windows platform if windows is not involved in the process? Use DBD::Oracle. Install Oracle client on your linux machine and set up DNS to use for connection. Ilya > -Original Message- > From: Mark A. Werner [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, June 26, 2001 5:39 PM > To: '[EMAIL PROTECTED]' > Subject: Help Please DBI/ODBC/ORACLE > > > Hello: > > I know this should bea simple question but I have chased it > through manuals > and FAQ's > and WEB pages for 3 days. > Any help woule be GREATLY appreciatted. > > I have an ORACLE database on a Solaris 2.7 system that I am accessing via > the PERL DBI > inteface Just Fine. > > But I need to be able get to it vai the PERL DBI from a LINUX 7.2 box. > > The perl DBI book from O"REELLY makes it seem like a piece of > cake, just use > DB Proxying. > > But each step in this seem like it requires 2more steps , and so on and so > on till I Lost! > > Here's where I am > > - On LINUX I tied to install DBD::ODBC > > - This requires you to tell it where your ODBC Driver > Manager IS > > > - This requires you specify a driver (which I am > clueless on) > > Have I headed down a wrong path ? > > Is the a SIMPLER, EASIER way to do this ? > > Again, I aplogize in advace for the "basic" nature of the question, but I > have tried > to look everywhere before wrting. > > THX IN Advance
RE: Help Please DBI/ODBC/ORACLE
Mark,
I currently use a connect like this :
my $dbh = DBI -> connect ("dbi:Oracle:", $ENV {ORAPWD}) ;
The environment variable $ORAPWD is a standard Oracle connect string. That
is, either username/password for a 'direct' connection, or
username/password@alias for a SQL*Net connection. You need to have SQL*Net
properly configured on both boxes. That is, an entry in the listener.ora on
the Sun box for your DB sid, and in the tnsnames.ora on the Linux box. Once
the configuration is OK, you need to start the SQL*Net listener on the Sun.
(Apologies if I'm teaching you to suck eggs here).
Hope this helps,
Steve
-Original Message-
From: Mark A. Werner [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 27 June 2001 7:58 AM
To: '[EMAIL PROTECTED]'
Subject: RE: Help Please DBI/ODBC/ORACLE
How do I tie the DBD:ORACLE to the SQL*NET ?
In other works, if I install the client and SQL*NET, how do I tell my
program to use it ?
Currently I just say
my $dbh = DBI->connect( "dbi:Oracle:SDADATA", "username", "password"
)
-Original Message-
From: Steven Baldwin [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 26, 2001 5:53 PM
To: Mark A. Werner; '[EMAIL PROTECTED]'
Subject: RE: Help Please DBI/ODBC/ORACLE
Couldn't you install the Oracle client on the linux box, and access the
Solaris box via SQL*Net - using DBD::Oracle on the Linux box exactly as you
do on the Sun box ?
-Original Message-
From: Mark A. Werner [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 27 June 2001 7:39 AM
To: '[EMAIL PROTECTED]'
Subject: Help Please DBI/ODBC/ORACLE
Hello:
I know this should bea simple question but I have chased it through manuals
and FAQ's
and WEB pages for 3 days.
Any help woule be GREATLY appreciatted.
I have an ORACLE database on a Solaris 2.7 system that I am accessing via
the PERL DBI
inteface Just Fine.
But I need to be able get to it vai the PERL DBI from a LINUX 7.2 box.
The perl DBI book from O"REELLY makes it seem like a piece of cake, just use
DB Proxying.
But each step in this seem like it requires 2more steps , and so on and so
on till I Lost!
Here's where I am
- On LINUX I tied to install DBD::ODBC
- This requires you to tell it where your ODBC Driver
Manager IS
- This requires you specify a driver (which I am
clueless on)
Have I headed down a wrong path ?
Is the a SIMPLER, EASIER way to do this ?
Again, I aplogize in advace for the "basic" nature of the question, but I
have tried
to look everywhere before wrting.
THX IN Advance
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**
Re: Shorthand for $dbh->prepare and $sth->execute?
- Original Message -
From: "Philip Mak" <[EMAIL PROTECTED]>
> > > $rows = $dbh->selectall_hashref("SELECT ...");
> > > for my $row (@{$rows}) {
> > > do something with $row
> > > }
> What's the efficient way of doing this? $sth->fetchrow_hashref?
A traditional for loop.
for ( my $i = 0; $i <= $#$rows; $i++ ) {
do something with %$row->[$i]
}
-Mike
RE: Help Please DBI/ODBC/ORACLE
How do I tie the DBD:ORACLE to the SQL*NET ? In other works, if I install the client and SQL*NET, how do I tell my program to use it ? Currently I just say my $dbh = DBI->connect( "dbi:Oracle:SDADATA", "username", "password" ) -Original Message- From: Steven Baldwin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 26, 2001 5:53 PM To: Mark A. Werner; '[EMAIL PROTECTED]' Subject: RE: Help Please DBI/ODBC/ORACLE Couldn't you install the Oracle client on the linux box, and access the Solaris box via SQL*Net - using DBD::Oracle on the Linux box exactly as you do on the Sun box ? -Original Message- From: Mark A. Werner [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 27 June 2001 7:39 AM To: '[EMAIL PROTECTED]' Subject: Help Please DBI/ODBC/ORACLE Hello: I know this should bea simple question but I have chased it through manuals and FAQ's and WEB pages for 3 days. Any help woule be GREATLY appreciatted. I have an ORACLE database on a Solaris 2.7 system that I am accessing via the PERL DBI inteface Just Fine. But I need to be able get to it vai the PERL DBI from a LINUX 7.2 box. The perl DBI book from O"REELLY makes it seem like a piece of cake, just use DB Proxying. But each step in this seem like it requires 2more steps , and so on and so on till I Lost! Here's where I am - On LINUX I tied to install DBD::ODBC - This requires you to tell it where your ODBC Driver Manager IS - This requires you specify a driver (which I am clueless on) Have I headed down a wrong path ? Is the a SIMPLER, EASIER way to do this ? Again, I aplogize in advace for the "basic" nature of the question, but I have tried to look everywhere before wrting. THX IN Advance ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
RE: Help Please DBI/ODBC/ORACLE
Couldn't you install the Oracle client on the linux box, and access the Solaris box via SQL*Net - using DBD::Oracle on the Linux box exactly as you do on the Sun box ? -Original Message- From: Mark A. Werner [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 27 June 2001 7:39 AM To: '[EMAIL PROTECTED]' Subject: Help Please DBI/ODBC/ORACLE Hello: I know this should bea simple question but I have chased it through manuals and FAQ's and WEB pages for 3 days. Any help woule be GREATLY appreciatted. I have an ORACLE database on a Solaris 2.7 system that I am accessing via the PERL DBI inteface Just Fine. But I need to be able get to it vai the PERL DBI from a LINUX 7.2 box. The perl DBI book from O"REELLY makes it seem like a piece of cake, just use DB Proxying. But each step in this seem like it requires 2more steps , and so on and so on till I Lost! Here's where I am - On LINUX I tied to install DBD::ODBC - This requires you to tell it where your ODBC Driver Manager IS - This requires you specify a driver (which I am clueless on) Have I headed down a wrong path ? Is the a SIMPLER, EASIER way to do this ? Again, I aplogize in advace for the "basic" nature of the question, but I have tried to look everywhere before wrting. THX IN Advance ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
Re: Help Please DBI/ODBC/ORACLE
You may want to look into something like iodbc and Openlink. - Original Message - From: "Mark A. Werner" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, June 27, 2001 7:38 AM Subject: Help Please DBI/ODBC/ORACLE > Hello: > > I know this should bea simple question but I have chased it through manuals > and FAQ's > and WEB pages for 3 days. > Any help woule be GREATLY appreciatted. > > I have an ORACLE database on a Solaris 2.7 system that I am accessing via > the PERL DBI > inteface Just Fine. > > But I need to be able get to it vai the PERL DBI from a LINUX 7.2 box. > > The perl DBI book from O"REELLY makes it seem like a piece of cake, just use > DB Proxying. > > But each step in this seem like it requires 2more steps , and so on and so > on till I Lost! > > Here's where I am > > - On LINUX I tied to install DBD::ODBC > > - This requires you to tell it where your ODBC Driver > Manager IS > > > - This requires you specify a driver (which I am > clueless on) > > Have I headed down a wrong path ? > > Is the a SIMPLER, EASIER way to do this ? > > Again, I aplogize in advace for the "basic" nature of the question, but I > have tried > to look everywhere before wrting. > > THX IN Advance
Help Please DBI/ODBC/ORACLE
Hello: I know this should bea simple question but I have chased it through manuals and FAQ's and WEB pages for 3 days. Any help woule be GREATLY appreciatted. I have an ORACLE database on a Solaris 2.7 system that I am accessing via the PERL DBI inteface Just Fine. But I need to be able get to it vai the PERL DBI from a LINUX 7.2 box. The perl DBI book from O"REELLY makes it seem like a piece of cake, just use DB Proxying. But each step in this seem like it requires 2more steps , and so on and so on till I Lost! Here's where I am - On LINUX I tied to install DBD::ODBC - This requires you to tell it where your ODBC Driver Manager IS - This requires you specify a driver (which I am clueless on) Have I headed down a wrong path ? Is the a SIMPLER, EASIER way to do this ? Again, I aplogize in advace for the "basic" nature of the question, but I have tried to look everywhere before wrting. THX IN Advance
Re: Shorthand for $dbh->prepare and $sth->execute?
Tim Bunce writes: > > Benchmarks are tricky things. They are indeed! Michael
Re: Shorthand for $dbh->prepare and $sth->execute?
On Tue, Jun 26, 2001 at 09:09:36AM -0700, Michael Peppler wrote:
> >
> > $t0 = [gettimeofday];
> > $sth = $dbh->prepare ("SELECT * FROM TEN_MILLION_ROW_TABLE");
> > print [gettimeofday] - $t0;
> >
> > $t0 = [gettimeofday];
> > $sth->execute;
> > # and so on..
> >
> > and definitely, the execute part took longest.. if what you're saying is
> > true, execute would return immediately and let me fetch records at will.
> > That does NOT happen. execute takes forever, THEN i can fetch records.
>
> That's interesting - shouldn't such a select return *very* quickly? It
> doesn't have to build a complex query plan, it just needs to follow
> the page or row chain and return the rows in any order that it wants
> to...
One other point about DBD::Oracle is that by default it sets up a row
cache and the initial 'execute and fetch' single Oracle OCI API call
will actually 'execute and fetch enough rows to fill the row cache'.
Benchmarks are tricky things.
Tim.
Re: Shorthand for $dbh->prepare and $sth->execute?
On Tue, Jun 26, 2001 at 11:48:34PM +0800, Orlando Andico wrote: > > and definitely, the execute part took longest.. if what you're saying is > true, execute would return immediately and let me fetch records at will. > That does NOT happen. execute takes forever, THEN i can fetch records. That's probably because DBD::Oracle uses the 'execute and fetch' OCI call that combines an execute with a fetch of the first row. That's what most people want most of the time and reduces the round-trips to the server. Especially when selecting a small number of rows. Especially 1. Tim.
Re: Shorthand for $dbh->prepare and $sth->execute?
On Tue, Jun 26, 2001 at 10:50:31PM +0800, Orlando Andico wrote:
>
> This may be true, but as I understand it (vaguely), in Oracle, when you do
> the execute it (by default) runs the query through and fetches all the
> rows. Although this behavior can be changed using an optimizer hint
> ("return rows as quickly as possible") which I forgot right now..
Reaility is far more subtle and complex than that.
Tim.
Re: getting a list of databases
On Mon, Jun 25, 2001 at 08:32:42PM -0700, Jonathan Leffler wrote: > Tim Bunce wrote: > > > Changes in DBI 1.16,30th May 2001 > > > > Added note to data_sources() method docs that some drivers may > > require a connected database handle to be supplied as an attribute. > > With all due respect, Tim, this is completely ducking the issue. Yes, after a >fashion it > resolves the problem, rather like saying "Under-age kids should not drink in pubs" >solves a > problem. It doesn't really cut to the meat of the issue though -- the data_sources() > method is not generally useful because the interface is incomplete. Portable applications rarely need to use data_sources(). The DBI always has to steer a course between the important and the impractical. The correct 'complete' interface is probably the ODBC SQLBrowseConnect() but I don't think you'd thank me for mandating that. Tim.
CTX_QUERY - Intermedia
Hi,
I'm installing a perl program to a new computer (new installation of the
DB on another computer, new installation of Apache, ...) . The program is
using CTX_QUERY but it dosent work. Intermedia is installed with Oracle
8.1.6 and I'm using a Apache 1.3.20 server on a RedHat 6.2 system.
Error in the error_log of Apache:
PLS-00302: component 'HITTAB' must be declared
ORA-06550: line 3, column 13:
PL/SQL: Statement ignored (DBD ERROR: OCIStmtExecute)
Search->lookup: execute failed: ORA-06550: line 3, column 23:
PLS-00302: component 'CONTAINS' must be declared
ORA-06550: line 3, column 13:
PL/SQL: Statement ignored (DBD ERROR: OCIStmtExecute)
The Perl source code look like this:
my $sth = $self->{DBH}->prepare(q{
BEGIN
ctx_query.gettab(CTX_QUERY.HITTAB, ?, 2);
END;
});
my $sth = $self->{DBH}->prepare(q{
BEGIN
ctx_query.contains(?, ?, ?);
END;
});
Can you help me ?
Tanks
Simon
disconnect from an active statement handle
I'm getting the error:
DBI::db=HASH(0xf0130)->disconnect invalidates 1 active statement handle
(either destroy statement handles or call finish on them before
disconnecting)...
What's interesting is the dbh handle has AutoCommit enabled and the lone
sth handle on that dbh is an insert... so I do not believe calling
finish() would do anything for me here.
In my object, the method _prepare_sql() is called by the object
constructor. In that method, I create both the dbh and sth and assign
them to an object attribute:
sub _prepare_sql
{
my $self = shift;
my $dbconn = "dbi:Sybase:server=$self->{server}";
my %attrs = (RaiseError=>0, PrintError=>1, ChopBlanks=>1);
$self->{dbh_fa_insert} = DBI->connect($dbconn, $self->{user},
$self->{passwd}, \%attrs);
$self->{dbh_fa_insert}->{AutoCommit} = 1;
.
$self->{fa_ins} = $self->{dbh_fa_insert}->prepare( <{database}..sa_fund_alias
(source_id, fund_alias_name)
values (?, ?)
EOF
);
return(0);
}
In another method, I open an input file and loop through each line. As
part of the datamapping, the above insert statement may be called:
sub process_file
{
my $self = shift;
...
while (defined(my $record = ))
{
...
if (...something...)
{
$self->{fa_ins}->execute($source_id, $par_fund_group);
return($DBI::errstr) if ($DBI::err);
}
...
}
$self->{dbh_fa_ins}->disconnect();
return($rc);
}
In all, I have 4 separate dbh handles that are alive and 3 of them are
used only to handle a single insert statement. 2 of the 3 have AutoCommit
enabled. This handle, however, is the only one that complains about there
being an active statement handle at the time of disconnect. If AutoCommit
is enabled and there's only the one insert statement, how could I be
getting this error.
I'm using Perl 5.00404, DBI 1.14 & DBD::Sybase 0.91 on Solaris 2.6.
Thank you,
Curt Crandall
RE: RE: Shorthand for $dbh->prepare and $sth->execute?
I'm an Oracle guy that also read the docs.
Processing SQL Statements
One of the most common tasks of an OCI program is to accept and process SQL
statements. This section outlines the specific steps involved in processing
SQL.
Once you have allocated the necessary handles and attached to a server, the
basic steps in processing a SQL statement are the following, as illustrated
in Figure 4-1, "Steps In Processing SQL Statements":
Prepare. Define an application request using OCIStmtPrepare().
Bind. For DML statements and queries with input variables, perform one or
more bind calls using OCIBindByPos(), OCIBindByName(), OCIBindObject(),
OCIBindDynamic() or OCIBindArrayOfStruct() to bind the address of each input
variable (or PL/SQL output variable) or array to each placeholder in the
statement.
Execute. Call OCIStmtExecute() to execute the statement. For DDL statements,
no further steps are necessary.
Describe. Describe the select-list items, if necessary, using OCIParamGet()
and OCIAttrGet(). This is an optional step; it is not required if the number
of select-list items and the attributes of each item (such as its length and
datatype) are known at compile time.
Define. For queries, perform one or more define calls to OCIDefineByPos(),
OCIDefineObject(), OCIDefineDynamic(), or OCIDefineArrayOfStruct() to define
an output variable for each select-list item in the SQL statement. Note that
you do not use a define call to define the output variables in an anonymous
PL/SQL block. You have done this when you have bound the data.
Fetch. For queries, call OCIStmtFetch() to fetch the results of the query.
-Original Message-
From: Ali Zaidi [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 26, 2001 12:26 PM
To: [EMAIL PROTECTED]
Subject: Re: RE: Shorthand for $dbh->prepare and $sth->execute?
Being an Oracle guy i can tell for sure the steps taken by
Oracle
Prepare: In this step Oracle parses your query and prepares the
execution plan.
Execute: In this step Oracle gets the data returned by your
query into its memory area
Fetch: This is when the data is returned to the user process.
On Tue, 26 Jun 2001, Michael Peppler
([EMAIL PROTECTED]) wrote:
> Orlando Andico writes:
> > On Tue, 26 Jun 2001, Marcotullio, Angelo wrote:
> > ..
> > > In Oracle, the $sth->execute() builds the cursor and
waits for the
> program
> > > to start fetching records. The hints don't change this
behavior,
> just the
> > > access plan.
> > ..
> >
> > Hrmmm... that's not what I've seen. I wrote some code
which used
> > Time::HiRes to measure the time taken at each step..
something like
> this:
> >
> > $t0 = [gettimeofday];
> > $sth = $dbh->prepare ("SELECT * FROM
TEN_MILLION_ROW_TABLE");
> > print [gettimeofday] - $t0;
> >
> > $t0 = [gettimeofday];
> > $sth->execute;
> > # and so on..
> >
> > and definitely, the execute part took longest.. if what
you're saying
> is
> > true, execute would return immediately and let me fetch
records at
> will.
> > That does NOT happen. execute takes forever, THEN i can
fetch
> records.
>
> That's interesting - shouldn't such a select return *very*
quickly? It
> doesn't have to build a complex query plan, it just needs to
follow
> the page or row chain and return the rows in any order that
it wants
> to...
>
> Michael
> --
> Michael Peppler - Data Migrations Inc. - [EMAIL PROTECTED]
> http://www.mbay.net/~mpeppler - [EMAIL PROTECTED]
> International Sybase User Group - http://www.isug.com
> *Looking for new project to tackle starting 8/1/01*
>
>
Get your own "800" number
Voicemail, fax, email, and a lot more
http://www.ureach.com/reg/tag
RE: Shorthand for $dbh->prepare and $sth->execute?
-Original Message-
From: Michael Peppler
To: Orlando Andico
Cc: Marcotullio, Angelo ; ''[EMAIL PROTECTED] ' '
Sent: 06/26/2001 10:09 AM
Subject: RE: Shorthand for $dbh->prepare and $sth->execute?
Orlando Andico writes:
> On Tue, 26 Jun 2001, Marcotullio, Angelo wrote:
> ..
> > In Oracle, the $sth->execute() builds the cursor and waits for the
program
> > to start fetching records. The hints don't change this behavior,
just the
> > access plan.
> ..
>
> Hrmmm... that's not what I've seen. I wrote some code which used
> Time::HiRes to measure the time taken at each step.. something like
this:
>
> $t0 = [gettimeofday];
> $sth = $dbh->prepare ("SELECT * FROM TEN_MILLION_ROW_TABLE");
> print [gettimeofday] - $t0;
>
> $t0 = [gettimeofday];
> $sth->execute;
> # and so on..
>
> and definitely, the execute part took longest.. if what you're saying
is
> true, execute would return immediately and let me fetch records at
will.
> That does NOT happen. execute takes forever, THEN i can fetch
records.
>That's interesting - shouldn't such a select return *very* quickly? It
>doesn't have to build a complex query plan, it just needs to follow
>the page or row chain and return the rows in any order that it wants
>to...
I would say the same, but some here are arguing that the whole query is
fetched into an Oracle cache before execute returns, so much for speed and
efficiency.
Ilya
>Michael
--
Michael Peppler - Data Migrations Inc. - [EMAIL PROTECTED]
http://www.mbay.net/~mpeppler - [EMAIL PROTECTED]
International Sybase User Group - http://www.isug.com
*Looking for new project to tackle starting 8/1/01*
RE: RE: Shorthand for $dbh->prepare and $sth->execute?
-Original Message-
From: Ali Zaidi
To: [EMAIL PROTECTED]
Sent: 06/26/2001 10:26 AM
Subject: Re: RE: Shorthand for $dbh->prepare and $sth->execute?
>Being an Oracle guy i can tell for sure the steps taken by
>Oracle
>Prepare: In this step Oracle parses your query and prepares the
>execution plan.
>Execute: In this step Oracle gets the data returned by your
>query into its memory area
All the data? I really doubt it, though I never tested myself. This would
seems very inefficient since if it fetches everything at execute, what
happens if you want to abort you query along the way. What's the point of
fetching everything at once, not knowing how many rows will be fetched. You
can probably set the cache to do that, but I would imaging that it caches a
reasonable amount of data to optimize the fetches, but not all the data
returned from a query.
Ilya
>Fetch: This is when the data is returned to the user process.
On Tue, 26 Jun 2001, Michael Peppler
([EMAIL PROTECTED]) wrote:
> Orlando Andico writes:
> > On Tue, 26 Jun 2001, Marcotullio, Angelo wrote:
> > ..
> > > In Oracle, the $sth->execute() builds the cursor and
waits for the
> program
> > > to start fetching records. The hints don't change this
behavior,
> just the
> > > access plan.
> > ..
> >
> > Hrmmm... that's not what I've seen. I wrote some code
which used
> > Time::HiRes to measure the time taken at each step..
something like
> this:
> >
> > $t0 = [gettimeofday];
> > $sth = $dbh->prepare ("SELECT * FROM
TEN_MILLION_ROW_TABLE");
> > print [gettimeofday] - $t0;
> >
> > $t0 = [gettimeofday];
> > $sth->execute;
> > # and so on..
> >
> > and definitely, the execute part took longest.. if what
you're saying
> is
> > true, execute would return immediately and let me fetch
records at
> will.
> > That does NOT happen. execute takes forever, THEN i can
fetch
> records.
>
> That's interesting - shouldn't such a select return *very*
quickly? It
> doesn't have to build a complex query plan, it just needs to
follow
> the page or row chain and return the rows in any order that
it wants
> to...
>
> Michael
> --
> Michael Peppler - Data Migrations Inc. - [EMAIL PROTECTED]
> http://www.mbay.net/~mpeppler - [EMAIL PROTECTED]
> International Sybase User Group - http://www.isug.com
> *Looking for new project to tackle starting 8/1/01*
>
>
Get your own "800" number
Voicemail, fax, email, and a lot more
http://www.ureach.com/reg/tag
RE: Shorthand for $dbh->prepare and $sth->execute?
You can use foreach (0..$#rows)
Ilya
-Original Message-
From: Philip Mak
To: Michael A Mayo
Cc: [EMAIL PROTECTED]
Sent: 06/26/2001 10:36 AM
Subject: Re: Shorthand for $dbh->prepare and $sth->execute?
On Tue, 26 Jun 2001, Michael A Mayo wrote:
> > Is this reasonably efficient for tables that have thousands of rows?
e.g.
> >
> > $rows = $dbh->selectall_hashref("SELECT ...");
> > for my $row (@{$rows}) {
> > do something with $row
> > }
>
> You may know this already, but the way you wrote this is particularly
> inefficent because it creates a temporary copy of all the data in an
array.
What's the efficient way of doing this? $sth->fetchrow_hashref?
-Philip Mak ([EMAIL PROTECTED])
Re: Shorthand for $dbh->prepare and $sth->execute?
On Tue, 26 Jun 2001, Michael A Mayo wrote:
> > Is this reasonably efficient for tables that have thousands of rows? e.g.
> >
> > $rows = $dbh->selectall_hashref("SELECT ...");
> > for my $row (@{$rows}) {
> > do something with $row
> > }
>
> You may know this already, but the way you wrote this is particularly
> inefficent because it creates a temporary copy of all the data in an array.
What's the efficient way of doing this? $sth->fetchrow_hashref?
-Philip Mak ([EMAIL PROTECTED])
Re: Shorthand for $dbh->prepare and $sth->execute?
- Original Message -
From: "Philip Mak" <[EMAIL PROTECTED]>
Sent: Tuesday, June 26, 2001 10:30 AM
> Is this reasonably efficient for tables that have thousands of rows? e.g.
>
> $rows = $dbh->selectall_hashref("SELECT ...");
> for my $row (@{$rows}) {
> do something with $row
> }
You may know this already, but the way you wrote this is particularly
inefficent because it creates a temporary copy of all the data in an array.
-Mike
Re: RE: Shorthand for $dbh->prepare and $sth->execute?
Being an Oracle guy i can tell for sure the steps taken by
Oracle
Prepare: In this step Oracle parses your query and prepares the
execution plan.
Execute: In this step Oracle gets the data returned by your
query into its memory area
Fetch: This is when the data is returned to the user process.
On Tue, 26 Jun 2001, Michael Peppler
([EMAIL PROTECTED]) wrote:
> Orlando Andico writes:
> > On Tue, 26 Jun 2001, Marcotullio, Angelo wrote:
> > ..
> > > In Oracle, the $sth->execute() builds the cursor and
waits for the
> program
> > > to start fetching records. The hints don't change this
behavior,
> just the
> > > access plan.
> > ..
> >
> > Hrmmm... that's not what I've seen. I wrote some code
which used
> > Time::HiRes to measure the time taken at each step..
something like
> this:
> >
> > $t0 = [gettimeofday];
> > $sth = $dbh->prepare ("SELECT * FROM
TEN_MILLION_ROW_TABLE");
> > print [gettimeofday] - $t0;
> >
> > $t0 = [gettimeofday];
> > $sth->execute;
> > # and so on..
> >
> > and definitely, the execute part took longest.. if what
you're saying
> is
> > true, execute would return immediately and let me fetch
records at
> will.
> > That does NOT happen. execute takes forever, THEN i can
fetch
> records.
>
> That's interesting - shouldn't such a select return *very*
quickly? It
> doesn't have to build a complex query plan, it just needs to
follow
> the page or row chain and return the rows in any order that
it wants
> to...
>
> Michael
> --
> Michael Peppler - Data Migrations Inc. - [EMAIL PROTECTED]
> http://www.mbay.net/~mpeppler - [EMAIL PROTECTED]
> International Sybase User Group - http://www.isug.com
> *Looking for new project to tackle starting 8/1/01*
>
>
Get your own "800" number
Voicemail, fax, email, and a lot more
http://www.ureach.com/reg/tag
Re: Shorthand for $dbh->prepare and $sth->execute?
Oracle seems to do 90% of the work before execute() returns, but it still
hasn't necessarily counted all the rows. The big difference between
fetchall* and fetching a row at a time is that until you fetch them, the
rows are in Oracle's memory and disk space, not your program's memory. That
is probably not a big difference for a few thousand rows, but when you are
processing larger sets it can be significant.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
- Original Message -
From: "Orlando Andico" <[EMAIL PROTECTED]>
To: "Marcotullio, Angelo " <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, June 26, 2001 08:48
Subject: RE: Shorthand for $dbh->prepare and $sth->execute?
> On Tue, 26 Jun 2001, Marcotullio, Angelo wrote:
> ..
> > In Oracle, the $sth->execute() builds the cursor and waits for the
program
> > to start fetching records. The hints don't change this behavior, just
the
> > access plan.
> ..
>
> Hrmmm... that's not what I've seen. I wrote some code which used
> Time::HiRes to measure the time taken at each step.. something like this:
>
> $t0 = [gettimeofday];
> $sth = $dbh->prepare ("SELECT * FROM TEN_MILLION_ROW_TABLE");
> print [gettimeofday] - $t0;
>
> $t0 = [gettimeofday];
> $sth->execute;
> # and so on..
>
> and definitely, the execute part took longest.. if what you're saying is
> true, execute would return immediately and let me fetch records at will.
> That does NOT happen. execute takes forever, THEN i can fetch records.
>
> note that my select is brain-dead simple, uses no indexes, etc.. =)
> of course this is an extreme case.. its not often one would want to fetch
> every row from a 10-million-row table..
RE: Shorthand for $dbh->prepare and $sth->execute?
See Perl DBI, page 111.
"In response to the execute() earlier, the database may do no more than set
a pointer, known as a cursor, to just above the first row of the table.
To extract the results data from the database, you need to explicitly fetch
them. This is the third stage in the cycle." (prepare, execute, fetch)
This is what I see in my Oracle environments with many 100+ million row
tables.
Do you see i/o on the datafiles during your execute()?
-Original Message-
From: Orlando Andico [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 26, 2001 11:49 AM
To: Marcotullio, Angelo
Cc: ''[EMAIL PROTECTED] ' '
Subject: RE: Shorthand for $dbh->prepare and $sth->execute?
On Tue, 26 Jun 2001, Marcotullio, Angelo wrote:
..
> In Oracle, the $sth->execute() builds the cursor and waits for the program
> to start fetching records. The hints don't change this behavior, just the
> access plan.
..
Hrmmm... that's not what I've seen. I wrote some code which used
Time::HiRes to measure the time taken at each step.. something like this:
$t0 = [gettimeofday];
$sth = $dbh->prepare ("SELECT * FROM TEN_MILLION_ROW_TABLE");
print [gettimeofday] - $t0;
$t0 = [gettimeofday];
$sth->execute;
# and so on..
and definitely, the execute part took longest.. if what you're saying is
true, execute would return immediately and let me fetch records at will.
That does NOT happen. execute takes forever, THEN i can fetch records.
note that my select is brain-dead simple, uses no indexes, etc.. =)
of course this is an extreme case.. its not often one would want to fetch
every row from a 10-million-row table..
--
Orlando Andico <[EMAIL PROTECTED]>
Mosaic Communications, Inc.
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GE d(-) s: a-25 C UBLSI$ P+++ L+++> E- W++ N(+)
o K? w O-- M- !V PS(++) PE- Y PGP-- t(+)@ 5(+) X++@ R(+) tv@
b++ DI++ G e++@ h--(*) r% y+
--END GEEK CODE BLOCK--
RE: Shorthand for $dbh->prepare and $sth->execute?
Orlando Andico writes:
> On Tue, 26 Jun 2001, Marcotullio, Angelo wrote:
> ..
> > In Oracle, the $sth->execute() builds the cursor and waits for the program
> > to start fetching records. The hints don't change this behavior, just the
> > access plan.
> ..
>
> Hrmmm... that's not what I've seen. I wrote some code which used
> Time::HiRes to measure the time taken at each step.. something like this:
>
> $t0 = [gettimeofday];
> $sth = $dbh->prepare ("SELECT * FROM TEN_MILLION_ROW_TABLE");
> print [gettimeofday] - $t0;
>
> $t0 = [gettimeofday];
> $sth->execute;
> # and so on..
>
> and definitely, the execute part took longest.. if what you're saying is
> true, execute would return immediately and let me fetch records at will.
> That does NOT happen. execute takes forever, THEN i can fetch records.
That's interesting - shouldn't such a select return *very* quickly? It
doesn't have to build a complex query plan, it just needs to follow
the page or row chain and return the rows in any order that it wants
to...
Michael
--
Michael Peppler - Data Migrations Inc. - [EMAIL PROTECTED]
http://www.mbay.net/~mpeppler - [EMAIL PROTECTED]
International Sybase User Group - http://www.isug.com
*Looking for new project to tackle starting 8/1/01*
Re: Shorthand for $dbh->prepare and $sth->execute?
On Tue, Jun 26, 2001 at 11:48:34PM +0800, Orlando Andico wrote:
> On Tue, 26 Jun 2001, Marcotullio, Angelo wrote:
> ..
> > In Oracle, the $sth->execute() builds the cursor and waits for the program
> > to start fetching records. The hints don't change this behavior, just the
> > access plan.
> ..
>
> Hrmmm... that's not what I've seen. I wrote some code which used
> Time::HiRes to measure the time taken at each step.. something like this:
>
> $t0 = [gettimeofday];
> $sth = $dbh->prepare ("SELECT * FROM TEN_MILLION_ROW_TABLE");
> print [gettimeofday] - $t0;
>
> $t0 = [gettimeofday];
> $sth->execute;
> # and so on..
>
> and definitely, the execute part took longest.. if what you're saying is
> true, execute would return immediately and let me fetch records at will.
> That does NOT happen. execute takes forever, THEN i can fetch records.
Nope, that's not what he's saying.
the total time taken for prepare, execute and all the fetches is lower
under the ALL_ROWS hint, than it is under the FIRST_ROWS. But there
are no guarantees about when the time is going to be occupied (but,
under FIRST_ROWS, the time taken for prepare, execute and *the first
fetch* is lower).
--
T.
RE: Shorthand for $dbh->prepare and $sth->execute?
On Tue, 26 Jun 2001, Marcotullio, Angelo wrote:
..
> In Oracle, the $sth->execute() builds the cursor and waits for the program
> to start fetching records. The hints don't change this behavior, just the
> access plan.
..
Hrmmm... that's not what I've seen. I wrote some code which used
Time::HiRes to measure the time taken at each step.. something like this:
$t0 = [gettimeofday];
$sth = $dbh->prepare ("SELECT * FROM TEN_MILLION_ROW_TABLE");
print [gettimeofday] - $t0;
$t0 = [gettimeofday];
$sth->execute;
# and so on..
and definitely, the execute part took longest.. if what you're saying is
true, execute would return immediately and let me fetch records at will.
That does NOT happen. execute takes forever, THEN i can fetch records.
note that my select is brain-dead simple, uses no indexes, etc.. =)
of course this is an extreme case.. its not often one would want to fetch
every row from a 10-million-row table..
--
Orlando Andico <[EMAIL PROTECTED]>
Mosaic Communications, Inc.
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GE d(-) s: a-25 C UBLSI$ P+++ L+++> E- W++ N(+)
o K? w O-- M- !V PS(++) PE- Y PGP-- t(+)@ 5(+) X++@ R(+) tv@
b++ DI++ G e++@ h--(*) r% y+
--END GEEK CODE BLOCK--
RE: Shorthand for $dbh->prepare and $sth->execute?
Orlando Wrote: This may be true, but as I understand it (vaguely), in
Oracle, when you do
the execute it (by default) runs the query through and fetches all the rows.
In Oracle, the $sth->execute() builds the cursor and waits for the program
to start fetching records. The hints don't change this behavior, just the
access plan.
ALL_ROWS Hint
The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a
statement block with a goal of best throughput (that is, minimum total
resource consumption).
FIRST_ROWS Hint
The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a
statement block with a goal of best response time (minimum resource usage to
return first row).
This hint causes the optimizer to make the following choices:
If an index scan is available, then the optimizer may choose it over a full
table scan.
If an index scan is available, then the optimizer may choose a nested loops
join over a sort-merge join whenever the associated table is the potential
inner table of the nested loops.
If an index scan is made available by an ORDER BY clause, then the optimizer
may choose it to avoid a sort operation.
-Original Message-
From: Orlando Andico [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 26, 2001 10:51 AM
To: Sterin, Ilya
Cc: 'Philip Mak '; 'Steven Lembark '; ''[EMAIL PROTECTED] ' '
Subject: RE: Shorthand for $dbh->prepare and $sth->execute?
On Tue, 26 Jun 2001, Sterin, Ilya wrote:
..
> Right. The first will fetch all rows, where the second can be stopped at
> any time.
..
> versus:
>
> $sth = $dbh->prepare("SELECT ...");
> $sth->execute;
> while ($row = $sth->fetchrow_hashref) {
> do something with $row
> }
This may be true, but as I understand it (vaguely), in Oracle, when you do
the execute it (by default) runs the query through and fetches all the
rows. Although this behavior can be changed using an optimizer hint
("return rows as quickly as possible") which I forgot right now..
I'm not sure if this is correct, btw. It's just that I've noticed when you
do large SELECTs in Oracle, the execute takes forever. When the execute
completes, a torrent of data comes in. So my conclusion that Oracle
executes the whole query first..
--
Orlando Andico <[EMAIL PROTECTED]>
Mosaic Communications, Inc.
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GE d(-) s: a-25 C UBLSI$ P+++ L+++> E- W++ N(+)
o K? w O-- M- !V PS(++) PE- Y PGP-- t(+)@ 5(+) X++@ R(+) tv@
b++ DI++ G e++@ h--(*) r% y+
--END GEEK CODE BLOCK--
RE: Shorthand for $dbh->prepare and $sth->execute?
> Have you ever tried selecting about a thousand or so rows? I just wonder > if it still would work, since the db driver really can't know how many > rows it fetched until they are all fetched. As I mentioned some drivers > fetch so many rows into a cache for better efficiency, but I don't know > how DBD::mysql does it since I rarelly use it, but I wonder if that's the > case for you. I have code that regularly has to select 1000's of rows from oracle -- though I normally use fetchall_arrayref w/ prepared queries. There are quite a few cases where preparing queries isn't all that helpful, however (e.g., when the table must be chosen at runtime). Proably need to benchmark what the system and particular config of mysql can handle to find out for sure...
ANNOUNCE: CGI::Application 2.0
Version 2.0 of CGI::Application is now available via CPAN! Download site for CGI::Application: http://www.cpan.org/authors/id/J/JE/JERLBAUM/ CHANGES SINCE VERSION 1.31: - Added ability to set mode_param() to use a call-back instance method (specified by subref) instead of a CGI parameter. - HTML::Template is now only loaded if load_tmpl() is called. (Thanks to Stephen Howard for the two preceding ideas!) - Run-modes may now return scalar-refs in addition to scalars. - Added new run-mode of last resort: "AUTOLOAD". See POD for usage. - Updated MAJOR REVISION number to 2 -- new functionality deserves it. Read the recent "Using CGI::Application" article on Perl.com for an overview of the module and its usage: http://www.perl.com/pub/2001/06/05/cgi.html CGI::Application is intended to make it easier to create sophisticated, reusable web-based applications. This module implements a methodology which, if followed, will make your web software easier to design, easier to document, easier to write, and easier to evolve. CGI::Application builds on standard, non-proprietary technologies and techniques, such as the Common Gateway Interface and Lincoln D. Stein's excellent CGI.pm module. CGI::Application judiciously avoids employing technologies and techniques which would bind a developer to any one set of tools, operating system or web server. The guiding philosophy behind CGI::Application is that a web-based application can be organized into a specific set of "Run-Modes." Each Run-Mode is roughly analogous to a single screen (a form, some output, etc). All the Run-Modes are managed by a single "Application Module" which is a Perl module. In your web server's document space there is an "Instance Script" which is called by the web server as a CGI (or an Apache::Registry script if you're using Apache + mod_perl). CGI::Application is an Object-Oriented Perl module which implements an Abstract Class. It is not intended that this package be instantiated directly. Instead, it is intended that your Application Module will be implemented as a Sub-Class of CGI::Application. If you have any questions, comments, bug reports or feature suggestions, post them to the support mailing list! To join the mailing list, simply send a blank message to "[EMAIL PROTECTED]". -- +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ Jesse Erlbaum ... CTO [EMAIL PROTECTED] . Vanguard Media v: 212.242.5317 x115 .. New York City +-+-+-+-+-+- http://www.vm.com/ +-+-+-+-+-+-+
RE: Shorthand for $dbh->prepare and $sth->execute?
-Original Message-
From: Orlando Andico
To: Sterin, Ilya
Cc: 'Philip Mak '; 'Steven Lembark '; ''[EMAIL PROTECTED] ' '
Sent: 06/26/2001 8:50 AM
Subject: RE: Shorthand for $dbh->prepare and $sth->execute?
On Tue, 26 Jun 2001, Sterin, Ilya wrote:
..
> Right. The first will fetch all rows, where the second can be stopped
at
> any time.
..
> versus:
>
> $sth = $dbh->prepare("SELECT ...");
> $sth->execute;
> while ($row = $sth->fetchrow_hashref) {
> do something with $row
> }
>This may be true, but as I understand it (vaguely), in Oracle, when you
>do
>the execute it (by default) runs the query through and fetches all the
>rows. Although this behavior can be changed using an optimizer hint
>("return rows as quickly as possible") which I forgot right now..
>I'm not sure if this is correct, btw. It's just that I've noticed when
>you
>do large SELECTs in Oracle, the execute takes forever. When the execute
>completes, a torrent of data comes in. So my conclusion that Oracle
>executes the whole query first..
Hmmm. I wonder than why rows() doesn't work (in my case) until everything
is fetched? You are probably right, that you can optimize it's operation.
Ilya
--
Orlando Andico <[EMAIL PROTECTED]>
Mosaic Communications, Inc.
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GE d(-) s: a-25 C UBLSI$ P+++ L+++> E- W++ N(+)
o K? w O-- M- !V PS(++) PE- Y PGP-- t(+)@ 5(+) X++@ R(+) tv@
b++ DI++ G e++@ h--(*) r% y+
--END GEEK CODE BLOCK--
RE: Shorthand for $dbh->prepare and $sth->execute?
On Tue, 26 Jun 2001, Sterin, Ilya wrote:
..
> Right. The first will fetch all rows, where the second can be stopped at
> any time.
..
> versus:
>
> $sth = $dbh->prepare("SELECT ...");
> $sth->execute;
> while ($row = $sth->fetchrow_hashref) {
> do something with $row
> }
This may be true, but as I understand it (vaguely), in Oracle, when you do
the execute it (by default) runs the query through and fetches all the
rows. Although this behavior can be changed using an optimizer hint
("return rows as quickly as possible") which I forgot right now..
I'm not sure if this is correct, btw. It's just that I've noticed when you
do large SELECTs in Oracle, the execute takes forever. When the execute
completes, a torrent of data comes in. So my conclusion that Oracle
executes the whole query first..
--
Orlando Andico <[EMAIL PROTECTED]>
Mosaic Communications, Inc.
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GE d(-) s: a-25 C UBLSI$ P+++ L+++> E- W++ N(+)
o K? w O-- M- !V PS(++) PE- Y PGP-- t(+)@ 5(+) X++@ R(+) tv@
b++ DI++ G e++@ h--(*) r% y+
--END GEEK CODE BLOCK--
RE: Shorthand for $dbh->prepare and $sth->execute?
Right. The first will fetch all rows, where the second can be stopped at
any time.
Ilya Sterin
-Original Message-
From: Philip Mak
To: Steven Lembark
Cc: '[EMAIL PROTECTED] '
Sent: 06/26/2001 8:30 AM
Subject: RE: Shorthand for $dbh->prepare and $sth->execute?
On Tue, 26 Jun 2001, Steven Lembark wrote:
> > What about SELECT statements, though? The selectrow_array functions
aren't
> > adequate for this, because they can only be used to return the first
row.
>
> The last version of DBI came with "selectall_*", no?
Is this reasonably efficient for tables that have thousands of rows?
e.g.
$rows = $dbh->selectall_hashref("SELECT ...");
for my $row (@{$rows}) {
do something with $row
}
versus:
$sth = $dbh->prepare("SELECT ...");
$sth->execute;
while ($row = $sth->fetchrow_hashref) {
do something with $row
}
Doesn't the first way have to retrieve all the results of the SELECT
query, but the second way has the option of stopping part way through if
it finds out that it doesn't need anymore data? (Or am I
misunderstanding
how this works?)
-Philip Mak ([EMAIL PROTECTED])
RE: Shorthand for $dbh->prepare and $sth->execute?
Again this depends on the database, so for writting portable code, I would avoid this. Have you ever tried selecting about a thousand or so rows? I just wonder if it still would work, since the db driver really can't know how many rows it fetched until they are all fetched. As I mentioned some drivers fetch so many rows into a cache for better efficiency, but I don't know how DBD::mysql does it since I rarelly use it, but I wonder if that's the case for you. Also you haven't convinced me that such a method, select_execute(), would be helpful. Why? How would you use placeholders? That's the real advantage of select() and execute(). Ilya -Original Message- From: Philip Mak To: Sterin, Ilya Cc: '[EMAIL PROTECTED] ' Sent: 06/26/2001 8:18 AM Subject: RE: Shorthand for $dbh->prepare and $sth->execute? On Tue, 26 Jun 2001, Sterin, Ilya wrote: > Why? You can prepare and execute with do() for update and insert > statements. What about SELECT statements, though? The selectrow_array functions aren't adequate for this, because they can only be used to return the first row. > $sth->rows does not guarantee and in most casese will not return the number > of rows that the select statement returns. In most cases it will return the > rows fetched so far, or depending on the db driver the rows fetched in the > cache. You should either use "select count(*) from ..." query to get the > number of rows or use a counter. I heard about this before, but it does not seem to be true for MySQL; I've never had trouble using $sth->rows under MySQL. -Philip Mak ([EMAIL PROTECTED])
Re: getting a list of databases
Perhaps what would be useful is a set of comliance attributes that
describes which "optional" DBI interfaces are supported and to what extent.
Jonathan Leffler wrote:
> Tim Bunce wrote:
>
>
>> Changes in DBI 1.16,30th May 2001
>>
>> Added note to data_sources() method docs that some drivers may
>> require a connected database handle to be supplied as an attribute.
>
>
> With all due respect, Tim, this is completely ducking the issue. Yes, after a
>fashion it
> resolves the problem, rather like saying "Under-age kids should not drink in pubs"
>solves a
> problem. It doesn't really cut to the meat of the issue though -- the data_sources()
> method is not generally useful because the interface is incomplete.
>
>
>
>> On Sun, Jun 10, 2001 at 03:36:12PM -0700, Jonathan Leffler wrote:
>>
>>> Michael Peppler wrote:
>>>
>>>
Curt Russell Crandall writes:
> I didn't have an Informix DB available at the time, but yes it does return
> a list of dbs with data_sources... however, Sybase does not as well as
> many others.
DBD::Sybase could be made to return the list of known servers (from
the interfaces file), but not the list of databases, as that requires
connecting to each of the servers in the interfaces file (and we need
to know user/pwd for each server, etc - gets a little hairy).
>>>
>>> We had a discussion about this on the dbi-users mailing list during February 2000,
>in
>>> the run-up to the release of DBI 1.14. Tim promised to look into it, but either
>came
>>> up with a negative decision without notifying us or (much more likely) overlooked a
>>> comparatively minor feature that only impacts a few database servers...
>>>
>>> == quote 1 ==
>>> Subject: Re: DBI->data_sources() - assumption of anonymous user is a bug
>>> Date: Wed, 09 Feb 2000 20:41:54 -0800
>>> From: Jonathan Leffler <[EMAIL PROTECTED]>
>>> To: Randall Whitman <[EMAIL PROTECTED]>
>>> CC: [EMAIL PROTECTED]
>>>
>>> Randall Whitman wrote:
>>>
>>>
We consider the assumption of an anonymous user to be a bug --
that is, the behavior that
DBI->data_sources("mysql");
returns an empty array because we do not have an anonymous
user in MySQL.
In other words, it should be possible to do one of the following:
@databases = DBI->data_sources("mysql", "USER", "PASSWORD");## or...
$dbh = DBI->connect("dbi:mysql:", "USER", "PASSWORD");
@databases = $dbh->data_sources("mysql");
>>>
>>> I sympathize with this; the last remaining test the DBD::Informix 1.00
>>> pre-releases sometimes skip (instead of failing as in earlier versions)
>>> is the data sources test, and it too is because there is no user
>>> information available even though it can be needed to connect to the
>>> database server to obtain the list of databases.
>>>
>>> I note in passing that if you do the connection first, DBD::Informix
>>> will generate a list of data sources OK, but the whole point of the
>>> data sources method is to find out what's available before formally
>>> connecting to one of the available data sources.
>>>
>>> Please can we modify DBI 1.14 to handle this, Tim?
>>> It looks like a simple, backwards compatible extension to me...
>>>
>>>
>>>
I realize that implementation of such would require support from the
actual driver module, but currently the DBI does not provide a way
to use such a feature. I have posted a note regarding this issue
to [EMAIL PROTECTED] [...]
>>>
[from msql-mysql-modules]
> We consider the assumption of an anonymous user in listdbs to be a bug.
..
I agree with you, but I refuse to support listdbs, as there is
the portable DBI->data_sources(). IMO the better way to go
is the support for user names and passwords with the data_sources
method. If you are interested in that, please contact Tim Bunce.
..
Thanks, Jochen
>>>
>>> == end quote 1 ==
>>>
>>> == quote 2 ==
>>>Date: Thu, 10 Feb 2000 10:11:23 +
>>>From: Tim Bunce <[EMAIL PROTECTED]>
>>>To: Jonathan Leffler <[EMAIL PROTECTED]>
>>>CC: Randall Whitman <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
>>>
>>> On Wed, Feb 09, 2000 at 08:41:54PM -0800, Jonathan Leffler wrote:
>>>
Please can we modify DBI 1.14 to handle this, Tim?
It looks like a simple, backwards compatible extension to me...
>>>
>>> I'll look into it.
>>>
>>> Tim.
>>>
>>> == end quote 2 ==
>>>
>>>
>>>
>>>
> On Thu, 7 Jun 2001, Jonathan Leffler wrote:
> > Curt Russell Crandall wrote:
> > > Is the DBI->data_sources method the only facility available in the DBI to
> > > get a list of databases on a server? [...]
>>>
> > > Sybase won't return anything with data_sources and I'm not
> > > sure if Informix will either... those are the 2 dbs I'm concerned with
>[...]
> >
> > Have y
RE: Shorthand for $dbh->prepare and $sth->execute?
On Tue, 26 Jun 2001, Steven Lembark wrote:
> > What about SELECT statements, though? The selectrow_array functions aren't
> > adequate for this, because they can only be used to return the first row.
>
> The last version of DBI came with "selectall_*", no?
Is this reasonably efficient for tables that have thousands of rows? e.g.
$rows = $dbh->selectall_hashref("SELECT ...");
for my $row (@{$rows}) {
do something with $row
}
versus:
$sth = $dbh->prepare("SELECT ...");
$sth->execute;
while ($row = $sth->fetchrow_hashref) {
do something with $row
}
Doesn't the first way have to retrieve all the results of the SELECT
query, but the second way has the option of stopping part way through if
it finds out that it doesn't need anymore data? (Or am I misunderstanding
how this works?)
-Philip Mak ([EMAIL PROTECTED])
RE: Shorthand for $dbh->prepare and $sth->execute?
> On Tue, 26 Jun 2001, Sterin, Ilya wrote: > >> Why? You can prepare and execute with do() for update and insert >> statements. > > What about SELECT statements, though? The selectrow_array functions aren't > adequate for this, because they can only be used to return the first row. The last version of DBI came with "selectall_*", no? sl
RE: Shorthand for $dbh->prepare and $sth->execute?
On Tue, 26 Jun 2001, Sterin, Ilya wrote: > Why? You can prepare and execute with do() for update and insert > statements. What about SELECT statements, though? The selectrow_array functions aren't adequate for this, because they can only be used to return the first row. > $sth->rows does not guarantee and in most casese will not return the number > of rows that the select statement returns. In most cases it will return the > rows fetched so far, or depending on the db driver the rows fetched in the > cache. You should either use "select count(*) from ..." query to get the > number of rows or use a counter. I heard about this before, but it does not seem to be true for MySQL; I've never had trouble using $sth->rows under MySQL. -Philip Mak ([EMAIL PROTECTED])
RE: Shorthand for $dbh->prepare and $sth->execute?
-Original Message-
From: Philip Mak
To: [EMAIL PROTECTED]
Sent: 06/26/2001 2:41 AM
Subject: Shorthand for $dbh->prepare and $sth->execute?
>I noticed that DBI now has some shortcut methods, e.g. selectrow_hashref
>which combines a prepare, execute and fetchrow in one function call.
>Is there a similar method to combine a prepare and execute?
Why? You can prepare and execute with do() for update and insert
statements.
>$sth = $dbh->prepare("SELECT ...");
>$sth->execute;
e.g. something like:
$sth = $dbh->prepare_execute("SELECT ...");
>at which point I could do $sth->rows, $sth->fetchrow_hashref, etc.
$sth->rows does not guarantee and in most casese will not return the number
of rows that the select statement returns. In most cases it will return the
rows fetched so far, or depending on the db driver the rows fetched in the
cache. You should either use "select count(*) from ..." query to get the
number of rows or use a counter.
Ilya
-Philip Mak ([EMAIL PROTECTED])
RE: Need help with DBI and Msql-Mysql
Hello, All. This is Stefan, again. I recently posted "Problems running DBD::mysql" Thanks to Mike Duncan, Ilya Sterin, and Hardy Merrill for their help so far. I've followed their suggestions with no luck yet :-( My mod info: ** Module id = DBI DESCRIPTION Generic Database Interface (see DBD modules) CPAN_USERID TIMB (Tim Bunce <[EMAIL PROTECTED]>) CPAN_VERSION 1.18 CPAN_FILET/TI/TIMB/DBI-1.18.tar.gz DSLI_STATUS amcO (alpha,mailing-list,C,object-oriented) MANPAGE DBI - Database independent interface for Perl INST_FILE/usr/local/lib/perl5/site_perl/5.6.1/i686-linux/DBI.pm INST_VERSION 1.18 Module id = DBD::mysql DESCRIPTION Mysql Driver for DBI CPAN_USERID JWIED (Jochen Wiedmann <[EMAIL PROTECTED]>) CPAN_VERSION 2.0901 CPAN_FILEJ/JW/JWIED/DBD-mysql-2.0901.tar.gz DSLI_STATUS RmcO (released,mailing-list,C,object-oriented) MANPAGE DBD::mSQL / DBD::mysql - mSQL and mysql drivers for the Perl5 Database Interface (DBI) INST_FILE /usr/local/lib/perl5/site_perl/5.6.1/i686-linux/DBD/mysql.pm INST_VERSION 2.0416 Module id = DBD::mysql::Install CPAN_USERID JWIED (Jochen Wiedmann <[EMAIL PROTECTED]>) CPAN_VERSION undef CPAN_FILEJ/JW/JWIED/Msql-Mysql-modules-1.2216.tar.gz INST_FILE(not installed) * Like Trevor, I run into the EXACT same problem when running install DBD::mysql::Install when it hits 'make test'. I've tried running 'force install...', as well, and this hasn't worked either. I've heard of the same problem from a fellow programmer, as well. I don't usually publish a "Me too!" post, but I thought the extra info might help. Thanks! Stefan -Original Message- From: pkp team [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 26, 2001 8:50 AM To: [EMAIL PROTECTED] Subject: Need help with DBI and Msql-Mysql Hi there, Sorry to bother you guys, but i need some help. I have been stuck on this issue for weeks now. I am trying to install DBI, Data-ShowTable and msql-mysql on my linux box. I am installing all the three from source. I have absolutely no problems installing DBI and Data-Showtable. But when i try to install msql-mysql, i get the following error on typing "make test" prompt>make test make[1]: Entering directory `/root/download/DBI/Msql-Mysql-modules-1.2211/mysql' make[1]: Leaving directory `/root/download/DBI/Msql-Mysql-modules-1.2211/mysql'make[1]: Entering directory `/root/download/DBI/Msql-Mysql-modules-1.2211/mysql' PERL_DL_NONLAZY=1 /usr/bin/perl -I../blib/arch -I../blib/lib -I/usr/lib/perl5/5. 00503/i386-linux -I/usr/lib/perl5/5.00503 -e 'use Test::Harness qw(&runtests $ve rbose); $verbose=0; runtests @ARGV;' t/*.t t/00baseok t/10dsnlist.ok t/20createdrop..ok t/40nulls...ok t/40numrows.ok t/50chopblanks..ok t/50commit..ok t/60leaks...skipping test on this platform t/ak-dbdok t/akmiscFAILED test 44 Failed 1/351 tests, 99.72% okay t/dbdadmin..ok t/mysql.FAILED test 23 Failed 1/68 tests, 98.53% okay t/mysql2ok Failed Test Status Wstat Total Fail Failed List of failed --- t/akmisc.t 3511 0.28% 44 t/mysql.t681 1.47% 23 1 test skipped. Failed 2/17 test scripts, 88.24% okay. 2/732 subtests failed, 99.73% okay. make[1]: *** [test_dynamic] Error 29 make[1]: Leaving directory `/root/download/DBI/Msql-Mysql-modules-1.2211/mysql' make: *** [test] Error 2 [root@localhost] Please help me out with this problem. I have Perl and mysql installed properly. Thanks in advance Trevor _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
Re: Need help with DBI and Msql-Mysql
On Tue, 26 Jun 2001, pkp team wrote: > I have absolutely no problems installing DBI and Data-Showtable. > But when i try to install msql-mysql, i get the following error on typing > "make test" I might be wrong on this (wait for other people to reply to your message to), but I've found that in some cases, it's just the test module that's faulty, i.e. you can do "make install" anyway after "make test" fails, but the code will still run fine. If you do this you could run into unexpected bugs later though... -Philip Mak ([EMAIL PROTECTED])
Need help with DBI and Msql-Mysql
Hi there, Sorry to bother you guys, but i need some help. I have been stuck on this issue for weeks now. I am trying to install DBI, Data-ShowTable and msql-mysql on my linux box. I am installing all the three from source. I have absolutely no problems installing DBI and Data-Showtable. But when i try to install msql-mysql, i get the following error on typing "make test" prompt>make test make[1]: Entering directory `/root/download/DBI/Msql-Mysql-modules-1.2211/mysql' make[1]: Leaving directory `/root/download/DBI/Msql-Mysql-modules-1.2211/mysql'make[1]: Entering directory `/root/download/DBI/Msql-Mysql-modules-1.2211/mysql' PERL_DL_NONLAZY=1 /usr/bin/perl -I../blib/arch -I../blib/lib -I/usr/lib/perl5/5. 00503/i386-linux -I/usr/lib/perl5/5.00503 -e 'use Test::Harness qw(&runtests $ve rbose); $verbose=0; runtests @ARGV;' t/*.t t/00baseok t/10dsnlist.ok t/20createdrop..ok t/40nulls...ok t/40numrows.ok t/50chopblanks..ok t/50commit..ok t/60leaks...skipping test on this platform t/ak-dbdok t/akmiscFAILED test 44 Failed 1/351 tests, 99.72% okay t/dbdadmin..ok t/mysql.FAILED test 23 Failed 1/68 tests, 98.53% okay t/mysql2ok Failed Test Status Wstat Total Fail Failed List of failed --- t/akmisc.t 3511 0.28% 44 t/mysql.t681 1.47% 23 1 test skipped. Failed 2/17 test scripts, 88.24% okay. 2/732 subtests failed, 99.73% okay. make[1]: *** [test_dynamic] Error 29 make[1]: Leaving directory `/root/download/DBI/Msql-Mysql-modules-1.2211/mysql' make: *** [test] Error 2 [root@localhost] Please help me out with this problem. I have Perl and mysql installed properly. Thanks in advance Trevor _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
Re: dbi:Proxy truncates long sqls?
No answers here - we used dbiproxy for quite a while and I never saw that error before, though we never generated SQL strings as long as yours. -- Hardy Merrill Mission Critical Linux, Inc. http://www.missioncriticallinux.com Douglas Smith [[EMAIL PROTECTED]] wrote: > > I have a mystery, and I can't seem to find the answer in the docs, > atleast not quickly. > > I have a perl program using the DBI (obviously) that produces a > very long sql, 116 words, 1225 characters (don't ask someone > else wrote the program and it was dropped in my lap). > > When I connect to Oracle using the DBD, everything works just > fine, but when I connect through the Proxy Server, using dbi:proxy > I get this error: > > Server returned error: Failed to execute method CallMethod: > DBD::Oracle::st execute failed: ORA-00933: SQL command not > properly ended (DBD ERROR: OCIStmtExecute) > > Every other sql I throw at the proxy server has worked so far. > So, my question is, is there something in the dbi:proxy interface > which would trim long sql statements? Like a limit on sqls of > 1k bytes? > > Just wondering if anyone has a good idea of what might be wrong? > > Douglas > > -- > --- > Douglas A. Smith [EMAIL PROTECTED] > Office: Bld 40, Rm Y105 (650)926-2369 > --- -- Hardy Merrill Mission Critical Linux, Inc. http://www.missioncriticallinux.com
Re: DBI Connect Failure
Rudy, Thanks for your mail. I have Postgres version 7.1, I can connect to my database with psql and make changes, and my operating system is Sun Solaris 5.7. After you and Jeff wrote, I - reinstalled DBI-1.13 with the environment variables for DBI_DSN, DBI_USER and DBI_PASS - downloaded and installed DBD-Pg-1.00, with environment variables for POSTGRES_LIB and POSTGRES_INCLUDE - accessed perldoc with my postgres account - got my first script to post an sql statement to my web page (but that's all) - checked my fetch row subroutine (couldn't find an error), and then my second script, which instead of printing an acronym and its meaning based on form input, simply prints out the whole acronym table. Now I got this error: "Can't locate object method "execute" via package "DBI::db" (perhaps you forgot to load DBI:db"? Database handle destroyed without explicit disconnect." I think that it can't find DBI:db because I have incorrect setenv DBI_DSN parameters. The DBI module instructions say that it should point to my database drivers, and that ODBC should be in there, but I have no ODBC modules installed, and the only odbc folder I could find was in the source directory of my postgres installation files. Anyway, DBI_DSN now has dbdi:ODBC:POSTGRES. Let me know if that's correct or if I should look elsewhere, thanks! Christine Rudy Lippan wrote: > > > I'm studying the DBI module to get smarter on this. I was not able to > > complete installation on the Pg module (I have pg95perl5-1.2.0), because > > I couldn't find Pg.pm modules in the right places. Tried to reinstall, > > but it failed on make because libpq.so.1 is missing from > > usr/local/pgsql/lib. Tried checking www.cpan.org today to make sure that > > I have the correct Pg module, but it was down. > > You can get the latest version of DBD::Pg from > 'http://dbi.symbolstone.org/index.html' which is probably what you want. I > don't think that you want be be using pg95perl5/Pg.pm. When you get > DBD::Pg it will make you set POSTGRES_INCLUDE, and POSTGRES_LIB which tell > DBD:Pg where to find libpq.so and other things that are required to build > DBD::Pg. > > By the by what version of postgres are you using? Can you connect to it > with psql? What operating system/version are you using? > > > I got no errors on the PgSQL.pm install, so it would be easier for me to > > use that, presuming I can get DBD running correctly. > > I don't know about that... > > > Tried using perl documentation, but got only the usage index. I can't > > run perldoc, because I get a warning: Superuser must not run ~/perldoc > > without security audit and taint checks. > > perldoc will not let you run it as root. Create another user and then run > perldoc as that user || you can try "su -c nobody 'perldoc DBI'" and see > if that works. > > > I have a colleague who's betting this is a total waste of time, given > > the ignorance I've just demonstrated. But as long as I do the other > > stuff I'm supposed to be doing, trying this out still beats learning MS > > Access. > > It is not a waste of time, for we all have to start somewhere. And as long > as you learn something, I would consider it a worthwhile exercise. > > Later, > > Rudy
DBD::ODBC, BC++ 5.5.1, ODBC32.LIB not found
I encountered a small problem while installing DBD-ODBC-0.28 in this
environment:
Summary of my perl5 (revision 5 version 6 subversion 1) configuration:
Platform:
osname=MSWin32, osvers=4.0, archname=MSWin32-x86-multi-thread
...
Compiler:
cc='bcc32', ccflags ='-O2 -5 -D_RTLDLL -g0 -DWIN32 -DHAVE_DES_FCRYPT
-D_MT -DPERL_IMPLICIT_CONTEXT -DPERL_IMPLICIT_SYS',
...
The compiler is Borland C++ 5.5.1!
Running perl Makefile.PL:
Note (probably harmless): No library found for 'ODBC32.LIB'
That's because ODBC32.LIB is in $Config{libpth}/PSDK now.
A possible solution could be:
$opts{LIBS} = ["-L$Config{libpth}/PSDK -lODBC32.LIB"] if $Config{cc}
eq 'bcc32';
However, this is specific for BC++ 5.5.x. I prefer:
qx'implib ODBC32.LIB %SystemRoot%\system32\odbc32.dll';
Other suggestions?
Attached is a patch for Makefile.PL.
Steffen Goeldner
*** DBD-ODBC-0.28/Makefile.PL Thu Mar 09 05:45:04 2000
--- DBD-ODBC-0.28a/Makefile.PL Tue Jun 26 11:00:42 2001
***
*** 50,55
--- 50,62
my $dbi_dir = dbd_dbi_dir();
my $dbi_arch_dir = dbd_dbi_arch_dir();
+ if ($^O eq 'MSWin32') {
+ if ($Config{cc} eq 'bcc32') {
+ # XXX: Test for compiler version (e.g. Borland C++ 5.5.x)?
+ print "Trying to generate ./ODBC32.LIB ...\n";
+ print qx'implib ODBC32.LIB %SystemRoot%\system32\odbc32.dll';
+ }
+ }
open(SQLH, ">dbdodbc.h") || die "Can't open dbdodbc.h: $!\n";
print SQLH "/* Do not edit this file. It is automatically written by Makefile.PL.\n";
print SQLH " Any changes made here will be lost. \n*/\n\n";
Re: sql error / placeholders update
Try this at the cpan command line prompt:
cpan> m DBI
it should tell you what the current CPAN version is, and what
the current install version(yours) is.
--
Hardy Merrill
Mission Critical Linux, Inc.
http://www.missioncriticallinux.com
Jeff Thies [[EMAIL PROTECTED]] wrote:
> This is an update to a previous SQL error post. The problem is in the
> placeholders.
>
> This fails:
>
> my $sql=q{INSERT INTO some_table(some_field) values(?)};
> my $sth=$dbh->prepare($sql);
> $sth->execute('some variable with a colon: in it');
>
> This is OK:
> my $sql=q{INSERT INTO some_table(some_field) values('some variable with
> a colon: in it')};
> my $sth=$dbh->prepare($sql);
> $sth->execute();
>
> as does a command line insert.
>
> There seems to be a problem with the placeholder. I don't recall having
> a problem like this before, so I suspect a DBI update by my webhost.
>
> How do I retrieve the DBI version?
>
> Jeff
Shorthand for $dbh->prepare and $sth->execute?
I noticed that DBI now has some shortcut methods, e.g. selectrow_hashref
which combines a prepare, execute and fetchrow in one function call.
Is there a similar method to combine a prepare and execute?
$sth = $dbh->prepare("SELECT ...");
$sth->execute;
e.g. something like:
$sth = $dbh->prepare_execute("SELECT ...");
at which point I could do $sth->rows, $sth->fetchrow_hashref, etc.
-Philip Mak ([EMAIL PROTECTED])
Re: getting a list of databases
Tim Bunce wrote:
> Changes in DBI 1.16,30th May 2001
>
> Added note to data_sources() method docs that some drivers may
> require a connected database handle to be supplied as an attribute.
With all due respect, Tim, this is completely ducking the issue. Yes, after a fashion
it
resolves the problem, rather like saying "Under-age kids should not drink in pubs"
solves a
problem. It doesn't really cut to the meat of the issue though -- the data_sources()
method is not generally useful because the interface is incomplete.
> On Sun, Jun 10, 2001 at 03:36:12PM -0700, Jonathan Leffler wrote:
> > Michael Peppler wrote:
> >
> > > Curt Russell Crandall writes:
> > > > I didn't have an Informix DB available at the time, but yes it does return
> > > > a list of dbs with data_sources... however, Sybase does not as well as
> > > > many others.
> > >
> > > DBD::Sybase could be made to return the list of known servers (from
> > > the interfaces file), but not the list of databases, as that requires
> > > connecting to each of the servers in the interfaces file (and we need
> > > to know user/pwd for each server, etc - gets a little hairy).
> >
> > We had a discussion about this on the dbi-users mailing list during February 2000,
>in
> > the run-up to the release of DBI 1.14. Tim promised to look into it, but either
>came
> > up with a negative decision without notifying us or (much more likely) overlooked a
> > comparatively minor feature that only impacts a few database servers...
> >
> > == quote 1 ==
> > Subject: Re: DBI->data_sources() - assumption of anonymous user is a bug
> > Date: Wed, 09 Feb 2000 20:41:54 -0800
> > From: Jonathan Leffler <[EMAIL PROTECTED]>
> > To: Randall Whitman <[EMAIL PROTECTED]>
> > CC: [EMAIL PROTECTED]
> >
> > Randall Whitman wrote:
> >
> > > We consider the assumption of an anonymous user to be a bug --
> > > that is, the behavior that
> > > DBI->data_sources("mysql");
> > > returns an empty array because we do not have an anonymous
> > > user in MySQL.
> > >
> > > In other words, it should be possible to do one of the following:
> > >
> > > @databases = DBI->data_sources("mysql", "USER", "PASSWORD");## or...
> > >
> > > $dbh = DBI->connect("dbi:mysql:", "USER", "PASSWORD");
> > > @databases = $dbh->data_sources("mysql");
> >
> > I sympathize with this; the last remaining test the DBD::Informix 1.00
> > pre-releases sometimes skip (instead of failing as in earlier versions)
> > is the data sources test, and it too is because there is no user
> > information available even though it can be needed to connect to the
> > database server to obtain the list of databases.
> >
> > I note in passing that if you do the connection first, DBD::Informix
> > will generate a list of data sources OK, but the whole point of the
> > data sources method is to find out what's available before formally
> > connecting to one of the available data sources.
> >
> > Please can we modify DBI 1.14 to handle this, Tim?
> > It looks like a simple, backwards compatible extension to me...
> >
> >
> > > I realize that implementation of such would require support from the
> > > actual driver module, but currently the DBI does not provide a way
> > > to use such a feature. I have posted a note regarding this issue
> > > to [EMAIL PROTECTED] [...]
> >
> > > [from msql-mysql-modules]
> > >
> > > > We consider the assumption of an anonymous user in listdbs to be a bug.
> > > ..
> > > I agree with you, but I refuse to support listdbs, as there is
> > > the portable DBI->data_sources(). IMO the better way to go
> > > is the support for user names and passwords with the data_sources
> > > method. If you are interested in that, please contact Tim Bunce.
> > > ..
> > > Thanks, Jochen
> >
> > == end quote 1 ==
> >
> > == quote 2 ==
> >Date: Thu, 10 Feb 2000 10:11:23 +
> >From: Tim Bunce <[EMAIL PROTECTED]>
> >To: Jonathan Leffler <[EMAIL PROTECTED]>
> >CC: Randall Whitman <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
> >
> > On Wed, Feb 09, 2000 at 08:41:54PM -0800, Jonathan Leffler wrote:
> > > Please can we modify DBI 1.14 to handle this, Tim?
> > > It looks like a simple, backwards compatible extension to me...
> >
> > I'll look into it.
> >
> > Tim.
> >
> > == end quote 2 ==
> >
> >
> >
> > > > On Thu, 7 Jun 2001, Jonathan Leffler wrote:
> > > > > Curt Russell Crandall wrote:
> > > > > > Is the DBI->data_sources method the only facility available in the DBI to
> > > > > > get a list of databases on a server? [...]
> >
> > > > > > Sybase won't return anything with data_sources and I'm not
> > > > > > sure if Informix will either... those are the 2 dbs I'm concerned with
>[...]
> > >
> > > > >
> > > > > Have you tried using data_sources with DBD::Informix? [...]
>
--
Jonathan Leffler ([EMAIL PROTECTED], [EMAIL PROTECTED])
Guardian of DBD::Informix 1.00.PC1 -- see http://www.cpan.org/
#include
