How to open a sqlite db file in Ti Desktop?

You must Login before you can answer or comment on any questions.

Hi. I have already read posts like http://developer.appcelerator.com/question/36421/cannot-open-database-file that seemingly answer this problem, but when I try that solution, I do not get a useable DB connection. Do I miss something? Just to make sure that the constraints of the question are understood: platform is desktop. language is javascript and php (I would not mind having a solution for php as well since php is also not working properly with PDO functions)

— asked 2 years ago by Christian Tzurcanu
2 Comments
  • The solution should include the installing part of the db file from resources dir, if needed.

    — commented 2 years ago by Christian Tzurcanu

  • Should I guess that not many people encounter problems with this? No answer and no vote..

    — commented 2 years ago by Christian Tzurcanu

5 Answers

Christian,

Here's a snippet of code that saves to localStorage. Put this in a simple hello world app as the index.html and it will store to a database in localStorage.

<html>
  <body>
    <button onclick="localStorage.setItem('x', '123')">Set 'x' with value '123'</button>
    <button onclick="alert(localStorage['x'])">Get value of 'x'</button>
  </body>
</html>
After you run this, click the button to set the value of x, and you should see a file in your local storage. This is the app data diretory, so on Lion would be in ~/Library/appname
[aland@canoe testDT]$ sqlite3 app_com.appcelerator.testdt_0.localstorage 
SQLite version 3.7.8 2011-09-19 14:49:19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
ItemTable
sqlite> select * from ItemTable;
x|123
sqlite> .quit
[aland@canoe testDT]$ ls
StorageTracker.db
app_com.appcelerator.testdt_0.localstorage
application.properties
cookies.dat
tiapp.log
[aland@canoe testDT]$
Hope that helps! :-)

— answered 2 years ago by Alan DuBoff
answer permalink
1 Comment
  • Well.. Thank you for your time and effort, but it misses the intention of my question. The question is referring to the use of a large sqlite DB by javascript and/or php. On the mobile platform the db needs to be installed and opened. Those operations did not work for me on the desktop. also the PDO sqlite driver does not seem to work. There.

    — commented 2 years ago by Christian Tzurcanu

(by "use" of a medium/large DB, I imply many reads and occasional writes. so the DB file is pre-made and it installs with the app)

— answered 2 years ago by Christian Tzurcanu
answer permalink
7 Comments
  • I am not sure what you are asking. The example I posted does in fact get data from a local sqlite3 database. It sets the data and reads it back.

    What do you want to do?

    — commented 2 years ago by Alan DuBoff

  • @Alan: that is another reason why I appreciate your answer. But the most important part of the question is: how do I install a sqlite DB that I already have. I already knew how to do a creation of db in js. Also I still don't know how to do a connection in php with PDO. Thank you again.

    — commented 2 years ago by Christian Tzurcanu

  • Christian,

    Did you look at Titanium.Database.open() ?

    You only have to copy your database to your Resources folder, and open it, AFAIK.

    Then you should be able to execute queries against it.

    Maybe I'm missing something here...

    — commented 2 years ago by Alan DuBoff

  • Show 4 more comments

Here's my method (this relates to Desktop apps, not Mobile):

//Configure DB Paths
var resPath = Titanium.Filesystem.getResourcesDirectory();
var defaultDBFile = Titanium.Filesystem.getFile(resPath, "myDB.rdb");
var dataPath = Titanium.Filesystem.getApplicationDataDirectory();
var workingDBFile = Titanium.Filesystem.getFile(dataPath, "myDB.rdb");
 
if(workingDBFile.exists()){
    if(defaultDBFile.modificationTimestamp() > workingDBFile.modificationTimestamp()){
    //Default DB has been modified so copy to working 
        defaultDBFeil.copy(workingDBFile);
    }
} else {
    //Working DB doesn't exist so copy default to working
    defaultDBFile.copy(workingDBFile);
}
 
var db = Titanium.Database.openFile(workingDBFile);

So, I put my original DB (defaultDB) into my resources path. When the app runs it checks for the existence of a "workingDB" in the data directory. If it's not there then it copies the default.

If it is there then it compares the modificationTimestamp on the two database files. This is a trick I do during development so if I change the default DB, maybe add a table or a field to a table or something, I don't have to remember to update my working DB. it automatically gets updated. I remove the timestamp check before going live.

I've used this method in several Desktop apps now and it works well for me.

