Android Question [Solved] Read SQL file and modify existing database

[asales]

I have a database sqlite in my app and I need modify with a new table and several records.
How I can read the this SQL file (present in Assets) and update the database:

-- Table: book
CREATE TABLE book (
    id  INTEGER  PRIMARY KEY AUTOINCREMENT,
    name  VARCHAR(50),
    author VARCHAR(50),
    purchaseDate DATE
);

INSERT INTO book (id, name, author, purchaseDate) VALUES (null,"Harry Potter and the Philosophers Stone","J.K. Rowling","20/07/2014")
INSERT INTO book (id, name, author, purchaseDate) VALUES (null,"Harry Potter and the Chamber of Secrets ","J.K. Rowling","22/08/2014")
INSERT INTO book (id, name, author, purchaseDate) VALUES (null,"Harry Potter and the Prisoner of Azkaban","J.K. Rowling","12/09/2014")
INSERT INTO book (id, name, author, purchaseDate) VALUES (null,"Harry Potter and the Goblet of Fire","J.K. Rowling","22/10/2014")
INSERT INTO book (id, name, author, purchaseDate) VALUES (null,"Harry Potter and the Order of the Phoenix","J.K. Rowling","23/10/2014")
INSERT INTO book (id, name, author, purchaseDate) VALUES (null,"Harry Potter and the Half-Blood Prince","J.K. Rowling","25/10/2014")
INSERT INTO book (id, name, author, purchaseDate) VALUES (null,"Harry Potter and the Deathly Hallows,","J.K. Rowling","30/10/2014")

 

[JonRubin]

Are you asking for the SQL syntax?

 

[asales]

Are you asking for the SQL syntax?

No.
I have the SQL file (text file "mybook.sql" with code above and syntax ok).
I need read the SQL file "mybook.sql" and modify the existing database "mysqlite.db" with the content of "mybook.sql" (create table and insert records).

 

[klaus]

You cannot read a database from the Assets directory, you must copy it somewhere else.
Either Files.DirInternal if you don't need to access the file from outsides.
Files.DirRootExternal with or without a subdirectory if you need to access the file from outsides.
There are examples in the forum and in the User's Guide.

 

[LucaMs]

... or DirDefaultExternal

 

[asales]

The database is not in Assets directory. The database "mysqlite.db" is in File.DirInternal.
In Assets directory is the text file "mybook.sql" with the content that I put in first post.
I want to read the "mybook.sql" and update the "mysqlite.db" (creating the new table "book" and insert records with "books of Harry Potter").
I guess I am not being clear in this issue. Sorry.

 

[LucaMs]

Uhm... I could be wrong (but it would be the first time ever, as DonManfred may confirm, LOL) ...

You could put a sign at the beginning and one at the end of the query (like here [ Code ] [ /Code ], read the text file, split it and run each query.

I was wrong : you do not need a beginning, simply a sign for the end.

 

[DonManfred]

I guess I am not being clear in this issue. Sorry.

you need to strip out the lines

-- Table: book
CREATE TABLE book (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(50),
author VARCHAR(50),
purchaseDate DATE
);

from your sql-file and then you can read the file and for every line in it you write the data for a row. Asuming that the SQL in this file is compatible with SQLite

 

[asales]

I tried to make this:

Dim txtSql As String
txtSql = File.ReadString(File.DirAssets, "mybook.sql")
SQL1.ExecNonQuery(txtSql)

the file "mybook.sql" (with the sql code to create table and insert records) is read and the new table "book" is created, but the records are not inserted.

 

[LucaMs]

Try splitting txtSql.

First query: Create...
then one query for each INSERT.

You could change your file:

CREATE TABLE book (
    id  INTEGER  PRIMARY KEY AUTOINCREMENT,
    name  VARCHAR(50),
    author VARCHAR(50),
    purchaseDate DATE
);

INSERT INTO book (id, name, author, purchaseDate) VALUES (null,"Harry Potter and the Philosophers Stone","J.K. Rowling","20/07/2014");
INSERT INTO book (id, name, author, purchaseDate) VALUES (null,"Harry Potter and the Chamber of Secrets ","J.K. Rowling","22/08/2014");
INSERT INTO book (id, name, author, purchaseDate) VALUES (null,"Harry Potter and the Prisoner of Azkaban","J.K. Rowling","12/09/2014");
INSERT INTO book (id, name, author, purchaseDate) VALUES (null,"Harry Potter and the Goblet of Fire","J.K. Rowling","22/10/2014");
INSERT INTO book (id, name, author, purchaseDate) VALUES (null,"Harry Potter and the Order of the Phoenix","J.K. Rowling","23/10/2014");
INSERT INTO book (id, name, author, purchaseDate) VALUES (null,"Harry Potter and the Half-Blood Prince","J.K. Rowling","25/10/2014");
INSERT INTO book (id, name, author, purchaseDate) VALUES (null,"Harry Potter and the Deathly Hallows,","J.K. Rowling","30/10/2014")

I've added ";" to the end of each INSERT (and deleted "-- Table: book")

So you could use:
Dim Queries() As String = Regex.Split(";". txtSql)
For I = 0 to Querys.Length -1
SQL1.ExecNonQuery(Queries(I))
Next

(or you could use something like this - see "Batch Inserts")

 

[asales]

Thanks for the tips.
Problem solved.
I tried this (put the CREATE TABLE in one line and remove the line "-- Table: book"):

Dim List1 As List
List1 = File.ReadList(File.DirAssets, "mybooks.sql")
For i = 0 To List1.Size - 1
   SQL1.ExecNonQuery(List1.Get(i))
Next

Now, all the SQL code is read and the database is updated with the new table and the records.