in reply to Best practices for closing database connections?

Hello Polyglot,

> Should the connection be explicitly closed? and if so, should some command, e.g. "commit", be given first? Should a single connection be opened, and all queries funneled through that before closing it (and the script itself)?

In my limited experience I saw disconnect as soon as possible, obviously after having committed your transactions (there is also autocommit available). And yes, one single connection as suggested by wiser hippo.

Tricks with DBI is still worth to read after (ugh!) 22 years, as The fine art of database programming, Databases made easy and notably DBI recipes by our sleeping brother gmax

PS from Programming the Perl DBI

> Explicit disconnection from the database is not strictly necessary if you are exiting from your program after you have performed all the work, but it is a good idea. We strongly recommend that you get into the habit of disconnecting explicitly.

L*

There are no rules, there are no thumbs..
Reinvent the wheel, then learn The Wheel; may be one day you reinvent one of THE WHEELS.

Replies are listed 'Best First'.
Re^2: Best practices for closing database connections?
by Polyglot (Chaplain) on Mar 17, 2022 at 11:28 UTC

    Thank you for the tips and those links. Those are helpful.

    The problem I have with understanding or using placeholders, as mentioned in the first link, is that my queries are all different. For example, I query the user's privileges, usage statistics, preferences, the language parameters for the browser interface, the content for various divs on the page, etc., in addition to setting certain parameters in the database such as updating the user's last login/connection time. Much of this is repeated with each subsequent AJAX-driven request from the client's browser, but each time it is a new and separate calling of the script. There's hardly a single query that would be consistently run multiple times within a single execution of the script, so I am baffled as to the benefits of placeholders in such a case.

    In fact, seeing as each of these queries originates in a separate subroutine of the script, it will be challenging enough for me to open one global connection for all such queries and then close it before the script exits, rather than opening the connection for each query as I have done to date.

    Blessings,

    ~Polyglot~

[reply]
[d/l]
      I am baffled as to the benefits of placeholders in such a case.

      I strongly recommend that you type "SQL injection attack" into your search engine of choice.

      See also: Little Bobby Tables.

      🦛

[reply]

        Thank you for the reminder. In my case, the following points seem pertinent.

        1. No one accesses these webpages/queries without logging in, and those logging in are definitely not computer-savvy enough to deliberately inject code such as for dropping tables; much less would they have the desire, seeing as it is their own work at stake.

        2. I like that cartoon, and based on seeing that some time back I actually did create a subroutine that sanitizes the query before passing it to the database.

        3. I still don't understand how placeholders would be implemented in my case without creating less efficiency in my code--as opposed to streamlining it. Is it not the case that the main purpose is to make the queries run more efficiently?

        Blessings,

        ~Polyglot~

[reply]

In Section Seekers of Perl Wisdom