I have a project connection to Mysql Community Server and code that works fine in Xojo 2022R2 appears to be broken in Xojo 2022R3.
Attached code attempts to get table information about the “servers” table in the database “mysql”. You’ll obviously need to supply your own connection info but all mysql database servers should have a mysql database with “servers” table. You can use any table in a database of your choosing. I modified the sample project MYSQLDatabase in the Examples (under API → Databases)
If Not IsConnected Then
MessageBox(“Connect to the database first.”)
Return
End If
DataList.RemoveAllRows
Var data As RowSet
Try
data = mDb.TableColumns(“Servers”)
Catch e As DatabaseException
MessageBox("DB Error: " + e.Message)
Return
End Try
If data <> Nil Then
For Each row As DatabaseRow In data
DataList.AddRow(row.ColumnAt(0).StringValue, row.ColumnAt(1).StringValue, _
row.ColumnAt(2).StringValue, row.ColumnAt(3).StringValue)
Next
data.Close
End If
In Xojo 2022R2, I am correctly getting a list of columns, datatypes etc.
In Xojo 2022R3, the “For Each row As DatabaseRow In data” statement never exits the loop. If you step through the loop you’ll find that is repeatedly returning extra nil records after the last valid row. (In other words the correct rows are returned but then additional nil rows are returned which shouldn’t be there. Adding a check for nil records in the For Loop is a work around to solve the problem: e.g.
if row.ColumnAt(0).Value = Nil Then Exit For
I am running Xojo on MacBook Pro M1 Pro on MacOS Monterey 12.6.
The mysql database server is version 8.0.30-0ubuntu0.20.04.2.
The iterator interface of such DB Class (and who knows if any other iterators), as you say, seems broken. How Xojo could break something that was working before I don’t know, as it was supposed to be untouched.They have touched something there and borked it.
That’s a messy “multiplatform” check. One user in another thread reported another problem that was labeled as “Framework”, “Linux”; but in fast research the users noticed that the same bug occurs in macOS too. I said the user to inform it IN THE Issue CASE asking such additional label trying to avoid such kind of partial fix due to lack of research around the bug in all platforms. That’s not the first time they fix one instance only of a generalized problem.
I confirmed the bug , here is a test code needing settings for your own MySQL server
One can make a sample from it and upload there:
Var db As New MySQLCommunityServer
// *** Set configs
db.Host = "SomeMySQLServer"
db.Port = 3306
db.DatabaseName = "some_database"
db.UserName = "username"
db.Password = "password"
// *** End of configs
Try
db.Connect
// Use the database
Catch error As DatabaseException
// DB Connection error
MessageBox(error.Message)
Return
End Try
db.ExecuteSQL("DROP TEMPORARY TABLE IF EXISTS garbage_table_erase_it;")
db.ExecuteSQL("CREATE TEMPORARY TABLE garbage_table_erase_it (`field1` varchar(255));")
db.ExecuteSQL("INSERT INTO garbage_table_erase_it (`field1`) VALUES ('AAA');")
db.ExecuteSQL("INSERT INTO garbage_table_erase_it (`field1`) VALUES ('AAA');")
db.ExecuteSQL("INSERT INTO garbage_table_erase_it (`field1`) VALUES ('AAA');")
Var rs As RowSet
Try
rs = db.TableColumns("garbage_table_erase_it")
// Use the database
Catch error As DatabaseException
// DB Connection error
MessageBox(error.Message)
Return
End Try
For Each row As DatabaseRow In rs
If row.ColumnAt(0).StringValue = "" Then
MessageBox "Iterator has a BUG!"
break
quit
End
Next
db.ExecuteSQL("DROP TEMPORARY TABLE IF EXISTS garbage_table_erase_it;")
MessageBox "Test passed OK!"
break
quit