Execute SQL query on Calc spreadsheet without using Base

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.

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...