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

asales

Well-Known Member
Licensed User
Longtime User
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:
B4X:
-- 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")
 

asales

Well-Known Member
Licensed User
Longtime User
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).
 
Upvote 0

klaus

Expert
Licensed User
Longtime User
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.
 
Upvote 0

asales

Well-Known Member
Licensed User
Longtime User
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.
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
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 :D: you do not need a beginning, simply a sign for the end.
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
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
 
Upvote 0

asales

Well-Known Member
Licensed User
Longtime User
I tried to make this:
B4X:
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.
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
Try splitting txtSql.

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

You could change your file:
B4X:
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")
 
Upvote 0

asales

Well-Known Member
Licensed User
Longtime User
Thanks for the tips.
Problem solved.
I tried this (put the CREATE TABLE in one line and remove the line "-- Table: book"):
B4X:
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.
 
Upvote 0
Top