I have a table with a few rows, I have a method GetAllProjects that I run on page load and then when I do a debugLog, the result variable is blank. Also tried to use what looks like a cursor iteration, MoveNext, but can’t seem to get it right after several variations. What’s the best way to get multiple rows from mysql and use them in an array? I’m familiar with using cursors (such as in pymysql) or data objects (php) … Thanks
GetAllProjects(){
Dim sql as String
Dim rs as RecordSet
sql = "SELECT * FROM project ORDER BY number DESC"
rs= APP.DB_CONN.SQLSelect(sql)
return rs
}
Event handler:
Dim data as RecordSet
Dim result() as Variant
Dim count as Integer
data = DAO.GetAllProjects()
if data <> Nil Then
While Not data.EOF
result[count].append(data)
data.MoveNext
count = count + 1
Wend
End if
yea, I kinda messed up the code I posted. I was trying this before I did data.next to see if I could see the different steps but it only showed the final.
changing the result.append line shows a blank variable. I need to have a multidimensional array of projects where each table row has it’s own index and the values are accessible by numerical or named index ie: result[0][“id”]
Appending data to the array will just add a pointer to the data recordset object, which is being updated internally on every movenext. Once you hit end of file data will have no populated fields. So your array will have X number of pointers to an empty recordset. What you need to do is copy out the data into your array before moving to the next row. You could copy the record sets data into a variant array, using column[‘name’] or idxField[index], and then append that to your results. Best if you know what you are copying. If there is a huge amount of data then maybe rethink how you are using this and keep the recordset around and iterate through it as you need it. There is nothing stopping you going back to the beginning of a recordset, I think.
your are attempt to append a RECORDSET, which I do not believe is possible, especially since an RS only contains the “current” record… meaning at best you will have “count” pointers to the LAST record read.
Oh… yeah… exactly what Graham had just said
Either do a RS.MOVEFIRST and go thru the recordset each time … or if speed is a real issue… copy table to an IN-MEMORY database
What I mean is basically exactly this, when I select a bunch of rows in Python or Php, the object I get back is a multi dimensional array. It seems rather than getting a nice object like that, the xojo docs suggest to use them right in place in a rather sloppy way. I pasted what their example says (and I tried with no results
I changed the event handler to the following, trying to populate the list while skipping the step I want to get an array out of this data.
Dim data as RecordSet
data = DAO.GetAllProjects()
if data <> Nil Then
While Not data.EOF
ProjectList.AddRow(data.IdxField(1).StringValue)
data.MoveNext
Wend
End if
I believe you are describing what I was trying to do. I want to take each row of the recordset and append it to another array (what I was trying to do with result[count].append(data), in something like javascript or anything else that or similar would add the current index of the object to another object. So the only way is to specify field by field? I also tried that and it left my list empty …
you append is incorrect (no need for [count])
and you can append an array to each array element but I think the best way would depend on how you want to use the data
if data <> Nil Then
While Not data.EOF
ProjectList.AddRow(data.IdxField(1).StringValue) //This needs to be a string in the database. Use value for a varient
data.MoveNext() // move next to avoid infinite loop
Wend
End if
[quote=372654:@Dave S]post how you attempted to do this…
you append is incorrect (no need for [count])
and you can append an array to each array element but I think the best way would depend on how you want to use the data[/quote]
I replied above (I took the count out, that was just an example of what I mean, usually can access the entire “row” of an object by index or with a cursor, it seems xojo does neither
dim data as RecordSet
dim i as integer
dim allData() as Variant
dim rowData() as Variant
while not data.EOF
redim rowData(-1)
for i = 1 to data.FieldCount
rowData.Append(data.IdxField(i).Value)
next i
allData.Append(rowData)
data.MoveNext()
wend
This is sort of what i was getting at. This is untested and i am not sure if idxfield is 0 or 1 based … it is 1 based
I mean I replied how I tried, changed to how the docs have it doing it row by row and doesnt do anything, this is my event handler and the method just does a select * of my table
Dim data as RecordSet
data = DAO.GetAllProjects()
if data <> Nil Then
While Not data.EOF
ProjectList.AddRow(data.IdxField(1).StringValue)
data.MoveNext
Wend
End if
Put that data into some sort of container with the ability to access it via index or field value
Query that container for specific data
If that is the case, and I might be completely wrong here, why not skip step 2? Just pull specific data from the database as needed.
If that is not the case, then it sounds like you want to create a database-like container to hold your data in memory. As Dave pointed out, you could take it out of your mysql db and turn around and put it in an in-memory sqlite db. Or you could create a custom class of your own design that has the various fields you want. Then iterate through your recordset, setting your class instance’s values based on the data in the recordset, and then append that class instance to your result array.
Dim data as RecordSet
data = DAO.GetAllProjects()
if data <> Nil Then
While Not data.EOF
ProjectList.AddRow(data.IdxField(1).StringValue)
data.MoveNext
Wend
End if
assuming GETALLPROJECTS returns a populated recordset
