Android Question How to update sqllite via runtime using B4A

[rafaelcamara]

I have an application that is using a sqllite database. When I have to release a new apk that has database changes, I have to do manualy. How to update sqllite via runtime using B4A?

Thanks,

Rafael Câmara

 

[OliverA]

Have a table with your DB version in it. Then when the App starts, check the DB version and if it is different, run the code that upgrades it. As you progress, keep each upgrade method and create newer methods for newer updates. This way, if you happen to run into an older DB version (like two or three versions back) you can iterate through the upgrade methods until the DB is brought up to speed.

 

[rafaelcamara]

Hi OliverA,

Thanks for the feedback but the logic I know. The problem is how to do by code. I´m not sure with I can use alter table to do update on DB sqllite. Do you have any ideia or some example how to do these update?

Thanks,

Rafael Câmara

 

[OliverA]

See: https://www.techonthenet.com/sqlite/tables/alter_table.php
for the SQL involved.

For dropping a column, you'll have to write your own code (SQLite has no SQL directive for that). @Mashiane has some code for that here: https://www.b4x.com/android/forum/t...dness-useful-methods.59058/page-2#post-476947

Clarification: Code only needed if you want a "dynamic" type update where you don't have to manually list all the fields. The SQL link above can be used create the SQL syntax manually when all the fields are known.

 

[OliverA]

Changing column type: https://www.b4x.com/android/forum/t...-column-type-in-a-sqlite-table.61850/#content

 

[Computersmith64]

Hi OliverA,

Thanks for the feedback but the logic I know. The problem is how to do by code. I´m not sure with I can use alter table to do update on DB sqllite. Do you have any ideia or some example how to do these update?

Thanks,

Rafael Câmara

What specifically do you want to do? If you want to add a column (field) you can do this:

SQL.ExecNonQuery($"ALTER TABLE ${TableName} ADD COLUMN ${NewColumn} ${ColumnType}"$)

There is no SQLite function that allows you to remove a column. To do that you'd have to basically rebuild the table without the column you want to remove.

Fyi - all my apps that use SQLite actually create the database at runtime (ie: I don't include a ready-made database in the package & then copy it the first time the app runs). Doing it this way, I can check the integrity of the database every time the app starts & rebuild the database or any missing tables as required.

- Colin.

 

[eps]

What database changes are we talking about? Are we talking about data structure changes or data changes? or both?

Obviously data changes are easier to handle - you can detect a DB version change, unload to a file or list and then update the database and then reload from where you stored them.

Data structure changes are more difficult and of course you'd need to understand the impact of those changes - some might not be upgradeable, but we don't know what changes you are talking about.. Please give us a clue as to what your talking about, there are too many unknowns for us to help you adequately.

Of course if you change the database structure the existing database will simply disappear.. but of course most of the time that isn't desired. Can you outline what database changes you are talking about and give us at least an outline scenario which you are thinking about.

In an ideal world you would have a decent database design from the start which you can then extend as opposed to change.

 

[johnB]

The lack of exact requirements makes an answer difficult, but from an SQL nob, can't you rename the existing db and create a new new one with the fields required (deleting what you don't want and adding new fields. The data for the new fields obviously needs to be in the existing fields or a constant or inserted after the alteration) using a simple SQL statement. I have done it many times and it's really quick, creates the new file, not need to process records sequentially. I'm on my phone, I'll post some "nob" code when I'm on my PC

    'Create Table
'SQL1.ExecNonQuery("CREATE TABLE Trans01_Copy AS Select ID, Portfolio, TranDate, Qty FROM Trans01")
'SQL1.ExecNonQuery("CREATE TABLE Trans01_Copy AS Select ID, Portfolio, TranDate, Qty FROM Trans01")

'Create Table with New Field at END
'SQL1.ExecNonQuery("CREATE TABLE IF NOT EXISTS Trans01_Copy02 (ID INTEGER, Portfolio TEXT, TranDate TEXT, Qty INTEGER, NewField TEXT)")
'SQL1.ExecNonQuery("INSERT INTO Trans01_Copy02 (ID, Portfolio, Trandate, Qty) Select ID, Portfolio, TranDate, Qty FROM Trans01")
  
'Add a New Field anywhere - 2 ways
'Specify Field in INSERt
SQL1.ExecNonQuery("CREATE TABLE IF NOT EXISTS Trans01_Copy03 (ID INTEGER, Portfolio TEXT, NewField TEXT, TranDate TEXT, Qty INTEGER)")
SQL1.ExecNonQuery("INSERT INTO Trans01_Copy03 Select ID, Portfolio, Null, TranDate, Qty FROM Trans01")
  
'No Need to NAME Field in INSERT
SQL1.ExecNonQuery("CREATE TABLE IF NOT EXISTS Trans01_Copy04 (ID INTEGER, Portfolio TEXT, NewField TEXT, TranDate TEXT, Qty INTEGER)")
SQL1.ExecNonQuery("INSERT INTO Trans01_Copy04 (ID, Portfolio, Trandate, Qty)  Select ID, Portfolio, TranDate, Qty FROM Trans01")

These were done using B4J (sorry didn't read the post correctly) but they should work in B4A