5 Replies Latest reply on Feb 7, 2018 9:01 AM by Steven Hawkins

    Problem importing schema from SQL Server

    gadeyne.bram Master

      Hi,

       

      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