— answered 2 years ago by Mike Casto
answer permalink
10 Comments
  • @Mike: Thank you, but.. Still no progress. I have tried your solution with this outcome:

    [18:16:59:518] [Titanium.Database.DB] [Debug] Execute called with SELECT * FROM jos_sliced_anatomica where name_la like "%h%"  order by random() limit 51
    [18:17:16:157] [Titanium.Database.DB] [Debug] DB Path = /Users/user1/Library/Nomina Anatomica Desk/Databases.db
    [18:17:16:157] [Titanium.Database.DB] [Debug] Creating table Origins
    [18:17:16:158] [Titanium.Database.DB] [Debug] Creating table Databases
    [18:17:16:158] [Titanium.Database.DB] [Debug] Execute called with create table if not exists last_check(time long)
    [18:17:16:158] [Titanium.Database.DB] [Debug] sql returned: 0 rows for result
    of course i've modified your code and instead of myDB.rdb i used db.sqlite. i also see that db.sqlite gets copied at /Users/user1/Library/Nomina Anatomica Desk/ and the above-mentioned SQL command should return 51 rows on that.

    but inside /Users/user1/Library/Nomina Anatomica Desk/app_com.sliced.desk.nominaanatomica_0/ folder i see 0000000000000002.db that has no tables.

    other ideas?

    — commented 2 years ago by Christian Tzurcanu

  • In your data path, which I assume is "/users/user1/library/nomina anatomica desk/" you should have a copy of the db.sqlite file (copied directly from your Resources path).

    The folder "app_com.sliced.des.nominaanatomica_0" is, as far as I can tell, used to store information about the app itself or maybe info it receives from the Titanium servers, not sure. You'd have to ask one of the developers about that.

    If you don't have a copy of "db.sqlite" in your data path then something is preventing it from getting copied. According to documentation the data path should always be writable by the app so, theoretically, it shouldn't be a permissions issue but I'm not sure what else would cause it to fail being copied.

    Do you have a copy of "db.sqlite" in your data path?

    — commented 2 years ago by Mike Casto

  • @Mike: I do. As I already stated: it got copied correctly and it has all data. 51 rows for that query. Thank you.

    — commented 2 years ago by Christian Tzurcanu

  • Show 7 more comments

@Mike: I'm happy to see that we are taking steps towards solving this. The DB that you have sent me works! So the problem is inside the structure of my table. My structure looks like:

CREATE TABLE "jos_sliced_anatomica" (
  "id" int(11) PRIMARY KEY NOT NULL,
  "timestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "sec1" int(11) DEFAULT NULL,
  "sec2" int(11) DEFAULT NULL,
  "parent_id" int(11) DEFAULT NULL,
  "section_1" int(5) DEFAULT NULL,
  "section_2" int(5) DEFAULT NULL,
  "dimension" varchar(5) DEFAULT NULL,
  "complexity" int(11) DEFAULT NULL,
  "ordering" varchar(5) DEFAULT NULL,
  "name_la" varchar(100) DEFAULT NULL,
  "synonyms_la" varchar(200) DEFAULT NULL,
  "tags" varchar(200) DEFAULT NULL,
  "name_en" varchar(100) DEFAULT NULL,
  "synonyms_en" varchar(200) DEFAULT NULL,
  "description_en" text,
  "ref" varchar(50) DEFAULT NULL,
  "name_ro" varchar(100) DEFAULT NULL,
  "synonyms_ro" varchar(200) DEFAULT NULL,
  "description_ro" text,
  "email" varchar(200) DEFAULT NULL,
  "email2" varchar(100) DEFAULT NULL,
  "temp" varchar(200) DEFAULT NULL
)
And I have tried it with only 10 records. The message I get is:

[Titanium.Database.DB] [Debug] Execute called with select * from jos_sliced_anatomica

no error and no usable recordset object. I should mention that I used your table1 in the same file. It worked. Any thoughts?

Now the conclusion: If the table has a timestamp field, a recordset object will not be returned for a SQL SELECT statement. :(

This should be considered a bug in Titanium Desktop. I really need to work with timestamped records..

— answered 2 years ago by Christian Tzurcanu
answer permalink
2 Comments
  • This is just a guess.....so don't flame me or downvote me....but try renaming your timestamp column....maybe there is a keyword issue with your table.

    — commented 2 years ago by Darren Adams

  • Yup. I'm not sure but "timestamp" might be considered a reserved word. However, I have previously encountered some issues with field types as well. I don't remember the specifics but I ended up restricting myself to "text" and "int" fields in the sqlite databases.

    I'm not sure this is a bug in Titanium. I think it has more to do with sqlite but, as I said, I don't remember the specifics.

    Timestamped records don't have to be timestamped by the DB. You can just include a timestamp in your insert/updates. Should it be necessary? Probably not. But it's easy enough to do and it works. You can assign a "text" field then just write your timestamp in whatever format you want (though I'd recommend one of the standard formats just for sanity's sake).

    — commented 2 years ago by Mike Casto

Your Answer

Think you can help? Login to answer this question!