SQLite Database Query Browser
Within the Connector Application is a tool called the SQLite Database Query Browser that allows data to be queried and retrieved from a version 3 SQLite Database. This query browser can help you find and understand the kinds of data that are stored within a SQLite database, test database queries written in the SQL database language, test connection credentials to a SQLite database, modify data within a SQLite database, as well as copy data from a SQLite database. The SQLite Database Query Browser can be a great tool to help data specialists work with data stored in a SQLite database, as well as help with setting up a Generic adaptor within the Connector.
Topics |
Prerequisites
Please make sure you have read through the Get Started/Overview before continuing down this document.
Additionally make sure you are familiar with the following:
Overview
SQLite databases are relational database management systems that can be embedded in single files, and be easily distributed or transferred. SQLite is one of the most popular databasing technologies, and is heavily used for storing data on mobile devices, such as iOS, Android, and other operating systems.
The SQlite Database Query Browser allows data to be read or written from a SQLite version 3 database file. The query browser users the Structure Query Language (SQL) to be able to query the database, for either reading or writing purposes. Data that is successfully read from from the database can then be shown with the query browser's results table. Data shown within the results table can then analysed, as well as be selected and copied into other applications (such as spreadsheets).
Notes
- SQLite databases support multiple threads reading a database file at the same time, but for writing data to a table in the database it may place locks on the database at the time of writing.
- The query browser uses the System.Data.SQLite driver to read and write data from a SQLite version 3 database file. Visit the driver's website to find out more information about the SQL functions and database operations that are supported.
- Note that the driver, nor the query browser within the Connector supports directly reading columns from an SQLite data table that stores column data as 64bit integers. If this is required then the table column's value needs to be cast as TEXT value, like so:
CAST(LastModifiedDateTime AS TEXT)
Query A SQLite Database
To query a SQLite Database follow these steps:
- Open the Connector application.
- Click on the Tools menu.
- Click on the SQLite Database Query Browser menu item.
- From the.SQLite Database Query Browser Window click on the Locate button.
- Navigate through the file system to find and select the SQLite Database file you wish to query.
- In the SQL text area type the SQL query you wish to run.
- Click on the Run Query button.
A connection will be attempted to be made to the SQLite database, if successfully the SQL query will be executed. If the query returns records then they will be displayed in the results table. If the query fails then in the results text area with the error message that the SQLite database returned.