[Mono-list] Oracle Connection Pooling
Hubert FONGARNAND
hubertf-1@altern.org
Wed, 2 Mar 2005 16:15:38 +0100
--Boundary-00=_bidJCvhErBfBxsf
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
I've implemented oracle connection Pooling by hand... i've tried the=20
System.Data.SqlClient algorithm...
It seems to work perfectly with Oracle... it speed up my application by t=
wo...
Here's the patch i've done for that, could you tell me if something is=20=
wrong...
NB: there's many verbose message inside...
Le Mercredi 02 Mars 2005 04:32, Daniel Morgan a =E9crit=A0:
> IntPtr is fine since the OCI types are opaque. Try looking at
> OciCalls.cs and OciGlue.cs for examples.
> CreateConnection shows how a connection to Oracle currently happens.
>
> ub4 is an unisigned four-byte integer which might work with uint.
> sb4 is a signed four-byte integer which might work with int.
> (text *) can be passed via a byte array.
>
> Many times when passing a string to Oracle, you might need to convert i=
t
> via the following code:
>
> string stringToPassToOracle =3D "Hello";
> int rsize =3D 0;
>
> // Get size of buffer
> OciCalls.OCIUnicodeToCharSet (statement.Parent, null, sDate, out rsize)=
;
>
> // Fill buffer
> byte[] bytes =3D new byte[rsize];
> OciCalls.OCIUnicodeToCharSet (statement.Parent, bytes, sDate, out rsize=
);
>
>
> sword OCIConnectionPoolCreate ( OCIEnv *envhp,
> OCIError *errhp,
> OCICPool *poolhp,
> OraText **poolName,
> sb4 *poolNameLen,
> CONST OraText *dblink,
> sb4 dblinkLen,
> ub4 connMin,
> ub4 connMax,
> ub4 connIncr,
> CONST OraText *poolUsername,
> sb4 poolUserLen,
> CONST OraText *poolPassword,
> sb4 poolPassLen,
> ub4 mode );
>
> Hubert FONGARNAND wrote:
> >I'm interested in implement connection pooling on oracle...
> >I just need some info about creating dllimport functions....
> >
> >The C function is :
> > OCIOCIConnectionPoolCreate((OCIEnv *)envhp,
> > (OCIError *)errhp, (OCICPool *)poolhp,
> > &poolName, &poolNameLen,
> > (text *)database,strlen(database),
> > (ub4) conMin, (ub4) conMax, (ub4) conIncr,
> > (text *)pooluser,strlen(pooluser),
> > (text *)poolpasswd,strlen(poolpasswd),
> > OCI_DEFAULT));
> >
> >i've began :
> >
> > //fongarnand
> > [DllImport ("oci")]
> > internal static extern int OCIConnectionPoolCreate(IntPtr envhp,
> > IntPtr errhp, IntPtr poolhp, ...
> >
> >But i don't know wich datatype to use...
> >
> >Le Lundi 28 F=E9vrier 2005 13:55, Daniel Morgan a =E9crit :
> >>Sure, you can implement. We accept patches.
> >>
> >>You could look at System.Data.SqlClient on how it does connection
> >> pooling.
> >>
> >>Another way is to use some OCI functions for connection pooling. So,=
> >>you might have to create DllImport functions for these OCI functions.=
> >>
> >>Oracle 9i Call Interface Programmer's Guide
> >>http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96584/o=
ci15
> >>re 2.htm#556061
> >>
> >>OCIConnectionPoolCreate
> >>OCIConnectionPoolDestroy
> >>
> >>Hubert FONGARNAND wrote:
> >>>Thank you for your fix... It works now without any problems...
> >>>Just a question, do you plan to implement connection pooling on orac=
le
> >>>client classes. I've seen that dealing with oracle database works we=
ll
> >>>with mono (now) but it's much more slower than MS.NET when you open =
and
> >>>close many connections... Is there a way to speed up this process.
> >>>I'm ready to help you implementing this, if you need...
> >>>
> >>>Le Samedi 26 F=E9vrier 2005 05:38, vous avez =E9crit :
> >>>>I recently committed a fix to svn trunk HEAD for strings in
> >>>>OracleParameter to use OCIUnicodeToCharSet for Oracle data types da=
tes,
> >>>>CLOBs, VARCHAR2/CHAR, etc...
> >>>>
> >>>>Can you try your tests again to see if they work now? I do not kno=
w
> >>>>French to test the changes. :-)
> >>>>
> >>>>Hubert FONGARNAND wrote:
> >>>>>Thanks to Daniel Morgan
> >>>>>DataTime and OracleDateTime works now perfectly. There's still a
> >>>>> problem with charset.When my NLS_LANG variable isn't set (on my m=
ono
> >>>>> client), french "=E9=E8" are seen as "??" in the database (MS.NET=
client
> >>>>> see "??"). The only way to display the "=E9=E8" correctly is to s=
et my
> >>>>> NLS_LANG to : NLS_LANG=3D"FRENCH_FRANCE.UTF8" (but normally, the =
client
> >>>>> should adapt itself to the server settings)
> >>>>>but there's still a problem with string length. eg: when I do an
> >>>>>"INSERT" with a string with some "=E9=E8" the string is "cut" by o=
racle,
> >>>>> so I can not see the entire string in the database...
> >>
> >>_______________________________________________
> >>Mono-list maillist - Mono-list@lists.ximian.com
> >>http://lists.ximian.com/mailman/listinfo/mono-list
>
> _______________________________________________
> Mono-list maillist - Mono-list@lists.ximian.com
> http://lists.ximian.com/mailman/listinfo/mono-list
--=20
Hubert FONGARNAND
Fiducial IT
_______________________________________________
Ce message et les =E9ventuels documents joints peuvent contenir des infor=
mations confidentielles.
Au cas o=F9 il ne vous serait pas destin=E9, nous vous remercions de bien=
vouloir le supprimer et en aviser imm=E9diatement l'exp=E9diteur. Toute =
utilisation de ce message non conforme =E0 sa destination, toute diffusio=
n ou publication, totale ou partielle et quel qu'en soit le moyen est for=
mellement interdite.
Les communications sur internet n'=E9tant pas s=E9curis=E9es, l'int=E9gri=
t=E9 de ce message n'est pas assur=E9e et la soci=E9t=E9 =E9mettrice ne p=
eut =EAtre tenue pour responsable de son contenu.
--Boundary-00=_bidJCvhErBfBxsf
Content-Type: text/x-diff;
charset="iso-8859-1";
name="patch_oracle_pool"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="patch_oracle_pool"
Index: mcs/class/System.Data.OracleClient/System.Data.OracleClient/OracleCo=
nnectionPool.cs
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=2D-- mcs/class/System.Data.OracleClient/System.Data.OracleClient/OracleCon=
nectionPool.cs (revision 0)
+++ mcs/class/System.Data.OracleClient/System.Data.OracleClient/OracleConne=
ctionPool.cs (revision 0)
@@ -0,0 +1,148 @@
+
+//
+// OracleConnection.cs=20
+//
+// Part of the Mono class libraries at
+// mcs/class/System.Data.OracleClient/System.Data.OracleClient
+//
+// Assembly: System.Data.OracleClient.dll
+// Namespace: System.Data.OracleClient
+//
+// Authors:=20
+// Hubert FONGARNAND <informatique.internet@fiducial.fr>
+// =20
+//
+//
+//
+// Licensed under the MIT/X11 License.
+//
+
+using System;
+using System.Collections;
+using System.Collections.Specialized;
+using System.ComponentModel;
+using System.Data;
+using System.Data.OracleClient.Oci;
+using System.Drawing.Design;
+using System.EnterpriseServices;
+using System.Text;
+using System.Threading;
+
+namespace System.Data.OracleClient=20
+{
+ internal class OracleConnectionPoolManager
+ {
+ Hashtable pools=3Dnew Hashtable();
+ =09
+ public OracleConnectionPoolManager()
+ {
+ Console.WriteLine("OracleConnectionPool.cs: Cr=E9ation du manager de po=
ol");
+ }
+ =09
+ public OracleConnectionPool GetConnectionPool(OracleConnectionInfo info)
+ {
+ string connectionString=3Dinfo.Username+info.Password+info.Database;=20
+ lock(pools)
+ {
+ =09
+ OracleConnectionPool pool=3D(OracleConnectionPool) pools[connectionStr=
ing];
+ if (pool=3D=3Dnull)
+ {
+ Console.WriteLine("Il n'existe pas encore de piscine pour cette conne=
xion : "+ connectionString);
+ pool=3Dnew OracleConnectionPool(this, info);
+ pools[connectionString]=3Dpool;
+ }
+ return pool;
+ }
+ }
+ =09
+ public virtual OciGlue CreateConnection(OracleConnectionInfo info)
+ {
+ OciGlue oci;
+ oci=3Dnew OciGlue();
+ oci.CreateConnection(info);
+ return oci;
+ }
+ }
+=09
+ internal class OracleConnectionPool
+ {
+ ArrayList list =3D new ArrayList(); // Liste contenant les connexions no=
n utilis=E9s...
+ OracleConnectionInfo info;
+ OracleConnectionPoolManager manager;
+ bool initialized;
+ int activeConnections=3D0;
+ const int PoolMinSize=3D3;
+ const int PoolMaxSize=3D10;
+ =09
+ =09
+ public OracleConnectionPool(OracleConnectionPoolManager manager, OracleC=
onnectionInfo info)
+ {
+ this.info=3Dinfo;
+ this.manager=3Dmanager;
+ initialized=3Dfalse;
+ }
+ =09
+ public OciGlue GetConnection()
+ {
+ OciGlue connection=3Dnull;
+ lock(list)
+ {
+ if (!initialized)
+ {
+ Console.WriteLine("Initialisation de la piscine de connections");
+ for (int n=3D0;n<PoolMinSize;n++)
+ list.Add(CreateConnection());
+ initialized=3Dtrue;
+ }
+ do {
+ if (list.Count>0)
+ {
+ // There are available connections in the pool
+ connection=3D(OciGlue)list[list.Count - 1];
+ list.RemoveAt(list.Count -1);
+ if (!connection.Connected){
+ connection=3Dnull;
+ Console.WriteLine("OracleConnectionPool.cs : !!!! Strange, la conne=
ction est fermee...");
+ continue;
+ }
+ }
+ =09
+ if (connection =3D=3D null && activeConnections < PoolMaxSize)
+ {
+ Console.WriteLine("Il ne reste plus de connections dans la piscine, =
on en recr=E9e une...");
+ connection=3DCreateConnection();
+ }
+ // Pas de connection disponible on attends que quelqu'un en libere une
+ if (connection=3D=3Dnull)
+ {
+ Monitor.Wait(list);
+ }
+ } while(connection=3D=3Dnull);
+ }
+ return connection;
+ }
+ =09
+ public void ReleaseConnection (OciGlue connection)
+ {
+ lock(list)
+ {
+ Console.WriteLine("On lib=E8re la connection");
+ list.Add(connection);
+ Monitor.Pulse(list);
+ }
+ }
+ =09
+ OciGlue CreateConnection()
+ {
+ activeConnections++;
+ return manager.CreateConnection(info);
+ }
+ =09
+ =09
+ =09
+=09
+ }
+=09
+=09
+}
\ No newline at end of file
Index: mcs/class/System.Data.OracleClient/System.Data.OracleClient/OracleCo=
nnection.cs
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=2D-- mcs/class/System.Data.OracleClient/System.Data.OracleClient/OracleCon=
nection.cs (revision 41358)
+++ mcs/class/System.Data.OracleClient/System.Data.OracleClient/OracleConne=
ction.cs (working copy)
@@ -52,6 +52,9 @@
OracleTransaction transaction =3D null;
string connectionString =3D "";
OracleDataReader dataReader =3D null;
+ const bool pooling=3Dtrue;
+ static OracleConnectionPoolManager Pools=3Dnew OracleConnectionPoolManag=
er();
+ OracleConnectionPool pool;
=20
#endregion // Fields
=20
@@ -60,7 +63,8 @@
public OracleConnection ()=20
{
state =3D ConnectionState.Closed;
=2D oci =3D new OciGlue ();
+ //if (pooling)
+ // oci =3D new OciGlue ();
}
=20
public OracleConnection (string connectionString)=20
@@ -291,7 +295,16 @@
=20
public void Open ()=20
{
=2D oci.CreateConnection (conInfo);
+ Console.WriteLine("OracleConnection.cs :Ouverture de la connexion");
+ if (!pooling)
+ { oci=3Dnew OciGlue();
+ oci.CreateConnection (conInfo);
+ }
+ else
+ {
+ pool=3DPools.GetConnectionPool(conInfo);
+ oci=3Dpool.GetConnection();
+ }
state =3D ConnectionState.Open;
CreateStateChange (ConnectionState.Closed, ConnectionState.Open);
}
@@ -322,10 +335,14 @@
=20
public void Close ()=20
{
+ Console.WriteLine("OracleConnection.cs : Fermeture de la connexion");
if (transaction !=3D null)
transaction.Rollback ();
=20
=2D oci.Disconnect ();
+ if (!pooling) {
+ oci.Disconnect ();
+ }else
+ if (pool!=3Dnull) pool.ReleaseConnection(oci);
state =3D ConnectionState.Closed;
CreateStateChange (ConnectionState.Open, ConnectionState.Closed);
}
Index: mcs/class/System.Data.OracleClient/System.Data.OracleClient.dll.sour=
ces
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=2D-- mcs/class/System.Data.OracleClient/System.Data.OracleClient.dll.sourc=
es (revision 41358)
+++ mcs/class/System.Data.OracleClient/System.Data.OracleClient.dll.sources=
(working copy)
@@ -33,6 +33,7 @@
System.Data.OracleClient.Oci/OciTransactionFlags.cs
System.Data.OracleClient.Oci/OciTransactionHandle.cs
System.Data.OracleClient/OciGlue.cs
+System.Data.OracleClient/OracleConnectionPool.cs
System.Data.OracleClient/OracleBFile.cs
System.Data.OracleClient/OracleBinary.cs
System.Data.OracleClient/OracleBoolean.cs
--Boundary-00=_bidJCvhErBfBxsf--