Problem importing schema from SQL Server
gadeyne.bram Feb 6, 2018 8:09 AMHi,
I'm trying to import a schema using a vdb.ddl file. I have this configuration:
create database vdb version '1.0.1'; use database vdb version '1.0.1'; CREATE FOREIGN DATA WRAPPER sqlserver; CREATE SERVER kd_matric FOREIGN DATA WRAPPER sqlserver OPTIONS ("jndi-name" 'java:/kd_matric'); CREATE SCHEMA kd_matric SERVER kd_matric; SET SCHEMA kd_matric; IMPORT FOREIGN SCHEMA dbo FROM SERVER kd_matric INTO kd_matric OPTIONS( importer.catalog 'kd_matric', importer.schemaPattern 'dbo', importer.importStatistics true, importer.useFullSchemaName false, importer.tableTypes 'TABLE,VIEW' );
In the logs (level FINE) I see this:
2018-02-06 13:48:30,449 DEBUG [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (Worker40_async-teiid-threads138) kd_matric: getConnection(null, WrappedConnectionRequestInfo@2c299c16[userName=kd_matric_qry]) [0/10]
2018-02-06 13:48:30,449 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerStatement] (Worker40_async-teiid-threads138) SQLServerStatement:99 created by (ConnectionID:1 ClientConnectionId: 5031b23d-f524-4793-bfb7-4cec500c0983)
2018-02-06 13:48:30,449 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerStatement] (Worker40_async-teiid-threads138) SQLServerStatement:99 Executing (not server cursor) SELECT 1
2018-02-06 13:48:30,456 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerResultSet] (Worker40_async-teiid-threads138) SQLServerResultSet:1022 created by (SQLServerStatement:99)
2018-02-06 13:48:30,457 INFO [org.teiid.CONNECTOR] (Worker40_async-teiid-threads138) SQLServerExecutionFactory Commit=true;DatabaseProductName=Microsoft SQL Server;DatabaseProductVersion=11.00.6518;DriverMajorVersion=4;DriverMajorVersion=2;DriverName=Microsoft JDBC Driver 4.2 for SQL Server;DriverVersion=4.2.6420.100;IsolationLevel=2
2018-02-06 13:48:30,457 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerStatement] (Worker40_async-teiid-threads138) SQLServerStatement:100 created by (ConnectionID:1 ClientConnectionId: 5031b23d-f524-4793-bfb7-4cec500c0983)
2018-02-06 13:48:30,457 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerStatement] (Worker40_async-teiid-threads138) SQLServerStatement:100 Executing (not server cursor) sp_datatype_info_100 @ODBCVer=3
2018-02-06 13:48:30,459 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerResultSet] (Worker40_async-teiid-threads138) SQLServerResultSet:1023 created by (SQLServerStatement:100)
2018-02-06 13:48:30,459 DEBUG [org.teiid.CONNECTOR] (Worker40_async-teiid-threads138) JDBCMetadataProcessor - Importing tables
2018-02-06 13:48:30,459 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerStatement] (Worker40_async-teiid-threads138) SQLServerCallableStatement:101 created by (ConnectionID:1 ClientConnectionId: 5031b23d-f524-4793-bfb7-4cec500c0983)
2018-02-06 13:48:30,459 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerStatement] (Worker40_async-teiid-threads138) SQLServerCallableStatement:101: calling sp_prepexec: PreparedHandle:0, SQL:EXEC sp_tables @P0, @P1, @P2, @P3
2018-02-06 13:48:30,463 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerResultSet] (Worker40_async-teiid-threads138) SQLServerResultSet:1024 created by (SQLServerCallableStatement:101)
2018-02-06 13:48:30,464 DEBUG [org.teiid.CONNECTOR] (Worker40_async-teiid-threads138) JDBCMetadataProcessor - Importing columns
2018-02-06 13:48:30,464 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerStatement] (Worker40_async-teiid-threads138) SQLServerCallableStatement:3: calling sp_execute: PreparedHandle:2, SQL:EXEC sp_columns_100 @P0, @P1, @P2, @P3, @P4, @P5
2018-02-06 13:48:30,466 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerResultSet] (Worker40_async-teiid-threads138) SQLServerResultSet:1025 created by (SQLServerCallableStatement:3)
2018-02-06 13:48:30,466 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerResultSetMetaData] (Worker40_async-teiid-threads138) SQLServerResultSetMetaData:283 created by (SQLServerResultSet:1025)
2018-02-06 13:48:30,466 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerStatement] (Worker40_async-teiid-threads138) SQLServerCallableStatement:3: calling sp_execute: PreparedHandle:2, SQL:EXEC sp_columns_100 @P0, @P1, @P2, @P3, @P4, @P5
2018-02-06 13:48:30,469 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerResultSet] (Worker40_async-teiid-threads138) SQLServerResultSet:1026 created by (SQLServerCallableStatement:3)
2018-02-06 13:48:30,469 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerResultSetMetaData] (Worker40_async-teiid-threads138) SQLServerResultSetMetaData:284 created by (SQLServerResultSet:1026)
.....
2018-02-06 13:48:30,651 DEBUG [org.teiid.CONNECTOR] (Worker40_async-teiid-threads138) JDBCMetadataProcessor - Importing primary keys
2018-02-06 13:48:30,651 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerStatement] (Worker40_async-teiid-threads138) SQLServerCallableStatement:4: calling sp_execute: PreparedHandle:3, SQL:EXEC sp_pkeys @P0, @P1, @P2
2018-02-06 13:48:30,687 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerResultSet] (Worker40_async-teiid-threads138) SQLServerResultSet:1100 created by (SQLServerCallableStatement:4)
....
2018-02-06 13:48:30,761 DEBUG [org.teiid.CONNECTOR] (Worker40_async-teiid-threads138) JDBCMetadataProcessor - Importing index info
2018-02-06 13:48:30,761 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerStatement] (Worker40_async-teiid-threads138) SQLServerCallableStatement:5: calling sp_execute: PreparedHandle:4, SQL:EXEC sp_statistics_100 @P0,@P1,@P2,@P3,@P4, @P5
2018-02-06 13:48:30,787 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerResultSet] (Worker40_async-teiid-threads138) SQLServerResultSet:1175 created by (SQLServerCallableStatement:5)
....
2018-02-06 13:48:30,882 DEBUG [org.teiid.CONNECTOR] (Worker40_async-teiid-threads138) JDBCMetadataProcessor - Importing foreign keys
2018-02-06 13:48:30,882 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerStatement] (Worker40_async-teiid-threads138) SQLServerCallableStatement:6: calling sp_execute: PreparedHandle:5, SQL:EXEC sp_fkeys @P0, @P1, @P2, @P3 , @P4 ,@P5
2018-02-06 13:48:30,964 FINE [com.microsoft.sqlserver.jdbc.internals.SQLServerResultSet] (Worker40_async-teiid-threads138) SQLServerResultSet:1250 created by (SQLServerCallableStatement:6)
....
2018-02-06 13:48:31,045 DEBUG [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (Worker40_async-teiid-threads138) kd_matric: returnConnection(26a35bd5, false) [0/10]
In the final virtual database ,when I make a connection using Squirrel SQL client, I don't see any tables or views under the kd_matric schema. I also see no error or warning messages.
Would the underscore in the name be causing any problems? Can I debug this any further somehow?
I've created a separate Java program that lists the tables:
DatabaseMetaData m = connection.getMetaData(); System.out.println("Search String Escape: '"+m.getSearchStringEscape()+"'"); System.out.println("Catalog: \""+connection.getCatalog()+"\""); System.out.println("\nCatalogs"); System.out.println("--------"); try(ResultSet rs=m.getCatalogs()){ while(rs.next()){ System.out.println(rs.getString(1)); } } System.out.println("\nSchemas"); System.out.println("-------"); try(ResultSet rs=m.getSchemas()){ while(rs.next()){ System.out.println(rs.getString(1)+" "+rs.getString(2)); } } System.out.println("\nTables"); System.out.println("------"); try(ResultSet rs = m.getTables(connection.getCatalog(), null, null, null)){ while (rs.next()) { System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4)); } } System.out.println("\nCatalog kd_matric, Schema dbo"); System.out.println("------"); try(ResultSet rs = m.getTables(catalogname, "dbo", null, null)){ while (rs.next()) { System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4)); } }
lists:
Search String Escape: '\'
Catalog: "kd_matric"
Catalogs
--------
.... (multiple other schema's here)
kd_matric
.... (multiple other schema's here)
Schemas
-------
db_accessadmin null
db_backupoperator null
db_datareader null
db_datawriter null
db_ddladmin null
db_denydatareader null
db_denydatawriter null
db_owner null
db_securityadmin null
dbo null
guest null
INFORMATION_SCHEMA null
sys null
Tables
------
kd_matric dbo AggregLabData TABLE
kd_matric dbo AggregMonData TABLE
kd_matric dbo AllLabData TABLE
kd_matric dbo AllMonData TABLE
kd_matric dbo Comorbidity TABLE
kd_matric dbo DATABASECHANGELOG TABLE
kd_matric dbo DATABASECHANGELOGLOCK TABLE
kd_matric dbo DiagnosisProcedures TABLE
kd_matric dbo ExtrAllLabData TABLE
kd_matric dbo ExtrAllMonData TABLE
kd_matric dbo ExtrMedTreatment TABLE
kd_matric dbo ExtrPatHospitalAdmissions TABLE
kd_matric dbo ExtrPatICUAdmissions TABLE
kd_matric dbo ExtrPatientData TABLE
kd_matric dbo Germ_Infection TABLE
kd_matric dbo Germs TABLE
kd_matric dbo Infections TABLE
kd_matric dbo md_admissionwards TABLE
kd_matric dbo md_atc TABLE
kd_matric dbo md_atcddd TABLE
kd_matric dbo md_atcdpp TABLE
kd_matric dbo md_categories TABLE
kd_matric dbo md_changelog TABLE
kd_matric dbo md_countrycodes TABLE
kd_matric dbo md_Datasets TABLE
kd_matric dbo md_Datasetvariables TABLE
kd_matric dbo md_dischargewards TABLE
kd_matric dbo md_FDatabases TABLE
kd_matric dbo md_FDvariables TABLE
kd_matric dbo md_FDvariables_nu TABLE
kd_matric dbo md_gender TABLE
kd_matric dbo md_hospitalcompleteness TABLE
kd_matric dbo md_hospitalsurvival TABLE
kd_matric dbo md_icusurvival TABLE
kd_matric dbo md_mv TABLE
kd_matric dbo md_mv_nu TABLE
kd_matric dbo md_ordinalvars TABLE
kd_matric dbo md_ordinalvars_nu TABLE
kd_matric dbo md_origins TABLE
kd_matric dbo md_placeref TABLE
kd_matric dbo md_placeref_nu TABLE
kd_matric dbo md_placeref_uzg TABLE
kd_matric dbo md_RouteGroups TABLE
kd_matric dbo md_routes TABLE
kd_matric dbo md_rules TABLE
kd_matric dbo md_savetables TABLE
kd_matric dbo md_units TABLE
kd_matric dbo MedTreatment TABLE
kd_matric dbo MedTreatmentSum TABLE
kd_matric dbo PatHospitalAdmissions TABLE
kd_matric dbo PatICUAdmissions TABLE
kd_matric dbo PatientData TABLE
kd_matric dbo QueryLog TABLE
kd_matric dbo ResistancePattern TABLE
kd_matric dbo Scores TABLE
kd_matric dbo TD_DiagnosisProceduresNotMapped TABLE
kd_matric dbo TD_MonDataNotMapped TABLE
kd_matric dbo TD_WrongHospAdmissionSurvival TABLE
kd_matric dbo TD_WrongHospCompleteness TABLE
kd_matric dbo diagnosis_view VIEW
kd_matric dbo v_aado2 VIEW
kd_matric dbo v_admissiondays VIEW
kd_matric dbo v_apache4 VIEW
kd_matric dbo v_apache4_deelscores VIEW
kd_matric dbo v_apache4_diagnosis VIEW
kd_matric dbo v_apache4_dx VIEW
kd_matric dbo v_apache4_gcs VIEW
kd_matric dbo v_apache4_gcs_selected VIEW
kd_matric dbo v_apache4_ph_pco2 VIEW
kd_matric dbo v_apache4_ph_pco2_selected VIEW
kd_matric dbo v_numbers VIEW
kd_matric dbo v_scores_clean VIEW
kd_matric dbo V_WardAdm VIEW
kd_matric dbo V_WardDis VIEW
kd_matric dbo VariableView VIEW
... (lists INFORMATION_SCHEMA objects)
... (lists sys objects)
Catalog kd_matric, Schema dbo
------
kd_matric dbo AggregLabData TABLE
kd_matric dbo AggregMonData TABLE
kd_matric dbo AllLabData TABLE
kd_matric dbo AllMonData TABLE
kd_matric dbo Comorbidity TABLE
kd_matric dbo DATABASECHANGELOG TABLE
kd_matric dbo DATABASECHANGELOGLOCK TABLE
kd_matric dbo DiagnosisProcedures TABLE
kd_matric dbo ExtrAllLabData TABLE
kd_matric dbo ExtrAllMonData TABLE
kd_matric dbo ExtrMedTreatment TABLE
kd_matric dbo ExtrPatHospitalAdmissions TABLE
kd_matric dbo ExtrPatICUAdmissions TABLE
kd_matric dbo ExtrPatientData TABLE
kd_matric dbo Germ_Infection TABLE
kd_matric dbo Germs TABLE
kd_matric dbo Infections TABLE
kd_matric dbo md_admissionwards TABLE
kd_matric dbo md_atc TABLE
kd_matric dbo md_atcddd TABLE
kd_matric dbo md_atcdpp TABLE
kd_matric dbo md_categories TABLE
kd_matric dbo md_changelog TABLE
kd_matric dbo md_countrycodes TABLE
kd_matric dbo md_Datasets TABLE
kd_matric dbo md_Datasetvariables TABLE
kd_matric dbo md_dischargewards TABLE
kd_matric dbo md_FDatabases TABLE
kd_matric dbo md_FDvariables TABLE
kd_matric dbo md_FDvariables_nu TABLE
kd_matric dbo md_gender TABLE
kd_matric dbo md_hospitalcompleteness TABLE
kd_matric dbo md_hospitalsurvival TABLE
kd_matric dbo md_icusurvival TABLE
kd_matric dbo md_mv TABLE
kd_matric dbo md_mv_nu TABLE
kd_matric dbo md_ordinalvars TABLE
kd_matric dbo md_ordinalvars_nu TABLE
kd_matric dbo md_origins TABLE
kd_matric dbo md_placeref TABLE
kd_matric dbo md_placeref_nu TABLE
kd_matric dbo md_placeref_uzg TABLE
kd_matric dbo md_RouteGroups TABLE
kd_matric dbo md_routes TABLE
kd_matric dbo md_rules TABLE
kd_matric dbo md_savetables TABLE
kd_matric dbo md_units TABLE
kd_matric dbo MedTreatment TABLE
kd_matric dbo MedTreatmentSum TABLE
kd_matric dbo PatHospitalAdmissions TABLE
kd_matric dbo PatICUAdmissions TABLE
kd_matric dbo PatientData TABLE
kd_matric dbo QueryLog TABLE
kd_matric dbo ResistancePattern TABLE
kd_matric dbo Scores TABLE
kd_matric dbo TD_DiagnosisProceduresNotMapped TABLE
kd_matric dbo TD_MonDataNotMapped TABLE
kd_matric dbo TD_WrongHospAdmissionSurvival TABLE
kd_matric dbo TD_WrongHospCompleteness TABLE
kd_matric dbo diagnosis_view VIEW
kd_matric dbo v_aado2 VIEW
kd_matric dbo v_admissiondays VIEW
kd_matric dbo v_apache4 VIEW
kd_matric dbo v_apache4_deelscores VIEW
kd_matric dbo v_apache4_diagnosis VIEW
kd_matric dbo v_apache4_dx VIEW
kd_matric dbo v_apache4_gcs VIEW
kd_matric dbo v_apache4_gcs_selected VIEW
kd_matric dbo v_apache4_ph_pco2 VIEW
kd_matric dbo v_apache4_ph_pco2_selected VIEW
kd_matric dbo v_numbers VIEW
kd_matric dbo v_scores_clean VIEW
kd_matric dbo V_WardAdm VIEW
kd_matric dbo V_WardDis VIEW
kd_matric dbo VariableView VIEW