Android Question Problem Database "Malformed" on some tables.

[vecino]

I'm having trouble making a copy of the BD to save.
First I close the DB, then I do a file.Copy finally I open the DB.

SQL.Close, close pending transactions, if any?
Is there a command to check if there are pending transactions and be able to execute them?
Something like:
if SQL.INTRANSACTION Then.
SQL.TRANSACTION.CLOSE

I do not know if I should also save the file -Journal of the database.

Can you orient myself with this topic?

Thanks.

 

[Mahares]

Can you orient myself with this topic?

Suppose you delete all records and then change your mind, and you want to rollback the deletion, the code will be for something like this. Of course it hinges on whether you can get in the database to issue those commands.:

SQL.BeginTransaction
    Dim strQuery As String ="DELETE FROM mytable"
    SQL.ExecNonQuery(strQuery)    
    SQL.ExecNonQuery("ROLLBACK")

If all fails, you may want to attach your database for other members to see if it is corrupted or not.

 

[TILogistic]

see:
This also happened to me when backing up a DBSQLite, it was a problem with the DATABASE journal,

I do not know if I should also save the file -Journal of the database.

SQLite Forum: backkup and restore sqlite database between different android phones

 

[Erel]

If the journal mode is 'wal', and it is likely to be, then there will be usually two additional files that are part of the database. Use File.ListFiles and you will see them.

 

[vecino]

...

Hello, the problem apparently arises when I close the dB, I do a backup (copy.file) and then reopen it again. It's already "Malformed" when I open it again.
For what information is lost from some tables, normally the table of sales lines.

 

[vecino]

...

That I have thought, but I do not know what solution to give it.
The process is the one that I have indicated, more or less:

DB.Close
Copy.File( FileDB, FileDBbak )
DB.Inicialize

And I can no longer access the table of sales lines.

 

[vecino]

If the journal mode is 'wal', and it is likely to be, then there will be usually two additional files that are part of the database. Use File.ListFiles and you will see them.

Yes, there is the DB file "-Journal", and there is not the DB "Wall". I guess the latter is eliminated when closing the dB.
And, yes, the DB is created with Journal Mode "Wall".

 

[vecino]

see:
This also happened to me when backing up a DBSQLite, it was a problem with the DATABASE journal,

SQLite Forum: backkup and restore sqlite database between different android phones

I just read in the link that when the DB closes, the "Journal" must be deleted, and if it does not delete it, it is because the DB is still doing something or has any connection.

So, I ask, what do I do to close it completely? Does not it serve db.close?

Can I repeat the order several times?
Db.close.
Db.close.
Db.close.

??? I do not know what to do.

 

[vecino]

I can not find information to ensure the closure of the DB, the file "-journal" is always there, it is never deleted :/

 

[Erel]

The wal files will not be deleted when the database is closed. The database is made of these three files.

 

[vecino]

The wal files will not be deleted when the database is closed. The database is made of these three files.

This is not what the documentation says.

A rollback journal is a temporary file used to implement atomic commit and rollback capabilities in SQLite. (For a detailed discussion of how this works, see the separate document titled Atomic Commit In SQLite.) The rollback journal is always located in the same directory as the database file and has the same name as the database file except with the 8 characters "-journal" appended. The rollback journal is usually created when a transaction is first started and is usually deleted when a transaction commits or rolls back. The rollback journal file is essential for implementing the atomic commit and rollback capabilities of SQLite. Without a rollback journal, SQLite would be unable to rollback an incomplete transaction, and if a crash or power loss occurred in the middle of a transaction the entire database would likely go corrupt without a rollback journal.
The rollback journal is usually created and destroyed at the start and end of a transaction, respectively. But there are exceptions to this rule.
If a crash or power loss occurs in the middle of a transaction, then the rollback journal file is left on disk. The next time another application attempts to open the database file, it notices the presence of the abandoned rollback journal (we call it a "hot journal" in this circumstance) and uses the information in the journal to restore the database to its state prior to the start of the incomplete transaction. This is how SQLite implements atomic commit.

The exceptions for not deleting the "journal" do not occur in this case, there is no "crash", no "power loss", no "locking_mode exclusive".
That is why I think, I believe, I deduce, it seems to me that something strange is happening and I don't know what it is. My knowledge on the subject is not enough.

 

[Erel]

Assuming that the journal mode is actually wal then there are two other temporary files: https://sqlite.org/tempfiles.html

 

[vecino]

Yes, indeed, but the "wal" file is created and destroyed on exit. The one that is never destroyed is the "journal", and that is what worries me because I don't know if it is normal or if there is a problem.

Anyway, I think that running from the application: sql.execnonquery(".backup dbbackup.db") I can make backups without problems and I can sleep more relaxed. It's not good that a client calls you to say that the DB is broken when a backup has been done, that's what happened until now, and the method I was using was that:

DB.close
file.copy("data.db", "databackup.db")
DB.Inicialize(...

The DB was already broken!!!!!

 

[KMatle]

Try it in B4J. Closing the db results in cleaning up all temp files (the journal contains unwritten data). I don't think SQlite reacts different in Android.

 

[vecino]

Anyway, I think that running from the application: sql.execnonquery(".backup dbbackup.db") I can make backups without problems and I can sleep more relaxed. It's not good that a client calls you to say that the DB is broken when a backup has been done, that's what happened until now, and the method I was using was that: The DB was already broken!!!!!

Okay, that doesn't work. I don't know how to run ".dump", nor do I know how to run ".backup".
I will continue to investigate.
Thanks, folks.