[Mono-list] Oracle data provider in Mono

Daniel Morgan danmorg@sc.rr.com
Mon, 17 Feb 2003 03:32:14 -0500


Hello,

Just in case you don't pay attention to the cvs commits.  Tim Coleman got
the Oracle ADO.NET provider (System.Data.OracleClient) to retrieve data from
an Oracle 8i database.  In current cvs Mono, you can only retrieve simple
character data.

For example, this example SQL works with the sample database installed by
Oracle:

"SELECT ename, job FROM emp"

Sample C# source code follows at the bottom.

OracleClient builds and runs on Linux and Windows.  It uses OCI (Oracle
Call-level Interface) which is a C API included in the Oracle Client
software for accessing Oracle databases.

Tim also removed the dependencies on a "glue" library between the
System.Data.OracleClient.dll assembly and OCI.   So, if you have a working
Mono and the Oracle client software installed, you  can build and use the
System.Data.OracleClient provider.

The OracleClient provider can retrieve simple character data using the SQL#
command-line and GTK# GUI versions in Mono.

Tim said he is now working on handling of data types and a data adapter for
the OracleClient provider.

C# Oracle sample
----------------

// MonoOracleSample.cs
//
// To build on Linux:
// mcs MonoOracleSample.cs \
//     -r System.Data.dll -r System.Data.OracleClient.dll
// To build on Windows via Cygwin:
// mono C:/cygwin/home/DanielMorgan/mono/install/bin/mcs.exe \
//      MonoOracleSample.cs \
//      -lib:/cygwin/home/DanielMorgan/mono/install/lib \
//      -r System.Data.dll -r System.Data.OracleClient.dll
using System;
using System.Data;
using System.Data.OracleClient;

public class MonoOracleSample
{
	public static void Main(string[] args)
	{
		string connectionString = String.Format(
			"Data Source={0};" +
			"User ID={1};" +
			"Password={2}",
			args[0], args[1], args[2]);

		OracleConnection con1 = new OracleConnection();
		con1.ConnectionString = connectionString;
		con1.Open();

		string selectSql =
			"SELECT ename, job FROM scott.emp";

		OracleCommand cmd = new OracleCommand();
		cmd.Connection = con1;
		cmd.CommandText = selectSql;
		OracleDataReader reader = cmd.ExecuteReader();
		Console.WriteLine("Schema");
		DataTable table;
		table = reader.GetSchemaTable();
		for(int c = 0; c < reader.FieldCount; c++) {
			Console.WriteLine("  Column " + c.ToString());
			DataRow row = table.Rows[c];

			string ColumnName = (string) row["ColumnName"];
			string BaseColumnName = (string) row["BaseColumnName"];
			int ColumnSize = (int) row["ColumnSize"];
			int NumericScale = Convert.ToInt32( row["NumericScale"]);
			int NumericPrecision = Convert.ToInt32(row["NumericPrecision"]);
			Type DataType = (Type) row["DataType"];
			Console.WriteLine("    ColumnName: " + ColumnName);
			Console.WriteLine("    BaseColumnName: " + BaseColumnName);
			Console.WriteLine("    ColumnSize: " + ColumnSize.ToString());
			Console.WriteLine("    NumericScale: " + NumericScale.ToString());
			Console.WriteLine("    NumericPrecision: " +
NumericPrecision.ToString());
			Console.WriteLine("    DataType: " + DataType.ToString());
		}

		int row = 0;
		Console.WriteLine("Data");
		while(reader.Read()) {
			row++;
			Console.WriteLine("  Row: " + row.ToString());
			for(int f = 0; f < reader.FieldCount; f++) {
				object ovalue;
				string svalue;
				ovalue = reader.GetValue(0);
				svalue = ovalue.ToString();
				Console.WriteLine("     Field: " + f.ToString());
				Console.WriteLine("         Value: " + svalue);
			}
		}
		if(row == 0)
			Console.WriteLine("No data returned.");

		reader.Close();
		reader = null;
		cmd.Dispose();
		cmd = null;
		con1.Close();
		con1 = null;
	}
}