Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Tracking number of DBI db connections

SizamSizam Posts: 3Member
Hello,
I'm trying to adopt DBI into one of our perl apps but I need to be able to track the total number of connections the application has to the DB, across sessions. So if 10 people have 10 browsers open to this cgi and there are 10 connections, I need to know that, does DBI support this? I thought maybe $db->{kids} would do this but it only tracks connections for that one db handle in that one session.

Thanks,
Sam

Comments

  • JonathanJonathan Posts: 2,914Member
    : I'm trying to adopt DBI into one of our perl apps but I need to be
    : able to track the total number of connections the application has to
    : the DB, across sessions. So if 10 people have 10 browsers open to
    : this cgi and there are 10 connections, I need to know that, does DBI
    : support this? I thought maybe $db->{kids} would do this but it only
    : tracks connections for that one db handle in that one session.
    I guess you've already looked at the DBI documentation:-
    http://search.cpan.org/author/TIMB/DBI-1.37/DBI.pm

    As far as I know, you create an instance of DBI with every script you run. Thus, that particular instance of DBI doesn't have access to information about any other instances of DBI on the system, just like if you have a variable $a in one Perl script then it has no relation to $a in another script, even if they run at the same time.

    So, you're going to have to have some way of tracking how many connections are open besides DBI I guess. Some crude approaches:-

    1) Have a file that maintains the number. When a script opens a connection, it increments it. When it closes one, it decrements the value. If the script crashes, the value is accurate. If you don't use file locking, you're in for it. ;-)

    2) You might be able to do something by looking at the proccess list.

    3) You could write a "server" and "client" connected through pipes or whatever. The server would proxy and count the DBI connections. This ain't crude though, it's quite possibly overkill, and you could lose performance too. And writing a server can often be forking fun. Of course, I'm referring to the fork system call.

    Wonder if there's a better way?

    Jonathan

    ###
    for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
    (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
    /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");

  • SizamSizam Posts: 3Member
    : : I'm trying to adopt DBI into one of our perl apps but I need to be
    : : able to track the total number of connections the application has to
    : : the DB, across sessions. So if 10 people have 10 browsers open to
    : : this cgi and there are 10 connections, I need to know that, does DBI
    : : support this? I thought maybe $db->{kids} would do this but it only
    : : tracks connections for that one db handle in that one session.
    : I guess you've already looked at the DBI documentation:-
    : http://search.cpan.org/author/TIMB/DBI-1.37/DBI.pm
    :
    : As far as I know, you create an instance of DBI with every script you run. Thus, that particular instance of DBI doesn't have access to information about any other instances of DBI on the system, just like if you have a variable $a in one Perl script then it has no relation to $a in another script, even if they run at the same time.
    :
    : So, you're going to have to have some way of tracking how many connections are open besides DBI I guess. Some crude approaches:-
    :
    : 1) Have a file that maintains the number. When a script opens a connection, it increments it. When it closes one, it decrements the value. If the script crashes, the value is accurate. If you don't use file locking, you're in for it. ;-)
    :
    : 2) You might be able to do something by looking at the proccess list.
    :
    : 3) You could write a "server" and "client" connected through pipes or whatever. The server would proxy and count the DBI connections. This ain't crude though, it's quite possibly overkill, and you could lose performance too. And writing a server can often be forking fun. Of course, I'm referring to the fork system call.
    :
    : Wonder if there's a better way?
    :
    : Jonathan
    :
    : ###
    : for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
    : (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
    : /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");

    Dang, figured as much, unfortunatly none of those are viable options for me as our local perl godheads would drop a brick if I wrote out to a file or got overly complicated and I don't think the processes list will be iron-clad enough for them. Our own personal DBlibs use the PerlSyb libraries which keep track of connections in a db_processes table within the db but you need to use Sybase::DBlib->dbopen to do so. So I think my only hope is to try to modify DBI to use Sybase::DBlib->dbopen to open a connection if the Sybase driver is used. Now I just gotta hack up DBI.pm which isn't going well at the moment :P

    Sam
  • JonathanJonathan Posts: 2,914Member
    : Dang, figured as much, unfortunatly none of those are viable options
    : for me as our local perl godheads would drop a brick if I wrote out
    : to a file or got overly complicated and I don't think the processes
    : list will be iron-clad enough for them. Our own personal DBlibs use
    : the PerlSyb libraries which keep track of connections in a
    : db_processes table within the db but you need to use Sybase::DBlib-
    : >dbopen to do so. So I think my only hope is to try to modify DBI
    : to use Sybase::DBlib->dbopen to open a connection if the Sybase
    : driver is used. Now I just gotta hack up DBI.pm which isn't going
    : well at the moment :P
    I'd recommend, rather than hacking up DBI.pm (a bad idea IMHO), that you create another class that either inherits from it OR contains it. You may find you only need to over-ride the connect and disconnect methods, and maybe implement a destructor, and you can inherit all the other stuff. You can even give your work back to the Perl community, that gave you DBI in the first place. ;-)

    Jonathan

    ###
    for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
    (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
    /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");

  • SizamSizam Posts: 3Member
    : : Dang, figured as much, unfortunatly none of those are viable options
    : : for me as our local perl godheads would drop a brick if I wrote out
    : : to a file or got overly complicated and I don't think the processes
    : : list will be iron-clad enough for them. Our own personal DBlibs use
    : : the PerlSyb libraries which keep track of connections in a
    : : db_processes table within the db but you need to use Sybase::DBlib-
    : : >dbopen to do so. So I think my only hope is to try to modify DBI
    : : to use Sybase::DBlib->dbopen to open a connection if the Sybase
    : : driver is used. Now I just gotta hack up DBI.pm which isn't going
    : : well at the moment :P
    : I'd recommend, rather than hacking up DBI.pm (a bad idea IMHO), that you create another class that either inherits from it OR contains it. You may find you only need to over-ride the connect and disconnect methods, and maybe implement a destructor, and you can inherit all the other stuff. You can even give your work back to the Perl community, that gave you DBI in the first place. ;-)
    :
    : Jonathan
    :
    : ###
    : for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
    : (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
    : /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");

    Yah, you're right, I was kinda thinking I should do it along those lines but then I found the DBD::Sybase.pm database module for DBI. In there, they mention extra arguments you can pass to the Connect method like this wonderful little gem: scriptName

    $dbh->DBI->connect("dbi:Sybase:scriptName=myScript", $user, $password);

    This is exactly what I was looking for, now I can check the master_process table in the db :) Those DBI guys are alright in my book.

    Thanks for the discussion!
  • JonathanJonathan Posts: 2,914Member
    : Yah, you're right, I was kinda thinking I should do it along those
    : lines but then I found the DBD::Sybase.pm database module for DBI.
    : In there, they mention extra arguments you can pass to the Connect
    : method like this wonderful little gem: scriptName
    :
    : $dbh->DBI->connect("dbi:Sybase:scriptName=myScript", $user, $password);
    :
    : This is exactly what I was looking for, now I can check the
    : master_process table in the db :)
    Ahhhh...yes! I'm from a MySQL background rather than Sybase, but in MySQL you can look at the db's proccess table too. Never thought of that...thanks for letting me know how you did it though!

    : Those DBI guys are alright in my book.
    Yeah, they do some great work and it's easy to forget how much people rely on DBI. Then, the things that "just work" are often easy to forget.

    : Thanks for the discussion!
    Thank you. :-)

    Jonathan

    ###
    for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
    (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
    /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");

Sign In or Register to comment.