sqlite3
 

i am trying to use sqlite3 command line to look at my poi.db file.

when i issue...

select * from poi;

i only get the prompt back. i invoke sqlite3 from the command line as such...

sqlite3 poi.db

from the dir that the poi.db file resides in. a .databases returns the file as the main database and some temp db, so the db is opened and connected/attached to, or so i think.

i have over 50 poi's in the db, and i am getting nothing back form the select statement, though .schema and .tables comes back with info. is my syntax off or does anyone have an idea?

Re: sqlite3
 

When you issue 'select * from poi' is poi your table name? That is what you re asking in your query.

Re: sqlite3
 

Brendan
The 'temp' database will have been created when you issued something like 'create temp table poi' . When you then 'select * from poi;' it will default to the temp table. Issue 'drop table temp.poi;' you will then get what you expect from your select.

Rick

Re: sqlite3
 

sqlite> .databases
seq name file
--- --------------- ----------------------------------------------------------
0 main /home/user/poi.db
1 temp /var/tmp/sqlite_UP0NBVV1OTeVdi5
sqlite> drop table temp.poi;
SQL error: no such table: temp.poi
sqlite> drop table temp.poi
...> ;
SQL error: no such table: temp.poi
sqlite> drop database temp;
SQL error: near "database": syntax error
sqlite> select * from main.poi;
sqlite>

this is what i got when i tried...

Re: sqlite3
 

I still think you are querying the wrong thing.. You cannot " Select from" a database, you must select from a table. Unless you named both the table and the database by the same name (not a good idea IMHO).

In your example, you used 'select * from main.poi;' Main is just a namr/pointer to tjhe db file. So the queruy should be either 'select * from sometable' or 'select * from poi.sometable'

Re: sqlite3
 

Bizshop
The default name for Maemo Mappers db is poi.db. It holds two tables poi and category.
Brendan
I don't understand your query - 'select * from main.poi;'
If you have a temp.db then you have created at least one temporary table. What is the output from .schema

Rick

Re: sqlite3
 

what didn't show up was that i also did a 'select * from poi;' and got no returned record set. i was trying to explicitly specify the database and table in dotted, object-oriented fashion. that, too, didn't work.

sqlite> .schema
CREATE TABLE category (cat_id integer PRIMARY KEY,label text, desc text, enabled integer);
CREATE TABLE poi (poi_id integer PRIMARY KEY, lat real, lon real, label text, desc text, cat_id integer);
sqlite>

Re: sqlite3
 

Brendan
It looks to me that you have lost your poi records. Does a .databases still list the temp database? 'select * from main.poi;' should really have listed any records in poi,dbs poi table in either case and .schema would normally list tables created in the temp db as well as the main db and you only seem to have the one poi table. If 'insert into poi (lat,lon,label,desc,cat_id) values (3.1,5.5,"Trial",Description",1);' and then a 'select * from poi;' returns your record then I think you have a good db with no records.

Rick