Hi.
I'm creating a process that updates DB structures and preserve data for SQLite, using the following steps (simply):
- Rename existing table: add a prefix
- Create new table keeping previous name
- Read and store columns name that matches in both columns
- Run a query "INSERT INTO newTable (cols) SELECT cols FROM oldTable"
- DROP oldTable
This work fine, but problems occurs when there is a PRIMARY KEY AUTOINCREMENT: in this case value in this column can change and when there are FOREIGN KEY in other table reference is lost.
To fix this problem I need to use a UUID, generate programmatically for use this value in a PRIMARY KEY column instead of an AUTOINCREMENT.
I have read a similare problem on B4I Forum with this solution:
Sub RandomUUID As String
Dim no AsNativeObjectReturn no.Initialize("NSUUID").RunMethod("UUID", Null).RunMethod("UUIDString", Null).AsString
End Sub
Unfortunately I was not able to run the solution on B4A.
Have you got any suggestion?
Thank you very much
I'm creating a process that updates DB structures and preserve data for SQLite, using the following steps (simply):
- Rename existing table: add a prefix
- Create new table keeping previous name
- Read and store columns name that matches in both columns
- Run a query "INSERT INTO newTable (cols) SELECT cols FROM oldTable"
- DROP oldTable
This work fine, but problems occurs when there is a PRIMARY KEY AUTOINCREMENT: in this case value in this column can change and when there are FOREIGN KEY in other table reference is lost.
To fix this problem I need to use a UUID, generate programmatically for use this value in a PRIMARY KEY column instead of an AUTOINCREMENT.
I have read a similare problem on B4I Forum with this solution:
Sub RandomUUID As String
Dim no AsNativeObjectReturn no.Initialize("NSUUID").RunMethod("UUID", Null).RunMethod("UUIDString", Null).AsString
End Sub
Unfortunately I was not able to run the solution on B4A.
Have you got any suggestion?
Thank you very much