Re: DBI query
Hi mike
Am Montag, 14. März 2005 18.38 schrieb mike:
Anyone got any any idea what is happening here
Apart from your actual problem, it happens that the coding below will give big
chances to have undetected or hard to find errors.
- use use strict; use warnings; at the top of the code
- declare your variables (as local to the first usage as possible)
- check the results of methods/functions
I did not run your (and my) code; just some annotations:
$dbh=DBI-connect(dbi:Pg:dbname=data_cc,$user,$pw);
my $dbh=DBI-connect(dbi:Pg:dbname=data_cc,$user,$pw) or die $DBI::errstr;
(an example for declaring $dbh and checking the result of connect())
$dump_dir='/home/data_cc/dump';
@names=$dbh-tables();
#$dbh-execute;
#print @names;
foreach $names(@names){
sidenote: since $names contains a scalar, $name would be a better name for
this variable.
if (substr($names,0,9) eq public.tb)
{
$file1= $dump_dir\/$names;
$dbh-prepare(COPY $names TO ? WITH DELIMITER AS #);
The prepare method returns a statement handle for further usage, check the
manual of DBI.
my $sth=$dbh-prepare(COPY $names TO ? WITH DELIMITER AS #)
or die $DBI::errstr
$dbh-bind_param(1,$file1);
$dbh-execute();
and the statement handle has the bind_param and execute methods:
$sth-bind_param(1,$file1) or die $DBI::errstr;
$sth-execute() or die $DBI::errstr;
There are several methods to retrieve the results from the statement handle
(see man DBI) - at this place in the code, not outside of the foreach loop.
}
}
results in output
[Mon Mar 14 17:30:10 2005] [error] [client 127.0.0.1] Can't locate
object method bind_param via package DBI::db at /home/www/cgi-
bin/dump_all.pl line 25.
This is correct, since the database handle has no such methods.
confused - it seems to be losing the connection to the db
No, this would not result in an object dropping a method.
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/ http://learn.perl.org/first-response
RE: DBI query
mike wrote:
Anyone got any any idea what is happening here
$dbh=DBI-connect(dbi:Pg:dbname=data_cc,$user,$pw);
$dump_dir='/home/data_cc/dump';
@names=$dbh-tables();
#$dbh-execute;
#print @names;
foreach $names(@names){
if (substr($names,0,9) eq public.tb)
{
$file1= $dump_dir\/$names;
$dbh-prepare(COPY $names TO ? WITH DELIMITER AS #);
$dbh-bind_param(1,$file1);
$dbh-execute();
}
}
results in output
[Mon Mar 14 17:30:10 2005] [error] [client 127.0.0.1] Can't locate
object method bind_param via package DBI::db at /home/www/cgi-
bin/dump_all.pl line 25.
bind_param and execute are *statement* handle method, and you're applying
them
to a *database* handle. You need to save the return value from prepare()
and call bind_param() and execute() on that.
Or, you can combine the prepare/bind/execute all into one call to do():
$dbh-do(COPY $names TO ? WITH DELIMITER AS #, undef, $file);
confused - it seems to be losing the connection to the db
No, the error message is a Perl error telling you that the package
that $dbh belongs to (DBI::db) doesn't have or inherit a method called
bind_param.
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/ http://learn.perl.org/first-response
Re: DBI Query rows Benchmark
Pablo Fischer wrote: Hello! I have a questionto those lovers of DBI and Databases: which method is faster to know the number of rows of a query: $sth-rows or my query but with a COUNT(id) and retrieve the value with: $query-bind_columns(undef, \$total); The table its 'big' cause it will manage like 12,000 rows. In $sth-rows I have this: SELECT idport FROM ports WHERE port='$portnumber'; in the bind_columns case I have SELECT COUNT(idport) FROM ports WHERE port='$portnumber'. I know that I can test it with EXPLAIN in MySql, however Im testing it with 150 records, not with 12,000 (or more) that will be the real size of the table. The latter should be faster, because the server can do all the counting and doesn't have to pass each row back to the client for counting. If there's an index on port, the count can be determined just by scanning the index, which could be even quicker. In general, you want your queries to return the mininum number of rows necessary. The second query will always return one row, so that's your best bet. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: DBI Query rows Benchmark
Thanks!!! El día Sunday 07 September 2003 7:59 a Bob Showalter mandó el siguiente correo: Pablo Fischer wrote: Hello! I have a questionto those lovers of DBI and Databases: which method is faster to know the number of rows of a query: $sth-rows or my query but with a COUNT(id) and retrieve the value with: $query-bind_columns(undef, \$total); The table its 'big' cause it will manage like 12,000 rows. In $sth-rows I have this: SELECT idport FROM ports WHERE port='$portnumber'; in the bind_columns case I have SELECT COUNT(idport) FROM ports WHERE port='$portnumber'. I know that I can test it with EXPLAIN in MySql, however Im testing it with 150 records, not with 12,000 (or more) that will be the real size of the table. The latter should be faster, because the server can do all the counting and doesn't have to pass each row back to the client for counting. If there's an index on port, the count can be determined just by scanning the index, which could be even quicker. In general, you want your queries to return the mininum number of rows necessary. The second query will always return one row, so that's your best bet. -- Pablo Fischer Sandoval ([EMAIL PROTECTED]) http://www.pablo.com.mx http://www.debianmexico.org GPG FingerTip: 3D49 4CB8 8951 F2CA 8131 AF7C D1B9 1FB9 6B11 810C Firma URL: http://www.pablo.com.mx/firmagpg.txt -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: DBI Query rows Benchmark
On Sunday, September 7, 2003, at 12:33 PM, Pablo Fischer wrote: Thanks!!! El día Sunday 07 September 2003 7:59 a Bob Showalter mandó el siguiente correo: Pablo Fischer wrote: Hello! I have a questionto those lovers of DBI and Databases: which method is faster to know the number of rows of a query: $sth-rows or my query but with a COUNT(id) and retrieve the value with: You should also be aware that the rows method does not always return the number of rows from a select (for example in the oracle oci libraries it will not always do this, since the size of the return set is not necessarily known when control is returned to you from execute()). George -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
