CODEWITHSUNDEEP
androidmysqlsqlite
user822819
user822819

Reputation: 1

Import MySQL DB to android SQLite DB

Can you please tell me how to import external SQLite or MySQL DB to android SQLite or how can i use the external db in the android application?

Upvotes: 0

Views: 4601

Answers (3)

Selecsosi
Selecsosi

Reputation: 1646

I know it can be a cheap way but I used a push message service (MQTT) to take a copy of my mysql dump file and push the message to the phone, where I run it through a parser to insert rows into my local db. The mqtt message service has a precompiled .jar library that you can use quite easily in an android app.

There are a variety of means in which to implement the mqtt on your web server (SAM->php, or what I did write a c++ listening service to forward messages [Using an open source mqtt library called mosquitto]). The code to parse in the data is only a few lines since the message I receive in my android service is just a byte array.

As an example, I convert the byte array into a string, tokenize the string, and then insert it into the database:

public void parseMysqlDump(byte[] thisPayload) {
    ContentResolver cr = this.getContentResolver();

    String mysqlDump = "";
    for(int i = 0; i < thisPayload.length; i++) {
        mysqlDump += (char) thisPayload[i];
    }
    String[] mysqlDumpNewLineTokens =  mysqlDump.split("\\n");
    //The first line is the table headers stuff so start with i=1 to get to the first
    //data line
    for(int i = 1; i < mysqlDumpNewLineTokens.length; i++) {
    String[] mysqlDumpSpaceTokens = mysqlDumpNewLineTokens[i].split("\\s");
    String dataTitle = mysqlDumpSpaceTokens[0];
    ContentValues cv = new ContentValues();
    cv.put(Vehicle_DataMetaData.DATA_VALUE, mysqlDumpSpaceTokens[1]);
    cr.insert(Vehicle_DataMetaData.CONTENT_URI, cv);
}

TL;DR: Take a mysql dump, send it as a string to the phone, parse the string into the db.

If you are interested in the mqtt stuff or wanted any clarification, just comment and I will get back to you

Upvotes: 0

Tim
Tim

Reputation: 5421

Take care that the external SQLite database you wish to import uses INTEGER datatypes for the primary key, not INT or any of the other variants of INT. Some flavors of SQLite from the various SQLite Consortium members treat INTEGER and INT primary keys the same whereas (flagship) SQLite treats them differently.

See section 2.0 here: http://www.sqlite.org/datatypes.html

and see section on RowId and Primary Key here: http://www.sqlite.org/lang_createtable.html#rowid

A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER". Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid. [emphasis added]

EDIT: If your external SQLite database has its integer primary keys defined as INT or any of the other variants of INT, rather than as "INTEGER", you can get erroneous results when attaching to the database file from a consortium member's implementation. Let's say the FK value in a child table is 110; a join that treats the integer value as an alias for the rowid will look to the parent table to grab the 110th physical row, which may or may not be the row whose PK value = 110 if, in the parent table, the PK was defined as INT or BIGINT any of the other variants! In SQLite only an INTEGER [verbatim] PK is treated as alias for rowid, but some implementations did not follow that rule and treat all INT types as aliases for the rowid. Thus, when attaching from one of those implementations to an external SQLite datafile that was created using flagship SQLite, it is imperative to have used "INTEGER" (verbatim) primary keys and not any of the other INT types.

Upvotes: 3

kgiannakakis
kgiannakakis

Reputation: 104168

Have a look here to find some converters tools that can convert an MySQL dumb to a SQLite database. You could then import the database in your application as usual.

It is impossible to use the MySQL db directly.

Upvotes: 1

Related Questions