Android Question aSQLite Problem

[mjtaryan]

I used a desktop (Windows) utility to create an SQLite 3 database. I then wrote a b4a utility to populate the db from text files (I had been using the Windows utility, but it was very cumbersome and time consuming to do it row by row). There are some individual records that need to be modified slightly (certain values changed or misspellings that need correcting).

I was going to use the Windows utility to do this, but I encountered a problem. The utility will no longer open the db after running the b4a app on my device. The Windows utility tells me the db seems to be encrypted and asks me to enter the encryption key. So, my questions are these:

1. Why the encryption (what does Android change that the Windows utility sees it as "encrypted")?
2. Is there a solution and, if so, what?
3. Can anyone recommend a Windows utility with a good UI where SQLite dbs can be used on both OSs without any problem?

Thanks.

 

[Erel]

Assuming that you didn't use SQLCipher library, the database was not encrypted. I'm using this tool to open SQLite databases: http://www.sqliteexpert.com/

 

[mjtaryan]

Assuming that you didn't use SQLCipher library, the database was not encrypted. I'm using this tool to open SQLite databases: http://www.sqliteexpert.com/

Thanks, Erel. No, all I used was the SQL library. I'll give sqliteexpert a try.

 

[mjtaryan]

Thanks, Erel. No, all I used was the SQL library. I'll give sqliteexpert a try.

Well, I tried sqliteexpert and about a half dozen other tools. Only the original database will open with them. When I try to open the copy to which I inserted additional rows on my device, they all (expert for SQLite Manager - in which I created the original database) give me a message saying "The database disk image is malformed."

Any ideas? Any solutions (I don't want to have to create the db all over again)? Thanks.

 

[Eric H]

Try comparing the default sqlite table of the two databases.

 

[Erel]

How are you copying the database from the device to the PC?

 

[edgar_ortiz]

Be sure to always close the database.

I had similar problems and this was the solution.

Regards

Edgar

 

[mjtaryan]

Try comparing the default sqlite table of the two databases.

Eric, thanks,

I'm not that familiar with sqlite (yet). What is the "default table" and how do I compare them if only the original can be opened in any tool I tried.

 

[mjtaryan]

How are you copying the database from the device to the PC?

Erel, I connected the device to the PC via USB, used Windows explorer to make a copy that I renamed the same as the database I was copying with a suffix of '2' (i.e. Abc.db --> Abc2.db). Is that a problem?

 

[mjtaryan]

Be sure to always close the database.

I had similar problems and this was the solution.

Regards

Edgar

Edgar, thanks. I forgot to do that when I first wrote the app and thought that might be the problem so I deleted the database from the device and tried again with a close statement. But I still have the problem of the database appended on the device not opening on the PC with any tool I've tried (they all give me the same error message).

 

[Eric H]

Eric, thanks,

I'm not that familiar with sqlite (yet). What is the "default table" and how do I compare them if only the original can be opened in any tool I tried.

Here is some FAQ info on SQLite databases, including info on default tables:
http://www.sqlite.org/faq.html#q7


(7) How do I list all tables/indices contained in an SQLite database

If you are running the sqlite3 command-line access program you can type ".tables" to get a list of all tables. Or you can type ".schema" to see the complete database schema including all tables and indices. Either of these commands can be followed by a LIKE pattern that will restrict the tables that are displayed.

From within a C/C++ program (or a script using Tcl/Ruby/Perl/Python bindings) you can get access to table and index names by doing a SELECT on a special table named "SQLITE_MASTER". Every SQLite database has an SQLITE_MASTER table that defines the schema for the database. The SQLITE_MASTER table looks like this:

CREATE TABLE sqlite_master (
  type TEXT,
  name TEXT,
  tbl_name TEXT,
  rootpage INTEGER,
  sql TEXT
);

For tables, the type field will always be 'table' and the name field will be the name of the table. So to get a list of all tables in the database, use the following SELECT command:

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

For indices, type is equal to 'index', name is the name of the index and tbl_name is the name of the table to which the index belongs. For both tables and indices, the sql field is the text of the original CREATE TABLE or CREATE INDEX statement that created the table or index. For automatically created indices (used to implement the PRIMARY KEY or UNIQUE constraints) the sql field is NULL.

The SQLITE_MASTER table is read-only. You cannot change this table using UPDATE, INSERT, or DELETE. The table is automatically updated by CREATE TABLE, CREATE INDEX, DROP TABLE, and DROP INDEX commands.

Temporary tables do not appear in the SQLITE_MASTER table. Temporary tables and their indices and triggers occur in another special table named SQLITE_TEMP_MASTER. SQLITE_TEMP_MASTER works just like SQLITE_MASTER except that it is only visible to the application that created the temporary tables. To get a list of all tables, both permanent and temporary, one can use a command similar to the following:

SELECT name FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type='table'
ORDER BY name

There is also a default table used in Android apps exclusively called "android_metadata". You can read more on that here: http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/

 

[Eric H]

note: none of the code above is B4A code, but I enclosed it in CODE tags to make it easier to read.

 

[mjtaryan]

note: none of the code above is B4A code, but I enclosed it in CODE tags to make it easier to read.

Thanks Eric! I'll have to keep the email notification of your reply.

However, an interesting development has appeared. I did some maintenance on my pc (running a cleanup package I have). I have no idea what prompted me, but just after I made my last post I tried again to open the file that has had problems in sqlite expert. And lo and behold it opened without any problem. I hope that's a permanent result. Nevertheless, I want to thank you and everyone else for your help.

 

[eps]

I would also recommend the Firefox Add On, I use that to manipulate my databases and the data.