Start a new topic

Import Table Data -- no mapped columns

I'm trying to import a CSV file using DbVis 10.0.8 (on Win10) into an existing table in an Oracle 10.2 database (driver is Oracle 11g Thin). Everything is fine until I get to the "Import Into" dialog at which point the "Map Table Columns with File Columns" area says "Result set is empty". Attempting to map columns using any of the options under the "Map by ..." dropdown does nothing.


I tried enabling the debug to see what was going on. At the point where I navigate into the "Import Into" dialog there are two SQL statements that are run. The first retrieves a list of all of the table names in my schema. The second retrieves the column names for one particular table, but the table name in the SQL statement is NOT the one that I've selected for import but rather the first table name from the list returned by the first SQL statement. Shouldn't this second SQL statement be pulling the columns for the import destination table? Or perhaps the first statement was just to verify the existence of the destination, in which case the table name should have been supplied as a parameter?


Thanks.


I tried testing the import on a local Oracle 11g database from DbVis 10.0.8 on MacOS. Although the SQL statements in the debug window appeared the same as in my post above, the column mapping was done correctly and I was able to complete the import. The problem described in the original post occurred in a client's database, whereas this second test was in a DB I set up myself.

Any idea what might be causing the problem?


 

Hi Steve,


When you navigate to the first page in the Import Table Data wizard dialog for an existing table, you should see a calls to the driver's getColumns() and getPrimaryKeys() methods with the selected table as the arguments in the debug log. That is where the data for the Destination page is collected.


The two SELECTs for all tables and the columns for the first are executed as a side-effect of how this is implemented. The Destination page is implemented as a class that is used both for importing to an existing table and for a new table. The information collected by the two SELECTs is actually only used in the latter case, to populate lists with tables and the columns for the first that can be selected if declaring a Foreign Key for the new table. It is probably possible to avoid executing them to an existing table, but this has not been a problem so far.


So the issue with the import with your client's database must be something else. Can you run with debugging and copy the lines for getColumns() and getPrimaryKey() into your reply. They look like this example:


2018-03-12 11:47:55.582 FINE   101 [ExecutorRunner-pool-4-thread-2203 - JDBCExecutor.runGetColumns] DbConnection='Oracle 12 HANS (VirualBox 1)' Catalog='null' Schema='HANS' Table='TEST/_VIRTUAL' Column='%'2018-03-12 11:47:55.583 FINE   101 [pool-5-thread-2218 - JDBCMetaDataHandler.doInvoke] RootConnection: OracleDatabaseMetaData.getColumns(null, "HANS", "TEST/_VIRTUAL", "%")2018-03-12 11:47:55.601 FINE   101 [ExecutorRunner-pool-4-thread-2203 - JDBCExecutor.runGetPrimaryKeys] DbConnection='Oracle 12 HANS (VirualBox 1)' Catalog='null' Schema='HANS' Table='TEST_VIRTUAL'2018-03-12 11:47:55.601 FINE   101 [pool-5-thread-2218 - JDBCMetaDataHandler.doInvoke] RootConnection: OracleDatabaseMetaData.getPrimaryKeys(null, "HANS", "TEST_VIRTUAL")


Also, the DDL for the table may also be helpful.


Best Regards, 

Hans

Hi Hans,

Thanks for your reply. The requested debugging lines are reproduced below. If you need the DDL as well just let me know.

2018-03-12 17:06:23.241 FINE   712 [ExecutorRunner-pool-3-thread-3 - M.?] DbConnection='Cytology' Catalog='null' Schema='WIDESKIES' Table='TMP//_ONCO//_PATIENTS' Column='%'
2018-03-12 17:06:23.242 FINE   712 [pool-4-thread-5 - E.?] RootConnection: OracleDatabaseMetaData.getColumns(null, "WIDESKIES", "TMP//_ONCO//_PATIENTS", "%")
2018-03-12 17:06:23.326 FINE   712 [ExecutorRunner-pool-3-thread-3 - M.?] DbConnection='Cytology' Catalog='null' Schema='WIDESKIES' Table='TMP_ONCO_PATIENTS'
2018-03-12 17:06:23.326 FINE   712 [pool-4-thread-5 - E.?] RootConnection: OracleDatabaseMetaData.getPrimaryKeys(null, "WIDESKIES", "TMP_ONCO_PATIENTS")

 

Aha! I got it.

I noticed that the driver version on the Mac side was more recent than the one I was using against the client database on Windows (Oracle 11g release 2 vs release 1). I copied the newer driver to the Windows side, re-configured it in the Driver Manager, then tried again. The column mappings came up just fine and I was able to complete the import. Looks like some sort of bug or incompatibility with the release 1 driver.

Thanks for your assistance!

 

Hi Steve,


Great, I was going to suggest upgrading the driver. From the debug output, I can see that the older version does to handle scaling of underscores in the table name correctly. It uses two slashes to escape, while the driver version I use uses only one, so that is most likely a driver bug and what caused the problem.


Best Regards,

Hans

Yes, it was the double slashes that tipped me off as well, especially when I noticed that the import on the Mac side used single slashes like yours.

Thanks again Hans.

 

Login or Signup to post a comment