B4J Question Create SQLite Database By Parsing SQL File

[cklester]

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:

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

It would be nice to have something like

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!

 

[mangojack]

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

 

[cklester]

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 .

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

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?

 

[mangojack]

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")

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:

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.