How do I get the MobiLink 12 sync wizard to show Oracle 10 views and synonyms?

[Breck_Carter]

Here is a question about MobiLink Version 12 and Oracle 10:

"We have created some views and synonyms in one of the Oracle Database Schema. When we use Sybase Central to create the Sync Model, we do not see the views and synonyms in the oracle schema that we connected to, the tool only shows the physical tables in the list on Sync objects. Can you tell if this is this expected behavior or is there something missing?"

I have reproduced this using SQL Anywhere 12.0.0.2589, Sybase Central 6.1.0.6403 and Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production.

The code's down below, but first, here's an image showing how the MobiLink Synchronization Model wizard does NOT display OWNER2 even though...

1. the wizard is connected to Oracle using OWNER2,
2. OWNER2 owns a view and a synonym for a table,
3. MobiLink's own "Consolidated databases - Tables (by Owner)" on the left shows OWNER2, and
4. the view OWNER2.V_TABLE2 and synonym OWNER2.TABLE1 are showing up in MobiLink's own "Synchronized Tables list on the left.

It looks like owners show up in the wizard only if they own at least one actual table, BUT... that doesn't help; the wizard might show the owner now but it won't show the view and synonym in a later window. The bottom line: the model wizard doesn't seem show Oracle views and synonyms.

-------------------------------------------------------------------------------------------
-- 1. Connect via SQL Plus 10 to ORACLE with a user id having sufficient privileges.

CREATE USER OWNER1 IDENTIFIED BY SQL;
GRANT ALL PRIVILEGES TO OWNER1;

CREATE USER OWNER2 IDENTIFIED BY SQL;
GRANT ALL PRIVILEGES TO OWNER2;

-------------------------------------------------------------------------------------------
-- 2. Connect via SQL Plus 10 as OWNER1/SQL

CREATE TABLE TABLE1
(
name        varchar2 (255) NULL,
displayName varchar2 (255) NULL,
--
    CONSTRAINT pk_TABLE1 PRIMARY KEY (name)
)
--
;

CREATE TABLE TABLE2
(
name        varchar2 (255) NULL,
displayName varchar2 (255) NULL,
--
    CONSTRAINT pk_TABLE2 PRIMARY KEY (name)
)
--
;

CREATE TABLE TABLE3
(
name        varchar2 (255) NULL,
displayName varchar2 (255) NULL,
--
    CONSTRAINT pk_TABLE3 PRIMARY KEY (name)
)
--
;

-------------------------------------------------------------------------------------------
-- 3. Connect via SQL Plus 10 as OWNER2/SQL

CREATE SYNONYM TABLE1 FOR OWNER1.TABLE1;

CREATE VIEW V_TABLE2 AS SELECT * FROM OWNER1.TABLE2;

-------------------------------------------------------------------------------------------
-- 4. Create an Oracle ODBC DSN connecting as OWNER2/SQL

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\\Software\\ODBC\\ODBC.INI\\silver_oracle_10_mobilink_12]
"Driver"="C:\\\\Program Files\\\\SQL Anywhere 12\\\\Bin32\\\\dboraodbc12.dll"
"DSN"="silver_oracle_10_mobilink_12"
"UID"="OWNER2"
"PWD"="SQL"
"PROC"="Yes"
"SIZE"="60000"
"EDTC"="No"
"SN"="XE"

-------------------------------------------------------------------------------------------
-- 5. Start a MobiLink project.

Use that Oracle DSN so that MobiLink connects as OWNER2.

-------------------------------------------------------------------------------------------
-- 6. Start a MobiLink model.

Here's the problem: The "Consolidated Schema Owners" dialog 
box does NOT show OWNER2 in the list, only OWNER1.

-------------------------------------------------------------------------------------------
-- 7. Try adding the synonym and view as "Synchronized Tables" first.

MobiLink 12 plugin
- Consolidated Databases 
- Synchronized Tables 
- right mouse - New - Synchronized Table...
- Create Synchronized Table Wizard
- check "Choose a table..."
- select OWNER2
- select TABLE1
- click Finish
- repeat for V_TABLE2

That doesn't help... OWNER2 still doesn't show up in the 
"Consolidated Schema Owners" dialog box.

Note that MANY MORE OWNERS show up in the Consolidated
databases - Tables (by Owner) list displayed by MobiLink 
itself; see the image.

Note also that if an actual table is created with OWNER2 
as its owner, then OWNER2 will show up, but when it comes
time to select tables then the synonym and view do not show up.

[Former Member · Accepted solution]

Synchronization models do not support synchronizing views. That is documented here:

MobiLink - Getting Started » Introducing MobiLink technology » MobiLink plug-in for Sybase Central » Introduction to synchronization models » Limitations of synchronization models

Sync model script generation relies on tables having primary keys (as stated on the first page of the Create Synchronization Model Wizard), and views don't have primary keys.

A workaround is to create a real table equivalent to a view and use that instead when creating a model.

Cheers,

Graham

[Breck_Carter · Accepted solution]

And synonyms? That's an undocumented limitation?

[Former Member · Accepted solution]

The list of tables displayed comes the from the ODBC metadata, eg. calling SQLTables with "TABLES" for the TableType parameter (and not "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", or "SYNONYM").

So it doesn't list synonyms directly, but the tables for which any synonym are defined can be listed.

[Breck_Carter · Accepted solution]

Sigh... in the real world, Oracle shops use synonyms in MobiLink setups a lot. Using ODBC to get schema metadata from Oracle is an interesting design decision, as in "let's use a technique that nobody working in an Oracle environment ever uses" (they use Oracle catalog views). That might have something to do with the wizard "Load Schema" stages running on a geological timescale. I'm thinking that running the model wizard one-time against a SQL Anywhere database that "looks just like" Oracle might be the easiest step... the migration wizard can get us 99% of the way there.

[Breck_Carter · Accepted solution]

Woohoo! The client understands perfectly... and they are willing to use real Oracle tables for the purposes of running the model wizard one time. They also understand they might have to do a global edit on the resulting generated scripts to fix the owner names. Me, I am just glad something awful hadn't crept into V12, like "MobiLink no longer works with Oracle synonyms"... it does, of course, always has, always will.