Add SQL result to sheet using LB Basic

[bradleys]

I have just started using LibreOffice Calc coming from Excel VBA.

I have managed to get a connection to my database using SQL, but I’m struggling to get the entire contents from the SQL results added to my active sheet.

The SQL results get 20 columns of data which I have limited to 10 records

In VBA I would have used something like this to enter the results into cell A1

Dim rs as recordset

Const strSQL = "SELECT * FROM owners LIMIT 10"
    
    Set rs = conn.Execute(strSQL)
    With ActiveSheet.QueryTables.Add(Connection:=rs, Destination:=Range("A1"))
        .Refresh
    End With

What can I use in LO to enter the all the data in rs into the sheet starting at position A1

My LO code is as follows:

Stmt=Conn.createStatement()

‘Create worksheet
Worksheets.Add(After :=Worksheets("README")).name = "Owners"
Set ws = wb.Sheets("Owners")

REM create an SQL command
strSQL="SELECT * FROM owners LIMIT 10"
rs = stmt.ExecuteQuery(strSQL)

[sokol92]

Hello!
The first step is to create and save an .odb file that will contain the parameters for connecting to the database.
See the documentation for the LibrеOffice Base component and the wonderful book “OpenOffice.org Base Macro Programming” by Andrew Pitonyak.

[bradleys]

@bradleys

    Set rs = conn.Execute(strSQL)
    With ActiveSheet.QueryTables.Add(Connection:=rs, Destination:=Range("A1"))
        .Refresh
    End With

I’ve connected to my database fine and received the results back. I just need to get the full SQL results added to the spreadsheet

[sokol92]

Sub Test()
  Dim fileURL As String, DBRangeName As String, sqlQuery As String
  Dim oBaseContext As Object, oDBSource As Object, oDoc As Object, oRange As Object
  Dim props(3) As New com.sun.star.beans.PropertyValue  
  
  fileURL=ConvertToURL("C:\Temp\PostgreDummy.odb")  ' ???
  
  ' A database range (Import_1) is created in a new Calc document.
  DBRangeName="Import_1"
  oDoc = StarDesktop.LoadComponentFromUrl("private:factory/scalc", "_blank", 0, Array())
  oDoc.DatabaseRanges.addNewByName(DBRangeName, oDoc.Sheets(0).getCellRangeByName("A1").RangeAddress)
  
  ' 3. The SQL query is executed and the result is placed in the Import_1 database range. 
  oBaseContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
  oDBSource=oBaseContext.getByName(fileURL)
  With oDBSource
   .User="test3"       ' ???
   .Password="test3"   ' ???
  End With   
  
  sqlQuery="Select * from f_tvar limit 10"  ' ???
  
  props(0).Name="DatabaseName" : props(0).Value=FileURL
  props(1).Name="SourceType"   : props(1).Value=1
  props(2).Name="SourceObject" : props(2).Value=sqlQuery
  props(3).Name="IsNative"     : props(3).Value=True
   
  oRange=oDoc.DatabaseRanges.getByName(DBRangeName).referredCells
  oRange.doImport props
End Sub

[Villeroy]

WIth a linked database range:

ThisComponent.DatabaseRanges.getByName("Import1").refresh()

[Wanderer]

@bradleys

What can I use in LO to enter the all the data in rs into the sheet starting at position A1

This is no precise answer to your question. If you don’t like to be steered to another type of working please stop reading here.
.
If you have a query in Base which gives the desired results you can drag this query (from data-sources window) to your A1. This will copy the results in your spreadsheet and leave a “link” to the datasource (query/table/view), so you can refresh data by menu (Data > refresh or similiar, my locale is not english). On some sheets I use a button to call a macro to refresh data for this…
.
IMHO this setup is often easier to handle, as the SQL stays in Base and Calc/Basic has only a simple refresh to do.
.
PS: Villeroy provided the code to refresh in the comments above