I want connect Calc to ODBC tables - how?

Discuss the spreadsheet application
Post Reply
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

I want connect Calc to ODBC tables - how?

Post by Albireo »

Hi!
I don't know if the tables is in SQL format or... (but SQL-questions work with other programs...)
I want to be able to read these tables in to LO Calc. What is required? What to do?

If the database is 32-bit, and the ODBC driver is 32-bit, - Should Calc also be in 32-bit? (now my OO calc is 4.1.5, and 64-bit Swe)
Must the tables / databases, be registered in any way first?
How?
OOo 4.1.X on Windows XP, Win7, 10
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: I want connect Calc to ODBC tables - how?

Post by Villeroy »

Create an ODBC source on system level. I remember that this used to be possible in the Windows system settings (XP? 2000?). I don't know how to do this with current Windows versions.

menu:File>New>Database...
[X] Connect to existing database
Type: ODBC
Specify the name of the source.
Yes, register the database.
Save the database.
Now you can use this database with Writer and with Calc without opening the database document which is just a configuration file.
You can add meaningful queries to that database including parameter queries.
--------------------------
In Calc hit F4 for the data source window.
Drag the icon of a table or query into the Calc document.
Or create a pivot table based on the data source.

[Tutorial] Using registered datasources in Calc
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

Re: I want connect Calc to ODBC tables - how?

Post by Albireo »

Thanks!
It works for me! - almost …

