How to use pre-populated sqlite database using kotlin?

1

I made database using “DB browser for sqlite”.
With java I know what to do and how to use it in an android project.
but with kotlin, NO.
Any tutorial?

2

You would do it the exact same way. Except all the java code would be kotlin code (you can use the kotlin converter for that).

3

Well I already tried that.
There’s a few problems.
Sometimes code converter isn’t helpful.
this is DBOpenHelper.kt

class DBOpenHelper(context: Context) : SQLiteOpenHelper(context,"dbname",null,1) {

override fun onCreate(db: SQLiteDatabase?) {}

override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {}

companion object {

    //general
    val dbVersion = 1
    val dbName = "dbname"
    val dbPath = "/data/data/com.myname.testapp3/databases/"

    //table maincat
    val maincat_tbl = "maincat"
    val main_id = "id"
    val main_nameFa = "name_fa"
    val main_nameEn = "name_en"
    val main_filter = "filter"
}

val mContext = context

private fun checkDB():Boolean{
    var db:SQLiteDatabase? = null
    try {
        db = SQLiteDatabase.openDatabase(dbPath+ dbName,null,SQLiteDatabase.OPEN_READONLY)
    }catch (e:SQLException){}
    return db != null
}

@Throws(IOException::class)
private fun copyDB(){
    val mo = FileOutputStream(dbPath + dbName)
    val bfr = ByteArray(1024)
    val mi = mContext.assets.open("$dbName.db")
    while (mi.read(bfr) > 0) {
        mo.write(bfr, 0, mi.read(bfr))
    }
    mi.close()
    mo.flush()
    mo.close()
}

@Throws(IOException::class)
fun importDB() {
    val ex = checkDB()
    if (!ex) {
        this.readableDatabase
        try {
            copyDB()
        } catch (e: IOException) { }
    }
}

var database:SQLiteDatabase? = null

fun open() {
    database = SQLiteDatabase.openDatabase(dbPath + dbName, null, SQLiteDatabase.OPEN_READWRITE)
}

override fun close() {
    database?.close()
}

and this is MainActivity.kt

        val helper = DBOpenHelper(this)
    try {
        helper.importDB()
    } catch (ioe: IOException) {
        throw Error("Unable to create database")
    }

When I run app It crashes and I get this Error: Could not open database

4

This is not really a Kotlin issue. It is more an Android issue. You should not hardcode application paths this way as they are not reliable. You can query the location of the database but you should let the openhelper worry about it. Let it open the database and provide you with the database object (and don’t drop exceptions as they may tell you what is wrong). You should probably hook up your debugger to figure out where things break (which line). You are mixing openhelper and direct access in curious ways that break (your initialisation really needs to live in onCreate - if you don’t want to implement the dbopenhelper contract don’t override it).

Your copy code is broken as it fails to record the amount of bytes read into the buffer, instead it reads the next block into the buffer (you are effectively omitting half of the file in 1024 byte chunks).

5

But with java it works fine. Not once but always.

db = SQLiteDatabase.openDatabase(dbPath+dbName,null, SQLiteDatabase.OPEN_READONLY)

this line makes the problem while it is OK in java.
I know I can do this part of project with java and the rest with kotlin. But the point is I wanna know how to do it with kotlin.
db path and db name are fine.
I don’t know what to do :expressionless:

6

OK. I found the ANSWER :heart_eyes:
This is my DBOpenHelper: (Works Fine. “TESTED”)

class DBOpenHelper(val context: Context){

companion object {

    //general
    val dbVersion = 1
    val dbName = "dbname"

    //table and columns
    val maincat_tbl = "maincat"
    val _id = "id"
    val _filter = "filter"
}

val database:SQLiteDatabase

init {
    database = open()
}

private fun open(): SQLiteDatabase {
    val dbFile = context.getDatabasePath("$dbName.db")
    if (!dbFile.exists()){
        try {
            val checkDB = context.openOrCreateDatabase("$dbName.db", Context.MODE_PRIVATE,null)
            checkDB.close()
            copyDatabase(dbFile)
        }catch (e:IOException){
            throw RuntimeException("Error opening db")
        }
    }
    return SQLiteDatabase.openDatabase(dbFile.path, null, SQLiteDatabase.OPEN_READWRITE)
}

private fun copyDatabase(dbFile: File) {
    val iss = context.assets.open("$dbName.db")
    val os = FileOutputStream(dbFile)

    val buffer = ByteArray(1024)
    while (iss.read(buffer) > 0) {
        os.write(buffer)
    }
    os.flush()
    os.close()
    iss.close()
}

fun close() {
    database.close()
}
}
7

It is still incorrect as it will append junk to the end of your database file. You need to record the actual amount of bytes read.

8

What should I do exactly? my database is 11 KB.

9 
val buffer = ByteArray(1024)
generateSequence{ val i = iss.read(buffer); if (i<0) null else i }
    .forEach {
        os.write(buffer, 0, i)
    }

Is a solution. Just don’t ignore the result of read. It is the amount of bytes read (or <0 when eof)

1 Like
10

There is also this stdlib extension:

iss.copyTo(os)
2 Likes
11

Hello Deadpool and sorry for my very bad English.

I use the same database copy mechanism to initialize it, but I have a FATAL EXECPTION on the line:
val iss = context.assets.open(dbName).

I can never initialize my Inputstream.

Did you run into the same problem?

Thanks for your help.
Nyer01