Request for code example for using a preloaded SQLite database file

[btofel]

Hi,

I tried asking this as a question about CloudObjects, but I really want to access a database by using SQL with a preloaded sqlite file. I am seeing some weird stuff, so wondering if someone could post an example of code and instruction on where to put the file. I'd like to make this work on at least Android and iOS. 

Here's what I know doesn't work so far:

I put the properly created, loaded and tested sqlite file, "MyDB", in the src directory.

I use the following code: 

myDataBase = com.codename1.db.Database.openOrCreate("MyDB")

Log.p("DB Path:" +myDataBase.getDatabasePath("MyDB"));

In the simulator I get:

[EDT] 0:0:0,0 - DB Path:/home/btofel/.cn1/database/MyDB

And openOrCreate chooses to create a fresh database on that path so later my SQL select fails with an unknown table error. And where the heck is "~/.cn1" and "database" coming from? This is undocumented behavior.

Running an Android build I get:

04-25 11:13:27.815: I/System.out(19255): [EDT] 0:0:0,0 - DB Path:/data/data/net.company.appname/databases/MyDB

again "/data/data/com.company.appname/databases" just seems to made up from points unknown and undocumented. I get the same eventual problem, openOrCreate creates a fresh database, does not use my file and dies when it can't find the table my select statement is looking for. Interestingly the generated APK has an "assets" directory and "MyDB" file is in there.

No idea how this would play out on an iOS build, but I could really use instructions and code sample of how this should work.

[Shai Almog]

You need to use getDatabasePath() before openOrCreate since the open or create method will create a database and you want to install yours.

.cn1 is the directory to which we store everything in the simulator, its the storage home directory. It would be a different directory on the device.
The database directory is something we created for the simulator since every device placed the database in a different location there is no point in documenting that either.

You need to do something like:

String p = Display.getInstance().getDatabasePath("MyDB");
if(!p.startsWith("file:")) {
    p = "file:/" + p;
}
if(!FileSystemStorage.getInstance().exists(p)) {
    OutputStream o = FileSystemStorage.getInstance().openOutputStream(p);
    InputStream i = Display.getInstance().getResourceAsStream("/dbName");
    Util.copy(o, i);
    Util.cleanup(o);
    Util.cleanup(i);
}
now open DB as usual

[bto...@firstcenturyenergy.com]

OK, got basically that code to work on the simulator, yey progress!

Here is what I have working, had to comment out the "file:/+" stuff to get it to work:

 String path = Display.getInstance().getDatabasePath(DB_NAME);
 Log.p("path:" +path);
// if(!path.startsWith("file:")) {
//    path = "file:/" + path;
// }
// path = FileSystemStorage.getInstance().getRoots()[0] + path;
 
 if(!FileSystemStorage.getInstance().exists(path)) {
    OutputStream o = FileSystemStorage.getInstance().openOutputStream(path);
    InputStream i = Display.getInstance().getResourceAsStream(getClass(), "/"+DB_NAME);
    Util.copy(i, o);
    Util.cleanup(o);
    Util.cleanup(i);
 }
 myDataBase = com.codename1.db.Database.openOrCreate(DB_NAME);
 Log.p("DB Path:" +myDataBase.getDatabasePath(DB_NAME));

