[Solved] Advanced SQL connecting to a Calc spreadsheet

[Balam]

I made a Base .odb file that connects to a Calc spreadsheet. I can list my tables and make some simple queries with them. Advanced commands on the other hand do not seem to be available, as there is:
- no aggregated functions (the list of functions in the query design window is empty)
- impossible to add more than one table and make joins between them

I am working with data that is originating from Exel files, combined with the DB possibilities of MS Query. Now trying to get all the functionalities into LibreOffice.

I know the SQL syntax in Base is dependant to the type of database. But I didn't find any clear directives specific to spreadsheets. So my questions are:
- Are these limitations due to the designer ?
- If so, where do I find a good overview of the SQL syntax possible when connecting to spreadsheets (thus not MySQL, HSQLDB, ...) ?
- If not, is there another way to get the data from my Calc spreadsheets ? for example, can I pick up the data through a HSQL server ?

For now, I need a way to (1) make a left outer join, (2) group by and (3) convert string into number.

Thanks for any advise, help ...

[Balam]

.

[DACM]

FIle-based drivers don't support advanced SQL functions, so you'll need to import (copy&paste) the spreadsheet data into a table managed by a relational database engine (like the built-in HSQLDB) in order to gain access to more advanced SQL commands and functions.
...

[Balam]

Thanks for the link. The document is dated 2003/07/08 and are specs fore OOo 2.0.
So no extensions have been added to this driver since then ? Sniff ...

Ok then let see what my options are then:

• Data in database: not feasible since to much calculations and what-if formulas, and the need for exceptions in calculations. So, data stays in Calc.
• Analysing and reporting: works ok in Calc (pivottables, charts, sorting & autofilter). Getting queried data into Calc can easily be done through registered databases, so the point is to get good queries into an .odb Base file.
• Fetching Calc data into a database structure: connecting Base to the spreadsheet is ok, but lacks advanced SQL possibilities. Setting up a database server is a bit overkill: the amount of data is small, multiple user access is not needed and automating the transfer of data from Calc to the database wont be so easy (I presume). Also, data doesn't need to be editable, a one way source data > destination report is fine.

The handling that I figured out for now (and that works):
1. have a link.odb file connected to the 'list' data range in a Calc spreadsheet
2. copy table 'list' from link.odb
3. paste it into a queries.odb file (structure + data)
4. create queries (advanced SQL possibilities since the data in queries.odb is 'embedded')
5. use queries.odb as a registered database to deliver data into a report.ods Calc spreadsheet.

What I finally would like to do:

• create some kind of refresh macro in reports.ods that copies (the data within) the table 'list' from the file link.odb into the the file queries.odb.
• eventually, create the link.odb file from within the macro so it's only a temporary file, or get the data into some recordset and fetch that into the queries.odb. Anyway, with a link.odb file I think to have the advantage of just needing to copy an paste a table, and Base will interprete the data automaticly, even if it changes. If there are anomalies in the data, I can trace it with the queries without breaking the workflow.

If somebody can point me to good resources on writing such a macro ? I am (was) familiar with VBA and SQL, but LibreOffice Basic is new to me. I found already some code to try from within Calc and see if I manage to do the copy & paste thing:

Code: Select all

Sub Main
  Dim oDatabaseContext, oDataSource, oDataDest As Object 
  Dim oConnectSource, oConnectDest As Object 
  oDatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext") 
  oDataSource = oDatabaseContext.getByName("link")
  oDataDest = oDatabaseContext.getByName("queries")
  oConnectSource = oDataSource.GetConnection("","")
  oConnectDest = oDataDest.GetConnection("","")
End Sub

Any advise greatly welcomed !

-----------------------------------------------------------------------

Just some information I gathered (correct me if something is wrong):

Registered databases in LibreOffice are in se .odb files. Only when using a stand alone .odb file ('embedded database'), all data resides within the .odb file. All other .odb files just contain the query, report and form definitions and a link to the source data.

each .odb file can connect to only one database source. Availability of SQL commands depends on the type of source and the used drivers libraries (italic):

if source = FILE
- stand alone .odb >> SQL based on HSQLDB (1) (native)
- spreadsheet >> limited SQL (2) (native)
- dBase >> limited SQL (2) (native)
- text >> limited SQL (2) (native)
- MS Access / JET >> SQL ??? (3) unixodbc + mdbtools

if source = DB SERVER that works with
- HSQLDB (native)
- MySQL libreoffice-mysql-connector OR unixodbc + libmyodbc OR libmysql-java
- PostgreSQL libreoffice-sdbc-postgresql OR unixodbc + odbc-postgresql OR libpg-java
- SQLite unixodbc + libsqliteodbc
- Sybase SQL / MS SQL unixodbc + tdsodbc OR libjtds-java

(1) HyperSQL with OpenOffice, List of Keywords
(2) SQL Functions for file based database drivers
(3) ???

[Balam]

Further questions in another thread: macro that copy/paste table from one .odb file into another