Simultaneous queries
Hello. I have three select queries (same DB) that I would like to have execute at the same time rather than sequentially. I seem to recall something about this - "chaining" queries with "and" in the perl code, but I've googled around a bit and have come up empty, so perhaps I'm way off base. Can someone refresh me as to how to execute queries simultaneously, or similiar suggestions? Thanks! -- Mike
RE: Using Perl to "Source" variables
-- Srikanth Madani <[EMAIL PROTECTED]>
When the script exists, this variable will no longer be available.
The variables are part of the currently running program:
int main( int argc, char **argv, char **env )
{^^
the third paramter are the local var's. You can update them
within the current process, but they are data local to the
process.
Updating the environment via
$ENV{FOO} = 'bar'.
has the same effect as:
export FOO='bar';
at the shell: it sets the variable for the duration of a
single execution.
If you want to modify the contents of ORACLE_SID or
ORACLE_ASK and then have oracle run with them you
need to set the environment up first then run the job:
export ORACLE_SID = 'foobar';
sqlplus scot/tiger;
or
$ENV{ORACLE_SID} = 'foobar';
system( 'sqlplus scot/tiger' );
will do the same thing. You could also fork/exec the
sqlplus instead of letting system do it for you.
--
Steven Lembark 2930 W. Palmer
Workhorse Computing Chicago, IL 60647
+1 888 359 3508
Re: Accessing large CSV files
Michael Gerdau wrote:
_If_ I would want to use DBD::CSV on large (i.e. 5 or more records)
CSV files, what would I have to do ?
Is there a buildin limit on the number of records DBD::CSV can handle ?
There is no built in limit. Yours is the first report I've had of the
problem. My guess is that in most situations for files of that size
you'd be better off loading them into into SQLite and doing your
querying there.
If you do want to deal with them in DBD::CSV, the LIMIT clause is not
the way to go. LIMIT first accumulates an entire result set in memory
and then reduces it to the specified numbers (LIMIT is logically based
on ORDER BY and you need to find the entire result set to use ORDER BY).
The way to reduce the amount held in memory is to reduce the result
set with WHERE clauses e.g. if you have a continuously incrementing id
field and a clause WHERE id > $num AND id < $num+5000, your result set
would be 5k records. With "COUNT (*) FROM x LIMIT 5000" your result
set will be the entire database which will get limited to 5k records
only after the result set has been gathered.
For SQL statements not using ORDER BY or aggregate functions like COUNT,
you can use DBD::AnyData (which uses the same SQL parsing engine as
DBD::CSV) to read lines in one at a time without accumulating a result
set. If you just replace "" in the script below with an open
filehandle, the script will go through the file one record at a time,
evaluate it against the SQL, then let you print (or otherwise process)
that line. No more than a single line is ever held in memory.
my $dbh = DBI->connect('dbi:AnyData:');
my($table,$cols) = ( 'tmp' , 'id,phrase' );
my $sql = "SELECT phrase FROM $table WHERE id > 1";
while () {
$dbh->ad_import( $table,'CSV',[$_],{col_names=>$cols});
my @row = $dbh->selectrow_array($sql);
print "@row\n" if @row;
$dbh->ad_clear($table);
}
__DATA__
1,foo
2,bar
3,qux
--
Jeff
Re: Accessing large CSV files
> > I'm trying to read a large CSV files (some 4 records) with > > DBD::CSV version 0.2002 and perl v5.8.1 built for > > i586-linux-thread-multi > > > > Reading smaller CSV files (i.e. around 5000 records) works like a > > charm. Only large ones fail. > > If all you want to do is to read all records it would IMHO be more > efficient to use Text::CSV_XS. If you needed any filtering you'd have > to implement it yourself though, but there should be no limit on the > number of rows. That's indeed all I want to do (at this point in time :) and is way more efficient. Thanks alot for the pointer. The program is now working as it should. However since I came across it: _If_ I would want to use DBD::CSV on large (i.e. 5 or more records) CSV files, what would I have to do ? Is there a buildin limit on the number of records DBD::CSV can handle ? And if yes, would it be easy to increase that ? (and if yes it possibly should be mentioned in the POD...) Best, Michael -- Vote against SPAM - see http://www.politik-digital.de/spam/ Michael Gerdau email: [EMAIL PROTECTED] GPG-keys available on request or at public keyserver
DBI->quote with DBD::Sysbase
Hello, I'm using the combination of DBI, DBD::Sysbase and FreeTDS to connect to an MS-SQL server. Due to the issues with placeholders and sp's (specifically not being able to use them), I'm forced to properly deal with any single and double quotes. It seems like DBI->quote does almost everything correctly with the exception of leaving double quotes unchanged. Is there some way to get the quoting method to continue to work as it does now but to also change " to "" Thanks! -Chris DBI 1.38 DBD::Sybase 1.47
Re: Accessing large CSV files
From: Michael Gerdau <[EMAIL PROTECTED]> > I'm trying to read a large CSV files (some 4 records) with > DBD::CSV version 0.2002 and perl v5.8.1 built for > i586-linux-thread-multi > > Reading smaller CSV files (i.e. around 5000 records) works like a > charm. Only large ones fail. If all you want to do is to read all records it would IMHO be more efficient to use Text::CSV_XS. If you needed any filtering you'd have to implement it yourself though, but there should be no limit on the number of rows. Jenda = [EMAIL PROTECTED] === http://Jenda.Krynicky.cz = When it comes to wine, women and song, wizards are allowed to get drunk and croon as much as they like. -- Terry Pratchett in Sourcery
Re: Oracle OCI
The DBI doesn't, the DBD::Oracle driver for DBI does. Tim. On Thu, Apr 08, 2004 at 04:41:09PM -0500, Thomas, Mathew wrote: > Sorry for the quick newbie question, but I couldn't find an answer on CPAN. > Does Perl DBI use Oracle OCI (Oracle Call Interface) underneath to connect > to an Oracle RDBMS?? > > Thanks, > Mathew Thomas > [EMAIL PROTECTED]
Accessing large CSV files
Hi !
I'm trying to read a large CSV files (some 4 records) with
DBD::CSV version 0.2002 and perl v5.8.1 built for i586-linux-thread-multi
Reading smaller CSV files (i.e. around 5000 records) works like a
charm. Only large ones fail.
After searching the archive I found a hint using the LIMIT clause
for select but that doesn't seem to help.
Here is a code fragment:
print "Providing ".scalar(@cols)." colnames to netting...\n";
$dbh->{'csv_tables'}->{'netting'} = {'col_names' => [EMAIL PROTECTED];
my $sth;
# test LIMIT clause
print "Preparing test LIMIT clause...\n";
$sth = $dbh->prepare("select count(*) from netting limit 1000") or die $DBI::errstr;
print "Executing test LIMIT clause...\n";
$sth->execute() or die $DBI::errstr;
print "Retrieving result for test LIMIT clause...\n";
while (my ($count) = $sth->fetchrow_array()) {
print("LIMIT 1000: found $count records in netting");
}
$sth->finish();
and here is the output:
Providing 36 colnames to netting...
Preparing test LIMIT clause...
Executing test LIMIT clause...
DBD::CSV::st execute failed: Error while reading file ./netting:
illegal filedescriptor at /usr/lib/perl5/site_perl/5.8.1/DBD/CSV.pm line 216,
chunk 27617.
[EMAIL PROTECTED]:> wc netting
42123 286032 16561068 netting
When I reduce the size of the CSV file to e.g. 2 records the
above code works as it should.
What is the proposed way of reading large CSV files (other than
splitting the file into smaller chunks and reading all chunks
seperately).
Any hint appreciated, best wishes,
Michael
--
Vote against SPAM - see http://www.politik-digital.de/spam/
Michael Gerdau email: [EMAIL PROTECTED]
GPG-keys available on request or at public keyserver
RE: Can't bind unknown placeholder '1' at ..site/lib/DBI/ProxyServer.pm line 329.
Change the sql to
my $sth = $dbh->prepare( "SELECT lkpver.text_string as
version,lkpst.text_string as status from release_target rt, lookup
lkpst, lookup lkpver where rt.product_issue_id = :1 and
lkpst.lookup_category_name = 'm2_release_target_status' and
lkpst.code_int = rt.status_lkp and lkpver.lookup_category_name =
'm2_fix_in' and lkpver.code_int = rt.fix_in_version_lkp");
You had no placeholder for the bind variable the variable $bfn would
have been evaluated at define time not execution time.
Ken.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 08 April 2004 18:50
To: [EMAIL PROTECTED]
Subject: Can't bind unknown placeholder '1' at
..site/lib/DBI/ProxyServer.pm line 329.
Hi Gurus,
Greetings!
My perl script is using the DBI 1.37 & DBD-ODBC 1.06 to connect from
Unix to our
Windows SQL Server via Proxy. The SQL query that I am doing is very
simple.
Here is the Query:
my $sth = $dbh->prepare( "SELECT lkpver.text_string as
version,lkpst.text_string as status from release_target rt, lookup
lkpst, lookup lkpver where rt.product_issue_id = $bfn and
lkpst.lookup_category_name = 'm2_release_target_status' and
lkpst.code_int = rt.status_lkp and lkpver.lookup_category_name =
'm2_fix_in' and lkpver.code_int = rt.fix_in_version_lkp");
$sth->execute($bfn);
while ( my $row = $sth->fetchrow_hashref) {
my $flag_no_row = 1;
$release_status = $row->{status};
$release_ID = $row->{version};
Any help/pointers to resolve this would be very much appreciated.
Thanks,
-Murali
Error Message from the dbi proxy server:
Thu Apr 8 10:20:34 2004 debug, Accepting client
Thu Apr 8 10:20:34 2004 debug, Client executes method
CallMethod
Thu Apr 8 10:20:34 2004 debug, CallMethod: =>
DBI::ProxyServer::db=HASH(0x3a7e790),STORE,AutoCommit,1
Thu Apr 8 10:20:34 2004 debug, CallMethod: <= 1
Thu Apr 8 10:20:34 2004 debug, Client executes method
CallMethod
Thu Apr 8 10:20:34 2004 debug, CallMethod: =>
DBI::ProxyServer::db=HASH(0x3a7e790),prepare,SELECT lkpver.text_string
as version,lkpst.text_string as status from release_target rt, lookup
lkpst, lookup lkpver where rt.product_issue_id = 83481 and
lkpst.lookup_category_name = 'm2_release_target_status' and
lkpst.code_int = rt.status_lkp and lkpver.lookup_category_name =
'm2_fix_in' and lkpver.code_int =
rt.fix_in_version_lkp,,ARRAY(0x3a77934)
Thu Apr 8 10:20:34 2004 debug, CallMethod died with: Can't bind
unknown placeholder '1' at
//eops1/netperl/site/lib/DBI/ProxyServer.pm line 329.
Thu Apr 8 10:20:34 2004 debug, Client executes method
DestroyHandle
Thu Apr 8 10:20:34 2004 debug, Child terminating.
Any opinions expressed in this E-mail may be those of the individual and not
necessarily the company. This E-mail and any files transmitted with it are
confidential and solely for the use of the intended recipient. If you are not the
intended recipient or the person responsible for delivering to the intended recipient,
be advised that you have received this E-mail in error and that any use or copying is
strictly prohibited. If you have received this E-mail in error please notify the
beCogent postmaster at [EMAIL PROTECTED]
Unless expressly stated, opinions in this email are those of the individual sender and
not beCogent Ltd. You must take full responsibility for virus checking this email and
any attachments.
Please note that the content of this email or any of its attachments may contain data
that falls within the scope of the Data Protection Acts and that you must ensure that
any handling or processing of such data by you is fully compliant with the terms and
provisions of the Data Protection Act 1984 and 1998.
Oracle OCI
Sorry for the quick newbie question, but I couldn't find an answer on CPAN. Does Perl DBI use Oracle OCI (Oracle Call Interface) underneath to connect to an Oracle RDBMS?? Thanks, Mathew Thomas [EMAIL PROTECTED]
Can't bind unknown placeholder '1' at ..site/lib/DBI/ProxyServer.pm line 329.
Hi Gurus,
Greetings!
My perl script is using the DBI 1.37 & DBD-ODBC 1.06 to connect from
Unix to our
Windows SQL Server via Proxy. The SQL query that I am doing is very
simple.
Here is the Query:
my $sth = $dbh->prepare( "SELECT lkpver.text_string as
version,lkpst.text_string as status from release_target rt, lookup
lkpst, lookup lkpver where rt.product_issue_id = $bfn and
lkpst.lookup_category_name = 'm2_release_target_status' and
lkpst.code_int = rt.status_lkp and lkpver.lookup_category_name =
'm2_fix_in' and lkpver.code_int = rt.fix_in_version_lkp");
$sth->execute($bfn);
while ( my $row = $sth->fetchrow_hashref) {
my $flag_no_row = 1;
$release_status = $row->{status};
$release_ID = $row->{version};
Any help/pointers to resolve this would be very much appreciated.
Thanks,
-Murali
Error Message from the dbi proxy server:
Thu Apr 8 10:20:34 2004 debug, Accepting client
Thu Apr 8 10:20:34 2004 debug, Client executes method
CallMethod
Thu Apr 8 10:20:34 2004 debug, CallMethod: =>
DBI::ProxyServer::db=HASH(0x3a7e790),STORE,AutoCommit,1
Thu Apr 8 10:20:34 2004 debug, CallMethod: <= 1
Thu Apr 8 10:20:34 2004 debug, Client executes method
CallMethod
Thu Apr 8 10:20:34 2004 debug, CallMethod: =>
DBI::ProxyServer::db=HASH(0x3a7e790),prepare,SELECT lkpver.text_string
as version,lkpst.text_string as status from release_target rt, lookup
lkpst, lookup lkpver where rt.product_issue_id = 83481 and
lkpst.lookup_category_name = 'm2_release_target_status' and
lkpst.code_int = rt.status_lkp and lkpver.lookup_category_name =
'm2_fix_in' and lkpver.code_int =
rt.fix_in_version_lkp,,ARRAY(0x3a77934)
Thu Apr 8 10:20:34 2004 debug, CallMethod died with: Can't bind
unknown placeholder '1' at
//eops1/netperl/site/lib/DBI/ProxyServer.pm line 329.
Thu Apr 8 10:20:34 2004 debug, Client executes method
DestroyHandle
Thu Apr 8 10:20:34 2004 debug, Child terminating.
Re: DBI
On Thu, 8 Apr 2004 11:42:41 +1000, Simon Jordan wrote:
>What is the equivalent dbi syntax for the following win32:odbc syntax ?
>
>@FieldNames = $O->FieldNames();
I don't know Win32::ODBC... but I guess you want
@FieldNames = @{ $sth->{NAME} };
where $sth is a DBI statement handle for a SELECT query.
--
Bart.