After I pressed "F4" I got the "data source window".
Then I drag one table to the Calc Window and it works for me with some tables.
But for some other tables, an error message occurs .: "invalid description index" (freely translated from Swedish).
(These tables are quite large (many rows and columns)
Why? What does this mean?
OOo 4.1.X on Windows XP, Win7, 10
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: I want connect Calc to ODBC tables - how?

Post by Villeroy »

Sorry, I don't know. I never used ODBC. I would try a query that limits the output, just in case that the size is the problem.

A simple test: SELECT * FROM "Table_Name" LIMIT 1000
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: I want connect Calc to ODBC tables - how?

Post by UnklDonald418 »

An internet search for that error message indicates that it is generated by the OCBC driver when it encounters a mismatch in index numbers.
It appears that generally it happens when the source table has a row with a primary key value of 0, but the ODBC driver begins numbering at 1.
Not sure, but maybe a query that doesn't include the primary key field would work.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

Re: I want connect Calc to ODBC tables - how?

Post by Albireo »

Thanks!
When I open OO Calc and press F4, it is possible to choose my registered database.
Under my selected database I can select a table (from about 300 tables). (The preview is show the table - I can see the preview of all the tables I have tried.)
So far, everything feels good.

But, where should I write the SQL-query?

Code: Select all

 SELECT * FROM "ARTICLE" LIMIT 1000
(I do not have the choice "Tools / SQL" in OOCalc)
Should quotation marks be before and after the name of table in the question?
Could it be a 32/64 bit problem? (32bit ODBC driver / 64bit OO Calc)?

It doesn't feel that way, because I can read some tables .
Last edited by Albireo on Mon Apr 08, 2019 6:13 pm, edited 1 time in total.
OOo 4.1.X on Windows XP, Win7, 10
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: I want connect Calc to ODBC tables - how?

Post by Villeroy »

Database things can be done in the database document. Open the database document, for instance via F4. right-click>Edit...
SELECT statements can be stored in queries. Select the queries section and create a new query in SQL view.

menu:Tools>SQL is for "action commands" which modify the database definition (CRETAE, DROP, ALTER) or which perform mass edits on stored data (INSERT, DELETE, UPDATE).

--------------------------
If UnklDonald418 is right (I'm rather confident he is), the problem lies between the ODBC driver and the database backend. you have to update some key then
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

Re: I want connect Calc to ODBC tables - how?

Post by Albireo »

Description .:
When I pressed F4, I got three windows in Calc (two small at the top and one big at the bottom).
- Right clicked on the left small window on the top,
- and selected "Edit database file"
OOBase is opened.
Now I can select .: "Tools / SQL"

A new window opens, with the field "Command to execute" (freely translated). The SQL-query .:

Code: Select all

SELECT * FROM ARTICLE LIMIT 1000
is written and "confirmed".

Result .:
I got the following result .:
1: [Sage Canada][PVX ODBC Driver]Unexpected extra token: 1000
Have I understand / done something wrong?
__________________________________________________

Another try
I tried with the SQL-query .:

Code: Select all

SELECT * FROM ARTICLE ;
gave the following results .:
1: Kommando utfört.
(freely translated .: "Command executed")

But I can't find any result anywhere...
OOo 4.1.X on Windows XP, Win7, 10
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: I want connect Calc to ODBC tables - how?

Post by UnklDonald418 »

Select Queries inthe main database window
then select Create Query in SQL View.
enter your query

Code: Select all

SELECT * FROM "ARTICLES" LIMIT 1000;
press F5 to Run the query.
It should display the first 1000 rows from the table named "ARTICLES", assuming it has that many rows.
If there is is primary key column with the value 0 that is probably what is generating the ODBC error message.
In that case you have a couple of options.
Change the primary key value to a unique value other than 0
or back in the Queries section of the main database window select Create Query in the Design View
Select the table "ARTICLES"
One by one add all the fields you want except the one holding the primary key.
Press F5 to display the results.
Save the Query
Back in the spreadsheet Data Sources window you should now be able to select the newly added query in the left pane and see the results in the right pane.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: I want connect Calc to ODBC tables - how?

Post by Villeroy »

If you would use LibreOffice instead of the outdated OpenOffice, you could see the result of a SELECT statement in the SQL window.
As I've already stated, SELECT statements are queries.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

Re: I want connect Calc to ODBC tables - how?

Post by Albireo »

Thanks! (great description)
UnklDonald418 wrote:Select Queries inthe main database window
then select Create Query in SQL View.
enter your Query

Code: Select all

SELECT * FROM "ARTICLE" LIMIT 1000;
press F5 to Run the query…
I got a new error message .:
The data content could not be loaded (freely translated)
[Sage Canada] [PVX ODBC Driver] Unexpected extra token: 1000
UnklDonald418 wrote:...If there is is primary key column with the value 0...
This table has twelve keys. I'm not sure which is the primary key, but maybe KEY1 is, and this KEY has unique values
UnklDonald418 wrote:...back in the Queries section of the main database window select Create Query in the Design View
Select the table "ARTICLES"
One by one add all the fields you want except the one holding the primary key.
Press F5 to display the results.
Save the Query
Back in the spreadsheet Data Sources window you should now be able to select the newly added query in the left pane and see the results in the right pane.
Yes! it works - I got the selected fields..

I can't see the problem, with probably the key field 1. (now I only selected a few fields of the 61 fields in the table)
OOo 4.1.X on Windows XP, Win7, 10
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: I want connect Calc to ODBC tables - how?

Post by Villeroy »

This is very confusing. The query should work with any kind of backend database unless menu:Edit>"Run SQL directly" is switched on. With this switch turned off, Base should send a precompiled query to the ODBC driver.

When menu:Edit>"Run SQL directly" is switched on, Base will not interprete the SQL statement and pass over the text statement directly to the database driver. If you are familiar with the SQL syntax of your database, you may prefer this variant which of course has some limitations on the Base side but at least you can drop these record sets into Calc sheets and you can create pivot tables. This used to work for me with pass-through queries as well as with parsed queries.

Since the query was just a suggestion to test a query with a limited size, you may apply other limits with a WHERE clause. SELECT TOP 1000 * FROM "Table" would be another syntactical variant of ... LIMIT 1000.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

Re: I want connect Calc to ODBC tables - how?

Post by Albireo »

I found one reason, why I get an error message. The ODBC-driver can not handle the SQL-command "LIMIT".
The SQL Keywords, supported by my version of ProvideX ODBC is .:
  • * COUNT NOT BETWEEN
    + DELETE NOT IN
    - (minus) DESC NOT LIKE
    - (negative) DISTINCT NOT NULL
    / EXISTS NULL
    < FROM OJ
    < > GROUP BY ON
    <= HAVING OR
    = IN ORDER BY
    > INSERT SELECT
    >= INTO SET
    ALL IS SUM
    AND LEFT OUTER JOIN UPDATE
    ANY LIKE VALUES
    ASC MAX WHERE
    AVG MIN BETWEEN NOT
(could not create the SQL-keyword in fine columns :? )
But this only explain, why I could not select 1000 rows...
OOo 4.1.X on Windows XP, Win7, 10
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: I want connect Calc to ODBC tables - how?

Post by Villeroy »

I think you have to solve a problem with that ODBC driver. I don't have this driver and any shots in the dark may ruin your database. For instance, it could be possible to increase all index values so the lowest value is 1 rather than 0 BUT any other fields referring to that index need to be updated accordingly, otherwise the references would be all wrong. Referencial integrity takes care of this but I don't know if and how referencial integrity is maintained by your database engine.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply