B4J Question SQLite Create table from code

[ThRuST]

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.

 

[Mashiane]

Unless I'm reading this wrongly, the DBUtils has scripts to create tables in SQLite..

https://www.b4x.com/android/forum/threads/dbutils-example.34611/#content

 

[ThRuST]

Really? How would you use that ;-)

 

[ThRuST]

In case you add a code block make sure it supports this

#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

 

[Mashiane]

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..

 

[Mashiane]

You might also want to watch the second video in this post, im using dbutils to do the updates, deletes, selections etc.

https://www.b4x.com/android/forum/threads/tableview-magical-inline-editing.87577/#content

I think the code is included too.

 

[ThRuST]

@Mashiane Thanks I will check it out

 

[ThRuST]

Those books sure is a great recource. This will come in most useful. Spendid @Mashiane Splendid