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