Re: oci to sql conversion?

pieter schrieb:
> Hi Volker,
>
> Thanks for your response. My scenario is like this: We do not control
> the database at all. I can say that is part of a legacy system, but
> that is not the whole story either. We provide an application that has
> to make use of this database though. It was written by an amalgamation
> of developers that did not really understand C++ or oracle, but knew
> enough to get something that appears to be working. Now that the hard
> problems are cropping up (memory leaks and the like) I am tearing my
> hair out from frustration.

Console yourself, a redesign is the only option.

>
> I saw from the online docs that you can query the "normal" columns from
> the database, and I've seen the sql. One of them might look like this:
>
> SELECT clast_id, geometry FROM map_clasts T1 WHERE MINE = 'MPONENG
> MINE' and SDO_RELATE(GEOMETRY,
> mdsys.sdo_geometry(2003,NULL,NULL,mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(-100,-100,100,100)),'mask=ANYINTERACT
> querytype=WINDOW') = 'TRUE';
>
> pretty standard stuff, actually.
>
> My problem here is that I want to get to the geometry information, like
> the types, and the ordinates and the like. So what my question should
> really have been is; Is there a way to query the information that is
> contained in the geometry type so that the results come in a table?
Yes, here's an example using VBScript, OO4O and your example geometry:

'Create the OO4O component

   set orasession=createobject("OracleInProcServer.XOraSession") 'log on

   set Usersession=orasession.opendatabase("mydatabase","myusername/mypassword",0)    Usersession.Autocommit=false 'something to keep in mind for dml stuff

'select data

   set Rows=usersession.createdynaset("select mdsys.sdo_geometry(2003,NULL,NULL,mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(-100,-100,100,100)) my_geometry from dual",0)    'process result

     while not rows.eof
       set Geom=Rows("my_geometry").Value
       msgBox(Geom.sdo_gtype)
       set InfoArray=Geom.sdo_elem_info 'Yes, that step is necessary for nested objects.
       msgBox(InfoArray(1)) 'And oracle collections start with 1!
       Rows.MoveNext
     wend
     Rows.Close

'log off

   usersession.Close
'clean up

   set orasession = nothing

Those collections probably have more methods and I suspect that ADO access data differently, but this should give you a rough idea what to expect.

Lots of Greetings!
Volker Received on Thu May 04 2006 - 12:52:53 CDT