Dumping SQL Query/ Recordset in to LO Calc Sheet using basic code and Range

Question

I’m new to Calc and I use to be able to do something similar to this in Excel / VB. The code below pulls a SP from MySql, and does exactly what I want, except for the While loop. In VB I was able to loop through the recordset and dump it into a Range like so, “ActiveWorkbook.Sheets(1).Range("A5").CopyFromRecordset rs”. Without having to go column by column. Is there anything in Basic-Calc like this? I’ve looked all over and haven't come across anything.

...

Sub  RetriveData()
Dim RowSetObj As Object, ConnectToDatabase As Object

DATABASE_NAME = "file:///home/jod/Documents/test.odb"

DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource=DBContext.getByName(DATABASE_NAME)

If Not DataSource.IsPasswordRequired Then
ConnectToDatabase = DataSource.GetConnection("","")
Else
  InteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
ConnectToDatabase = DataSource.ConnectWithCompletion(InteractionHandler)
End If


oURL="file:///home/jod/Documents/CodExm3.ods"
oDoc=StarDesktop.loadComponentFromURL(oURL, "_blank", 0, Array())
oSheet=oDoc.Sheets(0)
oSheet.Name="Tasks"

SQLQuery= "call GVWC_DB.TestSP();"
SQLStatement=ConnectToDatabase.createStatement
RowSetObj=SQLStatement.executeQuery (SQLQuery)

While RowSetObj.Next
i=i+1
oCell=oSheet.getCellByPosition(0,i)
oCell.String=RowSetObj.getString(1)
oCell=oSheet.getCellByPosition(1,i)
oCell.String=RowSetObj.getString(2)
oCell=oSheet.getCellByPosition(2,i)
oCell.String=RowSetObj.getString(3)
oCell=oSheet.getCellByPosition(3,i)
oCell.String=RowSetObj.getString(4)
oCell=oSheet.getCellByPosition(4,i)
oCell.String=RowSetObj.getString(5)
oCell=oSheet.getCellByPosition(5,i)
oCell.String=RowSetObj.getString(6)
oCell=oSheet.getCellByPosition(6,i)
oCell.String=RowSetObj.getString(7)
oCell=oSheet.getCellByPosition(7,i)
oCell.String=RowSetObj.getString(8)
oCell=oSheet.getCellByPosition(8,i)
oCell.String=RowSetObj.getString(9)
oCell=oSheet.getCellByPosition(9,i)
oCell.String=RowSetObj.getString(10)
oCell=oSheet.getCellByPosition(10,i)
oCell.String=RowSetObj.getString(11)
oCell=oSheet.getCellByPosition(11,i)
oCell.String=RowSetObj.getString(12)
oCell=oSheet.getCellByPosition(12,i)
oCell.String=RowSetObj.getString(13)
oCell=oSheet.getCellByPosition(13,i)
oCell.String=RowSetObj.getString(14)
oCell=oSheet.getCellByPosition(14,i)
oCell.String=RowSetObj.getString(15)
oCell=oSheet.getCellByPosition(15,i)




Wend

oSheet.getRows.insertByIndex( 0, 6 )

'Sheet.Columns.removeByIndex(5, 1)

End Sub
...

Answer 1

The LibreOffice API doesn't like gigantic do-everything-all-in-one-shot commands as VBA does. Instead, all it takes is a simple For loop.

resultSet = SQLStatement.executeQuery(SQLQuery)
While resultSet.next()
    For iCol = 1 To resultSet.getMetaData().getColumnCount()
        oCell = oSheet.getCellByPosition(iCol - 1, resultSet.getRow())
        oCell.String = resultSet.getString(iCol)
    Next
Wend

This works if all you need is a string representation of the data. When using methods such as getInt() with some columns, a For loop may not be the best choice.

Documentation: ResultSet