ODP.NET 11g, Oracle XE 10.2, ObjectARX.NET, Autodesk Map3d 2008, OSGeo FDO 3.2

Author: Jonio, Dennis

Just some basics to let you know my current bias. I do believe that FDO is the way of the way to go. Without a doubt this is the correct track to be on. Just like everything else however, it has a way to go to get to maturity. I do not like the Autodesk FDO provider for Oracle. IMHO, Haris Kurtagic’s King.Oracle Provider is the way to go. Of course his, FDO2FDO is essential and an excellent piece of work.

That being said I really did not wish to deal with all the "weight" that FDO brings to every application. So I thought I would code up an application that moved DWG geometry to an Oracle table. It seemed to me to be a fairly straightforward task since I had put together that UDT for MDSYS.SDO_GEOMETRY. I always wondered why I had not seen some code examples of doing this. Well folks, now I know...

ODP does not play well with Map3d. For those of you who do ObjectARX.NET applications there are significant problems in heap management between ODP and Map3d. The basic premise of this app is to open a DWG as an "aside" using ReadDwgFile(), iterate the geometry, convert it to the correct format and insert it into the table. Let me "cut to the chase" on this one. It ended up that I had to "load" a DWG before any call was made to any ODP.NET classes. (In all fairness to Autodesk they are very clear in that they do not support these foreign database access methods within there architecture.) Once this is done there is no difficulty. I am able to iterate through a list of DWG’s and instantiate the database with ReadDwgFile() any number of times. At least in my case the acad.exe heap is now toast. For example, if after netloading and executing my app I attempt to use FDO within Map 3D it will fail. Just restart Map 3D and all is fine.

I had set up my sdogeometry UDT in a separate class being that I did not wish to duplicate the code across any number of applications. This is of course doable but I had a very frustrating time getting ODP.NET to "see" my UDT class within the Map3d environment. As the documentation reads, Oracle.Access.Client does a reflection on the executing assembly and looks for the OracleCustomTypeMappingAttribute. This works great if your UDT class is internal to your assembly. If you have an external class assembly the alternative is to set up an xml App.config file that Client will look for or add an entry to the machine.config. I never got this to work. I found that I had to actually instantiate an NetSdoGeometry.sdogeometry object within the [CommandMethod("????")] decorated method. Wow … NOT a big sacrifice but it did take me some time to come up with the correct timings.

To do the conversions from Map3d I first used TF.NET to convert to a JTS IGeometry object and to Maksim’s credit this seems to work well. (I did, however, take the time to code up emulating a circle as a linestring.) To do the conversion to sdogeometry I used Oracle’s JGeometry object. Remember I am working with Oracle XE 10.2 and it does not come with the builtin JVM so WKB/WKT are there but return nothing. I choose not to use the monoGIS distribution and did the IKVM byte code conversions myself using the most current JGeometry version I could find. Since I was only interested in the WKB/WKT methods I was not too concerned with the warnings coming out of IKVM. I forget the compile order but I needed these 4: sdoapi, sdoutl, xmlparserv2 and ojdbc5.

At this point a have a fully functional application. Not the fastest piece of code I have done but I certainly do exercise TF.NET. The next step will be to code up the load of the sdogeometry objects directly. But, I am in no hurry. Computer cycles are a lot cheaper than my time.

Oracle's SDO_DIM_ARRAY as UDT and utilizing SDO_MIGRATE.TO_CURRENT as stored procedure

Author: Jonio, Dennis

Utilizing Oracle’s latest ODP for .NET the following is one implementation of the SDO_DIM_ARRAY and SDO_DIM_ELEMENT type(s) as a .NET class.

This one is easy to explain. I needed to use SDO_MIGRATE.TO_CURRENT to fix some ORA-13367: wrong orientation for interior/exterior rings errors. So I needed SDO_DIM_ARRAY and thusly SDO_DIM_ELEMENT because SDO_MIGRATE.TO_CURRENT requires a SDO_DIM_ARRAY as a parameter. I have included my method of utilizing SDO_MIGRATE.TO_CURRENT after the class definitions.

