Using an open connection to mysql database.

[theschaef]

Hello,
Sorry in advance if this is a trivial problem.
I have a script which is executed about 2000 times in a row, and requires a connection to the mysql database each time it is executed.

I am wondering if there is a way to have a seperate program which connects to mysql and then keeps the connection open so that I can save some time and not have to connect 2000 times to the same thing.

I tried just making a script that connects to the database and then is put in an infinite loop, im just not sure if i can reference that connection with my other script. When i tried to execute it, it didn't know that a connection was already open. In other words I would like to reference the line:
$dbh = DBI->connect($dsn, $user, $pass);
in the script that is run 2000 times.
Any help is appreciated.

[Kelicula]

The "connect" method maintains a constant connection, until the "disconnect" method is called.

You can simply:

 
use DBI;
use strict;
 
my $dbh = DBI->connect("DBI:mysql:shema_name:port", "username", "pass")|| die "Error connecting to database: $DBI::errstr";
 
my $sth = $dbh->prepare(qq~ INSERT INTO temp_table (number, time) values(?,?)~);
 
 
for(1..2000){
$sth->execute($_, time)|| die "Could not update table: $sth->errstr";
}
 
$sth->finish;
$dbh->disconnect;
 
 

That will insert into a table named "temp_table", 2000 values, with the current time they were inserted.

[Kelicula]

Or you can create a module that connects and shares it's database handle with another program.

package External;
 
use DBI;
use strict;
 
our $dbh = DBI->connect("DBI:mysql:schema_name:port", "username", "pass")|| die "Can not connect to DB: $DBI::errstr";
 
1;
 

Then in another file:

 
use strict;
use External;
 
my $dbh = $External::dbh;
 
for(1..2000){
do stuff
}
 
 

But I doubt it would work like you want.

You should proable move the file into a "block" within the file that connects, and then just call on it 2000 times.

Like a subroutine, instead of calling another file 2000 times, just call a block 200 times.

[Icecrack]

Can you post some your script or mainly how you are connecting to mysql, so we could improve on how you would do it.

[minimatrix]

you could always store the values of the sql query in a hash.
then you only need to connect to the database once to populate the hash.

Once you have done this you can walk through the hash and make any changes you require by adding or removing elements form the hash.
and printing out SQL commands.

You can then dump it to an sql file and use source 'filename.sql' to make the changes to the database. hope this makes sense.

MINIMATRIX