By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,834 Members | 2,265 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,834 IT Pros & Developers. It's quick & easy.

Using an open connection to mysql database.

P: 14
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.
Nov 19 '08 #1
Share this Question
Share on Google+
4 Replies


Kelicula
Expert 100+
P: 176
The "connect" method maintains a constant connection, until the "disconnect" method is called.

You can simply:
Expand|Select|Wrap|Line Numbers
  1.  
  2. use DBI;
  3. use strict;
  4.  
  5. my $dbh = DBI->connect("DBI:mysql:shema_name:port", "username", "pass")|| die "Error connecting to database: $DBI::errstr";
  6.  
  7. my $sth = $dbh->prepare(qq~ INSERT INTO temp_table (number, time) values(?,?)~);
  8.  
  9.  
  10. for(1..2000){
  11. $sth->execute($_, time)|| die "Could not update table: $sth->errstr";
  12. }
  13.  
  14. $sth->finish;
  15. $dbh->disconnect;
  16.  
  17.  
That will insert into a table named "temp_table", 2000 values, with the current time they were inserted.
Nov 19 '08 #2

Kelicula
Expert 100+
P: 176
Or you can create a module that connects and shares it's database handle with another program.

Expand|Select|Wrap|Line Numbers
  1. package External;
  2.  
  3. use DBI;
  4. use strict;
  5.  
  6. our $dbh = DBI->connect("DBI:mysql:schema_name:port", "username", "pass")|| die "Can not connect to DB: $DBI::errstr";
  7.  
  8. 1;
  9.  

Then in another file:

Expand|Select|Wrap|Line Numbers
  1.  
  2. use strict;
  3. use External;
  4.  
  5. my $dbh = $External::dbh;
  6.  
  7. for(1..2000){
  8. do stuff
  9. }
  10.  
  11.  
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.
Nov 19 '08 #3

Icecrack
Expert 100+
P: 174
Can you post some your script or mainly how you are connecting to mysql, so we could improve on how you would do it.
Nov 19 '08 #4

P: 5
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
Nov 20 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.