Source code (C#):

using System;
using System.Collections;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace NetSdoDimArray
{
    /// 

    /// ////////////////////////////////////
    /// sdodimarray
    /// ////////////////////////////////////
    /// 
    public class sdodimarray : IOracleCustomType, INullable
    {
        [OracleObjectMapping(0)]
        public sdodimelement[] _dimelements;
        private bool m_bIsNull;

        public bool IsNull
        {
            get
            {
                return m_bIsNull;
            }
        }

        public sdodimelement[] DimElements
        {
            get
            {
                return _dimelements;
            }
            set
            {
                _dimelements = value;
            }
        }

        public static sdodimarray Null
        {
            get
            {
                sdodimarray obj = new sdodimarray();
                obj.m_bIsNull = true;
                return obj;
            }
        }

        public override string ToString()
        {
            if (m_bIsNull)
                return "sdodimarray.Null";
            else
            {
                StringBuilder sb = new StringBuilder();
                foreach (sdodimelement i in _dimelements)
                {
                    sb.Append("sdodimelement(");
                    sb.Append(i._dimname + "=");
                    sb.Append(string.Format("{0:0.#####}", i._lb));
                    sb.Append(",");
                    sb.Append(string.Format("{0:0.#####}", i._ub));
                    sb.Append(",Tol=");
                    sb.Append(i._tolerance.ToString());
                    sb.Append(")");
                }
                return sb.ToString();
            }
        }

        public void ToCustomObject(OracleConnection con, IntPtr pUdt)
        {
            _dimelements = (sdodimelement[])OracleUdt.GetValue(con, pUdt, 0);
        }

        public void FromCustomObject(OracleConnection con, IntPtr pUdt)
        {
            OracleUdt.SetValue(con, pUdt, 0, _dimelements);
        }
    }

    [OracleCustomTypeMapping("MDSYS.SDO_DIM_ARRAY")]
    public class sdodimarrayFactory : IOracleArrayTypeFactory, IOracleCustomTypeFactory
    {
        // IOracleCustomTypeFactory Inteface
        public IOracleCustomType CreateObject()
        {
            return new sdodimarray();
        }

        // IOracleArrayTypeFactory.CreateArray Inteface
        public Array CreateArray(int numElems)
        {
            return new sdodimelement[numElems];
        }

        // IOracleArrayTypeFactory.CreateStatusArray
        public Array CreateStatusArray(int numElems)
        {
            // An OracleUdtStatus[] is not required to store null status information
            // if there is no NULL attribute data in the element array
            return null;
        }
    }

    /// 

    /// ////////////////////////////////////
    /// sdodimelement
    /// ////////////////////////////////////
    /// 
    public class sdodimelement : IOracleCustomType, INullable
    {
        [OracleObjectMapping(0)]
        public string _dimname;

        [OracleObjectMapping(1)]
        public double _lb;

        [OracleObjectMapping(2)]
        public double _ub;

        [OracleObjectMapping(3)]
        public double _tolerance;

        private bool m_bIsNull;

        public bool IsNull
        {
            get
            {
                return m_bIsNull;
            }
        }

        public static sdodimelement Null
        {
            get
            {
                sdodimelement obj = new sdodimelement();
                obj.m_bIsNull = true;
                return obj;
            }
        }

        public override string ToString()
        {
            if (m_bIsNull)
                return "sdodimelement.Null";
            else
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("sdodimelement(");
                sb.Append(_dimname + "=");
                sb.Append(string.Format("{0:0.#####}", _lb));
                sb.Append(",");
                sb.Append(string.Format("{0:0.#####}", _ub));
                sb.Append(",Tol=");
                sb.Append(_tolerance.ToString());
                sb.Append(")");
                return sb.ToString();
            }
        }

        public void ToCustomObject(OracleConnection con, IntPtr pUdt)
        {
            _dimname = (string)OracleUdt.GetValue(con, pUdt, 0);
            _lb = (double)OracleUdt.GetValue(con, pUdt, 1);
            _ub = (double)OracleUdt.GetValue(con, pUdt, 2);
            _tolerance = (double)OracleUdt.GetValue(con, pUdt, 3);
        }

        public void FromCustomObject(OracleConnection con, IntPtr pUdt)
        {
            OracleUdt.SetValue(con, pUdt, 0, _dimname);
            OracleUdt.SetValue(con, pUdt, 1, _lb);
            OracleUdt.SetValue(con, pUdt, 2, _ub);
            OracleUdt.SetValue(con, pUdt, 3, _tolerance);
        }
    }

    [OracleCustomTypeMapping("MDSYS.SDO_DIM_ELEMENT")]
    public class sdodimelementFactory : IOracleCustomTypeFactory
    {
        // IOracleCustomTypeFactory Inteface
        public IOracleCustomType CreateObject()
        {
            sdodimelement sdodimelement = new sdodimelement();
            return sdodimelement;
        }
    }
}

As you see, it utilizes both sdogeometry and sdodimarray...

public static sdogeometry MigrateToCurrentSP(string connectstring, sdogeometry _geoIn, sdodimarray _toleranceIn)
        {
            //ORA-13367:      wrong orientation for interior/exterior rings
            sdogeometry rtnval = null;
            using (OracleConnection conn = new OracleConnection(connectstring))
            {
                using (OracleCommand dbcmd = conn.CreateCommand())
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    dbcmd.CommandText = "SDO_MIGRATE.TO_CURRENT";

                    OracleParameter cParm1 = new OracleParameter();
                    cParm1.ParameterName = "geometry";
                    cParm1.OracleDbType = OracleDbType.Object;
                    cParm1.Value = _geoIn;
                    cParm1.UdtTypeName = "MDSYS.SDO_GEOMETRY";
                    cParm1.Direction = ParameterDirection.Input;

                    OracleParameter cParm2 = new OracleParameter();
                    cParm2.ParameterName = "tolerance";
                    cParm2.OracleDbType = OracleDbType.Object;
                    cParm2.Value = _toleranceIn;
                    cParm2.UdtTypeName = "MDSYS.SDO_DIM_ARRAY";
                    cParm2.Direction = ParameterDirection.Input;

                    OracleParameter cParm3 = new OracleParameter();
                    cParm3.ParameterName = "RETURNVALUE";
                    cParm3.OracleDbType = OracleDbType.Object;
                    cParm3.UdtTypeName = "MDSYS.SDO_GEOMETRY";
                    cParm3.Direction = ParameterDirection.ReturnValue;

                    // Note the order
                    dbcmd.Parameters.Add(cParm3);
                    dbcmd.Parameters.Add(cParm1);
                    dbcmd.Parameters.Add(cParm2);

                    try
                    {
                        conn.Open();
                        dbcmd.ExecuteNonQuery();
                        rtnval = (sdogeometry)dbcmd.Parameters["RETURNVALUE"].Value;
                    }
                    catch (Exception _Ex)
                    {
                        throw (_Ex); // Actually rethrow
                    }
                    finally
                    {
                        if (conn != null && conn.State == ConnectionState.Open)
                        {
                            conn.Close();
                        }
                    }
                }//using cmd
            }//using conn

            return rtnval;
        }