Hello
I use a MySQL database (App.db). I can add a row in timesheet table this way
Var row As New DatabaseRow
row.Column("title") = Self.title
row.Column("duration_minutes") = Self.durationMinutes
Try
App.db.AddRow("timesheet", row)
Catch error As DatabaseException
MessageBox(error.Message)
End Try
How can I get the primary key value (id as auto-inc primary value)?
I try this code
Self.ID = App.db.AddRow("timesheet", row, "id")
System.DebugLog(Self.ID.ToString)
I got this, where 822 is the primary key
13:21:47 : Apr 17 13:21:47 edisontv.debug[32753] : 822
This works but the next SQL Command (Select, Add…) throw an exception:
Commands out of sync; you can’t run this command now
How can I solve my problem?
On the documentation I found this on this page:
Var lastValue As Integer lastValue = db.LastInsertedRowID
But I can’t found any information about LastInsertedRowID…
From the Release Notes: Xojo 2024r2:
- Added an overloaded Database.AddRow(tableName As String, row As DatabaseRow, idColumnName As String = ) As Integer This returns a unique id of the newly inserted row. This API should be preferred over using the more specific SQLiteDatabase.LastRowID and MySQLCommunityServer.LastInsertedRowID which are now deprecated. (76031)
So you’re using this new(er) feature:
Self.ID = App.db.AddRow("timesheet", row, "id")
…and it seems to me that it’s been implemented wrongly for the MySQL Plugin by Xojo - causing this bug.
According to the MySQL documentation:
https://dev.mysql.com/doc/refman/8.4/en/commands-out-of-sync.html
If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order.
This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.
So… I guess you should file an Issue for Xojo regarding that.
And meanwhile maybe continue to use the deprecated function?
Var db As New MySQLCommunityServer
// insert a database record
Var id As UInt64 = db.LastInsertedRowID
Another Thread about Databases “LastID” which may contain related/useful information: Last ID
2 Likes