Since I’ve been working most of my time with iPhone Dev for the last couple of months, I thought it will be worthwile to post how to import files to SQLite3. In a nutshell, use MesaSQLite’s IMPORT function. It will save you a lot of time. Now it is up to you how to export into a CSV or TAB delimited file. For Oracle, I used SQLDeveloper. For other databases, such as MySQL or Postgres, I’ve used Navicat. Read more…
Note this tutorial will be updated in the near future…
1. The code below (based from the SQLiteBooks Example) will copy the database from your bundle to the “Documents” directory. You don’t have to do this every time your app launches, so there is a check at “success” below. To check this, in the iPhone Simulator navigate to the Documents directory:
Read more…
Tools
1. MesaSQLite for MacOSX.
SQL
1. Getting the current time
MySQL = Now();
SQLite3 = CURRENT_TIMESTAMP;
insert into jobstemp(full_address, datecreated, dateupdated)
VALUES("9 Bishop Street", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
2. Getting the local current time
SELECT datetime(dateupdated, 'localtime')
3. Trim
SELECT trim(name) FROM table
4. Vacuum
rupert:Desktop rupert$ sqlite3 photos-2.0.0.db
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> vacuum;
sqlite> .quit;
5. Quit
rupert:Desktop rupert$ sqlite3 photos-2.0.0.db
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .quit;