[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--