Number of dimensions of Array

Hi,

When working with RowSet, I realised MS SQL Server only supports the MoveToNextRow method. This isn’t great if I wish to iterate the RowSet multiple times if stored as a property for example.

Therefore, I think I should be storing the RowSet data into a multi-dimensional Array. If I don’t know how many rows and columns the RowSet will have (needs to handle variable dimensions), it’s not an issue as I can iterate over all rows and columns and write it to a multi-dimensional array.
But how can I then determine the number of dimensions the array has when trying to iterate over it when required e.g. when writing array contents to a WebListBox? Arrays don’t seem to have a Dimensions property/method but only gives the length of each dimension if the dimension number if specified.

The following thread didn’t help as there isn’t a clear solution:
Detect array dimensions in a particular array programmatically at runtime - General - Xojo Programming Forum

When you create an array you determine at that time whether it is one dimensional or 2 dimensional. For example:

// a one dimensional array
Var My1D_array() As String

// a two dimensional array
Var My2D_array(-1,-1) As String

If you are populating an array with row and column data it will need to be a 2 dimensional array. The first dimension is the rows and the second dimension is columns. Here is an example of a generic Method to query a database, populate and array and then return the array:

Function DBReturnRecordSetArrary2D(sqlString As String) As String(,)
// Dimension the RowSet variable
Var rs As RowSet
// Dimension integer variables that will keep track of the row numbers and Column numbers in the RowSet 
Var row,col As Integer
//Dimension a 2D array to store and return the RowSet Query Result
Var mArray(-1,-1) As String

// Query the database and return the result into the RowSet variable
rs = db.SelectSQL(sqlString )

// Check to see if the query caused a Databade error
If db.Error Then
  // Dimension the error variables
  Var desc,err As String
  // Define the database error information
  desc = "DatabaseMethods.DBReturnRowSetArrary2D.DatabaseError on " + db_Name
  err = "Error Code: " + Str(db.ErrorCode) + " - Error Message: " + db.ErrorMessage
  // Notify the user of the error
  MessageBox(desc + err)
  // Return an empty array
  Return mArray
End If

// If the RowSet is not Nil
If Not (rs = NIL) Then
  // Resize the dimensions of the 2D array to equal the contents of the RowSet
  mArray.ResizeTo(rs.RowCount - 1,rs.ColumnCount - 1)
  
  // Move to the first row in the RowSet and 
  rs.MoveToFirstRow
  // Set the row variable to the first row in the array
  row = 0
  // Loop through the rows in the RowSet until after the last row
  While Not rs.AfterLastRow
    //Loop through the columns in the row and add the column contents to array
    For col = 0 to rs.ColumnCount -1
      mArray(row,col) = rs.ColumnAt(col).StringValue
    Next
    // Move to the next row in the RowSet
    rs.MoveToNextRow
    // Set the row variable to the next row in the array
    row = row + 1
  wend
  // Return the array of Database values
  Return mArray
  
  // If the RowSet is Nil handle the error and Notify the user of the error 
Else 
  // Dimension the error variables
  Var desc,err As String
  // Define the database error information
  desc = "DatabaseMethods.DBReturnRowSetArrary2D.DatabaseError on " + db_Name
  err = "The rowset returned Nil using:" + EndOfLine + sqlString 
  // Notify the user of the error
  MessageBox(desc + err)
  // Return an empty array
  Return mArray
End If
End Function

You can get the number of rows from the returned array to populate a Listbox like so, (assumes the first row is the Primary Key and you want to store it in the Listbox RowTag:

// Dimension integer variables that will keep track of the row numbers and Column numbers in the RowSet 
Var row,col As Integer
// Dimension a string variable that is your query.
Var sqlstrng As String = "SELECT * from MyTable"
// Dimension an empty 2D array to hold the returned data from the method
Var dbData(-1,-1) As String

// Call the Method to do the query using the sqlstring variable
// The format for the method is DBReturnRecordSetArrary2D(sqlString As String) As String(,)
dbData = DBReturnRecordSetArrary2D(sqlstrng)

// Check to make sure the returned array is not empty by checking to see that there are rows
// The first row is zero so the LastIndex must be greater than -1
If dbData.Lastindex(1) > -1 Then
  // Set the listbox ColumnCount to one less that the actual Arraysecond dimension last index
  // Because the 0 column is the Primary Key which we do not want to show 
  // Edit: Since the array Last Index(2) is one less than counting from 1 for a Colun Count we just use the LastIndex(2)
  ListBox1.ColumnCount = dbData.Lastindex(2)
  // Loop through the rows in the Array
  For row = 0 to dbData.Lastindex(1)
    // Add a row
    ListBox1.AddRow
    //Populate the Primary Key in the rowTag
    ListBox1.CellTagAt(ListBox1.LastAddedRowIndex) = dbData(row,0)
    //Loop through the columns in the row and add the column contents to the Listbox
    // The first column in the array is the Primary Key so we start at 1 and then store the 0 column in the RowTag
    For col = 1 to dbData.Lastindex(2)
      ListBox1.CellTextAt(row,col-1) = dbData(row,col)
    Next
  Next
  
  // The array was returned empty so handle the error
Else
  // Notify the user of the error
  MessageBox("The Database Query returned no data")
End If
2 Likes

Try using an array of dictionaries.

Thanks for the detailed response - that all made sense to me!

I had a bit of brain fog when writing the question. For some reason, I was thinking a one-column query will return 1D but of course it’s still 2D with length of 1 in second dimension.

1 Like

Why not create an in memory SQLIte DB to hold the query results from MS SQLServer? That would give you plenty of flexibility!

-karen

2 Likes

Just because the RowSet is 2 dimensional doesn’t mean you can’t return a one column query as a one dimensional array. You just move through the rows and the data is always rs.ColumnAt(0).StringValue which you add to a one dimensional array. The 1D array is useful for populating a PopupMenu since they are a one dimensional list.

1 Like

Yes that’s a good point

Interesting - that’s also a good idea, I hadn’t thought of that! Is there a quick way of populating the SQLite DB from RowSet returned from SQL Server DB? Or would I have to go through row by row and do an INSERT statement using the SQLiteDb ExecuiteSQL method?
I can potentially see this being useful when the end-user wants to filter results in a WebListBox. Their filters can be used to query the in-memory SQLite DB in a simple way and then re-populate the WebListBox.

Also would this have any performance benefit over @Tom_Dixon 's solution (persisting RowSet to Array instead) e.g. for populating WebListBox etc?

RowCount method is not supported in MS SQL Server database
How else can I determine RowSet dimensions to be able to resize the Array. The only thing I can think of is to resize the array by incrementing its row size each time you iterate RowSet row. But not sure if there’s a better solution to this?

It is easier if you create a Class with all the properties of a row, then a unidimensional array to hold the instances of that class.

1 Like

I find that hard to believe since RowCount is a property of RowSet which holds the data returned by the database. If the database only returns a row when you MoveToNextRow that would be different from any other database I’ve ever connected to, (SQLite, Oracle, MySQL). However resizing the array each time you move to a new row is a perfectly valid way to go about fitting the data in the array.

    row = row + 1
    mArray.ResizeTo(row,rs.ColumnCount -1)

Unfortunately it’s true, as stated in the documentation:
RowSet — Xojo documentation

An UnsupportedOperationException is raised and I have tested it myself to confirm it.

How about doing a simple

SELECT count(*) FROM table_name;

To get the toal number or rows