Re: DBD::Oracle DRCP_1.25

[luben karavelov]

As I have promissed, here comes the documentation patch. I am not native 
speeker, so may be it will need an edit.


Also, I have added processing of environment valiables ORA_POOL_CLASS,
ORA_POOL_MIN, ORA_POOL_MAX, ORA_POOL_INCR if there is ORA_DRCP env set.

Best regards
luben
Index: Oracle.pm
===
--- Oracle.pm   (revision 14227)
+++ Oracle.pm   (working copy)
@@ -227,14 +227,26 @@
# create a 'blank' dbh
 
$user = '' if not defined $user;
-(my $user_only = $user) =~ s:/.*::;
-
-if (substr($dbname,-7,7) eq ':POOLED'){
-   $dbname=substr($dbname,0,-7);
-   $attr-{ora_drcp} = 1;
+(my $user_only = $user) =~ s:/.*::;
+
+if (substr($dbname,-7,7) eq ':POOLED'){
+   $dbname=substr($dbname,0,-7);
+   $attr-{ora_drcp} = 1;
+}
+elsif ($ENV{ORA_DRCP}){ 
+$attr-{ora_drcp} = 1;
+if (exists $ENV{ORA_POOL_CLASS}) {
+$attr-{ora_pool_class} = $ENV{ORA_POOL_CLASS}
 }
-elsif ($ENV{ORA_DRCP}){ 
-  $attr-{ora_drcp} = 1;
+if (exists $ENV{ORA_POOL_MIN}) {
+$attr-{ora_pool_min} = $ENV{ORA_POOL_MIN}
+}
+if (exists $ENV{ORA_POOL_MAX}) {
+$attr-{ora_pool_max} = $ENV{ORA_POOL_MAX}
+}
+if (exists $ENV{ORA_POOL_INCR}) {
+$attr-{ora_pool_incr} = $ENV{ORA_POOL_INCR}
+}
}

my ($dbh, $dbh_inner) = DBI::_new_dbh($drh, {
@@ -1346,7 +1358,7 @@
 
   $dbh = DBI-connect('dbi:Oracle:','usern...@db:POOLED','password')
   
-  $dbh = DBI-connect('dbi:Oracle:DB','username','password',{ora_drcp=1})
+  $dbh = DBI-connect('dbi:Oracle:DB','username','password',{ora_drcp=1, 
ora_pool_class='my_app', ora_pool_min=10})
 
   $dbh = 
DBI-connect('dbi:Oracle:host=foobar;sid=ORCL;port=1521;SERVER=POOLED', 
'scott/tiger', '')
 
@@ -1354,7 +1366,7 @@
   (ADDRESS=(PROTOCOL=TCP)(HOST= foobar)(PORT=1521))
   (CONNECT_DATA=(SID=ORCL)(SERVER=POOLED)))}, )
 
-  if ORA_DRCP environemt var is set the just this
+  if ORA_DRCP and ORA_POOL_CLASS environemt var are set you could use just this
   
   $dbh = DBI-connect('dbi:Oracle:DB','username','password')
  
@@ -1530,13 +1542,60 @@
 
 =item ora_drcp
 
-If you have an 11.2 or greater database your can utilize the the DRCP by 
setting
-this attribute to 1 at connect time. 
+If you have an 11.2 or greater database your can utilize the the DRCP 
+(Database Resident Connection Pooling) by setting this attribute to 1 
+at connect time. 
 
 For convenience I have added support for a 'ORA_DRCP'
 environment variable that you can use at the OS level to set this
 value.  If used it will take the value at the connect stage.
 
+=item ora_pool_class
+
+If you use DRCP, you could set CONNECTION_CLASS at connection time.
+
+The connections are pooled based on unique SID/USERNAME/CONNECTION_CLASS.
+If you do not set the class attribute the connections will be pooled only
+within the boundaries of the process, becoming equivalent to a plain 
+client-side session pooling application. 
+
+For convenience I have added support for a 'ORA_POOL_CLASS'
+environment variable that you can use at the OS level to set this
+value. It will be used only if you use 'ORA_DRCP' environment variable.
+If used it will take the value at the connect stage.
+
+=item ora_pool_min
+
+If you use DRCP, you could set initial servers in the pool that will get
+started. Default value is 4 as per DBMS_CONNECTION_POOL default.
+
+For convenience I have added support for a 'ORA_POOL_MIN'
+environment variable that you can use at the OS level to set this
+value. It will be used only if you use 'ORA_DRCP' environment variable.
+If used it will take the value at the connect stage.
+
+
+=item ora_pool_max
+
+If you use DRCP, you could set maximum servers in the pool. Default value 
+is 40 as per DBMS_CONNECTION_POOL default.
+
+For convenience I have added support for a 'ORA_POOL_MAX'
+environment variable that you can use at the OS level to set this
+value. It will be used only if you use 'ORA_DRCP' environment variable.
+If used it will take the value at the connect stage.
+
+=item ora_pool_incr
+
+If you use DRCP, you could set how much new servers will get started if all
+servers in the pool are busy. Default value is 2 as per DBMS_CONNECTION_POOL
+default.
+
+For convenience I have added support for a 'ORA_POOL_INCR'
+environment variable that you can use at the OS level to set this
+value. It will be used only if you use 'ORA_DRCP' environment variable.
+If used it will take the value at the connect stage.
+
 =item ora_session_mode
 
 The ora_session_mode attribute can be used to connect with SYSDBA

Re: Async notifications

[luben karavelov]

Tim Bunce wrote:

Since it's very postgres specific I'd suggest it's added to DBD::Pg
as a set of new driver-specific methods:

  $dbh = DBI-connect($data_source, $username, $auth, \%attr);
  $dbh-pg_register_listener(foo,\foo);  # on foo notification call foo sub
  $dbh-pg_register_listener(bar,\foo);  # on bar notification call foo sub
  $dbh-pg_register_listener(bar, undef); # unregister callback for bar
  $dbh-pg_register_listener(baz, sub { print baz\n } );
  $dbh-pg_listen(); # loops forever waiting for events

Ideally pg_listen should integrate with existing perl event loops
so it can be used in Tk, POE etc.

Tim.




I see what you mean. I will look to integrate the functionality in DBD::pg.

Best regards
luben

Async notifications

[luben karavelov]

Hello

I am new to the list. So to present myself. I am mostly SQL/perl/C 
developer, Working with DBI since 2001 mostly with Postgresql.


I have written a small framework to handle postgresql async 
notifications that is in production here for 2 years. I intend to post 
it a CPAN and make it public. So I have some questions.


Is DBI::Listen the right namespace? The module is called DBI::Listen::pg.

Do some other databases have concept of async notifications?

Thanks in advance
Luben

Re: Async notifications

[luben karavelov]

Martin Evans wrote:
I don't know exactly what postgres async notifications are but I know 
DBD::ODBC has limited support for a asynchronous mode which is pretty 
useful for picking up debug output etc from procedures.


DBD::ODBC has odbc_async_exec flag which is described as:

Allow asynchronous execution of queries. Right now, this causes a 
spin-loop (with a small sleep) until the sql is complete. This is 
useful, however, if you want the error handling and asynchronous 
messages (see the err_handler) below. See t/20SQLServer.t for an example 
of this.


Not sure if this helps you at all or not but you did ask about other 
databases.


Martin


It is different concept. In psql you could register listener in one
process with :

LISTEN FOO;

Then if another process executes

NOTIFY FOO;

The first process receives notification.

I use it mostly to execute some code on the system when there is data
inserted, deleted or updated in some table.


Here is an example :

use DBI;
use DBI::Listen::pg;

#Callback function
sub foo {
$dbh = shift;
#... etc.
}

$dbh = DBI-connect($data_source, $username, $auth, \%attr);
$dbl = DBI::Listen::pg-new($dbh);
$dbl-register(’foo’,\foo);  # on foo notification call foo sub
$dbl-register(’bar’,\foo);  # on bar notification call foo sub
$dbl-unregister(’bar’);  # unregister callback for bar
$dbl-register(baz, sub { print baz\n } );
$dbl-run(); # loops forever waiting for events

#-- end

NOTIFY could be executed by query or stored procedure/trigger.


I hope this clarification helps

luben