B4J Question SQLite Create table from code

ThRuST

Well-Known Member
Licensed User
Longtime User
I'm looking for a useful code script we all can use to create a SQLite database with a table in B4J.

I hope you experts out there can share a code block we all can use for this.

One single Table, with a few records in it that functions as a template.

Code that create a database, a table, a few records, preferably with autoincrement.
 
Last edited:

ThRuST

Well-Known Member
Licensed User
Longtime User
In case you add a code block make sure it supports this

B4X:
#AdditionalJar: sqlite-jdbc-3.7.2

' Declares variable for SQLite connection
Private SQLiteCon As SQL

' Connect to SQLite database
SQLiteCon.InitializeSQLite( PathToDatabase , "myDatabase.sqlite", False)

' Inserts data into SQLite database
SQLiteCon.ExecNonQuery2("INSERT INTO myTable VALUES(?, ?)", Array (Null, myString)) ' id, data

' Inserts multiple items into SQLite database
SQLiteCon.BeginTransaction
SQLiteCon.ExecNonQuery2("INSERT INTO myTable VALUES(?, ?)", Array (Null, myString1)) ' id, data
SQLiteCon.ExecNonQuery2("INSERT INTO myTable VALUES(?, ?)", Array (Null, myString2)) ' id, data
SQLiteCon.TransactionSuccessful

' Update data in SQLite database
SQLiteCon.ExecNonQuery2("UPDATE myTable SET myRecord=? WHERE id=?",Array (myString, 1))

' Update multiple items in SQLite database
SQLiteCon.BeginTransaction
SQLiteCon.ExecNonQuery2("UPDATE myTable SET myRecord=? WHERE id=?",Array (myString1, 1))
SQLiteCon.ExecNonQuery2("UPDATE myTable SET myRecord=? WHERE id=?",Array (myString2, 2))
SQLiteCon.TransactionSuccessful

' Delete data from SQLite database
SQLiteCon.ExecNonQuery("DELETE FROM myTable WHERE id = 1")

' Load from SQLite database
Dim myVariable As String
Dim RS1 As ResultSet = SQLiteCon.ExecQuery("SELECT myRecord FROM myTable WHERE id=1")
myVariable = RS1.GetString("myRecord")
RS1.Close
 
Upvote 0

Mashiane

Expert
Licensed User
Longtime User
In the code module, there is a method to create a table, you pass it a map variable with the field name as key and field type as value. You need to copy dbutils on the shared folder and reference it in your code.

Although I have not read it, Klaus has written a book about using SQLite db, perhaps check it out, it might have all the answers you need. Here is the link to the books, they have been very helpful to my learning too.

https://www.b4x.com/android/files/Booklets.zip

I personally are using my own modification of the DBUtils class with autoincrement field features, you might find something useful. All the best..
 

Attachments

  • DBUtils.bas
    78.4 KB · Views: 261
Upvote 0
Top