I've implemented oracle connection Pooling by hand... i've tried the
System.Data.SqlClient algorithm...
It seems to work perfectly with Oracle... it speed up my application by two...
Here's the patch i've done for that, could you tell me if something is
wrong...
NB: there's many verbose message inside...
Le Mercredi 02 Mars 2005 04:32, Daniel Morgan a �crit�:
> 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 it
> via the following code:
>
> string stringToPassToOracle = "Hello";
> int rsize = 0;
>
> // Get size of buffer
> OciCalls.OCIUnicodeToCharSet (statement.Parent, null, sDate, out rsize);
>
> // Fill buffer
> byte[] bytes = 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�vrier 2005 13:55, Daniel Morgan a �crit :
> >>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/oci15
> >>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 oracle
> >>>client classes. I've seen that dealing with oracle database works well
> >>>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�vrier 2005 05:38, vous avez �crit :
> >>>>I recently committed a fix to svn trunk HEAD for strings in
> >>>>OracleParameter to use OCIUnicodeToCharSet for Oracle data types dates,
> >>>>CLOBs, VARCHAR2/CHAR, etc...
> >>>>
> >>>>Can you try your tests again to see if they work now? I do not know
> >>>>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 mono
> >>>>> client), french "��" are seen as "??" in the database (MS.NET client
> >>>>> see "??"). The only way to display the "��" correctly is to set my
> >>>>> NLS_LANG to : NLS_LANG="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 "��" the string is "cut" by oracle,
> >>>>> so I can not see the entire string in the database...
> >>
> >>_______________________________________________
> >>Mono-list maillist - [email protected]
> >>http://lists.ximian.com/mailman/listinfo/mono-list
>
> _______________________________________________
> Mono-list maillist - [email protected]
> http://lists.ximian.com/mailman/listinfo/mono-list
--
Hubert FONGARNAND
Fiducial IT
_______________________________________________
Ce message et les �ventuels documents joints peuvent contenir des informations
confidentielles.
Au cas o� il ne vous serait pas destin�, nous vous remercions de bien vouloir
le supprimer et en aviser imm�diatement l'exp�diteur. Toute utilisation de ce
message non conforme � sa destination, toute diffusion ou publication, totale
ou partielle et quel qu'en soit le moyen est formellement interdite.
Les communications sur internet n'�tant pas s�curis�es, l'int�grit� de ce
message n'est pas assur�e et la soci�t� �mettrice ne peut �tre tenue pour
responsable de son contenu.
Index: mcs/class/System.Data.OracleClient/System.Data.OracleClient/OracleConnectionPool.cs
===================================================================
--- mcs/class/System.Data.OracleClient/System.Data.OracleClient/OracleConnectionPool.cs (revision 0)
+++ mcs/class/System.Data.OracleClient/System.Data.OracleClient/OracleConnectionPool.cs (revision 0)
@@ -0,0 +1,148 @@
+
+//
+// OracleConnection.cs
+//
+// 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:
+// Hubert FONGARNAND <[EMAIL PROTECTED]>
+//
+//
+//
+//
+// 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
+{
+ internal class OracleConnectionPoolManager
+ {
+ Hashtable pools=new Hashtable();
+
+ public OracleConnectionPoolManager()
+ {
+ Console.WriteLine("OracleConnectionPool.cs: Cr�ation du manager de pool");
+ }
+
+ public OracleConnectionPool GetConnectionPool(OracleConnectionInfo info)
+ {
+ string connectionString=info.Username+info.Password+info.Database;
+ lock(pools)
+ {
+
+ OracleConnectionPool pool=(OracleConnectionPool) pools[connectionString];
+ if (pool==null)
+ {
+ Console.WriteLine("Il n'existe pas encore de piscine pour cette connexion : "+ connectionString);
+ pool=new OracleConnectionPool(this, info);
+ pools[connectionString]=pool;
+ }
+ return pool;
+ }
+ }
+
+ public virtual OciGlue CreateConnection(OracleConnectionInfo info)
+ {
+ OciGlue oci;
+ oci=new OciGlue();
+ oci.CreateConnection(info);
+ return oci;
+ }
+ }
+
+ internal class OracleConnectionPool
+ {
+ ArrayList list = new ArrayList(); // Liste contenant les connexions non utilis�s...
+ OracleConnectionInfo info;
+ OracleConnectionPoolManager manager;
+ bool initialized;
+ int activeConnections=0;
+ const int PoolMinSize=3;
+ const int PoolMaxSize=10;
+
+
+ public OracleConnectionPool(OracleConnectionPoolManager manager, OracleConnectionInfo info)
+ {
+ this.info=info;
+ this.manager=manager;
+ initialized=false;
+ }
+
+ public OciGlue GetConnection()
+ {
+ OciGlue connection=null;
+ lock(list)
+ {
+ if (!initialized)
+ {
+ Console.WriteLine("Initialisation de la piscine de connections");
+ for (int n=0;n<PoolMinSize;n++)
+ list.Add(CreateConnection());
+ initialized=true;
+ }
+ do {
+ if (list.Count>0)
+ {
+ // There are available connections in the pool
+ connection=(OciGlue)list[list.Count - 1];
+ list.RemoveAt(list.Count -1);
+ if (!connection.Connected){
+ connection=null;
+ Console.WriteLine("OracleConnectionPool.cs : !!!! Strange, la connection est fermee...");
+ continue;
+ }
+ }
+
+ if (connection == null && activeConnections < PoolMaxSize)
+ {
+ Console.WriteLine("Il ne reste plus de connections dans la piscine, on en recr�e une...");
+ connection=CreateConnection();
+ }
+ // Pas de connection disponible on attends que quelqu'un en libere une
+ if (connection==null)
+ {
+ Monitor.Wait(list);
+ }
+ } while(connection==null);
+ }
+ return connection;
+ }
+
+ public void ReleaseConnection (OciGlue connection)
+ {
+ lock(list)
+ {
+ Console.WriteLine("On lib�re la connection");
+ list.Add(connection);
+ Monitor.Pulse(list);
+ }
+ }
+
+ OciGlue CreateConnection()
+ {
+ activeConnections++;
+ return manager.CreateConnection(info);
+ }
+
+
+
+
+ }
+
+
+}
\ No newline at end of file
Index: mcs/class/System.Data.OracleClient/System.Data.OracleClient/OracleConnection.cs
===================================================================
--- mcs/class/System.Data.OracleClient/System.Data.OracleClient/OracleConnection.cs (revision 41358)
+++ mcs/class/System.Data.OracleClient/System.Data.OracleClient/OracleConnection.cs (working copy)
@@ -52,6 +52,9 @@
OracleTransaction transaction = null;
string connectionString = "";
OracleDataReader dataReader = null;
+ const bool pooling=true;
+ static OracleConnectionPoolManager Pools=new OracleConnectionPoolManager();
+ OracleConnectionPool pool;
#endregion // Fields
@@ -60,7 +63,8 @@
public OracleConnection ()
{
state = ConnectionState.Closed;
- oci = new OciGlue ();
+ //if (pooling)
+ // oci = new OciGlue ();
}
public OracleConnection (string connectionString)
@@ -291,7 +295,16 @@
public void Open ()
{
- oci.CreateConnection (conInfo);
+ Console.WriteLine("OracleConnection.cs :Ouverture de la connexion");
+ if (!pooling)
+ { oci=new OciGlue();
+ oci.CreateConnection (conInfo);
+ }
+ else
+ {
+ pool=Pools.GetConnectionPool(conInfo);
+ oci=pool.GetConnection();
+ }
state = ConnectionState.Open;
CreateStateChange (ConnectionState.Closed, ConnectionState.Open);
}
@@ -322,10 +335,14 @@
public void Close ()
{
+ Console.WriteLine("OracleConnection.cs : Fermeture de la connexion");
if (transaction != null)
transaction.Rollback ();
- oci.Disconnect ();
+ if (!pooling) {
+ oci.Disconnect ();
+ }else
+ if (pool!=null) pool.ReleaseConnection(oci);
state = ConnectionState.Closed;
CreateStateChange (ConnectionState.Open, ConnectionState.Closed);
}
Index: mcs/class/System.Data.OracleClient/System.Data.OracleClient.dll.sources
===================================================================
--- mcs/class/System.Data.OracleClient/System.Data.OracleClient.dll.sources (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
