Apache OpenOffice Community Forum

Hello there!

I am trying to get all tables out of our old ERP system. I have a ODBC driver for the Micro Focus databases on which our old ERP is based on. When I connect to the database in OOO Base 3.1 it says the connection is ok. The tables also show up in the settings tab ob the ODBC driver in the Windows-ODBC setup. And the tables also show when connecting to the database via the freeware program ODBCTableView.
So it looks like the driver itself works. But in Base 3.1 the tables simply don´t appear in the lower part of the main menu. I tried to alter lots of settings in the database options in Base, but with no result.

Any ideas?

Best
Woeba

Can you provide a screenshot with confidential information wiped out?

Also, are you hiding tables via the Tools pulldown menu and the Table Filter option?

P.S. With my experience doing the same thing with our ERP program may I add one comment? Make sure you validate your data once you extract it. ODBC connectivity can be tricky at times and sometimes the different applications can interpret different characters in the wrong way. Ensure that your Edit | Database | * settings stuff is all correct.

Hi,

first I would like to thank you for the fast response.
I attached a screenshot showing the result of opening the ODBC-DNS with the freeware program ODBCTables. All tables are listed on the left side. Opening the DNS in OOO-Base reveals not a single table. When opening the settings tab the connection to the database can be testes with a positive result.
In the meantime I found out, that the ODBC driver of our old file-bases Database (Parkway Connectware for Micro Focus files) does not support the SQL "SHOW" statement. Perhaps this is why OOO cannot read the list ob the tables. I found out that you can get the list of all tables in a database with queries like

SELECT * FROM INFORMATION_SCHEMA.TABLES;

which depends on the type the database-server. (This example is for a MS-SQL server). Perhaps ODBCTables tries several SELECT statements to get the tables, while OOO only tries "the wrong ones" or just "SHOW TABLES".

I really have no clue

Let me ask a few questions to see where we are at.

What kind of status response do you get when you run the command directly via Tools | SQL | Command to execute = SELECT * FROM INFORMATION_SCHEMA.TABLES;

What happens when you run SQL commands via View | Database Objects | Query | Create Query in SQL view | Query = SELECT * FROM INFORMATION_SCHEMA.TABLES;

Yet again, what happens when you run SQL commands *directly* via View | Database Objects | Query | Create Query in SQL view | Edit | Run SQL command directly | Query = SELECT * FROM INFORMATION_SCHEMA.TABLES;

Also, don't forget to ensure that your Edit | Database | * settings stuff is all correct.

Hi!

>What kind of status response do you get when you run the command directly via Tools | SQL | Command to execute = SELECT * FROM INFORMATION_SCHEMA.TABLES;

Response: [PARKWAY][SQL Abfragen]Nach dem Ende der SQL Anweisung folgen weitere Zeichen
Translated: "[PARKWAY][SQL Queries]After the end of the SQL statement several characters are following"

>What happens when you run SQL commands via View | Database Objects | Query | Create Query in SQL view | Query = SELECT * FROM INFORMATION_SCHEMA.TABLES;

A info-window pops up:
"The Datacontent could not be loaded"
"[PARKWAY][SQL Queries]After the end of the SQL statement several characters are following"

, as above

>Yet again, what happens when you run SQL commands *directly* via View | Database Objects | Query | Create Query in SQL view | Edit | Run SQL command directly | Query = SELECT * FROM INFORMATION_SCHEMA.TABLES;

Response: Save pop-up as above.

Looks like the driver does not find anything in "INFORMATION_SCHEMA.TABLES"?

Well, it appears you are talking with the server and correctly using OO.o as the front end. I wonder if you have the ODBC SQL statement correct. I did a Google search on the phrase "After the end of the SQL statement several characters are following" and this is what appears to be the case.

I know when I was working on our ERP software the ODBC language the ODBC SQL statements were different than the native SQL statements!!!!

If ODBCTables doesn't provide the functionality you need to do what you need, then what might be helpful to you would be to sniff the traffic to and from the server and ODBCTables. This will give you the successful SQL statement that ODBCTables is using to extract the tables. You then use that statement from within OO.o.

Can ODBCTables extract the data you need into a CSV files? If so, then you are set as CSV files are very portable to anything.

Yeah, looks like the driver simply is not SQL compatible "enough" to handle it.
ODBCTables has a feature to dump tables, but the resulting TXT files are not CSV-like. And I need a way to dump these tables automatically for the data conversion the day before we start our new ERP. I found a nice little SQL-shell (ODBC View) which has a good function for cvs-export. It does not list the tables, but with a "SELECT * FROM AAA" I can get the data. Now I must find out how to get the program to dump all tables into seperate files. MY ODBC driver does not support the DUMP statement, so I have to find another way to get all these tables into OOO-Base in an automatic way. Lots of work ahead

Great! Glad something worked out for you. ERP conversions are a huge task. Good luck with the work you have ahead of you.

Test, test, test.