However Android build blows up on the line:

 if(!FileSystemStorage.getInstance().exists(path)) {

Because,

String path = Display.getInstance().getDatabasePath(DB_NAME);

returned "path" as null. See output below

04-25 13:24:19.457: I/System.out(2887): [EDT] 0:0:0,0 - path:null
04-25 13:24:19.457: W/System.err(2887): java.lang.NullPointerException
04-25 13:24:19.457: W/System.err(2887): at java.io.File.fixSlashes(File.java:185)
04-25 13:24:19.457: W/System.err(2887): at java.io.File.<init>(File.java:134)
04-25 13:24:19.457: W/System.err(2887): at com.codename1.impl.android.AndroidImplementation.exists(AndroidImplementation.java:3373)
04-25 13:24:19.457: W/System.err(2887): at com.codename1.io.FileSystemStorage.exists(FileSystemStorage.java:180)
04-25 13:24:19.457: W/System.err(2887): at userclasses.DataBaseHelper.openDataBase(DataBaseHelper.java:157)

It works in simulator but not the Android build which makes me worry a little for the iOS build since I won't be able to test that as easily.

[Chen Fishbein]

Try this instead:

        Database myDataBase = com.codename1.db.Database.openOrCreate(DB_NAME);

        

        if(Storage.getInstance().readObject("initalized") == null){

            myDataBase.close();

            

            String path = com.codename1.db.Database.getDatabasePath(DB_NAME);            

            OutputStream o = FileSystemStorage.getInstance().openOutputStream(path);

            InputStream i = Display.getInstance().getResourceAsStream(getClass(), "/" + DB_NAME);

            Util.copy(i, o);

            Util.cleanup(o);

            Util.cleanup(i);

            

            myDataBase = com.codename1.db.Database.openOrCreate(DB_NAME);

            Storage.getInstance().writeObject("initalized", "true");

        }        

[bto...@firstcenturyenergy.com]

Thanks! Tested on both the simulator and Android build. Would love confirmation this works on iOS too, but I guess I'll be able to provide that eventually if no one else can confirm before me.

IMHO this code snippet should be inserted in the docs under the SQL section, maybe in a subsection on "Working With Pre-Loaded Sqlite Files".

I think lots of people will want to do this, especially people porting working code from iOS or Android (like me) since sqlite is present on 98% of the current market for smartphones and devices (Android and iOS both have it). Sqlite seems like a fairly prevalent way of handling small data sets.

 I think the comment in the docs about SQL being overly complex for embedded device programming tasks should really be stripped. Codename One has support for sqlite - celebrate it! Many people will want to use this for data storage, especially this type demonstrated here: a pre-loaded sqlite database shipped with the app, maintained off-line with off-the-shelf database utility apps.

[pli...@gmail.com]

Well i tried your code and i get this error:

C:\Documents and Settings\Owner\.cn1/database/mydatabase.sqlite

java.lang.NullPointerException

at com.codename1.io.Util.copy(Util.java:114)

at com.codename1.io.Util.copy(Util.java:100)

at userclasses.StateMachine.initListModelMultiList(StateMachine.java:90)

at generated.StateMachineBase.setListModel(StateMachineBase.java:588)

at com.codename1.ui.util.UIBuilder.createComponent(UIBuilder.java:1405)

at this code:

 @Override

    protected boolean initListModelMultiList(List cmp) throws IOException {

    Vector vec = new Vector();    

    String p = Display.getInstance().getDatabasePath(DB_NAME);

    System.out.println(p);

    Cursor c;

    try {

    c = getAll(myDataBase);

   

    while (c.next()) {

    Hashtable h = new Hashtable();

    System.out.println(String.valueOf(c.getRow().getString(c.getColumnIndex("PtName"))));

    h.put("Line1", String.valueOf(c.getRow().getString(c.getColumnIndex("PtName"))));

   

    vec.addElement(h);

    }

    } catch (IOException e) {

    // TODO Auto-generated catch block

    e.printStackTrace();

    }

    

    cmp.setModel(new DefaultListModel(vec)) ;        

   

    return true;

    }

the error is on   Util.copy(i, o);

i have to mention that i declared the name of database at that point of code:

public class StateMachine extends StateMachineBase {

private Database db;

private String DB_NAME="mydatabase.sqlite";

I also have to tell you that i put a copy of the database in cn1/database folder and in projects folder also. What am i doing wrong?

Thanks for help

[Shai Almog]

The file DB_NAME is missing from the root of your src dir. The input stream is returned as null.

[klindb...@gmail.com]

Hi,

I'm trying to accomplish the same thing - install prebuilt/pretested sqlite file. I get the sam error when running the project:

java.lang.NullPointerException

at com.codename1.io.Util.copy(Util.java:113)

at com.codename1.io.Util.copy(Util.java:99)

at userclasses.StateMachine.getDb(StateMachine.java:108)

at userclasses.StateMachine.initListModelSqlData(StateMachine.java:73)

at generated.StateMachineBase.setListModel(StateMachineBase.java:296)

I've put the file "My_DB.sqlite" in: 

user/.cn1/

user/.cn1/database/

Project root directory

Project 'src' directory

I get the same error no matter where I put the sqlite file.

My code:

public class StateMachine extends StateMachineBase {

private Database myDataBase;

private String DB_NAME = "My_DB.sqlite";

 . . . . . . .

   @Override

    protected boolean initListModelSqlData(List cmp) {

    Database d = getDb();

    if(d != null) {

    try {

    Cursor c = d.executeQuery("select title, description from _penalcode");

    Vector entries = new Vector();

    while (c.next()) {

    Row r = c.getRow();

    String title = r.getString(0);

    String desc = r.getString(1);

    Hashtable value = new Hashtable();

    value.put("Line1", title);

    value.put("Line2", desc);

    entries.addElement(value);

    cmp.setModel(new DefaultListModel(entries));

    }

    

    

    } catch (IOException ex) {

    Log.e(ex);

    }

    }

    return true;

    }

    

    

    private Database getDb() {

    try {

    Database myDataBase = com.codename1.db.Database.openOrCreate(DB_NAME);

       

        if(Storage.getInstance().readObject("initalized") == null){

            myDataBase.close();

            

            String path = com.codename1.db.Database.getDatabasePath(DB_NAME);            

            OutputStream o = FileSystemStorage.getInstance().openOutputStream(path);

            InputStream i = Display.getInstance().getResourceAsStream(getClass(), "/" + DB_NAME);

            Util.copy(i, o);

            Util.cleanup(o);

            Util.cleanup(i);

            

            myDataBase = com.codename1.db.Database.openOrCreate(DB_NAME);

            Storage.getInstance().writeObject("initalized", "true");

        }    

   } catch (IOException ex) {

   Log.e(ex);

        }

        return myDataBase;

      }

[Shai Almog]

Put the sqlite file in your src dir.

[klindb...@gmail.com]

Hi,

I've tried Running it with the sqlite file in my Project/src/ directory, I've tried it with the sqlite file in my Project/ directory, neither works. I've also put it in the user/.cn1/ directory and the user/.cn1/database/ directory. None of them work.

By the way, I also tried following the sql tutorial on the udemy.com training site. I got the same error - 'java.lang.NullPointerException'. I went through the tutorial twice, double checking everything by what was entered on the training video and what was on the downloaded pdf - copying and pasting the code. It would not run without getting that error. I tried sending it to the codenameone as an Android build and got the same error on my test Android phone.

Is there something wrong in my settings? I'm on Mac OS X 10.8.5, Java version 7 update 45, and the latest installation of eclipse and Netbeans. (I've tried building the sql tutorial in both). Same error.

Would it work better to add it as a Data file in the Designer?

Thank you,

[klindb...@gmail.com]

Is it possible that there is something in my sqlite file - inaccessible version # or something? It's a small test file built with SQLite Database Browser and RazorSQL on the Mac. I've just reverified that both can open the file, and they give no errors upon opening the sqlite file.

Would it be better to implement this sqlite file using the Data->Add File area in the Codename One Designer?

Thank you,

Ken

[Shai Almog]

The correct place is in the src directory under your project (not a subdirectory of that or any other place).
Make sure the case for the filename is correct and that there are no spaces within the name etc. also make sure that the error didn't change to some other error once you do that correctly. If you have a different error then post that and we can help you thru that issue.

[klindb...@gmail.com]

Hi,

Yes, just verified that all spelling is the same, same type case 'My_DB.sqlite'. Reverified that the sqlite file is valid and that it is in the Project/src/ folder, not in a subfolder within that.

I'm suspecting there is something else wrong because when I follow the SQL tutorial on the udemy.com training session, I get the same error after going through the tutorial twice and following the video step by step.

The error message I get after confirming spelling/file location is:

java.lang.NullPointerException

at com.codename1.io.Util.copy(Util.java:113)

at com.codename1.io.Util.copy(Util.java:99)

at userclasses.StateMachine.getDb(StateMachine.java:108)

at userclasses.StateMachine.initListModelSqlData(StateMachine.java:73)

at generated.StateMachineBase.setListModel(StateMachineBase.java:296)

at com.codename1.ui.util.UIBuilder.createComponent(UIBuilder.java:1422)

at com.codename1.ui.util.UIBuilder.createComponent(UIBuilder.java:1363)

at com.codename1.ui.util.UIBuilder.createContainer(UIBuilder.java:409)

at com.codename1.ui.util.UIBuilder.createContainer(UIBuilder.java:394)

at com.codename1.ui.util.UIBuilder.showForm(UIBuilder.java:2439)

at generated.StateMachineBase.startApp(StateMachineBase.java:58)

at generated.StateMachineBase.<init>(StateMachineBase.java:31)

at generated.StateMachineBase.<init>(StateMachineBase.java:102)

at userclasses.StateMachine.<init>(StateMachine.java:39)

at com.agpcom.storedata.StoreData.start(StoreData.java:20)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at com.codename1.impl.javase.Executor$1.run(Executor.java:95)

at com.codename1.ui.Display.processSerialCalls(Display.java:1051)

at com.codename1.ui.Display.mainEDTLoop(Display.java:871)

at com.codename1.ui.RunnableWrapper.run(RunnableWrapper.java:119)

at com.codename1.impl.CodenameOneThread.run(CodenameOneThread.java:176)

java.lang.reflect.InvocationTargetException

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at com.codename1.impl.javase.Executor$1.run(Executor.java:95)

at com.codename1.ui.Display.processSerialCalls(Display.java:1051)

at com.codename1.ui.Display.mainEDTLoop(Display.java:871)

at com.codename1.ui.RunnableWrapper.run(RunnableWrapper.java:119)

at com.codename1.impl.CodenameOneThread.run(CodenameOneThread.java:176)

Caused by: java.lang.NullPointerException

at com.codename1.ui.util.UIBuilder.showForm(UIBuilder.java:2342)

at com.codename1.ui.util.UIBuilder.showForm(UIBuilder.java:2440)

at generated.StateMachineBase.startApp(StateMachineBase.java:58)

at generated.StateMachineBase.<init>(StateMachineBase.java:31)

at generated.StateMachineBase.<init>(StateMachineBase.java:102)

at userclasses.StateMachine.<init>(StateMachine.java:39)

at com.agpcom.storedata.StoreData.start(StoreData.java:20)

... 9 more

[Shai Almog]

Hi,
the file is received as null. This is the code from Util.java the last line is 113 (where you get a null pointer exception):

    public static void copy(InputStream i, OutputStream o, int bufferSize) throws IOException {
        try {
            byte[] buffer = new byte[bufferSize];
            int size = i.read(buffer);

As you can see, the only option is that i is null which means that the input stream that you got from

InputStream i = Display.getInstance().getResourceAsStream(getClass(), "/" + DB_NAME);

If you execute this with the debugger you will see that i is null. I'm not exactly sure, why this is happening to you from your current explanation. If you are a pro user you can send the project to the support email and I can have a look. But the issue you are having is here.

[klindb...@gmail.com]

Hi Shai,

What is the support email? I tried to attach the project .zip file to this post, but the blog website said the attachment is too large. I don't see any links on the codenameone.com website with a support email address.

Thank you for your help!

Ken

[Shai Almog]

We took this offline since Ken is a pro subscriber (support email is in the welcome email you receive when signing up for pro).

The end result was that the DB initialization code was invoked when showing the first form in a GUI builder application. This form is invoked by the constructor of the base class which means the constants of the Statemachine aren't yet initialized.
That means the constant for the database name was still null and so a null value was returned from get resources (no null name etc.).

The solution is simple either initialize the constant in the initVars() method or make it public static final String.

[infobest...@gmail.com]

Hi Shai,

I'm sorry but I have to reactivate this thread. I have applied the algorithm described above to work with a predefined SQLITE db in a file. The file is correct I can open and run queries on it with a separate SQLITE browser. I have placed the file in the src directory as instructed and run the code. There are no errors but the DB is empty, one cannot run any queries against it, cursor next() either returns false or I get errors "table does not exist" for table that should have been there. I checked the .cn1/database directory and I have a name.sqlite file (which should be my db, it is the name used for openOrCreate() method, the name of the place placed in src directory) but it is empty, 0 Kb. Next to it there is a sqlite_master.sql file which contains insert queries for populating the sqlite_master table of SQLITE (contains all the structure of my db). It looks like the creation process has been frozen somewhere in the middle. Any ideas? What could be the problem? Thanks,

Cristian

[Shai Almog]

Hi,
if the file is 0kb you didn't create it correctly.

[Cristian Ionitoiu]

Hi Shai,

What software do you recommend for creation of these files? I have used mozilla extension for sqlite to create/export/import file, which also loaded up without any problem by the binary taken from
https://www.sqlite.org/download.html.Thanks,

Cristian

[Steve Hannah]

An alternative you might want to consider.  I developed a data access library that allows you to include an SQL file that will automatically be loaded at first run.

https://github.com/shannah/cn1-data-access-lib

It supports versioning so that you can include incremental SQL updates corresponding to your app updates.

Steve

--
You received this message because you are subscribed to the Google Groups "CodenameOne Discussions" group.
To unsubscribe from this group and stop receiving emails from it, send an email to codenameone-discu...@googlegroups.com.
Visit this group at http://groups.google.com/group/codenameone-discussions.

To view this discussion on the web visit https://groups.google.com/d/msgid/codenameone-discussions/86284f8f-d248-4314-a8c5-bc87b3da6e48%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
Steve Hannah

Web Lite Solutions Corp.

[Shai Almog]

Hi,
as I said we don't use that.
However, the problem is in your copying code not in your SQL code. If the file is 0 size you didn't write it correctly.

[Cristian Ionitoiu]

Hi Steve,

Thanks and I will definitely have a look at that as well. However, I'm kind of under quite some pressure. We are trying to see if we could use CN1 for a real life application with medium to complex functionality, and I see pattern developing, too often something described either on manual or forum does not seem to work when deployed on a real platform, or there is a trick to be applied that only few people know about. Currently we are able to deploy only on Android, but shortly we will start to deploy on iOS too. Would it be too much to ask to attach to this ticket a sample SQLite db (something simple a table and some columns) that it is supposed to work without problems. I would like to try that on my system and then with my SQLite tools. Thanks,

Cristian

On Wednesday, July 16, 2014 8:44:46 PM UTC+3, shannah wrote:

An alternative you might want to consider.  I developed a data access library that allows you to include an SQL file that will automatically be loaded at first run.

https://github.com/shannah/cn1-data-access-lib

It supports versioning so that you can include incremental SQL updates corresponding to your app updates.

Steve

On Wed, Jul 16, 2014 at 10:41 AM, Cristian Ionitoiu <cioni...@gmail.com> wrote:

Hi Shai,

What software do you recommend for creation of these files? I have used mozilla extension for sqlite to create/export/import file, which also loaded up without any problem by the binary taken from
https://www.sqlite.org/download.html.Thanks,

Cristian

On Wednesday, July 16, 2014 6:09:14 PM UTC+3, Shai Almog wrote:

Hi,
if the file is 0kb you didn't create it correctly.

--
You received this message because you are subscribed to the Google Groups "CodenameOne Discussions" group.

To unsubscribe from this group and stop receiving emails from it, send an email to codenameone-discussions+unsub...@googlegroups.com.

Visit this group at http://groups.google.com/group/codenameone-discussions.

To view this discussion on the web visit https://groups.google.com/d/msgid/codenameone-discussions/86284f8f-d248-4314-a8c5-bc87b3da6e48%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Cristian Ionitoiu]

Hi Shai,

Does it matter where the DB code is placed? I have it placed in the init() method right affter the theme file is read. Thanks,

Cristian

[Cristian Ionitoiu]

Hi Shai,

This is sample file tht does not work for in CN1, but it open without problems by sqllite addon in Mozila and by SQLite Expert. What is wrong with this file? Thanks,

Cristian

On Wednesday, July 16, 2014 6:09:14 PM UTC+3, Shai Almog wrote:

[Shai Almog]

Hi,
if the file is 0 sized the problem isn't in the DB. The problem is that your copy code is incorrect. You wrote quite a bit but didn't actually paste the code you used to copy the db which seems to be the source of your problem.

[Cristian Ionitoiu]

Hi Shai,

As I said the copying code, if that's what you are referring to, is taken from the above example. That's why I reactivated the discussion in the first place. Please find it below, once again:

          if(Storage.getInstance().readObject("initalized") == null){
                myDataBase.close();
           

                String path = com.codename1.db.Database.getDatabasePath("Test.sqlite");           
                OutputStream o = FileSystemStorage.getInstance().openOutputStream(path);
                InputStream i = Display.getInstance().getResourceAsStream(getClass(), "/" + "Test.sqlite");
                Util.copy(i, o);
                Util.cleanup(o);
                Util.cleanup(i);
           
                myDataBase = com.codename1.db.Database.openOrCreate("Test.sqlite");

                Storage.getInstance().writeObject("initalized", "true");
            }

[Shai Almog]

Hi,
and is the file Test.sqlite (case sensitive) in the root of the build/classes directory (in the final jar)?
Is the file created if you stop at this point (before the open and create call)?
If not then there is a problem there.

Assuming there is, what is the path you are getting?

[satan...@gmail.com]

You need to put your database file into theme.res under "Data" section. 

When I put it at src/* it is not recognized and NullPointerException is being thrown.

[Shai Almog]

There is a sample where it is in the src directory in the sql playground demo:
https://www.codenameone.com/blog/sql-demo-revisited.html