Android Question How to "dump" a database to a text file?

[vecino]

Hi, from my application, how can I execute a "dump" of a database?
sql.execnonquery("dump > dbbak.sql")
With some shell command?
Thanks.

 

[aeric]

Which database you are using and management software?

Edit: You mean generate the CREATE TABLE schema and all the INSERT SQL commands from SQLite into a text file?

 

[vecino]

Which database you are using and management software?
Edit: You mean generate the CREATE TABLE schema and all the INSERT SQL commands from SQLite into a text file?

Yes, that's right, although the records are enough for me, I don't need the structure of the tables.
Now I do it with a simple loop sending it to a text file, but I would like to do it by sqlite ".dump", or something like that.

 

[aeric]

I think you can copy out the .db file to your PC then use a sqlite3 tool command to create the dump.

 

[vecino]

Hello, this option does not work for me. What I want to do is to make a backup of the data of some tables, which are then sent by email.
So far the process I have always done has been:
-Close the database.
-Save the DB in a zip file.
-Send the zip file by email.
-Open the DB again.
The problem is that I have found that the DB usually breaks.

 

[aeric]

How about use DBUtils ExecuteJSON to create a JSON output?

 

[vecino]

Hi, that option can be useful for me if there is an easy way to import that json to another database.

 

[TILogistic]

Do you want to backup your database schema and data?

like this:

Command Line Shell For SQLite

 

[vecino]

Hello again, I think we have succeeded, simply by executing:
sql.execnonquery(".backup dbbackup.db"
Thank you, friends.

 

[vecino]

Ultimately, that doesn't work :/

 

[aeric]

Ultimately, that doesn't work :/

You mean the command isn't working or the file is corrupted?

 

[Sandman]

Ultimately, that doesn't work :/

Why? Seemed like a working solution from where I'm sitting. What's the problem with it?

 

[vecino]

Hi, the problem is that "Sql.ExecNonQuery(".debug dbbak.db") does not work, error at point ".").
I don't know how to execute that command from the application.

 

[aeric]

.debug

You mean .dump or .backup work but not .debug? Why do you want to use .debug ?

 

[vecino]

No, none of them work.
Surely there is another way to run it: shell(...) ?

 

[Sandman]

Surely there is another way to run it: shell(...) ?

Every time in the past when I've mentioned shell as a great way to do things, Erel pops out to inform me that Android != Linux. So I imagine shell isn't the way forward, but give it a shot and post the results?

For completeness, could you also try to use a filename without a period (as the error message doesn't say with absolute certainty which period it dislikes - I imagine it's the first, but who knows)

Also, are you sure you're feeding it a correct path for the file?

 

[aeric]

What I do is:
1. Connect B4A to phone using B4A-Bridge with "FTP-Server" enabled
2. In B4A menu, Tools -> B4A Bridge -> File Explorer, browse to the phone storage.
The address looks like this ftp://172.20.10.7:6781/Android/data/com.puterise.learn/files/
3. Copy the data.db file to the PC or desktop. Now I have the db file.
4. To create a dump file, I use DB Browser for SQLite ( https://sqlitebrowser.org/ ). Open the data.db
5. Open menu File, Export -> Database to SQL file...
6. "Select All" tables and any options or just leave as default then click OK button
7. Save as data.db.sql file.

 

[Sandman]

What I do is

Seems like a working solution for some use-cases, but not the one described:

Hi, from my application, how can I execute a "dump" of a database?

 

[vecino]

I have tried with dot and without dot, with path to the output file and without path.
The error is that it does not know "debug" and does not know the dot ".".

    Try
        globales.DBconex.ExecNonQuery("debug kk1.db")
    Catch
        Log(LastException)
    End Try
    
    Try
        globales.DBconex.ExecNonQuery(".debug kk2.db")
    Catch
        Log(LastException)
    End Try   

    Try
        globales.DBconex.ExecNonQuery(".debug "&globales.cDBruta&"/"&"kk3.db")
    Catch
        Log(LastException)
    End Try

Error occurred on line: 283 (acUtilidades)
android.database.sqlite.SQLiteException: near "debug": syntax error (code 1): , while compiling: debug kk1.db
(Exception) java.lang.Exception: android.database.sqlite.SQLiteException: near "debug": syntax error (code 1): , while compiling: debug kk1.db

Error occurred on line: 289 (acUtilidades)
android.database.sqlite.SQLiteException: near ".": syntax error (code 1): , while compiling: .debug kk2.db
(Exception) java.lang.Exception: android.database.sqlite.SQLiteException: near ".": syntax error (code 1): , while compiling: .debug kk2.db

Error occurred on line: 295 (acUtilidades)
android.database.sqlite.SQLiteException: near ".": syntax error (code 1): , while compiling: .debug /storage/emulated/0/Android/data/conkex.preventa/files/kk3.db
(Exception) java.lang.Exception: android.database.sqlite.SQLiteException: near ".": syntax error (code 1): , while compiling: .debug /storage/emulated/0/Android/data/conkex.preventa/files/kk3.db

I think I'm going to abandon the attempt and just loop through the records and write them to a text file.

 

[vecino]

What I do is:

Indeed, but the problem is that users have to make backups from the application, and then send it by email, encrypted.
The problem is that the "usual" way corrupts the DB.

DB.Close
File.Copy( DB, DBBAK.DB )
DB.Initialize(... )

The original DB is corrupted, even.

I hadn't seen Sandman's response, but that's it.