B4J Question Create SQLite Database By Parsing SQL File

cklester

Well-Known Member
Licensed User
I've searched but have been unable to find a way to parse an *.sql file into an SQLite database. It doesn't look like DBUtils has that functionality. Did I miss anything? Am I going to have to load and parse this myself?

Sample SQL file to create tables
Sample SQL file to add records

(Technically, those could be put together into one file.)

I did find this comment that offered this code:

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

It would be nice to have something like

B4X: 
SQL1.ParseSQL(File.DirAssets,"mydb.sql",File.DirAssets,"mydb.db")
'or
SQL1.CreateFromSQL(File.DirAssets,"mydb.sql",File.DirAssets,"mydb.db")

which I will try tomorrow. It's late here and past my bedtime!
 
Sort by date Sort by votes

mangojack

Expert
Licensed User
Longtime User
I might be wrong, but I think you will find there is no need to parse this file ... it just has been given an .sql extension, instead of .db

As a test , I renamed one of my .db databases to .sql and was still able to use it . And my DB Utility (see below) was able to open it as normal

Change the extension to .db and take it for a spin.


Ps: This is a handy tool for Creating / Modifying /Inspectiing .db and .sql files ... It is simple and there are more powerful utilities out there
but it meets my needs.

DB Browser for SQLite

sqlitebrowser.org
 
Upvote 0

cklester

Well-Known Member
Licensed User

Take a look at the sample text files I posted. They are definitely not database files. They are schema files, and they are plain text and need to be parsed or loaded or otherwise used to create a database file.

I installed that SQLite DB Browser earlier today. It is definitely a useful app! However, I couldn't figure out how to define a composite foreign key. Do you have any idea how to do that?
 
Upvote 0

mangojack

Expert
Licensed User
Longtime User
cklester said:
I've searched but have been unable to find a way to parse an *.sql file into an SQLite database.
'.....................................

It would be nice to have something like
SQL1.ParseSQL(File.DirAssets,"mydb.sql",File.DirAssets,"mydb.db")
Click to expand...


There is no need to parse the file . Either just work with the .sql file or rename the extension to .db if that makes you happier.
(as a side note ... you would not be parsing / saving to read only Assets folder)


Those sample files are just that, .txt files. I would just copy and paste the SQL statements into your Test app. ie:

B4X: 
Private oSQL as SQL
If Not(File.Exists(File.DirData("myApp"),"mydb.db")) Then    '***  Or .sql
    File.Copy(File.DirAssets, "mydb.db", File.DirData("myApp"), "mydb.db")
End If
oSQL.InitializeSQLite(File.DirData("myApp"),"mydb.db",False)
'oSQL.ExecNonQuery("CREATE TABLE MyTable (ID INTEGER PRIMARY KEY, FirstName TEXT, LastName TEXT, Phone TEXT)")

  'Or   From the Posted Text Files ...

oSQL.ExecNonQuery("CREATE TABLE countries (country_id text NOT NULL,country_name text NOT NULL, ......................... ").


I am not that experienced in SQL, and to date have had no need for Composite Foreign key ... so cannot help there.

As I said , there are other more feature packed db utilities out there that might help with that regard.


Edit: just realized B4j question ... adjusted above code.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Solved
B4J Question [B4XPages] How to create a sqlite for all platform
Replies
5
Views
1K
yfleury
  • Article
B4J Code Snippet Parse SQL Schema File Into SQLite
Replies
6
Views
2K
giannimaione
P
  • Question
B4J Question DBUtils create sqlite file with a 2 field primary Key [SOLVED]
Replies
11
Views
2K
drgottjr
  • Article
B4J Library SQLite 2 JavaScript / JSON
Replies
1
Views
2K
tchart
  • Question
B4J Question SQLite Create table from code
Replies
7
Views
3K
ThRuST
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…