[FRG-3] IMPORT FOREIGN SCHEMA fails completely if one of the types in one of the tables is not recognized

[dynamobi-build]

[reporter="schoi", created="Wed, 7 Dec 2005 15:22:22 -0500 (GMT-05:00)"] With mssql server as foreign server, IMPORT FOREIGN SCHEMA fails with the below exception and none of the tables get imported. (even without the table that caused this error, it would be good if the other tables still got loaded)

Error: java.lang.AssertionError: null (state=,code=0)
net.sf.farrago.util.FarragoUtil$FarragoSqlException: java.lang.AssertionError: null
        at net.sf.farrago.type.FarragoTypeFactoryImpl.createJdbcType(FarragoTypeFactoryImpl.java:345)
        at net.sf.farrago.type.FarragoTypeFactoryImpl.createJdbcColumnType(FarragoTypeFactoryImpl.java:332)
        at net.sf.farrago.namespace.jdbc.MedJdbcNameDirectory.queryColumnsImpl(MedJdbcNameDirectory.java:404)
        at net.sf.farrago.namespace.jdbc.MedJdbcNameDirectory.queryColumns(MedJdbcNameDirectory.java:349)
        at net.sf.farrago.namespace.jdbc.MedJdbcNameDirectory.queryMetadata(MedJdbcNameDirectory.java:219)
        at net.sf.farrago.ddl.DdlImportForeignSchemaStmt.preValidate(DdlImportForeignSchemaStmt.java:148)
        at net.sf.farrago.ddl.DdlValidator.validate(DdlValidator.java:688)
        at net.sf.farrago.db.FarragoDbSession.executeDdl(FarragoDbSession.java:796)
        at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:777)
        at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:713)
        at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:152)
        at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:104)

[dynamobi-build]

[author="schoi", created="Wed, 7 Dec 2005 15:29:47 -0500 (GMT-05:00)"] specific data type in sql server is text.

[dynamobi-build]

[author="schoi", created="Wed, 7 Dec 2005 15:44:19 -0500 (GMT-05:00)"] to repro in sqllineEngine in //open/luciddb:

create foreign data wrapper test_jdbc library '../farrago/plugin/FarragoMedJdbc.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=xx;password=xx', QUALIFYING_CATALOG_NAME 'BENCHMARK');

create schema sql_schema;

import foreign schema "dbo" from server sql_server into sql_schema;

[dynamobi-build]

[author="jvs", created="Thu, 8 Dec 2005 00:57:37 -0500 (GMT-05:00)"] Fixed in eigenchange 4617.

The behavior now is to map unknown datatypes into VARCHAR(1024); since ResultSet.getString can usually handle most datatypes, this allows the metadata import to succeed and provides at least a chance that the data can be queried. I wanted to post warnings, but we don't have a warning framework yet for returning them via JDBC, so I left that as a TODO. Instead, if you want to see them, you have to check the trace log.

I also mapped DECIMAL->DOUBLE (since we don't support it yet), BIT->BOOLEAN, and masked all datetime precision, setting it to 0 (otherwise we get an error when storing the column definition to the catalog since we don't support datetime precision yet).

The original statement now passes, but I expect there will be more of this kind of thing as we test out other back-ends.

Sunny, could you add a test case before closing this?

[dynamobi-build]

[author="schoi", created="Thu, 8 Dec 2005 15:36:38 -0500 (GMT-05:00)"] If one of the values in an unknown datatype column is null, querying the data returns:

Error: Target CAST(Java(((java.sql.ResultSet) resultSet).getString( 1 ))):VARCHAR(1024) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL cannot be assigned null value (state=,code=0)
net.sf.farrago.util.FarragoUtil$FarragoSqlException: Target CAST(Java(((java.sql.ResultSet) resultSet).getString( 1 ))):VARCHAR(1024) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL cannot be assigned null value
        at net.sf.farrago.resource.FarragoResource$_Def0.ex(FarragoResource.java:1142)
        at net.sf.farrago.runtime.FarragoRuntimeContext.checkNotNull(FarragoRuntimeContext.java:590)
        at net.sf.farrago.dynamic.stmt61955.ExecutableStmt$1.makeRow(Unknown Source)
        at org.eigenbase.runtime.ResultSetIterator.moveToNext(ResultSetIterator.java:112)
        at org.eigenbase.runtime.ResultSetIterator.hasNext(ResultSetIterator.java:60)
        at org.eigenbase.runtime.IteratorResultSet.next(IteratorResultSet.java:689)
        at net.sf.farrago.runtime.FarragoIteratorResultSet.next(FarragoIteratorResultSet.java:94)
        at sqlline.SqlLine$BufferedRows.(Unknown Source)


repro: using the same server and schema as above, try:

select * from sql_schema.ann;

(in akela, ANN has 1 text column, with 2 rows, both null)

[dynamobi-build]

[author="jvs", created="Thu, 8 Dec 2005 15:45:42 -0500 (GMT-05:00)"] Oops. Right. I need to make preserve nullability in this and the other cases where I mapped the type.

[dynamobi-build]

[author="jvs", created="Thu, 8 Dec 2005 23:52:38 -0500 (GMT-05:00)"] OK, fixed in eigenchange 4626. Back to you for verification.

[dynamobi-build]

[author="schoi", created="Tue, 10 Jan 2006 10:42:43 -0500 (GMT-05:00)"] test to import schema from sqlserver and oracle in luciddb/test/sql/bench/importschema.sql