Execute SQL query on Calc spreadsheet without using Base

Question

in MS Excel, with VBA one can query a range of cells with SQL as if a data table (with the use of an ActiveX component).

Say data are stored in range "A1:C10" in "Sheet1". With VBA one can use a connection, recordset and SQL to select the data needed from this range and then put the result in any spreadsheet of the same file (or anywhere else).

Is it possible to do the same with OpenOffice/LibreOffice Calc using Stabasic macro without using Base ? I'd like to keep a simple ODS file with macro enabled and SQL queries without using any ODB file. There's a lot of examples on how to reference a spreadsheet as a database and use it with Base, but nothing like what I'm looking for.

As of now, I don't know how to do or if it's even possible.

Regards.

Answer 1

To anser (a bit lately, sorry Jim K..) to the question why I don't want to use Base, I'd say that my collegues are not really at their ease with a computer. They are basic users of MS Excel, Word, Firefox and Thunderbird. Referencing by themselves a Calc spreadsheet (I dare not to thing about an ODB file...) is out of their world. And if I ask our IT departement to do this for all our collegues, it will be the end of their world. Embeding SQL inside an Excel file was the easiest and simpliest way I've found to deal with spreadsheet data that doesn't need to be replace by a real database application. I was hopping to find a way to do the same with LO Calc.

Until yesterday, I thought it was not possible, but since this morning...

Here's my solution :

Option Explicit

Sub CalcAsSimpleDatabaseWithoutUsingBase()

    GlobalScope.BasicLibraries.LoadLibrary("Tools")

    Dim driverManager As Object
        driverManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
        
    Dim absolutPathDB As String
        absolutPathDB = GetFileNameWithoutExtension(ThisComponent.getLocation()) & ".ods"

    Dim urlDB As String
        urlDB = "sdbc:calc:" & absolutPathDB
        
    Dim propertyValue() As New com.sun.star.beans.PropertyValue 
    
    Dim connexionDB As Object
        connexionDB = driverManager.getConnectionWithInfo(urlDB, propertyValue())
        
    Dim driverCalc As Object, defintionDB As Object, statementDB As Object
    Dim resultSet As Object, headerCell As Object, dataCell As Object 
    Dim sqlQuery As String
    Dim i As Long 
    
    if not IsNull(connexionDB)  then
        driverCalc = driverManager.getDriverByURL(urlDB)
      
        defintionDB = driverCalc.getDataDefinitionByConnection(connexionDB)
      
        sqlQuery = "SELECT ""id"" as ""ID"", ""something"" as ""Type"" FROM t_Database ORDER BY ""Type"""

        statementDB = connexionDB.createStatement()
        resultSet = statementDB.executeQuery(sqlQuery)
        
        For i = 1 To resultSet.getMetaData().getColumnCount()
            headerCell = ThisComponent.Sheets.getByName("t_Database").getCellByPosition(i + 2, 0)
            headerCell.String = resultSet.getMetaData().getColumnName(i)
        Next i      
        
        While resultSet.next()
            For i = 1 To resultSet.getMetaData().getColumnCount()
                dataCell = ThisComponent.Sheets.getByName("t_Database").getCellByPosition(i + 2, resultSet.getRow())
                dataCell.String = resultSet.getString(i)
            Next i
        Wend 

        resultSet.close
        connexionDB.close
        connexionDB.dispose  
    end If


End Sub

The spreadsheet looks like this :

I've targeted columns D and E, retrived the headers (which was not that simple to figure out) and the rest of the resultset in the yellow zone. It sure can be improve, but as it is everything works fine.

Hope it will help...

Answer 2

What you're asking for specifically isn't possible. You could try using Calc to accomplish the necessary task through clever use of formulas, such as an Imitation SQL query.

However, I find that combining Calc with Base can be quite powerful if done correctly. Is there a reason you don't want to use an ODB file? Think of it as a file needed to manage a database connection, with the actual data in most cases being stored somewhere else.

There is somewhat of a difference between LibreOffice and MS Office design in that LibreOffice applications are more tightly integrated, almost like different windows into a single large app. Whereas Excel and Access are two separate but related apps. So, it wouldn't make as much sense for Excel to rely on Access for its connections in the way that Calc relies on Base.

Answer 3

Yes, it is possible, but you have to first register the file as a database in OO/LO. The best way to do that is:

1. Open a Writer document (new one is OK)
2. Tools-->Mail Merge Wizard
3. In step 1, click on "Exchange Databases..." button.
4. In the Exchange Databases dialog box, click on "Browse"
5. Find the Calc (ODS) file that contains the data you want, then click "Open" button.
6. Now you should see the name of the file in the "Available Databases" portion of the dialog box. Click the "+" sign next to the name, and select the sheet you want to use, then click "Define". This would finalize the addition of the file to the available databases.
7. Close the Exchange Databases dialog.
8. Click "Cancel" on the Mail Merge Wizard.
9. Close the Writer document (don't even need to save it).

Here is the sample Basic code to query the first 2 columns from Sheet1. You do need to replace the "CalcFile" with the registered name of your Calc file.

Sub QueryData
    Dim DatabaseContext As Object
    Dim DataSource As Object
    Dim Conn As Object
    Dim InteractionHandler as Object
     
    DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")

    DataSource = DatabaseContext.getByName("CalcFile")
     
    If Not DataSource.IsPasswordRequired Then
      Conn = DataSource.GetConnection("","")
    Else
      InteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
      Conn = DataSource.ConnectWithCompletion(InteractionHandler)
    End If
    Stmt=Conn.createStatement()
    REM create an SQL command
    strSQL="SELECT * FROM SHEET1"
    REM finally, execute and store results in ResultSet Object
    Result=Stmt.executeQuery(strSQL)
    While Result.next()
        REM for now, let us just use the getString() method to get the
        REM columns. Note that they are being accessed by index
        MsgBox (Result.getString(1) & " " & Result.getString(1))
    Wend
    Conn.close()
End Sub

The code is based on PDF: "Database Development - OpenOffice.org & OOBasic" by Roberto C. Benitez, but I can't find the link to it now.

Similar information is on: Open Office Wiki - Database Access