Hi Hugh, Thanks for following up on this. Is there a possibility of getting new functionality to handle this in future?
My thinking is 1. Allow the tweaking of the mapping between virtuoso and the other database to be able to explode the data. Perhaps using sql? Or alternatively - 2. Allow a virtual table to be created with all columns with exotic datatypes to be dropped. So that a single column doesn't stop the mapping of the entire table. While creating views to explode the data will probably be the preferable option it may not always be possible. One of the key features (and selling points) for virtuoso is to be able to integrate data without changing anything on the source system. Cheers Mark On 26 May 2010 04:54, Hugh Williams <[email protected]> wrote: > Hi Mark, > > I have been looking into this issue further and as the ODBC standard itself > does not support complex datatypes, then their is no means of passing these > types to an ODBC application ie the Virtuoso Virtual database engine in this > case. Thus the only solution I can see would be create views with the > exploded standard datatypes of these complex data types you want to access > and link the view into Virtuoso instead ... > > Best Regards > Hugh Williams > Professional Services > OpenLink Software > Web: http://www.openlinksw.com > Support: http://support.openlinksw.com > Forums: http://boards.openlinksw.com/support > Twitter: http://twitter.com/OpenLink > > On 25 May 2010, at 11:11, Hugh Williams wrote: > > Hi Mark, > > An iODBC Driver Manager trace would be useful to have to see where the > fails are occurring with the EasySoft ODBC driver. We have not performed any > testing of Virtuoso with the EasySoft ODBC driver and thus would recommend > the use of the OpenLink Multi-Tier ODBC Drivers, which have been extensively > tested with the Virtuoso. > > The Virtuoso Virtual Database Engine, attempt to use the rich metadata > available in ODBC to dynamically determine the mappings of remote Database > types to Virtuoso equivalents, but we would need to see specifically what > ODBC calls are failing to comment on what type cannot be mapped below. For > point 1 below you could use replication to attempt to replicate the tables > from Oracle to Virtuoso, but given it uses the the VDB layer will probably > encounter similar issues. For Point 2 their is no means of manually choosing > the type the VDB maps datatypes into Virtuoso it is all done dynamically. > For point 3 you could create views in Oracle with the columns in the tables > you want to be linked into Virtuoso as views and procedures can be linked > into Virtuoso also. Generally you would be best to use the OpenLink > Multi-Tier ODBC Driver as suggested in the support case you logged. > > Best Regards > Hugh Williams > Professional Services > OpenLink Software > Web: http://www.openlinksw.com > Support: http://support.openlinksw.com > Forums: http://boards.openlinksw.com/support > Twitter: http://twitter.com/OpenLink > > On 25 May 2010, at 03:33, Mark James wrote: > > Hi, > I'm trying to load a number of oracle tables as a virtual tables. These > tables contain complex datatypes within certain columns. > > Eg - > CREATE or REPLACE TYPE "CUST_ADDRESS_TYP" > as object > (streed_address varhar2(40), > postal_code varchar2(10), > city varhar2(30), > state_province varchar2(10), > country_id char(2) > ); > > or > create or replace type "phone_list_type" as varray(5) OF varchar2(25); > > > When trying to load these tables I get the error - > OE.CUSTOMERS HY000 VD052: Remote DSN ORACLE_OE: > [Easysoft][Oracle]Unable to map datatype( 108 ) for column ( 4 ) > > Is there a way to either - > > 1. Map these objects directly into objects within virtuoso > 2. Map these objects by exploding them into standard data types within > virtuoso > 3. Simply exclude these columns from the table import > > None of these options seemed to be available within conductor. > > Cheers > Mark > > ------------------------------------------------------------------------------ > > _______________________________________________ > Virtuoso-users mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/virtuoso-users > > > >
