Using SQLite in Codename One Application

Question

I have a working sqlite db which I have place in my /src folder. I then went onto the Codename One website and followed their doc example

    Database db = null;
    Cursor cur = null;
    try {
        db = Display.getInstance().openOrCreate("MyDb.db");
        if(query.getText().startsWith("select")) {
            cur = db.executeQuery(query.getText());
            int columns = cur.getColumnCount();
            frmMain.removeAll();
            if(columns > 0) {
                boolean next = cur.next();
                if(next) {
                    ArrayList<String[]> data = new ArrayList<>();
                    String[] columnNames = new String[columns];
                    for(int iter = 0 ; iter < columns ; iter++) {
                        columnNames[iter] = cur.getColumnName(iter);
                    }
                    while(next) {
                        Row currentRow = cur.getRow();
                        String[] currentRowArray = new String[columns];
                        for(int iter = 0 ; iter < columns ; iter++) {
                            currentRowArray[iter] = currentRow.getString(iter);
                        }
                        data.add(currentRowArray);
                        next = cur.next();
                    }
                    Object[][] arr = new Object[data.size()][];
                    data.toArray(arr);
                    frmMain.add(BorderLayout.CENTER, new Table(new DefaultTableModel(columnNames, arr)));
                } else {
                    frmMain.add(BorderLayout.CENTER, "Query returned no results");
                }
            } else {
                frmMain.add(BorderLayout.CENTER, "Query returned no results");
            }
        } else {
            db.execute(query.getText());
            frmMain.add(BorderLayout.CENTER, "Query completed successfully");
        }
        frmMain.revalidate();
    } catch(IOException err) {

        frmMain.removeAll();
        frmMain.add(BorderLayout.CENTER, "Error: " + err);
        frmMain.revalidate();
    } finally {
        Util.cleanup(db);
        Util.cleanup(cur);
    }

However when I run the example and try and execute a simple select query I get this error ...

java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such table: MyTable)

• So I have added the DB
• I have used the 'openOrCreate' statement

Have I missed a step?

Thanks

Answer 1

Thanks for all the input guys.

Unfortunately none of the advice worked for me. However I did solve it in the end.

It turns out that there is a folder in my home directory called '.cn1/database'. Once I placed the DB into this folder it worked.

Two things: 1] If the db does not exist then it will create it and place it into this directory 2] The db does not show up anywhere in Netbeans (well not that I could see anyway)

Thanks again

Answer 2

Are you shure that you current working directory at execution is ./src ?

Try

db = Display.getInstance().openOrCreate("./src/MyDb.db");

or open with absolute filename:

db = Display.getInstance().openOrCreate("/path/to/src/MyDb.db");

Answer 3

You can try this cn1lib https://github.com/shannah/cn1-data-access-lib

I have used it and it works a charm, except doesn't work for 2 tables in the same query and can't perform delete operations.

Cheers

Answer 4

From the developer guide:

Some SQLite apps ship with a "ready made" database. We allow you to replace the DB file by using the code:

String path = Display.getInstance().getDatabasePath(“databaseName”);

You can then use the FileSystemStorage class to write the content of your DB file into the path. Notice that it must be a valid SQLite file!

Important: getDatabasePath() is not supported in the Javascript port. It will always return null.

This is very useful for applications that need to synchronize with a central server or applications that ship with a large database as part of their core product.

You are relying on paths that make sense in the simulator, in the device you need to copy a resource into location. Check out the SQL demo where this is implemented: https://www.codenameone.com/blog/sql-demo-revisited.html

Answer 5

Use following code to copy database to Codenameone storage after put the database file in src folder, the database will copy to directory ".cn1/database" after running

String DB_NAME = "DBNAME.db";
Database temp_db = Database.openOrCreate(DB_NAME); //To create an empty file before copy, otherwise Android will throw file not found exception
temp_db.close();
String p = Database.getDatabasePath(DB_NAME);
OutputStream o = FileSystemStorage.getInstance().openOutputStream(p);
InputStream i = Display.getInstance().getResourceAsStream(getClass(), "/" + DB_NAME);
Util.copy(i, o);

Answer 6

This doesn't seem like a complete answer.

I'm going through the demo, and something seems to be missing:
Shouldn't a build task copy this db resource to the correct target folder? Otherwise how can a deployment ever work? (if the db doesnt get packaged up then how will it get deployed?) without it the app cant run