1

I am working on one project, where I am using mysql database with three tables: Requests, Responses, and Users.

The first (index) page is pure HTML where links are available. Those links will execute the Perl script to fetch data from the database and pass the data to a template file, so the next page will be generated.

My question is every time I need to fetch data from the MySQL database, do I need to open a connection with database, and after getting the query result close it again? How can I maintain a connection across queries so there is no need to reconnect to the database?

Can I have each and every query in a single Perl file in seperate functions? If so how to provide the link for Perl function?

Borodin
  • 126,100
  • 9
  • 70
  • 144
Devesh Agrawal
  • 8,982
  • 16
  • 82
  • 131
  • 1
    This was asked a couple of years ago: [Perl Connection Pooling][1] [1]: http://stackoverflow.com/questions/3267591/perl-connection-pooling – bluevector May 29 '12 at 17:18
  • 2
    @jonnyGold: I may be wrong on this but it's my understanding that a standard CGI application does not remain resident between requests. It starts fresh, executes, and exits upon every request. The suggestions given in the SO question to which you linked point the user to DBIx::Connector, which only maintains a connection so long as the handle remains in scope. Since a basic CGI process exits after each request, the scope closes each time, and the connection will get closed. This question should not get closed as an exact duplicate: it isn't. – DavidO May 29 '12 at 18:45

1 Answers1

2

Well, this actually seems more like four questions than one. As @DavidO pointed out, under CGI you won't be maintaining persistent database connections across page requests. However, let me answer the main question you did ask:

"My question is every time I need to fetch data from the MySQL database, do I need to open a connection with database, and after getting the query result close it again?"

If your CGI script needs to run more than one query against the database on a given request, you do not need to close and re-open the connection for each query. Have a look at the DBI docs and you'll see that you can create one database handle per request, which you can use to run an arbitrary amount of queries against your database as long as the connection remains open.

Moving forward, I'd encourage you to look at DBIx::Class, which can abstract away much of these details for you. It's a lot to get your head around if you're just getting started with Perl and databases, but it's a real lifesaver once you get comfortable with it.

oalders
  • 5,239
  • 2
  • 23
  • 34