Using WinSQL with SQLite

SQLite is a popular embedded database that is used in many off-the-shelf applications. Once such popular application is Mozilla Firefox, which stores user preferences and browser history in a relational database.

Following white paper demonstrate how to download SQLite's ODBC and using it with WinSQL. For demonstration purposes we will take an existing database that is used by Mozilla Firefox and view its data.

Download and installation

The first step is to download an ODBC driver for SQLite, which is available at http://www.ch-werner.de/sqliteodbc/. Download sqliteodbc.exe file from this website.  IMPORTANT: Download this file even if you are running a 64-bit version of Windows. Since WinSQL is a 32-bit application, it will only work with a 32-bit driver.

After downloading the file, double click it to run the installer. This opens the following screen.



Click Next all the way to accept default values.

Confirming the installation

Aside from installing the necessary files, this will also register 3 ODBC drivers on your machine. The following steps confirm the driver is installed and is correctly registered.

• Start WinSQL on your machine
• Click Open ODBC Manager under the File menu. Refer to the image below.
  
  
  
  
• This opens the ODBC manager on your machine. Click the Drivers tab and ensure you see the 3 drivers for SQLite.
  
  
  
  

Preparing Firefox's database

Since Firefox is a production application, it is recommended you make a backup of the database file and then view its contents. We strongly discourage using the database in its default location.

Skip this step if you are not planning to use Firefox's database for this exercise.

• Using Windows Explorer navigate to the following location on your hard disk
• XP, 2002 & 2003 - C:\Documents and Settings\YourUserName\Application Data\Mozilla\Firefox\Profiles\XXXXX.default
• Vists, 2008 and Windows 7 - C:\Users\YourUserName\AppData\Roaming\\Mozilla\Firefox\Profiles\XXXXX.default
  
  
  Replace XXXXX with the actual values that appear on your machine.
  
  
• You will see a handful of files with extension .sqlite . I recommend you copy places.sqlite to another folder, for example c:\temp\places.sqlite
• Rename places.sqlite to places.db in this temp folder. This is because .db extension is the default extension for SQLite and is a bit easier to work with.
  

Creating an ODBC DSN

The next step is to create an ODBC DSN referring to c:\temp\places.db file.

• Start WinSQL. Click Open ODBC Manager under the File menu to bring up the ODBC manager.
  
• Click the Add button. This brings up the "Create New Data Source" window
• Select SQLite3 ODBC Driver from the list and click Finish
  
  
  
  
• The next screen allows you to specify a name for this DNS and the path for your database.
  
  
  
  
• To create a new database file, specify a file name that does not exist on your hard drive. If the database file is not found, the driver will create a new blank database.

Connecting from WinSQL

Finally, create a new connecting in WinSQL. Select the newly created DNS in the previous step and click OK. SQLite does not require any user id/password. Therefore, you can either leave these values blank or use some arbitrary string.




Once connected you can write SQL queries or view existing catalog.