dynamobi/dynamobi — Issue #841: [FRG-29] IMPORT FOREIGN SCHEMA will not execute any foreign table definition if even one fails.
Author: dynamobi-build
Created: 2011-12-30 03:52:16+00:00
State: open
Labels: FRG, SQL/MED

[reporter="schoi", created="Fri, 27 Jan 2006 15:28:01 -0500 (GMT-05:00)"]
IMPORT FOREIGN SCHEMA will fail if any of the foreign tables cannot be created; either if the specified table does not exist in the foreign schema or if the table exists in the specified local schema already.

Ideally, what foreign tables can be defined should still be created; otherwise, you basically can IMPORT FOREIGN SCHEMA from a specific foreign server only once onto a local schema.

to repro from //open/luciddb using sqllineEngine:

-- using jdbc foreign data wrapper but same with flatfile data wrapper

create foreign data wrapper test_jdbc library 'plugin/FarragoMedJdbc3p.jar' language java;

create server sql_server foreign data wrapper test_jdbc options(driver_class 'net.sourceforge.jtds.jdbc.Driver', url 'jdbc:jtds:sqlserver://AKELA:1433;user=sa;password=xxxxxx', QUALIFYING_CATALOG_NAME 'BENCHMARK');

create schema local_sch;

IMPORT FOREIGN SCHEMA "dbo" LIMIT TO TABLE_NAME LIKE 'a%' FROM SERVER sql_server into local_sch;

-- time passed. assume more tables were created in sql_server, so importing again...

IMPORT FOREIGN SCHEMA "dbo" LIMIT TO TABLE_NAME LIKE 'a%' FROM SERVER sql_server into local_sch;

=> fails, and no tables are added to local_sch:

Error: Target schema "LOCALDB"."LOCAL_SCH" already contains foreign table "abc" with same name (state=,code=0)

net.sf.farrago.util.FarragoUtil$FarragoSqlException: Target schema "LOCALDB"."LOCAL_SCH" already contains foreign table "abc" with same name

at net.sf.farrago.ddl.DdlValidator.newPositionalError(DdlValidator.java:1057)

at net.sf.farrago.ddl.DdlValidator.validateUniqueNames(DdlValidator.java:948)

at net.sf.farrago.ddl.DdlRelationalHandler.validateDefinition(DdlRelationalHandler.java:93)

at net.sf.farrago.ddl.DdlRelationalHandler.validateModification(DdlRelationalHandler.java:102)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.eigenbase.util.ReflectUtil.invokeVisitor(ReflectUtil.java:249)

at net.sf.farrago.ddl.DdlValidator.invokeHandler(DdlValidator.java:1251)

at net.sf.farrago.ddl.DdlValidator.validateAction(DdlValidator.java:1235)

at net.sf.farrago.ddl.DdlValidator.validate(DdlValidator.java:823)

at net.sf.farrago.db.FarragoDbSession.validateDdl(FarragoDbSession.java:805)

at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:783)

at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:719)

at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:151)

at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:104)