SQLite database table problem
209 views
Skip to first unread message
sromalewski
Jan 5, 2011, 4:27:08 PM1/5/11
to phonegap
I'm porting a webOS app over to iPhone, using PhoneGap and Jquery/
JQTouch to do so. I've run into a problem, though, with an SQLite
database that is stumping me from the start.
My app displays local, officially designated landmarked buildings in
various cities in the US. In webOS, I'd include a JSON file with the
landmark info (name, date designated, etc), then Ajax that in to an
SQLite database and run queries against the database to display info
in the app. In webOS that works fine, though Ajax'ing it in is an
extra step.
It seems that with iPhone you can preload an SQLite database with the
application and read that directly (I don't need write access, just
read). I've read the thread at http://groups.google.com/group/phonegap/browse_thread/thread/ca6c85f7d6c3976c
and also the various SQLite/iOS tutorials, including Jonathan Stark's
book.
But the database (or my code, more likely) isn't working. I used the
Firefox extension SQLite Manager to import a CSV file with the
landmark info to create the database. It has a single table. On my
Mac, I dragged the file (landmarks.sqlite) into the Resources folder
of my application in Xcode. I used JavaScript modified from Stark's
book at http://ofps.oreilly.com/titles/9780596805784/ch05.html#ch05_id35933084
to open the database and then run queries against it. But nothing is
displayed from the resultset. I also modified the "...AppDelegate.h"
and "...AppDelegate.m" files per the thread that I referenced above,
but still nothing is displayed.
When I open the application locally in Safari, I see that the database
is listed (in the Storage section of Web Inspector). But no tables
are listed. So it seems that somehow the database container is being
referenced, but no tables and therefore no data have been included
(even though all 1,580 rows of data are displayed in SQLite Manager
before copying the database to my application).
I'd be glad to post my code, but I thought I'd see if anyone on the
list had any insights/suggestions/ideas about basic things I may be
missing. It seems like it should be straightforward, but alas
something isn't going right.
Thanks very much!
Steve
JQTouch to do so. I've run into a problem, though, with an SQLite
database that is stumping me from the start.
My app displays local, officially designated landmarked buildings in
various cities in the US. In webOS, I'd include a JSON file with the
landmark info (name, date designated, etc), then Ajax that in to an
SQLite database and run queries against the database to display info
in the app. In webOS that works fine, though Ajax'ing it in is an
extra step.
It seems that with iPhone you can preload an SQLite database with the
application and read that directly (I don't need write access, just
read). I've read the thread at http://groups.google.com/group/phonegap/browse_thread/thread/ca6c85f7d6c3976c
and also the various SQLite/iOS tutorials, including Jonathan Stark's
book.
But the database (or my code, more likely) isn't working. I used the
Firefox extension SQLite Manager to import a CSV file with the
landmark info to create the database. It has a single table. On my
Mac, I dragged the file (landmarks.sqlite) into the Resources folder
of my application in Xcode. I used JavaScript modified from Stark's
book at http://ofps.oreilly.com/titles/9780596805784/ch05.html#ch05_id35933084
to open the database and then run queries against it. But nothing is
displayed from the resultset. I also modified the "...AppDelegate.h"
and "...AppDelegate.m" files per the thread that I referenced above,
but still nothing is displayed.
When I open the application locally in Safari, I see that the database
is listed (in the Storage section of Web Inspector). But no tables
are listed. So it seems that somehow the database container is being
referenced, but no tables and therefore no data have been included
(even though all 1,580 rows of data are displayed in SQLite Manager
before copying the database to my application).
I'd be glad to post my code, but I thought I'd see if anyone on the
list had any insights/suggestions/ideas about basic things I may be
missing. It seems like it should be straightforward, but alas
something isn't going right.
Thanks very much!
Steve
Samuel M
Jan 6, 2011, 10:19:28 AM1/6/11
to phon...@googlegroups.com
Hi,
I also use a SQLite DB in my iPhone apps, but I dont preload the DB, I use the same technics as yours in WebOS :
at first launch, I load the DB from JSON, and it's fast enough (i have more than 4000 rows in 5 tables).
This method has also the advantage to update the DB with new content with the same code.
Hope that help.
Regards,
Samuel
Giacomo Balli
Jan 6, 2011, 10:47:41 AM1/6/11
to phonegap
seeing some relevant code always helps...
Nick McCloud
Jan 6, 2011, 1:42:23 PM1/6/11
to phonegap
You can't just copy over any sqlite db of your choice. You are best
off creating the database using HTML5/JS on your machine, which you
can then populate via SQLite Manager. You'll need to leave the name
alone (yes, all those 00000's!) and you will have to copy over the
database.db as well. The HTML5 database access is firewalled - you
can't just directly open your own database. Re-read the first posting
you refer to and you'll see the details of what I'm talking about.
On Jan 5, 9:27 pm, sromalewski <spatialitya...@gmail.com> wrote:
> I'm porting a webOS app over to iPhone, using PhoneGap and Jquery/
> JQTouch to do so. I've run into a problem, though, with an SQLite
> database that is stumping me from the start.
>
> My app displays local, officially designated landmarked buildings in
> various cities in the US. In webOS, I'd include a JSON file with the
> landmark info (name, date designated, etc), then Ajax that in to an
> SQLite database and run queries against the database to display info
> in the app. In webOS that works fine, though Ajax'ing it in is an
> extra step.
>
> It seems that with iPhone you can preload an SQLite database with the
> application and read that directly (I don't need write access, just
> read). I've read the thread athttp://groups.google.com/group/phonegap/browse_thread/thread/ca6c85f7...
off creating the database using HTML5/JS on your machine, which you
can then populate via SQLite Manager. You'll need to leave the name
alone (yes, all those 00000's!) and you will have to copy over the
database.db as well. The HTML5 database access is firewalled - you
can't just directly open your own database. Re-read the first posting
you refer to and you'll see the details of what I'm talking about.
On Jan 5, 9:27 pm, sromalewski <spatialitya...@gmail.com> wrote:
> I'm porting a webOS app over to iPhone, using PhoneGap and Jquery/
> JQTouch to do so. I've run into a problem, though, with an SQLite
> database that is stumping me from the start.
>
> My app displays local, officially designated landmarked buildings in
> various cities in the US. In webOS, I'd include a JSON file with the
> landmark info (name, date designated, etc), then Ajax that in to an
> SQLite database and run queries against the database to display info
> in the app. In webOS that works fine, though Ajax'ing it in is an
> extra step.
>
> It seems that with iPhone you can preload an SQLite database with the
> application and read that directly (I don't need write access, just
> and also the various SQLite/iOS tutorials, including Jonathan Stark's
> book.
>
> But the database (or my code, more likely) isn't working. I used the
> Firefox extension SQLite Manager to import a CSV file with the
> landmark info to create the database. It has a single table. On my
> Mac, I dragged the file (landmarks.sqlite) into the Resources folder
> of my application in Xcode. I used JavaScript modified from Stark's
> book athttp://ofps.oreilly.com/titles/9780596805784/ch05.html#ch05_id35933084
> book.
>
> But the database (or my code, more likely) isn't working. I used the
> Firefox extension SQLite Manager to import a CSV file with the
> landmark info to create the database. It has a single table. On my
> Mac, I dragged the file (landmarks.sqlite) into the Resources folder
> of my application in Xcode. I used JavaScript modified from Stark's
sromalewski
Jan 6, 2011, 2:39:44 PM1/6/11
to phonegap
Thanks for your responses.
My expectation that you could preload an SQLite database came from
reading Apple's iOS documentation (http://developer.apple.com/library/
ios/#codinghowtos/DataManagement/#FILE_MANAGEMENT-
BUNDLE_AN_SQLITE_DATABASE_WITH_MY_APPLICATION), as well as other
tutorials/blog posts (such as http://answers.oreilly.com/topic/1633-how-to-store-iphone-app-data/
, http://icodeblog.com/2008/08/19/iphone-programming-tutorial-creating-a-todo-list-using-sqlite-part-1/
and http://leefalin.com/blog/2008/10/02/iphone-sqlite-database-basics/).
But perhaps the issue is that the links I reference above pertain to
native apps, rather than web apps (or, Phonegap-implemented native
apps)? I'm new to the iPhone and Phonegap worlds so perhaps my
terminology isn't spot on, my apologies.
Either way, I think I'll rely on the approach that Samuel outlined
(based on what I've done already with webOS).
Thanks again!
My expectation that you could preload an SQLite database came from
reading Apple's iOS documentation (http://developer.apple.com/library/
ios/#codinghowtos/DataManagement/#FILE_MANAGEMENT-
BUNDLE_AN_SQLITE_DATABASE_WITH_MY_APPLICATION), as well as other
tutorials/blog posts (such as http://answers.oreilly.com/topic/1633-how-to-store-iphone-app-data/
, http://icodeblog.com/2008/08/19/iphone-programming-tutorial-creating-a-todo-list-using-sqlite-part-1/
and http://leefalin.com/blog/2008/10/02/iphone-sqlite-database-basics/).
But perhaps the issue is that the links I reference above pertain to
native apps, rather than web apps (or, Phonegap-implemented native
apps)? I'm new to the iPhone and Phonegap worlds so perhaps my
terminology isn't spot on, my apologies.
Either way, I think I'll rely on the approach that Samuel outlined
(based on what I've done already with webOS).
Thanks again!
Nick McCloud
Jan 6, 2011, 4:54:55 PM1/6/11
to phonegap
You can pre-populate the database - you just have to give it the names
that iOS needs to access it via HTML5 as per the posting you
referenced. It is as simple as not just using the names you want to
use.
On Jan 6, 7:39 pm, sromalewski <spatialitya...@gmail.com> wrote:
> Thanks for your responses.
>
> My expectation that you could preload an SQLite database came from
> reading Apple's iOS documentation (http://developer.apple.com/library/
> ios/#codinghowtos/DataManagement/#FILE_MANAGEMENT-
> BUNDLE_AN_SQLITE_DATABASE_WITH_MY_APPLICATION), as well as other
> tutorials/blog posts (such ashttp://answers.oreilly.com/topic/1633-how-to-store-iphone-app-data/
> ,http://icodeblog.com/2008/08/19/iphone-programming-tutorial-creating-...
> andhttp://leefalin.com/blog/2008/10/02/iphone-sqlite-database-basics/).
that iOS needs to access it via HTML5 as per the posting you
referenced. It is as simple as not just using the names you want to
use.
On Jan 6, 7:39 pm, sromalewski <spatialitya...@gmail.com> wrote:
> Thanks for your responses.
>
> My expectation that you could preload an SQLite database came from
> reading Apple's iOS documentation (http://developer.apple.com/library/
> ios/#codinghowtos/DataManagement/#FILE_MANAGEMENT-
> BUNDLE_AN_SQLITE_DATABASE_WITH_MY_APPLICATION), as well as other
> tutorials/blog posts (such ashttp://answers.oreilly.com/topic/1633-how-to-store-iphone-app-data/
> andhttp://leefalin.com/blog/2008/10/02/iphone-sqlite-database-basics/).
Giacomo Balli
Jan 7, 2011, 5:37:57 AM1/7/11
to phonegap
or you just copy and paste ObjC code to access/edit the db and make it
a plugin.
a plugin.
ben ben
Jan 11, 2011, 12:30:54 AM1/11/11
to phonegap
i get the same issue as sromalewski,i use web database (html5 special)
to store my data , and success in one page , when i access the
database in another page using "window.openDatabase" "select * from
userInfo" , LogCat feedback to me the error "sqlite returned:error
code = 1,msg = no such table userInfo", so anyone can give me some
suggestion ? thanx
to store my data , and success in one page , when i access the
database in another page using "window.openDatabase" "select * from
userInfo" , LogCat feedback to me the error "sqlite returned:error
code = 1,msg = no such table userInfo", so anyone can give me some
suggestion ? thanx
Ricardo Andrade
Jan 13, 2011, 6:58:07 PM1/13/11
to phonegap
Hi There, I did the same example http://docs.phonegap.com/phonegap_storage_storage.md.html,
and I get erro "no such table: DEMO", Anyone has suggestion ? :-(
Best regards,
Randrade
On 11 ene, 03:30, ben ben <wangdybyt.hea...@gmail.com> wrote:
> i get the same issue as sromalewski,i use web database (html5 special)
> to store my data , and success in one page , when i access the
> database in another page using "window.openDatabase" "select * from
> userInfo" , LogCat feedback to me the error "sqlite returned:error
> code = 1,msg = nosuchtableuserInfo", so anyone can give me some
> > > > > > landmark info to create the database. It has a singletable. On my
and I get erro "no such table: DEMO", Anyone has suggestion ? :-(
Best regards,
Randrade
On 11 ene, 03:30, ben ben <wangdybyt.hea...@gmail.com> wrote:
> i get the same issue as sromalewski,i use web database (html5 special)
> to store my data , and success in one page , when i access the
> database in another page using "window.openDatabase" "select * from
> userInfo" , LogCat feedback to me the error "sqlite returned:error
> suggestion ? thanx
>
> On Jan 7, 6:37 pm, Giacomo Balli <giacomoba...@gmail.com> wrote:
>
>
>
> > or you just copy and paste ObjC code to access/edit the db and make it
> > a plugin.
>
> > On Jan 6, 10:54 pm, Nick McCloud <n...@descartes.co.uk> wrote:
>
> > > You can pre-populate the database - you just have to give it the names
> > > that iOS needs to access it via HTML5 as per the posting you
> > > referenced. It is as simple as not just using the names you want to
> > > use.
>
> > > On Jan 6, 7:39 pm, sromalewski <spatialitya...@gmail.com> wrote:
>
> > > > Thanks for your responses.
>
> > > > My expectation that you could preload an SQLite database came from
> > > > reading Apple's iOS documentation (http://developer.apple.com/library/
> > > > ios/#codinghowtos/DataManagement/#FILE_MANAGEMENT-
> > > > BUNDLE_AN_SQLITE_DATABASE_WITH_MY_APPLICATION), as well as other
> > > > tutorials/blog posts (suchashttp://answers.oreilly.com/topic/1633-how-to-store-iphone-app-data/
>
> On Jan 7, 6:37 pm, Giacomo Balli <giacomoba...@gmail.com> wrote:
>
>
>
> > or you just copy and paste ObjC code to access/edit the db and make it
> > a plugin.
>
> > On Jan 6, 10:54 pm, Nick McCloud <n...@descartes.co.uk> wrote:
>
> > > You can pre-populate the database - you just have to give it the names
> > > that iOS needs to access it via HTML5 as per the posting you
> > > referenced. It is as simple as not just using the names you want to
> > > use.
>
> > > On Jan 6, 7:39 pm, sromalewski <spatialitya...@gmail.com> wrote:
>
> > > > Thanks for your responses.
>
> > > > My expectation that you could preload an SQLite database came from
> > > > reading Apple's iOS documentation (http://developer.apple.com/library/
> > > > ios/#codinghowtos/DataManagement/#FILE_MANAGEMENT-
> > > > BUNDLE_AN_SQLITE_DATABASE_WITH_MY_APPLICATION), as well as other
sromalewski
Feb 2, 2011, 4:30:55 PM2/2/11
to phonegap
I ended up relying on the approach I used in webOS (and that Samuel M
reiterated above) -- loading a JSON file into an SQLite database, then
using SQLite query syntax to extract data when I need it. I haven't
tested it yet on an iPhone, but it works fine in the iOS simulator.
At one point in my testing I was able to get the alternative approach
to work -- creating an SQLite database outside the iPhone environment,
then renaming it (as Nick suggests above) and copying it into Xcode.
But I couldn't retrace my steps successfully a second time, so I went
back to the JSON approach. Works for now, and I'm comfortable with
it, so that's what I'll do going forward.
On Jan 13, 6:58 pm, Ricardo Andrade <gvainformat...@gmail.com> wrote:
> Hi There, I did the same examplehttp://docs.phonegap.com/phonegap_storage_storage.md.html,
reiterated above) -- loading a JSON file into an SQLite database, then
using SQLite query syntax to extract data when I need it. I haven't
tested it yet on an iPhone, but it works fine in the iOS simulator.
At one point in my testing I was able to get the alternative approach
to work -- creating an SQLite database outside the iPhone environment,
then renaming it (as Nick suggests above) and copying it into Xcode.
But I couldn't retrace my steps successfully a second time, so I went
back to the JSON approach. Works for now, and I'm comfortable with
it, so that's what I'll do going forward.
On Jan 13, 6:58 pm, Ricardo Andrade <gvainformat...@gmail.com> wrote:
> Hi There, I did the same examplehttp://docs.phonegap.com/phonegap_storage_storage.md.html,
> and I get erro "no such table: DEMO", Anyone has suggestion ? :-(
>
> Best regards,
>
> Randrade
>
> On 11 ene, 03:30, ben ben <wangdybyt.hea...@gmail.com> wrote:
>
>
>
>
>
>
>
> > i get the same issue as sromalewski,i use web database (html5 special)
> > to store my data , and success in one page , when i access the
> > database in another page using "window.openDatabase" "select * from
> > userInfo" , LogCat feedback to me the error "sqlite returned:error
> > code = 1,msg = nosuchtableuserInfo", so anyone can give me some
> > suggestion ? thanx
>
> > On Jan 7, 6:37 pm, Giacomo Balli <giacomoba...@gmail.com> wrote:
>
> > > or you just copy and paste ObjC code to access/edit thedband make it
>
> Best regards,
>
> Randrade
>
> On 11 ene, 03:30, ben ben <wangdybyt.hea...@gmail.com> wrote:
>
>
>
>
>
>
>
> > i get the same issue as sromalewski,i use web database (html5 special)
> > to store my data , and success in one page , when i access the
> > database in another page using "window.openDatabase" "select * from
> > userInfo" , LogCat feedback to me the error "sqlite returned:error
> > code = 1,msg = nosuchtableuserInfo", so anyone can give me some
> > suggestion ? thanx
>
> > On Jan 7, 6:37 pm, Giacomo Balli <giacomoba...@gmail.com> wrote:
>
> > > a plugin.
>
> > > On Jan 6, 10:54 pm, Nick McCloud <n...@descartes.co.uk> wrote:
>
> > > > You can pre-populate the database - you just have to give it the names
> > > > that iOS needs to access it via HTML5 as per the posting you
> > > > referenced. It is as simple as not just using the names you want to
> > > > use.
>
> > > > On Jan 6, 7:39 pm, sromalewski <spatialitya...@gmail.com> wrote:
>
> > > > > Thanks for your responses.
>
> > > > > My expectation that you couldpreloadan SQLite database came from
>
> > > On Jan 6, 10:54 pm, Nick McCloud <n...@descartes.co.uk> wrote:
>
> > > > You can pre-populate the database - you just have to give it the names
> > > > that iOS needs to access it via HTML5 as per the posting you
> > > > referenced. It is as simple as not just using the names you want to
> > > > use.
>
> > > > On Jan 6, 7:39 pm, sromalewski <spatialitya...@gmail.com> wrote:
>
> > > > > Thanks for your responses.
>
> > > > > reading Apple's iOS documentation (http://developer.apple.com/library/
> > > > > ios/#codinghowtos/DataManagement/#FILE_MANAGEMENT-
> > > > > BUNDLE_AN_SQLITE_DATABASE_WITH_MY_APPLICATION), as well as other
> > > > > tutorials/blog posts (suchashttp://answers.oreilly.com/topic/1633-how-to-store-iphone-app-data/
> > > > > ,http://icodeblog.com/2008/08/19/iphone-programming-tutorial-creating-...
> > > > > andhttp://leefalin.com/blog/2008/10/02/iphone-sqlite-database-basics/).
>
> > > > > But perhaps the issue is that the links I reference above pertain to
> > > > > native apps, rather than web apps (or, Phonegap-implemented native
> > > > > apps)? I'm new to the iPhone and Phonegap worlds so perhaps my
> > > > > terminology isn't spot on, my apologies.
>
> > > > > Either way, I think I'll rely on the approach that Samuel outlined
> > > > > (based on what I've done already with webOS).
>
> > > > > Thanks again!
>
> > > > > On Jan 6, 1:42 pm, Nick McCloud <n...@descartes.co.uk> wrote:
>
> > > > > > You can't just copy over any sqlitedbof your choice. You are best
> > > > > ios/#codinghowtos/DataManagement/#FILE_MANAGEMENT-
> > > > > BUNDLE_AN_SQLITE_DATABASE_WITH_MY_APPLICATION), as well as other
> > > > > tutorials/blog posts (suchashttp://answers.oreilly.com/topic/1633-how-to-store-iphone-app-data/
> > > > > ,http://icodeblog.com/2008/08/19/iphone-programming-tutorial-creating-...
> > > > > andhttp://leefalin.com/blog/2008/10/02/iphone-sqlite-database-basics/).
>
> > > > > But perhaps the issue is that the links I reference above pertain to
> > > > > native apps, rather than web apps (or, Phonegap-implemented native
> > > > > apps)? I'm new to the iPhone and Phonegap worlds so perhaps my
> > > > > terminology isn't spot on, my apologies.
>
> > > > > Either way, I think I'll rely on the approach that Samuel outlined
> > > > > (based on what I've done already with webOS).
>
> > > > > Thanks again!
>
> > > > > On Jan 6, 1:42 pm, Nick McCloud <n...@descartes.co.uk> wrote:
>
> > > > > > off creating the database using HTML5/JS on your machine, which you
> > > > > > can then populate via SQLite Manager. You'll need to leave the name
> > > > > > alone (yes, all those 00000's!) and you will have to copy over the
> > > > > > database.dbas well. The HTML5 database access is firewalled - you
> > > > > > can then populate via SQLite Manager. You'll need to leave the name
> > > > > > alone (yes, all those 00000's!) and you will have to copy over the
> > > > > > can't just directly open your own database. Re-read the first posting
> > > > > > you refer to and you'll see the details of what I'm talking about.
>
> > > > > > On Jan 5, 9:27 pm, sromalewski <spatialitya...@gmail.com> wrote:
>
> > > > > > > I'm porting a webOS app over to iPhone, using PhoneGap and Jquery/
> > > > > > > JQTouch to do so. I've run into a problem, though, with an SQLite
> > > > > > > database that is stumping me from the start.
>
> > > > > > > My app displays local, officially designated landmarked buildings in
> > > > > > > various cities in the US. In webOS, I'd include a JSON file with the
> > > > > > > landmark info (name, date designated, etc), then Ajax that in to an
> > > > > > > SQLite database and run queries against the database to display info
> > > > > > > in the app. In webOS that works fine, though Ajax'ing it in is an
> > > > > > > extra step.
>
> > > > > > > It seems that with iPhone you canpreloadan SQLite database with the
> > > > > > you refer to and you'll see the details of what I'm talking about.
>
> > > > > > On Jan 5, 9:27 pm, sromalewski <spatialitya...@gmail.com> wrote:
>
> > > > > > > I'm porting a webOS app over to iPhone, using PhoneGap and Jquery/
> > > > > > > JQTouch to do so. I've run into a problem, though, with an SQLite
> > > > > > > database that is stumping me from the start.
>
> > > > > > > My app displays local, officially designated landmarked buildings in
> > > > > > > various cities in the US. In webOS, I'd include a JSON file with the
> > > > > > > landmark info (name, date designated, etc), then Ajax that in to an
> > > > > > > SQLite database and run queries against the database to display info
> > > > > > > in the app. In webOS that works fine, though Ajax'ing it in is an
> > > > > > > extra step.
>
Reply all
Reply to author
Forward
0 new messages